date:20030324

Jonathan,

What is the 'Haken' factor?

Jared






"Jonathan Lewis" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 02:24 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: Reorganizing tables



This may have been mentioned already, but a
table re-org can be a positive threat to performance.

If your primary tables have (for example) a three-stage
life:
initial row size is small
row grows after a couple of weeks
row reaches full size after another 4 weeks,
then what value are you going to use for your
PCTFREE when you rebuild ?

If you set it to zero to avoid wasting space
for the vast percentage of rows that are full
size then many incomplete rows will end up
migrating.

If you set it to match the growth requirement
of the newest rows, then you leave lots of
empty space in the blocks that hold only old
rows.

(The optimum storage answer is to set the
Hakan factor before moving the table - but
that's not a trivial exercise because of a bug
whose number I can't remember).


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd
Denmark May 21-23rd
USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 24 March 2003 20:53


> Thanks Jared, Rachel, Tom, Dick, Prakash, Ron
>
> Excellent points. Very much appreciated. Unfortunately at this point
people
> are asking "but have you tested it?". So I need to construct some
type of
> test that will demonstrate how much effect a reorg will have. After
I've
> answered that question, then I can move on to some of the other
issues that
> you mention. I have joked that if the results are strongly positive,
they
> won't see me much after that because I'll be touring the world
selling my
> performance solution that never occurred to anyone else.
>Our test system is cloned from an RMAN backup of production so
the tables
> should be close to production. I'm thinking of creating a new table
and
> copying the contents of a production table into it and then tracing
> full-table scans and comparing the results.
>
> Jared - is there a way to estimate block-level fragmentation?
Comparing the
> average row length with the number of blocks used?
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: win2k system shutdown scripts--suitable for db shutdown?

Roy,

You don't need scripts to shutdown Oracle on Win2k.

See Metalink doc # 136214.1 

Jared





"Pardee, Roy E" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 02:59 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:win2k system shutdown scripts--suitable for db shutdown?


Greetings all,

I'm reading that win2k supports shutdown scripts (w/the group policy mmc
snap-in).  Is anyone using these to shutdown their win2k-hosted oracle
databases?  Right now our netadmins are running shutdown scripts as a 
manual
step, but if it's susceptible of scripting, we'd like to do it that way
instead.  Are there any gotchas?

Thanks!

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

win2k system shutdown scripts--suitable for db shutdown?

2003-03-24 Thread Pardee, Roy E

Greetings all,

I'm reading that win2k supports shutdown scripts (w/the group policy mmc
snap-in).  Is anyone using these to shutdown their win2k-hosted oracle
databases?  Right now our netadmins are running shutdown scripts as a manual
step, but if it's susceptible of scripting, we'd like to do it that way
instead.  Are there any gotchas?

Thanks!

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:RE: Reorganizing tables

I've been told that there is no such thing as an 'unbalanced' B*+ index.

The key values may be skewed due to being generated sequentially,
but that's a data problem, not an unbalanced index.

Rebuilding indexes can really kill performance at times since new 
index entries force lots of index block splits.

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 03/24/2003 01:29 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re:RE: Reorganizing tables


Dennis,

I don't reorg tables for performance reasons.  That strategy has been 
proved
faulty more than once.  Actually there is data out on the web that I've 
come
across that points in the opposite direction.  Rebuilding indexes many 
times
improves performance by making the index more effective.  A badly 
unbalanced
index is nobodies friend.  Now if your reorging for space management 
issues
that's a whole new ballgame.  Having multiple objects in a tablespace with
different sized extents does make filling the datafile messy to say the 
least,
therefore an occasional raking of the sandbox for this reason is 
reasonable. 
Just don't expect a performance gain.

Dick Goulet

Reply Separator
Author: DENNIS WILLIAMS <[EMAIL PROTECTED]>
Date:   3/24/2003 12:53 PM

Thanks Jared, Rachel, Tom, Dick, Prakash, Ron

Excellent points. Very much appreciated. Unfortunately at this point 
people
are asking "but have you tested it?". So I need to construct some type of
test that will demonstrate how much effect a reorg will have. After I've
answered that question, then I can move on to some of the other issues 
that
you mention. I have joked that if the results are strongly positive, they
won't see me much after that because I'll be touring the world selling my
performance solution that never occurred to anyone else. 
   Our test system is cloned from an RMAN backup of production so the 
tables
should be close to production. I'm thinking of creating a new table and
copying the contents of a production table into it and then tracing
full-table scans and comparing the results. 
 
Jared - is there a way to estimate block-level fragmentation? Comparing 
the
average row length with the number of blocks used?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 2:30 PM
To: [EMAIL PROTECTED]
Cc: DENNIS WILLIAMS
Importance: High


Dennis,


Table reorgs can be useful when there is block level fragmentation, and 
you
do a lot of FTS.  eg. you have a table with 1000 blocks and all the data 
will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally 

always
sorted on the same keys.  You can load them in sorted order, though at the 

moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any 
problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted
to do a whole tablespace at a time.  I talked her out of it, or at least, 
she sensed
that I wasn't very cooperative on that subject.  :)

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:38 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
cc: 
Subject:Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue 
to
add reports and the reports do quite a few full table scans. The reports 
are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Plea

RE: Reorganizing tables

Dennis,

Like you said, you could analyze the table and just check avg row length.

If it's not reasonable for the number of blocks used then you are probably
incurring extra IO in FTS.

I like to use DBMS_SPACE for this as well.  Just remember that 
EMPTY_BLOCKS
in DBA_TABLES and UNUSED_BLOCKS in DBMS_SPACE are not the 
same thing.  Empty blocks are emtpy and included in FTS, unused blocks
have never been used.

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 12:53 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: Reorganizing tables


Thanks Jared, Rachel, Tom, Dick, Prakash, Ron

Excellent points. Very much appreciated. Unfortunately at this point 
people
are asking "but have you tested it?". So I need to construct some type of
test that will demonstrate how much effect a reorg will have. After I've
answered that question, then I can move on to some of the other issues 
that
you mention. I have joked that if the results are strongly positive, they
won't see me much after that because I'll be touring the world selling my
performance solution that never occurred to anyone else. 
   Our test system is cloned from an RMAN backup of production so the 
tables
should be close to production. I'm thinking of creating a new table and
copying the contents of a production table into it and then tracing
full-table scans and comparing the results. 
 
Jared - is there a way to estimate block-level fragmentation? Comparing 
the
average row length with the number of blocks used?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 2:30 PM
To: [EMAIL PROTECTED]
Cc: DENNIS WILLIAMS
Importance: High


Dennis,

Table reorgs can be useful when there is block level fragmentation, and 
you
do a lot of FTS.  eg. you have a table with 1000 blocks and all the data 
will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally 

always
sorted on the same keys.  You can load them in sorted order, though at the 

moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any 
problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted
to do a whole tablespace at a time.  I talked her out of it, or at least, 
she sensed
that I wasn't very cooperative on that subject.  :)

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:38 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
cc: 
Subject:Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue 
to
add reports and the reports do quite a few full table scans. The reports 
are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http

Re: Reorganizing tables

2003-03-24 Thread Jonathan Lewis


This may have been mentioned already, but a
table re-org can be a positive threat to performance.

If your primary tables have (for example) a three-stage
life:
initial row size is small
row grows after a couple of weeks
row reaches full size after another 4 weeks,
then what value are you going to use for your
PCTFREE when you rebuild ?

If you set it to zero to avoid wasting space
for the vast percentage of rows that are full
size then many incomplete rows will end up
migrating.

If you set it to match the growth requirement
of the newest rows, then you leave lots of
empty space in the blocks that hold only old
rows.

(The optimum storage answer is to set the
Hakan factor before moving the table - but
that's not a trivial exercise because of a bug
whose number I can't remember).


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd
Denmark May 21-23rd
USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )
UK_(Manchester)_May
Estonia___June (provisional)
USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 24 March 2003 20:53


> Thanks Jared, Rachel, Tom, Dick, Prakash, Ron
>
> Excellent points. Very much appreciated. Unfortunately at this point
people
> are asking "but have you tested it?". So I need to construct some
type of
> test that will demonstrate how much effect a reorg will have. After
I've
> answered that question, then I can move on to some of the other
issues that
> you mention. I have joked that if the results are strongly positive,
they
> won't see me much after that because I'll be touring the world
selling my
> performance solution that never occurred to anyone else.
>Our test system is cloned from an RMAN backup of production so
the tables
> should be close to production. I'm thinking of creating a new table
and
> copying the contents of a production table into it and then tracing
> full-table scans and comparing the results.
>
> Jared - is there a way to estimate block-level fragmentation?
Comparing the
> average row length with the number of blocks used?
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

trace files

2003-03-24 Thread Ehresmann, David

List, I have a 9iRel2 instance on AIX5.1 64-bit.  Everything is fine but I
consisently  get these 2 trace files in pairs in bdump:

1st one:
/bdump/cosint7x_ora_671922.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = product/64/9.2.0
System name:AIX
Node name:  x
Release:1
Version:5
Machine:
Instance name: cosint7x
Redo thread mounted by this instance: 0 
Oracle process number: 0
671922

*** 2003-03-24 13:19:25.956
*** SESSION ID:(8.1) 2003-03-24 13:19:25.949
OPIRIP: Uncaught error 1089. Error stack:
ORA-01089: immediate shutdown in progress - no operations are permitted


2nd one:
/bdump/cosint7x_lgwr_1916984.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /product/64/9.2.0
System name:AIX
Node name:  xxx
Release:1
Version:5
Machine:
Instance name: cosint7x
Redo thread mounted by this instance: 1
Oracle process number: 4
Unix process pid: 1916984, image: [EMAIL PROTECTED] (LGWR)

*** SESSION ID:(3.1) 2003-03-24 13:19:29.399
Archiving is disabled

I am not achiving this instance,  I did a sql>archive log list  and it is
disabled.  LOG_ARCHVE_DEST_1-10 are not set, but LOG_ARCHIVE_DEST_STATE_1-10
are enabled when I do a sql>show parameter log.  This must be a default
setting?  Can anybody explain these two trace files?

thanks,

David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:RE: Reorganizing tables

Dennis,

I don't reorg tables for performance reasons.  That strategy has been proved
faulty more than once.  Actually there is data out on the web that I've come
across that points in the opposite direction.  Rebuilding indexes many times
improves performance by making the index more effective.  A badly unbalanced
index is nobodies friend.  Now if your reorging for space management issues
that's a whole new ballgame.  Having multiple objects in a tablespace with
different sized extents does make filling the datafile messy to say the least,
therefore an occasional raking of the sandbox for this reason is reasonable. 
Just don't expect a performance gain.

Dick Goulet

Reply Separator
Author: DENNIS WILLIAMS <[EMAIL PROTECTED]>
Date:   3/24/2003 12:53 PM

Thanks Jared, Rachel, Tom, Dick, Prakash, Ron

Excellent points. Very much appreciated. Unfortunately at this point people
are asking "but have you tested it?". So I need to construct some type of
test that will demonstrate how much effect a reorg will have. After I've
answered that question, then I can move on to some of the other issues that
you mention. I have joked that if the results are strongly positive, they
won't see me much after that because I'll be touring the world selling my
performance solution that never occurred to anyone else. 
   Our test system is cloned from an RMAN backup of production so the tables
should be close to production. I'm thinking of creating a new table and
copying the contents of a production table into it and then tracing
full-table scans and comparing the results. 
 
Jared - is there a way to estimate block-level fragmentation? Comparing the
average row length with the number of blocks used?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 2:30 PM
To: [EMAIL PROTECTED]
Cc: DENNIS WILLIAMS
Importance: High


Dennis,


Table reorgs can be useful when there is block level fragmentation, and 
you
do a lot of FTS.  eg. you have a table with 1000 blocks and all the data 
will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally 
always
sorted on the same keys.  You can load them in sorted order, though at the 
moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any 
problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted
to do a whole tablespace at a time.  I talked her out of it, or at least, 
she sensed
that I wasn't very cooperative on that subject.  :)

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:38 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue 
to
add reports and the reports do quite a few full table scans. The reports 
are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).

Re: Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?

2003-03-24 Thread Stephane Faroult

> Nick Wagner wrote:
> 
> Can I take a hot backup of an 8.1.7 instance...  and then upgrade the
> backup to 9.2.0 (upgrading data dictionary tables and everything) and
> then apply logs created by the 8.1.7 instance to this 9.2.0 backup?
> 
> Please answer as soon as possible...
> 
> Thanks!
> Nick Wagner
> 
> 

With all the new stuff related to redo logs (logical standby, etc.)
brought by 9.x, I wouldn't try it.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database instan

2003-03-24 Thread Adams, Matthew (GECP, MABG, 088130)





I did 
this a couple of weeks ago.  The 
answer is yes, but you recover
first, then 
upgrade.
 
You've got to 

 
0) 
Note the sequence number of the log file being written
to in 
the source database before you start.
1) put 
source in hot backup mode
2) 
copy files to new destination
3) 
take source out of backup mode
4) 
NOTE the date/time
5) 
'alter system switch logfile' on the source
6) 
copy all archived log files from the one
   noted in the step 0 to the most recent 
(inclusive) to destination
7) 
(optional) on source, do a 'alter database backup controlfile to 
trace'
8) 
(optional) copy the trace file to destination
9)  (optional) using the 9.2.0 executables, use 
trace file to re-create control file, renaming database 
10) on 
destination do 'alter database recover automatic until time 'TIME NOTED IN STEP 
4'  using backup controlfile'
 The 9.2.0 executables can read 
and understand log files from 8.1.7
11) on 
destination do 'alter database open resetlogs'
12)  On destination, perform steps for manual 
upgrade from 8.1.7 to 9.2.0
13) 
Celebrate with a couple of truely great beers (i recommend Sierra Nevada 
Celebration Ale)
 
Good 
luck
Matt Adams - GE 
Appliances - [EMAIL PROTECTED]Contrary to popular opinion, Unix is user 
friendly. It's just particular about who it makes friends with. 

 
Matt Adams - GE Appliances - 
[EMAIL PROTECTED]Contrary to popular opinion, Unix is user 
friendly. It's just particular about who it makes friends with. 

 
-Original Message-From: Nick Wagner 
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003 3:24 
PMTo: Multiple recipients of list ORACLE-LSubject: Quick 
Question -- 8.1.7 logs applied to 9.2.0 database instance?
Can I take a hot 
backup of an 8.1.7 instance...  and then upgrade the backup to 9.2.0 
(upgrading data dictionary tables and everything) and then apply logs created by 
the 8.1.7 instance to this 9.2.0 backup?  
 
Please answer as 
soon as possible...
 
Thanks!  

Nick Wagner

Re: Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?

2003-03-24 Thread Joe Testa

Nick I dont believe so since in the startup migrate command redo logs 
are converted to a 9i format.

joe

Nick Wagner wrote:

Can I take a hot backup of an 8.1.7 instance...  and then upgrade the 
backup to 9.2.0 (upgrading data dictionary tables and everything) and 
then apply logs created by the 8.1.7 instance to this 9.2.0 backup? 
 
Please answer as soon as possible...
 
Thanks! 
Nick Wagner
 
 


--
Joseph S Testa
Chief Technology Officer
Data Management Consulting
614-791-9000
It's all about the "CACHE"
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joe Testa
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Reorganizing tables

2003-03-24 Thread Wolfgang Breitling

Title: RE: Reorganizing tables

Re "I do not use the 'alter table ... move ...' command since it retains 
the old extent size for the very 1st extent in the new tablespace."

If you have LMT with uniform size and you move a table "up", each extent, 
including the first will be of the uniform size. There is no "retaining the 
old extent size". Eeven if you move "down", all extents, including the 
first, will be of the uniform size, you just get enough initially to cover 
whatever is requested for initial - which is why all my tables have an 
initial  2K, next 2K storage clause. That leaves it entirely to the LMT to 
allocated the necessary extents.

Alternately, you can specify a storage clause with a different, fitting 
initial extent in the move command.

At 11:34 AM 3/24/2003 -0800, you wrote:
>Hi Dennis,
>
>Let me try to answer part of question#1. We only deal with warehouse
>applications. So there are only inserts and updates.
>
>All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M).
>
>I do not mix the staging (insert and truncate) tables and the normal tables
>in the same tablespace.
>
>Once I month, I run a job for tables in 128K and 4M tablespaces to see
>whether I need to promote them to a higher extent size. If so, I export,
>drop, recreate and import in a new tablespace. I do not use the 'alter table
>... move ...' command since it retains the old extent size for the very 1st
>extent in the new tablespace.

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited.  If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately.  We honour similar requests relating to the privacy of email communications.

Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie.  Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit.  Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement.  Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.

RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database instan

2003-03-24 Thread Nick Wagner




nevermind... I found the answer...  'No'.  :) 

 
Thanks! 
 
-Original Message-From: Nick Wagner Sent: 
Monday, March 24, 2003 12:24 PMTo: Multiple recipients of list 
ORACLE-LSubject: Quick Question -- 8.1.7 logs applied to 9.2.0 
database instance?
Can I take a hot 
backup of an 8.1.7 instance...  and then upgrade the backup to 9.2.0 
(upgrading data dictionary tables and everything) and then apply logs created by 
the 8.1.7 instance to this 9.2.0 backup?  
 
Please answer as 
soon as possible...
 
Thanks!  

Nick Wagner

Re: How to enable Java on ORACLE 9ir2 database???

2003-03-24 Thread Govindan K

 --- mike mon <[EMAIL PROTECTED]> wrote: > We have ORACLE
9ir2 on SUN Solaris.  We plan to use
> Java on ORACLE very soon and need you help.
> 
> 1. how to check the database we have is "java enable"?
> 

  connect as system/sys and 
  SQL> desc dbms_java

> 2. If database on "java enable", which files we need
> run to make it "java" enable"?

  $OH/javavm/install/initjvm.sql (no need to do if you
  are able to describe as above (Check readme files in
  the dir/subdir under $OH/javavm)

> 
> 3. on init.ora file their has a entry call
> "java_pool_size = ".  What value should I put in
> there?
> 
  Put min.50Mb. If not 
  "alter java class  resolve" will give errors
  and the java class(es) will show invalid.



> 4. except "java_pool_size = ??", does their has other
> entry we need put on "init.ora" file to support JAVA?
> 
  Keep shared_pool_size to min 50Mb.


  HTH
  GovindanK


__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Govindan=20K?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AIX question

2003-03-24 Thread Veronica Levin

Hi Lisa,

lscfg | more

this will give the list of all the devices installed on your server.

An example of the output is:
INSTALLED RESOURCE LIST

The following resources are installed on the machine.
+/- = Added or deleted from Resource List.
*   = Diagnostic support not available.

+ scsi3 27-09 Wide/Ultra-2 SCSI I/O Controller
+ hdisk227-09-00-8,0  16 Bit LVD SCSI Disk Drive (9100 MB)
+ hdisk327-09-00-9,0  16 Bit LVD SCSI Disk Drive (9100 MB)
+ hdisk427-09-00-10,0 16 Bit LVD SCSI Disk Drive (9100 MB)
+ hdisk527-09-00-11,0 16 Bit LVD SCSI Disk Drive (9100 MB)
+ hdisk627-09-00-12,0 16 Bit LVD SCSI Disk Drive (9100 MB)
+ hdisk727-09-00-13,0 16 Bit LVD SCSI Disk Drive (9100 MB)

hope this helps!

Saludos, 
Verónica Levin Enríquez
Compañía Cervecera de Nicaragua


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Enviado el: Wednesday, March 19, 2003 10:49 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Re: AIX question


Hi Lisa

Here is the link to all AIX manuals online for 4.3

http://publib16.boulder.ibm.com/pseries/en_US/infocenter/base/aix43.htm


I think lspv is the one you want

Cheers


--
=
Peter McLarty   E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane,  AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
A great pleasure in life is doing what people say you cannot do.

- Walter Bagehot (1826-1877 British Economist)
=
Mincom "The People, The Experience, The Vision"

=

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
22/02/2003 03:19 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:AIX question


AIX 4.3.3 
Can anyone tell me if there's a command to determine what volumes/disks 
are on each controller? 
I'm way out of my element here but the SA for this system is scarce. 
Thanks for any suggestions, and have a great weekend everyone 
Lisa Koivu 
Oracle Database Administrator 
Fairfield Resorts, Inc. 
5259 Coconut Creek Parkway 
Ft. Lauderdale, FL, USA  33063 
Office: 954-935-4117 
Fax:954-935-3639 
Cell:954-683-4459 
"The sender believes that this E-Mail and any attachments were free of any 
virus, worm, Trojan horse, and/or malicious code when sent. This message 
and its attachments could have been infected during transmission.  By 
reading the message and opening any attachments, the recipient accepts 
full responsibility for taking proactive and remedial action about viruses 
and other defects. The sender's business entity is not liable for any loss 
or damage arising in any way from this message or its attachments."
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:
Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network 
Services -- 858-538-5051 http://www.fatcity.com San Diego, California --
Mailing list and web hosting services 
- To 
REMOVE yourself from this mailing list, send an E-Mail message to: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the 
message BODY, include a line containing: UNSUB ORACLE-L (or the name of 
mailing list you want to be removed from). You may also send the HELP 
command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Veronica Levin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services

RE: Reorganizing tables

Thanks Jared, Rachel, Tom, Dick, Prakash, Ron

Excellent points. Very much appreciated. Unfortunately at this point people
are asking "but have you tested it?". So I need to construct some type of
test that will demonstrate how much effect a reorg will have. After I've
answered that question, then I can move on to some of the other issues that
you mention. I have joked that if the results are strongly positive, they
won't see me much after that because I'll be touring the world selling my
performance solution that never occurred to anyone else. 
   Our test system is cloned from an RMAN backup of production so the tables
should be close to production. I'm thinking of creating a new table and
copying the contents of a production table into it and then tracing
full-table scans and comparing the results. 
 
Jared - is there a way to estimate block-level fragmentation? Comparing the
average row length with the number of blocks used?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 2:30 PM
To: [EMAIL PROTECTED]
Cc: DENNIS WILLIAMS
Importance: High


Dennis,

Table reorgs can be useful when there is block level fragmentation, and 
you
do a lot of FTS.  eg. you have a table with 1000 blocks and all the data 
will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally 
always
sorted on the same keys.  You can load them in sorted order, though at the 
moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any 
problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted
to do a whole tablespace at a time.  I talked her out of it, or at least, 
she sensed
that I wasn't very cooperative on that subject.  :)

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:38 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue 
to
add reports and the reports do quite a few full table scans. The reports 
are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database instan

2003-03-24 Thread Kevin Lange




We 
just went 8.0.5 to 8.1.7 and the instructions explicitly said to NOT apply logs 
from the 8.0.5 on the 8.1.7.   Not sure on the 
9.2.0.

  -Original Message-From: Nick Wagner 
  [mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003 2:24 
  PMTo: Multiple recipients of list ORACLE-LSubject: Quick 
  Question -- 8.1.7 logs applied to 9.2.0 database 
instance?
  Can I take a hot 
  backup of an 8.1.7 instance...  and then upgrade the backup to 9.2.0 
  (upgrading data dictionary tables and everything) and then apply logs created 
  by the 8.1.7 instance to this 9.2.0 backup?  
   
  Please answer as 
  soon as possible...
   
  Thanks!  
  
  Nick Wagner

RE: How to enable Java on ORACLE 9ir2 database???

2003-03-24 Thread Stephane Paquette

1. Check in dba_objects, you should have java objects as the object type.
   You should have java class, java data, java resource, java source

2. It depends what you want to do, at minimum run initjvm. You have to run
more scripts to use xml and other stuff, check on metalink.

3. At least 50M

4. I'm not sure.

Stephane

-Original Message-
Sent: Monday, March 24, 2003 2:44 PM
To: Multiple recipients of list ORACLE-L


We have ORACLE 9ir2 on SUN Solaris.  We plan to use
Java on ORACLE very soon and need you help.

1. how to check the database we have is "java enable"?

2. If database on "java enable", which files we need
run to make it "java" enable"?

3. on init.ora file their has a entry call
"java_pool_size = ".  What value should I put in
there?

4. except "java_pool_size = ??", does their has other
entry we need put on "init.ora" file to support JAVA?

Thanks.

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: mike mon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

UNIX fstab settings and prealloc command

2003-03-24 Thread Brooks, Russ




Hi,
  We're running 
online JFS on HP.  We had a recommendation given to us to replace the 
settings in fstab for the online redo logs and archive logs.  The settings 
were:
 
/dev/vgPRD03/lvPRDsaparch 
/oracle/PRD/saparch    vxfs  delaylog   
0    
/dev/vgPRD02/lvPRDoriglogB    
/oracle/PRD/origlogB   vxfs  delaylog   
0    
/dev/vgPRD01/lvPRDoriglogA    
/oracle/PRD/origlogA   vxfs  delaylog   
0    
/dev/vgPRD01/lvPRDmirrlogB    
/oracle/PRD/mirrlogB   vxfs  delaylog   
0    
/dev/vgPRD02/lvPRDmirrlogA    
/oracle/PRD/mirrlogA   vxfs  delaylog   
0
 
The recommended 
change would be:
 
/dev/vgPRD03/lvPRDsaparch 
/oracle/PRD/saparch    vxfs  
nodatainlog,convosync=direct   
0    
/dev/vgPRD02/lvPRDoriglogB    
/oracle/PRD/origlogB   vxfs  
nodatainlog,convosync=direct   
0    
/dev/vgPRD01/lvPRDoriglogA    
/oracle/PRD/origlogA   vxfs  
nodatainlog,convosync=direct   
0    
/dev/vgPRD01/lvPRDmirrlogB    
/oracle/PRD/mirrlogB   vxfs  
nodatainlog,convosync=direct   
0    
/dev/vgPRD02/lvPRDmirrlogA    
/oracle/PRD/mirrlogA   vxfs  
nodatainlog,convosync=direct   
0    
 
What a different 
branch of the consultants gave us was:
 
/oracle/DV1/origlogA vxfs delaylog,convosync=direct  0 
2/oracle/DV1/origlogB vxfs delaylog,convosync=direct  0 
2/oracle/DV1/mirrlogA vxfs delaylog,convosync=direct  0 
2/oracle/DV1/mirrlogB vxfs delaylog,convosync=direct  0 
2
 
They based this on a 
quick test using the following command:
 
/bin/time -p prealloc myfile 10
 
which 
indicated a slight negative impact to I/O with the nodatainlog.  I've read 
the man pages on prealloc, but it isn't clear that this is a valid simulation of 
the online redo logs.  Does anyone have experience with this?  Was 
this a valid test?
 
Best 
regards,
Russ 
Brooks

RE: How to enable Java on ORACLE 9ir2 database???

Mike
   How are you planning to use Java? Generally, there are 3 ways:
1. Java stored procedures, like PL/SQL stored procedures. This is where the
java_pool_size setting becomes very important. Otherwise, set it low after
installation so it doesn't waste memory. Most sites don't seem very
interested in Java stored procedures, as near as I can tell.
2. Java thick client
3. Java thin client
Either way, you probably have an application server installed somewhere,
like Oracle 9i A.S., Tomcat, BEA WebLogic, etc. For #2 and #3 you don't need
to do anything in Oracle. #3 doesn't even use SQL*Net, from what I can see.
The A.S. administrators will probably want to do connection pooling, so they
will ask you to create a login with broad access so several applications can
use it.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L


We have ORACLE 9ir2 on SUN Solaris.  We plan to use
Java on ORACLE very soon and need you help.

1. how to check the database we have is "java enable"?

2. If database on "java enable", which files we need
run to make it "java" enable"?

3. on init.ora file their has a entry call
"java_pool_size = ".  What value should I put in
there?

4. except "java_pool_size = ??", does their has other
entry we need put on "init.ora" file to support JAVA?

Thanks.

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mike mon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:OCI and Visual C++

Stephane,

An invalid SQL statement is an invalid statement no matter what the OS. 
Smells like something else is fishy.

Dick Goulet

Reply Separator
Author: "Stephane Paquette" <[EMAIL PROTECTED]>
Date:   3/24/2003 11:48 AM

Hi all,

I have a developper complaining that is C++ with embedded SQL program is
working on DOS but not in Windows XP.
I've never used OCI. This is with Oracle 8172.

The compile runs fine, it's failling at run time on Windows XP but runs fine
oin DOS.

These are the librairies used when linking :emb8dm32.lib sqllib80.lib
ociw32.lib ora8dm32.lib.
According to metalink, oci.dll only should be ok.

At run time, there is
ORA-00900: Invalid SQL statement
RETURN CODE - 53264
OCCURED AT STATEMENT -
DECLARE slfss001_1 CURSOR FOR STORED_SUB slfss001(:p1)


Anybody ?



Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
[EMAIL PROTECTED] 




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Reorganizing tables

Dennis,

Table reorgs can be useful when there is block level fragmentation, and 
you
do a lot of FTS.  eg. you have a table with 1000 blocks and all the data 
will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally 
always
sorted on the same keys.  You can load them in sorted order, though at the 
moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any 
problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted
to do a whole tablespace at a time.  I talked her out of it, or at least, 
she sensed
that I wasn't very cooperative on that subject.  :)

Jared






DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:38 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue 
to
add reports and the reports do quite a few full table scans. The reports 
are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Storage guidelines in 9iR1 ??

2003-03-24 Thread Kevin Toepke

Nope. I didn't get a chance to create a TAR on this. And a metalink search
turned up nothing. It was probably something related to my particular
environment.

Note to self. Think before you post. (still slapping myself silly over
this one. Ouch!)

Kevin

-Original Message-
Krishna Vaidyanatha
Sent: Monday, March 24, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L


Kevin,

Thanks for the clarification. So the real problem was
not with the "number of extents", but with the number
of extents for a segment in an LMT. This could be a
bug associated with LMTs. Did you file a tar for this?
If I were you, I would, just so that if there are any
bugs in this, it will get resolved. Do you see the
need for us to be "precise and detailed" while making
statements on the list. The concern I have is that,
some novice DBA will take your words as gospel and
spread the myth that Oracle objects should always have
less than 1024 extents. That is what I was trying to
deter. Let me know when you get more on this. BTW, you
can contact me on my private e-mail address
[EMAIL PROTECTED] This one is for the list only.

Cheers,

Gaja
--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> Gaja,
>
> You're correct. I should have quantified what I
> meant by significant. As
> well as given more detail on what I was doing. That
> said, here is what I
> remember of what I was doing
>
> Specifically, At the request of management, I was
> testing the performance
> and extent allocation of locally managed tablespaces
> v.s. dictionary managed
> tablespaces. I was to give a summary of my results
> and a recommendation as
> to how new tablespaces were to be created.
>
> That said, I create 2 tablespaces. One dictionary
> managed and one locally
> managed (uniform extent size) on the same instance,
> same logical volume on
> the disk array and same extent sizes (1mb)
>
> The same table was created in both tablespaces,
> using the default storage
> clauses.
>
> I used SQL Loader to load the same data into both
> tables tablespaces
> multiple times. The source file was about 1mb.
>
> I was mostly testing non-direct path insert
> performance (via sqlldr) and
> select performance via several scripts (using
> sqlplus).
>
> What I found was that the performance of sqlldr
> stayed remarkably steady for
> the dictionary-managed tablespace well past 2000
> extents. The sqlldr
> run-times increased by about 5-10% for the
> locally-managed after about 1024
> extents had been reached.
>
> The performance of the select statements degraded in
> a linear fashion, based
> on the number of rows. The exception was that the
> LMT table saw a 5-10%
> degradation in performance after about 1024 extents
> were reached.
>
> No updates or deletes were performed on the tables.
> Also, there were no
> indexes or constraints on the tables. Nor did I
> generate statistics.
>
> And this was repeatable as I dropped and recreated
> the tablespaces several
> times.
>
> The methodology was as follows, recording the timing
> at each step
> 1)Load the file one time into each of the tables
> 2)note the number of extents
> 3)perform the selects
>   -- count(*)
>   -- select * from xxx where id = 1;
>   -- a select with a group by.
>
> Kevin
>
> -Original Message-
> Krishna Vaidyanatha
> Sent: Friday, March 21, 2003 1:15 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Kevin,
>
> Long time no talk or see. Hope things are well with
> you. Going forward it will nice for us to "quantify"
> any performance differences that we observe,
> preferably with data supporting the claim. Don't get
> me wrong, I am not trying to beat you up on this,
> but
> trying to bring some clarity to the situation. Your
> original posting very strongly suggested that 1024
> was
> some kind of magical number beyond which SQL
> performance took a "significant" dive. We need to
> find
> out (at this stage hypothesise), what caused the
> performance decrease.
>
> If we were to look at this more objectively, it is
> very unlikely that full-table scans would perform
> worse with more extents, assuming that everything
> else
> remains constant. This is because, Oracle would have
> issued the same number of "read system calls", to
> process the data below the high-water
> mark,regardless
> of the number of extents. This ofcourse assumes that
> the value of  db_file_multiblock_read_count did not
> change over time.
>
> Index scans are also rarely affected by the number
> of
> extents, because an index-scan will still require
> reading of the same root, branch and leaf nodes to
> determine the ROWIDs for the search (regardless of
> the
> number of extents in the table).
>
> One very plausible and probable cause for the
> performance decrease, could be the onset of
> "block-level fragmentation" that happens over time.
> This can be measured by calculating the "data
> density"
> (defined as rows/block below the high-water mark) of
> the blocks in the segment over time

ORA-00445: background process "PMON" did not start after 120 seco

2003-03-24 Thread Schauss, Peter

I am getting the above message intermittently from the cron job which 
restarts the database after backup.

Environment:  is AIX 4.3/Oracle 8.1.7

It appears that the startup process gets far enough to create a shared
memory
segment, since, I usually see an unattached segment belonging to Oracle with
a creation time corresponding to the time when my cron job tried to start
Oracle.  I have to delete this segment before trying to start the instance
by hand.

There is one other instance on the box which always goes through its
nightly backup and shutdown routine first and never has this problem.

I have not yet encountered this problem during a manual startup.

Looking in the documentation for AIX, I do not find anyplace where
it specifies relationships between AIX system parameters and init.ora
settings as was the case for Solaris.  Am I missing something?

Thanks,
Peter Schauss
Northrop Grumman Corporation
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Schauss, Peter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?

2003-03-24 Thread Nick Wagner




Can I take a hot 
backup of an 8.1.7 instance...  and then upgrade the backup to 9.2.0 
(upgrading data dictionary tables and everything) and then apply logs created by 
the 8.1.7 instance to this 9.2.0 backup?  
 
Please answer as 
soon as possible...
 
Thanks!  

Nick Wagner

Re: How to enable Java on ORACLE 9ir2 database???

2003-03-24 Thread Darrell Landrum

On your system, look in your $ORACLE_HOME/javavm/doc/readme.txt; I think
it answers all of these questions and provides other necessary
information regarding Java in Oracle.




Darrell Landrum
Database Administrator
Zale Corporation

>>> [EMAIL PROTECTED] 03/24/03 01:44PM >>>
We have ORACLE 9ir2 on SUN Solaris.  We plan to use
Java on ORACLE very soon and need you help.

1. how to check the database we have is "java enable"?

2. If database on "java enable", which files we need
run to make it "java" enable"?

3. on init.ora file their has a entry call
"java_pool_size = ".  What value should I put in
there?

4. except "java_pool_size = ??", does their has other
entry we need put on "init.ora" file to support JAVA?

Thanks.

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: mike mon
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Perl Use Net8 Encryption?

2003-03-24 Thread JApplewhite


A Perl client app on Linux can successfully access an 8.1.7.4 DB via a
listener on a non-standard port through a firewall.  The Perl developer
figured out where he has to configure the non-standard port# to connect to
the DB.

However, we want this client to access the DB using native Net8 encryption,
which works just fine using SQL*Plus from the client.  We can't force the
DB to require encryption, since there are lots of clients inside our
firewall that don't have Net8 encryption configured.

Since the Perl connection is specified outside of tnsnames.ora and
sqlnet.ora, how can I ensure that the Perl connection will use Net8
encryption?

BTW, I don't speak Perl and he doesn't speak Oracle.

TIA.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partitioning

2003-03-24 Thread Basavaraja, Ravindra

Title: RE: partitioning



Hi 
Jacques,
 
How do 
I exactly implement this.In the before insert trigger what after I generate the 
value for the new partition column.How does the
records go into that partition.
 
Have 
you tried this.How is the performance for an insert into a table of 10 
records everyday.Executing the trigger for every 
insert 
for high volume of data may be costly on the performance..?
 
Can we 
achieve this or anything closer using HASH partitioning as suggested by 
others.
 
thanks

  -Original Message-From: Jacques Kilchoer 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003 
  11:52 AMTo: '[EMAIL PROTECTED]'Cc: 
  '[EMAIL PROTECTED]'Subject: RE: 
  partitioning
  You could accomplish this with a before insert trigger and a 
  partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin    select mod 
  (sequence.nextval, 8) into :new.partition_column     from dual ; end ; 
  / 
  Something similar would be achieve by hash partitioning, which 
  is easier to implement. 
  > -Original Message- > 
  From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] 
  > > I am wondering if there is 
  any way to achieve horizontal > partitioning in 
  Oracle. > > Assuming 
  that I have about 8 partitions for a table.When > 
  there is INSERT onto this table I want one record > 
  to be inserted into each partition i.e > 1st record 
  goes into partition 1 > 2nd record goes into 
  partition 2 > 3rd record goes into partition 
  3 > . > . 
  > 8th record goes into partition 8 > 9th record goes into partition 1. > 
  > I guess this feature is available in Informix 
  handled by The > informix engine.I am not sure if 
  Oracle has something > similiar to this OR is it 
  possible to design a logic and > embede it ,but 
  what would be the performance effect? > 
  > Any thoughts or similiar ideas

RE: AW: Remove killed session from v$session

Well, it will kill of your oracle processes and deallocate any
shared memory that has been allocated. It will transcend the problem
to the different plane of existence.

-Original Message-
Sent: Monday, March 24, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L


{innocently} but this *will* take care of the problem, right?  :)


-Original Message-
Sent: Monday, March 24, 2003 12:59 PM
To: Multiple recipients of list ORACLE-L


Mladen, you really need to put a :) on these, lest some
newbie take you seriously.

:)

Jared






"Gogala, Mladen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:18 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: AW: Remove killed session from v$session


This is going to remove killed session from v$session:

ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m

Next time you connect to the database, the killed session will not
be visible in the v$session table.

-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L



Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why
it
> takes so long before the session actually disappears from v$session. 
It's
a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient 
schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht 
unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde 
bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und 
Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must 
decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the 
session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ?
Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail 
an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde
bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Sc

OCI and Visual C++

2003-03-24 Thread Stephane Paquette

Hi all,

I have a developper complaining that is C++ with embedded SQL program is
working on DOS but not in Windows XP.
I've never used OCI. This is with Oracle 8172.

The compile runs fine, it's failling at run time on Windows XP but runs fine
oin DOS.

These are the librairies used when linking :emb8dm32.lib sqllib80.lib
ociw32.lib ora8dm32.lib.
According to metalink, oci.dll only should be ok.

At run time, there is
ORA-00900: Invalid SQL statement
RETURN CODE - 53264
OCCURED AT STATEMENT -
DECLARE slfss001_1 CURSOR FOR STORED_SUB slfss001(:p1)


Anybody ?



Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
[EMAIL PROTECTED] 




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

rh9 ALREADY?

2003-03-24 Thread Joseph S Testa

This is scary:

We appreciate your support of Red Hat Network and wanted to alert you
to a special service that we are extending to paying Red Hat Network
customers such as yourself.  For the past couple months we've gathered
feedback and listened to our customers.  We've heard that one of the
things you want most is early access to Red Hat Linux ISOs.

Well, we've responded.  Starting March 31st at 9am Eastern, you can
start downloading Red Hat Linux 9 ISOs -- a week before they will be
generally available in retail stores or via Red Hat FTP. 


8.1 is not even out, go figure.

joe



Joseph S Testa
Chief Technology Officer
Data Management Consulting
p: 614-791-9000
f: 614-791-9001
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joseph S Testa
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

How to enable Java on ORACLE 9ir2 database???

2003-03-24 Thread mike mon

We have ORACLE 9ir2 on SUN Solaris.  We plan to use
Java on ORACLE very soon and need you help.

1. how to check the database we have is "java enable"?

2. If database on "java enable", which files we need
run to make it "java" enable"?

3. on init.ora file their has a entry call
"java_pool_size = ".  What value should I put in
there?

4. except "java_pool_size = ??", does their has other
entry we need put on "init.ora" file to support JAVA?

Thanks.

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mike mon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:Reorganizing tables

Dennis,

We don't re-org tables, but we do rebuild indexes on occassion.

Dick Goulet

Reply Separator
Author: DENNIS WILLIAMS <[EMAIL PROTECTED]>
Date:   3/24/2003 9:38 AM

We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue to
add reports and the reports do quite a few full table scans. The reports are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Reorganizing tables

2003-03-24 Thread BALA,PRAKASH (HP-USA,ex1)

Hi Dennis,

Let me try to answer part of question#1. We only deal with warehouse
applications. So there are only inserts and updates.

All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M). 

I do not mix the staging (insert and truncate) tables and the normal tables
in the same tablespace.

Once I month, I run a job for tables in 128K and 4M tablespaces to see
whether I need to promote them to a higher extent size. If so, I export,
drop, recreate and import in a new tablespace. I do not use the 'alter table
... move ...' command since it retains the old extent size for the very 1st
extent in the new tablespace.

I have your other questions in my 'to do' list but have not got time to work
on them yet.

Thanks
Prakash




-Original Message-
Sent: Monday, March 24, 2003 12:39
To: Multiple recipients of list ORACLE-L


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue to
add reports and the reports do quite a few full table scans. The reports are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: BALA,PRAKASH (HP-USA,ex1)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Interesting MetaLink note for 9i patching

2003-03-24 Thread Odland, Brad

I guess that would the THE terminial release then...



-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L


If you have 9.2, you  need to see this note on applying interim patches.

Doc ID 229773.1

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Odland, Brad
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Single sign on

2003-03-24 Thread Dave Morgan

>From dbi-users

>Personally, I use a password server daemon that supplies
>passwords to authenticated users, encrypted with RC4 as they
>traverse the network.

Single sign-on in open source cool...

>That way I only need keep the passwords in one file. No passwords
>on the command line, which is handy for automated stuff.  
>Since it is written in Perl ( based on a daemon in the Perl Cookbook)
>it has a native Perl interface as well as a command line interface.
>You can download it at 

new URL
http://www.oreilly.com/catalog/oracleperl/pdbatoolkitPDBA-1.0.tar.gz 

>You can buy the book too if you like,
>though it's probably not necessary for many folks on this list.  :)
>Jared

And an excuse too Thanks :) 

My Oracle bookshelf is pretty limited as there are a bunch
of marketing and accounting manuals competing for space :(

Unix in a Nutshell
Perl in a Nutshell
Oracle 8 The Complete Reference (just about time for an upgrade)
and Velepuri's Backup and Recovery(not used often but just in case :).

"sed & awk", Roberts RMAN Handbook and Pete Finnegans Oracle 
Security pretty well round out my regular references. 

Not counting the binders of man and pod printouts on DBI, Apache,
mod-perl, etc from the early days, again, not used alot but needed.

Thanks for the code, loading it on to my development server as I write

Dave


-- 
Dave Morgan
Operations Manager, Rigskills Canada
Canada's Geographical Oilfield Services Locator
http://www.rigskills.ca
[EMAIL PROTECTED]
403 399 2442
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dave Morgan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AW: Remove killed session from v$session

2003-03-24 Thread Mercadante, Thomas F

{innocently} but this *will* take care of the problem, right?  :)


-Original Message-
Sent: Monday, March 24, 2003 12:59 PM
To: Multiple recipients of list ORACLE-L


Mladen, you really need to put a :) on these, lest some
newbie take you seriously.

:)

Jared






"Gogala, Mladen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:18 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: AW: Remove killed session from v$session


This is going to remove killed session from v$session:

ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m

Next time you connect to the database, the killed session will not
be visible in the v$session table.

-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L



Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why
it
> takes so long before the session actually disappears from v$session. 
It's
a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient 
schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht 
unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde 
bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und 
Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must 
decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the 
session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ?
Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail 
an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde
bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> > rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
> > ausschliessen.
> >
> > As you are probably aware, e-mails sent via the Internet can easily be
> > copied or manipulated by third parties. For this reason we would ask 
for
> > your under

Re: Reorganizing tables

2003-03-24 Thread Rachel Carmichael

have you done a 10046 trace (yes Cary, I listen to you!) on one of the
reports to see why/if there is a performance problem and if there is
one, where it is?

one sentence bothers me "the users continue to add reports" -- who
writes the SQL for these reports? Is it as efficient as it could be?
While FTS is not necessarily wrong, how large are the tables that are
being read?

--- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote:
> We have a new manager, and at his last employer the DBAs reorganize
> Oracle
> tables on a regular basis. I don't reorg tables on a regular basis.
> He is
> lobbying us to investigate this and test whether it would or wouldn't
> increase performance. We are on Oracle 8.1.6, Compaq Tru64, all
> tables are
> LMT with uniform extents. This is an OLTP system, but the users
> continue to
> add reports and the reports do quite a few full table scans. The
> reports are
> probably the critical performance issue at this time.
>- Do you do regular table reorgs? What benefit does that give you?
>- Are there any indicators of when a table reorg would be
> beneficial?
>- What sort of test would verify whether a table reorg changed
> performance?
> 
> 
> 
> Dennis Williams 
> DBA, 40%OCP, 100% DBA 
> Lifetouch, Inc. 
> [EMAIL PROTECTED] 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Reorganizing tables

2003-03-24 Thread Ron Rogers

Dennis,
 If your tables are LMT with uniform extents then there is no "normal"
reason to reorg. The only place where I can think that it might be an
advantage to reorg is when there is a lot of random deletes and add to
the table. Then an export/truncate/import will reset the HWM and perhaps
make things a little faster.
Ron

>>> [EMAIL PROTECTED] 03/24/03 12:38PM >>>
We have a new manager, and at his last employer the DBAs reorganize
Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He
is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables
are
LMT with uniform extents. This is an OLTP system, but the users
continue to
add reports and the reports do quite a few full table scans. The
reports are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be
beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AW: Remove killed session from v$session

I agree. This is, essentially, a unix equivalent of shutdown abort.
It kills all oracle processes and removes any shared memory allocated to 
oracle, even if there is more then one instance on the box.

-Original Message-
Sent: Monday, March 24, 2003 1:00 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High


Mladen, you really need to put a :) on these, lest some
newbie take you seriously.

:)

Jared






"Gogala, Mladen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:18 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: AW: Remove killed session from v$session


This is going to remove killed session from v$session:

ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m

Next time you connect to the database, the killed session will not
be visible in the v$session table.

-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L



Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why
it
> takes so long before the session actually disappears from v$session. 
It's
a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient 
schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht 
unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde 
bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und 
Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must 
decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the 
session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ?
Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail 
an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde
bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> > rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
> > ausschliessen.
> >
> > As you ar

RE: Reorganizing tables

2003-03-24 Thread Mercadante, Thomas F

Dennis,

I don't reorganize tables anymore.  I used to with Oracle 7.x.  But now, it
just doesn't matter.

If I were you, I would spend my time analyzing the sql's the developers are
using to solve the FTS by either adding indexes, adding summary tables, MV's
or even partitioning.

Good Luck!

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Monday, March 24, 2003 12:39 PM
To: Multiple recipients of list ORACLE-L


We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue to
add reports and the reports do quite a few full table scans. The reports are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: standby on SAN ? or use internal storage

Hear hear!

Our  SAN was down for 30 hours.  Standby on the same SAN wouldn't have 
helped much.

Jared






"Arup Nanda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 07:43 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: standby on SAN ? or use internal storage


Rahul,
 
The purpose of standby is to failover to it when the primary fails - which 
could range from the CPU failure to SAN failure. Although modern SANs are 
pretty robust, you account for the remote chance of failure by building a 
standby. Placing the database on teh same SAN as the primary does not 
really buy you any extra high availability feature, does it? You have a 
single point of failure, the SAN.
 
So your Standby should use storage not in the same place as the primary. 
However, using the standby server's internal disks could render your 
filesystems inaccessible if the server fails. But in some hosts, this is 
not a problem either; the SAs can mount the internal disks on another 
machine and recover data - check with your SA. If this is not the case, 
place the standby database on a different SAN.
 
So your preffered options are (in descending order)
 
(1) Primary Server - with two primary instances
Primary SAN with the two primary databases
Standby server 
Standby SAN
 
(2) Primary Server - with two primary instances 
Primary SAN with the two primary databases
Standby server 
Internal storage on standby server
 
HTH.
 
Arup
- Original Message - 
To: Multiple recipients of list ORACLE-L 
Sent: Monday, March 24, 2003 12:18 AM

list, i'm a bit confused on whether to put the standby DB on the SAN 
storage or 
use the internal storage of the standby host !!! 
 
config a
two instances on primary server, data-files on SAN, hot standby db files 
on SAN too.
standby instances for both primary on another machine
 
config b
both primary on SAN, standby db files on the itnernal storage of standby 
machine
 
any thoughts  ? 
 
TIA
Rahul
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AW: Remove killed session from v$session

This is going to remove killed session from v$session:

ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m

Next time you connect to the database, the killed session will not
be visible in the v$session table.

-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L



Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why
it
> takes so long before the session actually disappears from v$session. It's
a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ?
Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde
bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> > rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
> > ausschliessen.
> >
> > As you are probably aware, e-mails sent via the Internet can easily be
> > copied or manipulated by third parties. For this reason we would ask for
> > your understanding that, for your own protection and ours, we must
> > decline all legal responsibility for the validity of the statements and
> > comments given above.
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Stefan Jahnke
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself fr

Interesting MetaLink note for 9i patching

If you have 9.2, you  need to see this note on applying interim patches.

Doc ID 229773.1

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AW: Remove killed session from v$session

Mladen, you really need to put a :) on these, lest some
newbie take you seriously.

:)

Jared






"Gogala, Mladen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/24/2003 09:18 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: AW: Remove killed session from v$session


This is going to remove killed session from v$session:

ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m

Next time you connect to the database, the killed session will not
be visible in the v$session table.

-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L



Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why
it
> takes so long before the session actually disappears from v$session. 
It's
a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient 
schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht 
unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde 
bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und 
Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must 
decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the 
session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ?
Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail 
an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde
bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> > rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
> > ausschliessen.
> >
> > As you are probably aware, e-mails sent via the Internet can easily be
> > copied or manipulated by third parties. For this reason we would ask 
for
> > your understanding that, for your own protection and ours, we must
> > decline all legal responsibility for the validity of the statements 
and
> > comments given above.
> >
> >
> >
> >

RE: Storage guidelines in 9iR1 ??

2003-03-24 Thread Gaja Krishna Vaidyanatha

Kevin,

Thanks for the clarification. So the real problem was
not with the "number of extents", but with the number
of extents for a segment in an LMT. This could be a
bug associated with LMTs. Did you file a tar for this?
If I were you, I would, just so that if there are any
bugs in this, it will get resolved. Do you see the
need for us to be "precise and detailed" while making
statements on the list. The concern I have is that,
some novice DBA will take your words as gospel and
spread the myth that Oracle objects should always have
less than 1024 extents. That is what I was trying to
deter. Let me know when you get more on this. BTW, you
can contact me on my private e-mail address
[EMAIL PROTECTED] This one is for the list only.

Cheers,

Gaja
--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> Gaja,
> 
> You're correct. I should have quantified what I
> meant by significant. As
> well as given more detail on what I was doing. That
> said, here is what I
> remember of what I was doing
> 
> Specifically, At the request of management, I was
> testing the performance
> and extent allocation of locally managed tablespaces
> v.s. dictionary managed
> tablespaces. I was to give a summary of my results
> and a recommendation as
> to how new tablespaces were to be created.
> 
> That said, I create 2 tablespaces. One dictionary
> managed and one locally
> managed (uniform extent size) on the same instance,
> same logical volume on
> the disk array and same extent sizes (1mb)
> 
> The same table was created in both tablespaces,
> using the default storage
> clauses.
> 
> I used SQL Loader to load the same data into both
> tables tablespaces
> multiple times. The source file was about 1mb.
> 
> I was mostly testing non-direct path insert
> performance (via sqlldr) and
> select performance via several scripts (using
> sqlplus).
> 
> What I found was that the performance of sqlldr
> stayed remarkably steady for
> the dictionary-managed tablespace well past 2000
> extents. The sqlldr
> run-times increased by about 5-10% for the
> locally-managed after about 1024
> extents had been reached.
> 
> The performance of the select statements degraded in
> a linear fashion, based
> on the number of rows. The exception was that the
> LMT table saw a 5-10%
> degradation in performance after about 1024 extents
> were reached.
> 
> No updates or deletes were performed on the tables.
> Also, there were no
> indexes or constraints on the tables. Nor did I
> generate statistics.
> 
> And this was repeatable as I dropped and recreated
> the tablespaces several
> times.
> 
> The methodology was as follows, recording the timing
> at each step
> 1)Load the file one time into each of the tables
> 2)note the number of extents
> 3)perform the selects
>   -- count(*)
>   -- select * from xxx where id = 1;
>   -- a select with a group by.
> 
> Kevin
> 
> -Original Message-
> Krishna Vaidyanatha
> Sent: Friday, March 21, 2003 1:15 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi Kevin,
> 
> Long time no talk or see. Hope things are well with
> you. Going forward it will nice for us to "quantify"
> any performance differences that we observe,
> preferably with data supporting the claim. Don't get
> me wrong, I am not trying to beat you up on this,
> but
> trying to bring some clarity to the situation. Your
> original posting very strongly suggested that 1024
> was
> some kind of magical number beyond which SQL
> performance took a "significant" dive. We need to
> find
> out (at this stage hypothesise), what caused the
> performance decrease.
> 
> If we were to look at this more objectively, it is
> very unlikely that full-table scans would perform
> worse with more extents, assuming that everything
> else
> remains constant. This is because, Oracle would have
> issued the same number of "read system calls", to
> process the data below the high-water
> mark,regardless
> of the number of extents. This ofcourse assumes that
> the value of  db_file_multiblock_read_count did not
> change over time.
> 
> Index scans are also rarely affected by the number
> of
> extents, because an index-scan will still require
> reading of the same root, branch and leaf nodes to
> determine the ROWIDs for the search (regardless of
> the
> number of extents in the table).
> 
> One very plausible and probable cause for the
> performance decrease, could be the onset of
> "block-level fragmentation" that happens over time.
> This can be measured by calculating the "data
> density"
> (defined as rows/block below the high-water mark) of
> the blocks in the segment over time. Data density
> issues are usually caused by un-optimal settings of
> PCTUSED and PCTFREE. Again, if PCTUSED is causing
> the
> data density issue, then even that can be overcome
> by
> using Automatic Segment Space Management in 9i.
> 
> So, if the high-water mark of the table has overtime
> inflated to 10 blocks (due to frequent INSERT &
> DELETE operations),

Reorganizing tables

We have a new manager, and at his last employer the DBAs reorganize Oracle
tables on a regular basis. I don't reorg tables on a regular basis. He is
lobbying us to investigate this and test whether it would or wouldn't
increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are
LMT with uniform extents. This is an OLTP system, but the users continue to
add reports and the reports do quite a few full table scans. The reports are
probably the critical performance issue at this time.
   - Do you do regular table reorgs? What benefit does that give you?
   - Are there any indicators of when a table reorg would be beneficial?
   - What sort of test would verify whether a table reorg changed
performance?



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: can't create database [INFO]

2003-03-24 Thread Schwerdtfeger,

Just a note for everyone.
Installing the glibc 2.3.2 helped running Oracle, but the OUI
won't run anymore because of problems with the JRE 1.3.1
(Missing link in jre/lib/libjava.so).

I tried replacing the 1.3.1 JRE with the one of JDK 1.3.1, but the
OUI won't start.
Not giving any message, just didn't start.

But the database works, so I guess I won't need the installer again
for a while ;)

-- 
Christoph Schwerdtfeger <[EMAIL PROTECTED]>

SoftConcept GmbH
Borriesstrasse 35
D-32257 Bünde

Tel:(05223) 4970-20
E-Mail: [EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Schwerdtfeger, "Christop
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: the number of concurrent queries

2003-03-24 Thread Gurelei

thank you

--- Stephane Paquette
<[EMAIL PROTECTED]> wrote:
> select * from v$sysstat where NAME   in ('user
> commits','user rollbacks');
> 
>   
> 
> -Original Message-
> Sent: Monday, March 24, 2003 7:54 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I guess I have been using a "sql statement" and a 
> "query" interchangebly. I am using status = 'ACTIVE'
> to identify the sqls currently running.  For the
> user
> commits and rollbacks, which table contain these
> values?
> 
> thank you
> gene
> --- Stephane Paquette
> <[EMAIL PROTECTED]> wrote:
> > v$session.status indicates that a sql statement is
> > going on.
> > I do not know any indicator on query.
> > 
> > But you do have indicators on transactions.
> > 
> > The total transactions are the sum of user commits
> > and user rollbacks.
> > Check those 2 statistics.
> > 
> > Stephane
> > 
> > -Original Message-
> > Sent: Friday, March 21, 2003 12:29 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Stephane:
> > 
> > Why do you think if would records the number of 
> > concurrent transactions, but not the queries? what
> > is
> > the difference from the v$session standpoint/
> > 
> > 
> > --- Stephane Paquette
> > <[EMAIL PROTECTED]> wrote:
> > > I do not think that you could record the number
> of
> > > concurrent queries.
> > > On the other hand,  you can record the number of
> > > concurrent transactions.
> > > That is what your query will do.
> > > 
> > > Another way is to use statspack.
> > > We're running statspack on our 22 production
> > > instances at each hour.
> > > We're keeping 2 months of stats.
> > > We have a central point collecting data through
> > > views on statspack tables
> > > and producing nice graphs 
> > > 
> > > My next job is to convince that we should
> monitor
> > > jobs also.
> > > 
> > > 
> > > Stephane
> > > 
> > > 
> > > -Original Message-
> > > Sent: Friday, March 21, 2003 9:24 AM
> > > To: Multiple recipients of list ORACLE-L
> > > 
> > > 
> > > Hi.
> > > 
> > > I'm trying to get a feel of the number of the
> > > queries
> > > executed at the same time against a database. I
> > have
> > > been using the following script (actually this
> is
> > a
> > > part of a script)
> > > 
> > > Select a.type, count(1) col1
> > > from v$session a,
> > >  v$process b
> > > where
> > > a.paddr=b.addr
> > > and a.status like 'ACTIVE%'
> > > and a.type <> 'BACKGROUND'
> > > and a.osuser <> 'oracle'
> > > 
> > > I want to see only ACTIVE sessions,
> not-background
> > > and
> > > not executed by oracle. Does anyone see anything
> > > wrong
> > > with that?
> > > 
> > > thanks
> > > Gene
> > > 
> > > 
> > >
> __
> > > Do you Yahoo!?
> > > Yahoo! Platinum - Watch CBS' NCAA March Madness,
> > > live on your desktop!
> > > http://platinum.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: Gurelei
> > >   INET: [EMAIL PROTECTED]
> > > 
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> > web
> > > hosting services
> > >
> >
>
-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > > 
> > > -- 
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > -- 
> > > Author: Stephane Paquette
> > >   INET: [EMAIL PROTECTED]
> > > 
> > > Fat City Network Services-- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California-- Mailing list and
> > web
> > > hosting services
> > >
> >
>
-
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed
> > > from).  You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > > 
> > 
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Platinum - Watch CBS' NCAA March Madness,
> > live on your desktop!
> > http://platinum.yahoo.com
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > -- 
> > Author: Gurelei
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and
> web
> > hosting services
> >
>
-

Re: AW: Remove killed session from v$session

2003-03-24 Thread Jared Still


Using orakill will not make the rollback happen any faster.

My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a 
lock on a table.

Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.

Jared

On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
> Thanks all, the time for the rollback to take place seems to explain why it
> takes so long before the session actually disappears from v$session. It's a
> development system running long data conversion procedures. Hence, there
> will be lots of rollback. ... and I guess I'll opt for orakill then.
>
> Stefan Jahnke
> Consultant
> BOV Aktiengesellschaft
> Voice: +49 201 - 4513-298
> Fax: +49 201 - 4513-149
> mailto: [EMAIL PROTECTED]
> Please remove nospam to contact me via email.
>
> visit our website: http://www.bov.de
> subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
>
> Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen!
> Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> mailto:[EMAIL PROTECTED]
>
> Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter
> fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde bitten
> wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen
> ausschliessen.
>
> As you are probably aware, e-mails sent via the Internet can easily be
> copied or manipulated by third parties. For this reason we would ask for
> your understanding that, for your own protection and ours, we must decline
> all legal responsibility for the validity of the statements and comments
> given above.
>
>
> -Ursprüngliche Nachricht-
> Von: Arup Nanda [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 21. März 2003 15:39
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: Remove killed session from v$session
>
>
> Stefan,
>
> The session stays in the KILLED status because of a lot of reasons - the
> primary being the rollback being performed for all the changes the session
> did. At some point the session rollback will be complete and the session
> will be removed. Don't worry about the session being there.
>
> HTH.
>
> Arup Nanda
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, March 21, 2003 8:53 AM
>
> > Hi list
> >
> > Is there any way to remove a killed session from v$session. Is it even
> > necessary to do that ?
> > I ran this:
> >
> > select spid, status, osuser, s.program from
> > v$process p, v$session s where p.addr=s.paddr
> >
> > To check for the killed processes' spid in order to remove the thread
>
> (it's
>
> > 9.2 on win2k) with orakill.
> > But for the killed process, no process is shown. So, what's left ? Should
>
> I
>
> > even bother or just wait
> > till Oracle removes the killed session ?
> >
> > Regards,
> > Stefan
> >
> > Stefan Jahnke
> > Consultant
> > BOV Aktiengesellschaft
> > Voice: +49 201 - 4513-298
> > Fax: +49 201 - 4513-149
> > mailto: [EMAIL PROTECTED]
> > Please remove nospam to contact me via email.
> >
> > visit our website: http://www.bov.de
> > subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
> >
> > Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
> > schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
> > mailto:[EMAIL PROTECTED]
> >
> > Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter
> > fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde bitten
> > wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
> > rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen
> > ausschliessen.
> >
> > As you are probably aware, e-mails sent via the Internet can easily be
> > copied or manipulated by third parties. For this reason we would ask for
> > your understanding that, for your own protection and ours, we must
> > decline all legal responsibility for the validity of the statements and
> > comments given above.
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Stefan Jahnke
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.

Re: Test

2003-03-24 Thread Ruth Gramolini

You passed!!  Ruth

  - Original Message !- 
  From: 
  Scott Stefick 
  To: Multiple recipients of list ORACLE-L 

  Sent: Friday, March 21, 2003 4:28 
PM
  Subject: Test
  This is a test, I have not received any messages in the last 
  couple of 
  days.-Scott**Scott 
  StefickOracle Certified DBAWm. Rainey Harper 
  College847.925.6130**-- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Scott 
  Stefick  INET: [EMAIL PROTECTED]Fat 
  City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT 
  spelling of 'ListGuru') and inthe message BODY, include a line containing: 
  UNSUB ORACLE-L(or the name of mailing list you want to be removed 
  from).  You mayalso send the HELP command for other information (like 
  subscribing).

Re: standby on SAN ? or use internal storage

2003-03-24 Thread Arup Nanda




Rahul,
 
The purpose of standby is to failover to it when 
the primary fails - which could range from the CPU failure to SAN failure. 
Although modern SANs are pretty robust, you account for the remote chance of 
failure by building a standby. Placing the database on teh same SAN as the 
primary does not really buy you any extra high availability feature, does it? 
You have a single point of failure, the SAN.
 
So your Standby should use storage not in the same 
place as the primary. However, using the standby server's internal disks could 
render your filesystems inaccessible if the server fails. But in some 
hosts, this is not a problem either; the SAs can mount the internal disks on 
another machine and recover data - check with your SA. If this is not the case, 
place the standby database on a different SAN.
 
So your preffered options are (in descending 
order)
 
(1) Primary Server - with two primary 
instances
Primary SAN with the two primary 
databases
Standby server 
Standby SAN
 
(2) Primary Server - with two primary instances
Primary SAN with the two primary 
databases
Standby server 
Internal storage on standby server
 
HTH.
 
Arup

  - Original Message - 
  From: 
  Rahul 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Monday, March 24, 2003 12:18 
  AM
  Subject: standby on SAN ? or use internal 
  storage
  
  list, i'm a bit confused on whether to put the 
  standby DB on the SAN storage or 
  use the internal storage of the standby host !!! 
  
   
  config a
  two instances on primary server, data-files on 
  SAN, hot standby db files on SAN too.
  standby instances for both primary on another 
  machine
   
  config b
  both primary on SAN, standby db files on the 
  itnernal storage of standby machine
   
  any thoughts  ? 
   
  TIA
  Rahul

Re:RE: pl/sql engine doubt

" c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting."

To put it MILDLY!!!

Dick Goulet



Reply Separator
Author: "Gogala; Mladen" <[EMAIL PROTECTED]>
Date:   3/24/2003 7:08 AM

Let me throw some shade onto the issue:
a) PL/SQL engine does not execute SQL statements - ever. It passes them 
   over to the SQL Executor engine.
b) PL/SQL is just a procedural enclosure of the SQL language. There is 
   another one: it's called Java.
c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting.

-Original Message-
Sent: Monday, March 24, 2003 4:29 AM
To: Multiple recipients of list ORACLE-L


Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920
/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sam d
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

PL/SQL can be seen as a stand-alone interpreted language and can as such
exist in various environments. "Various envrionments" does in real life
mean the PL/SQL engine is found in both the Oracle server and in the Oracle
Forms tool. If an application needs to execute some PL/SQL it should (at
least in principle) be able to execute this in any available environment
where the PL/SQL engine is present, in practice, however, this is only the
case in the database server and in Oracle Forms (plus probably Oracle Reports,
I really don't know, but it's beside the point). In practical terms, if
you are coding forms (plus probably reports), you have PL/SQL available in
the tool and in the database server; in all other cases, it's only in the
database server. Whenever PL/SQL needs to execute some SQL statements, it
will have to go to the Oracle server; if your PL/SQL happens to already execute
there, it is a simple internal context switch inside the server, if your
PL/SQL happens to execute in Oracle Forms (or reports), it will have to go
over your SQL*Net connection to execute the SQL code.

To confuse things somewhat, PL/SQL actually has a SQL parser; hence, PL/SQL
can verify SQL statements during parse without actually talking to the database.
This has some interesting side effects in version 8, where the SQL parser
inside PL/SQL tend to not have adopted all the latest SQL features, i.e.
there is valid SQL (in e.g. 8.1.7), that you cannot use in PL/SQL without
using dynamic SQL (either DBMS_SQL or native dynamic SQL).

/Bjørn.

sam d wrote:

Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."

para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."

Now my doubt is:
1.In para1 - Does pl/sql engine only processes
the procedural statements and always passes SQL
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous.
Pls shade some light.

Thanks
Sam

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

--
Bjørn Engsig, Miracle A/S
Member of Oak Table Network
[EMAIL PROTECTED] - http://MiracleAS.dk

RE: pl/sql engine doubt

Let me throw some shade onto the issue:
a) PL/SQL engine does not execute SQL statements - ever. It passes them 
   over to the SQL Executor engine.
b) PL/SQL is just a procedural enclosure of the SQL language. There is 
   another one: it's called Java.
c) Procedural part can be parsed either by an application tool or oracle
   kernel. The thing to watch for is the version of the PL/SQL engine
   embedded in the application tool. The ideal situation is when these
   two versions are the same. If they are not, life can get interesting.

-Original Message-
Sent: Monday, March 24, 2003 4:29 AM
To: Multiple recipients of list ORACLE-L


Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920
/a96624/01_oview.htm#962

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sam d
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Bind variable use in C++ SQL Calls

2003-03-24 Thread Bjørn Engsig





What is the type of your pDatabase class?  If it is an ODBC database container,
you should use the bind variable facility of ODBC, where you use a question-mark
as your placeholder.  Hence, in stead of assembling the SQL statement using
printf like formating, you merely put a number of ?'s in there as in

  select * from avamas where ava_nodeid = ? and ava_wkday = ? and ...

The ?'s are subsequently bound to program variables using the bind routines
of ODBC.  (I actaully don't know the ODBC API).

If your pDatabase class is not (derived from) ODBC, I presume it is something
of your own, that most likely is implemented using Oracle Call Interface,
OCI.  If that is the case, you need to augment your pDatabase class with
calls to the various bind routines of ODBC, and you need to change your code
to use standard style placeholders with : in stead of the ODBC-style ?, as
in:

    select * from avamas where ava_nodeid = :1 and ava_wkday = :2 and ..

/Bjørn.


Karen Morton wrote:

  All,

I've got an application that does not use bind variables.  The code is written 
in Microsoft Visual C++.  I have no background with C++ and need some help in 
telling the developers how to use bind variables in their code (they don't 
know and aren't sure how to find out).  I pulled the following examples out of 
the code for different ways they execute SQL.  If anyone can assist with 
specific examples on how to rewrite this to use bind variables, it would be 
immensely helpful.

Thanks,
Karen Morton



Samples
---
Mystring.Format("SELECT AVA_SERIAL FROM avamas \
WHERE  ava_tabname = 'sys_node' \
ANDava_nodeid  =  %-d  \
ANDava_wkday   =  %-d  \
ANDava_sdate   =  %s \
ANDava_stime   = '%-s'",

cAvalObject->cItemSerial, m_weekday, ConvertDateToODBCStr (pDatabase, 
m_sdatetime), m_schartime);

rSpanRecord.Open (CRecordset::forwardOnly, cSpanSelect);

if (rSpanRecord.IsEOF () == 0)
{   rSpanRecord.GetFieldValue ("AVA_SERIAL", vCDBVariant);

m_serial = atol (ObjectConvert (&vCDBVariant));

vCDBVariant.Clear ();
}

rSpanRecord.Close ();

CSysNumSet SysnumSet(pDatabase);
SysnumSet.m_TableParam = strFile; 
SysnumSet.m_strFilter = "myid = 1234 and yourmom = 'NICE'"

SysnumSet.Open();

if (SysnumSet.IsOpen())
   lNewSysNo = SysnumSet.m_file_identity;
else
   lNewSysNo = 0;


strSQL.Format("UPDATE sys_file WITH (ROWLOCK) SET file_identity = 
file_identity + 1 WHERE file_table = '%s' ",  strFile);

pDatabase->ExecuteSQL(strSQL);


  


-- 
 Bjørn Engsig, Miracle A/S 
 Member of Oak Table Network 
 [EMAIL PROTECTED] - http://MiracleAS.dk

RE: the number of concurrent queries

2003-03-24 Thread Stephane Paquette

select * from v$sysstat where NAME   in ('user commits','user rollbacks');



-Original Message-
Sent: Monday, March 24, 2003 7:54 AM
To: Multiple recipients of list ORACLE-L


I guess I have been using a "sql statement" and a 
"query" interchangebly. I am using status = 'ACTIVE'
to identify the sqls currently running.  For the user
commits and rollbacks, which table contain these
values?

thank you
gene
--- Stephane Paquette
<[EMAIL PROTECTED]> wrote:
> v$session.status indicates that a sql statement is
> going on.
> I do not know any indicator on query.
> 
> But you do have indicators on transactions.
> 
> The total transactions are the sum of user commits
> and user rollbacks.
> Check those 2 statistics.
> 
> Stephane
> 
> -Original Message-
> Sent: Friday, March 21, 2003 12:29 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Stephane:
> 
> Why do you think if would records the number of 
> concurrent transactions, but not the queries? what
> is
> the difference from the v$session standpoint/
> 
> 
> --- Stephane Paquette
> <[EMAIL PROTECTED]> wrote:
> > I do not think that you could record the number of
> > concurrent queries.
> > On the other hand,  you can record the number of
> > concurrent transactions.
> > That is what your query will do.
> > 
> > Another way is to use statspack.
> > We're running statspack on our 22 production
> > instances at each hour.
> > We're keeping 2 months of stats.
> > We have a central point collecting data through
> > views on statspack tables
> > and producing nice graphs 
> > 
> > My next job is to convince that we should monitor
> > jobs also.
> > 
> > 
> > Stephane
> > 
> > 
> > -Original Message-
> > Sent: Friday, March 21, 2003 9:24 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Hi.
> > 
> > I'm trying to get a feel of the number of the
> > queries
> > executed at the same time against a database. I
> have
> > been using the following script (actually this is
> a
> > part of a script)
> > 
> > Select a.type, count(1) col1
> > from v$session a,
> >  v$process b
> > where
> > a.paddr=b.addr
> > and a.status like 'ACTIVE%'
> > and a.type <> 'BACKGROUND'
> > and a.osuser <> 'oracle'
> > 
> > I want to see only ACTIVE sessions, not-background
> > and
> > not executed by oracle. Does anyone see anything
> > wrong
> > with that?
> > 
> > thanks
> > Gene
> > 
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Platinum - Watch CBS' NCAA March Madness,
> > live on your desktop!
> > http://platinum.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Gurelei
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and
> web
> > hosting services
> >
>
-
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > -- 
> > Author: Stephane Paquette
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and
> web
> > hosting services
> >
>
-
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> > 
> 
> 
> __
> Do you Yahoo!?
> Yahoo! Platinum - Watch CBS' NCAA March Madness,
> live on your desktop!
> http://platinum.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Gurelei
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 
> -- 
> Please see the official ORACLE-L F

RE: Oracle Interview questions for Implementation/Maintenence