date:20030113

Nirmal - I believe there are four:
db_name
control_file
db_block_size
compatibility
 
This is from John Hibbard, a great Oracle Education instructor.
But why not try for yourself? Save off your init.ora, then create a new
init.ora with just the above parameters. If Oracle comes up, then remove
parameters. If there is another parameter, Oracle will tell you.



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

-Original Message-
Sent: Monday, January 13, 2003 4:59 AM
To: Multiple recipients of list ORACLE-L


List,
 
I'm interested to know the minimum required parameters to startup the
database.
 
Pls anybody list out that?
 
Nirmal.,

-- 
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: Different Backups - A Comparartive analysis

Vivek - I believe you are off to a good start. I'm assuming you are
considering the database to be in archivelogmode in all the scenarios.
   Also, keep in mind that export is not a valid sole backup method for a
production database. There are many situations that having an export alone
will not enable you to recover. However, export is an excellent supplement
to the other backup methods.

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


-Original Message-
Sent: Monday, January 13, 2003 6:59 AM
To: Multiple recipients of list ORACLE-L



Need to Do a Comparative analysis of General Characteristics of HOT , COLD ,
exp , RMAN 
backups 

What additional Headings can be Explored ?

Some Feadings below :-

1) Database Status during backup - UP / Down
2) Time of Backup 
3) Restoration effort  time
4) Reovery facility
5) Dependency on Database Size 
6) Backup Devices - Can exp be taken Directly on TAPES  Extracted therefrom
confidently OR does ?
7) Ease of Backup 
8) De-Skilling of Backup Job
9) Dependency on Oracle Software 
10) Performance Overheads during Backup 
11) Incremental Backups - Do HOT / Cold Backups allow any incremental
Backups ?
Is incremental Backup Disabled from 8i onwards for exp ?
12) Limitations - Compress/Splitting of exp files etc ?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  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: Big Brother is Here

2003-01-13 Thread Todd Carlson

2 + 2 = 4

Todd


-Original Message-
[EMAIL PROTECTED]
Sent: Friday, January 10, 2003 12:46 PM
To: Multiple recipients of list ORACLE-L

OK, this is a little off topic, but if MicroSlop gets this to work, how
long
before OEM will have a link into the same technology??  I always thought
that
the computers were here to server us, not the other way around.  Just
what I
need, an OEM job blowing and my having to fix it from the head!!

Dick Goulet

Microsoft eyes global radio network to support smart devices

Microsoft plans to build a global FM radio network to support its Smart
Personal
 Objects Technology efforts, embedding an FM receiver that works
anywhere in the
 world.

http://computerworld.com/newsletter/0%2C4902%2C77442%2C0.html?nlid=AM
-- 
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: Todd Carlson
  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).

Oracle 7.3.4 Real-Time Re-indexing - Additional Information

2003-01-13 Thread Conrad Meertins


Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Conrad Meertins
  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).

Oracle 7.3.4 Real-Time Re-indexing

2003-01-13 Thread Conrad Meertins


Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Conrad Meertins
  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: Minimum required init.ora parameters

2003-01-13 Thread Hemant K Chitale


compatibility or compatible is not a mandatory init.ora parameter.
I would think that only the first three are required.

Hemant

At 06:08 AM 13-01-03 -0800, you wrote:

Nirmal - I believe there are four:
db_name
control_file
db_block_size
compatibility

This is from John Hibbard, a great Oracle Education instructor.
But why not try for yourself? Save off your init.ora, then create a new
init.ora with just the above parameters. If Oracle comes up, then remove
parameters. If there is another parameter, Oracle will tell you.



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

-Original Message-
Sent: Monday, January 13, 2003 4:59 AM
To: Multiple recipients of list ORACLE-L


List,

I'm interested to know the minimum required parameters to startup the
database.

Pls anybody list out that?

Nirmal.,

--
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).


Hemant K Chitale
My web site page is :  http://hkchital.tripod.com


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 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: Different Backups - A Comparartive analysis

To add to your list:

13) Is an additional volume of redo generated, resulting solely from being
in backup mode?
14) Is backup capable of any other purpose, such as checking for corruption
in database blocks and/or archivelog files?
15) Additional hidden costs of integrating with tape subsystem (i.e.
additional integration with media-management software)?
16) Is a consistent restore even possible when backup occurs while database
is being actively updated? (this is not the same as database status
up/down)

Another question:  should SQL BackTrack be included for consideration?

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 5:58 AM



 Need to Do a Comparative analysis of General Characteristics of HOT , COLD
, exp , RMAN
 backups

 What additional Headings can be Explored ?

 Some Feadings below :-

 1) Database Status during backup - UP / Down
 2) Time of Backup
 3) Restoration effort  time
 4) Reovery facility
 5) Dependency on Database Size
 6) Backup Devices - Can exp be taken Directly on TAPES  Extracted
therefrom confidently OR does ?
 7) Ease of Backup
 8) De-Skilling of Backup Job
 9) Dependency on Oracle Software
 10) Performance Overheads during Backup
 11) Incremental Backups - Do HOT / Cold Backups allow any incremental
Backups ?
 Is incremental Backup Disabled from 8i onwards for exp ?
 12) Limitations - Compress/Splitting of exp files etc ?


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: VIVEK_SHARMA
   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: Tim Gorman
  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).

Internet file system

2003-01-13 Thread Chris Stephens

Title: Internet file system






as per usual, technet website is confusing as heck. I am trying to locate the latest release on IFS. I am on the products website and see the following on the IFS webpage:

Content Management Software Development Kit (CMSDK)


Version 9.0.3
 (Note: this release requires Oracle 9.2 or Oracle 9.0.1)
Content Management Software Development Kit (CMSDK) version 9.0.3 for Windows 
Content Management Software Development Kit (CMSDK) version 9.0.3 for Linux
Content Management Software Development Kit (CMSDK) version 9.0.3 for HP-UX
Content Management Software Development Kit (CMSDK) version 9.0.3 for HP Tru64
Content Management Software Development Kit (CMSDK) version 9.0.3 for AIX



Oracle Internet File System Downloads


Version 9.0.2
Oracle Internet File System Release 9.0.2 can now be downloaded from the Oracle9iAS software page in the 'Supplemental CD's' section.

==


Has IFS been renamed to Content Management Software Development Kit?? or no?
I don't want to waste my time downloading what I think is IFS v9.0.3 when v9.0.2 is the latest release and CMSDK is just for developers working with IFS.

Thanks for your help.

compatible in 9iRel2

List, 

I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 Solaris
8 database.  If you bounce the instance the 
sqlshow parameter compatible=9.0.0  stays the same, it does not change.  If
you shutdown and reboot the server you get an:

ora-01033: Initialization or shutdown in progressWhich basically states
that you are trying to connect to an instance that is being shutdown down or
starting up.  I believe it goes into NOMOUNT stage and reads the init.ora
and hangs because of the compatible parameter being set to 8i. I saw the
document Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01
stating how to downgrade, but it seems to defeat the purpose of having a 9i
instance.   Is there any way to do this without stripping the 9i database
down to 8i?  


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: Recommendation for SQL Tuning Book

2003-01-13 Thread Igor Neyman

Check out this white paper by Tim Gorman:

http://www.evdbt.com/SearchIntelligenceCBO.doc.

There is (somewhere, don't remember URL) also a white paper by Mark Gurry,
the author of the book you bought.  BTW, I think the book is very good.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 4:38 AM



 Question for those who have gone before...

 Is there a consensus on the best book to use to teach myself SQL tuning
 under the cost-based optimiser, particularly the use of hints.

 I have lots of experience under the rule-based optimiser, so I need
 hard-core info on use of hints and other features, not the general
 principles of SQL.

 I have found Oracle SQL Tuning Pocket Reference by Mark Gurry which
looks
 like it will fit the bill.  Does anyone have a better suggestion?

 Thanks in advance.

 David Jones
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jones, 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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Conrad Meertins
  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).

8.1.6: possible to set role in db's logon trigger?

2003-01-13 Thread Pardee, Roy E

Greetings all,

I'm trying to support a COTS application that is back-end agnostic  makes
only minimal use of security on the db.  In particular, it requires that
users be granted a default role that has *very* heavy permissions--enough to
do some major mischief should they ever figure out how to use odbc or
sql*plus.

My collegues  I have devised a kludgy method for getting around this
problem, involving a shill startup program that turns the default-ness of
the role on  off in conjunction with users opening  closing the client
program.  This works, but is a pain to maintain.

I've recently discovered the v$session.program field  am now wondering
whether it would be possible to use the new-fangled logon system trigger to
set the role only for cases where v$session.program = the COTS client.

Can anybody comment as to whether this is a viable approach on an 8.1.6
database  if not, on a 9i db?

In particular, there are two things I don't know--first, how to select just
the one row in v$session that corresponds to the current connection.  If a
user was to start up the COTS client  then connect to the same db via
sql*plus, I would want the role set *only* for the COTS client session.  My
best thought so far here is to use the most recently started connection
based on v$session.logon_time.

Second, whether the SET ROLE statement is legal in a logon trigger.

All help will be most welcome.

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: Minimum required init.ora parameters

Hemant - I was just going from my hastily written class notes. Sorry about
misspelling the parameter. However, just tried it and 8.1.6 won't start
without the COMPATIBLE parameter set in init.ora.

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


-Original Message-
Sent: Monday, January 13, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L



compatibility or compatible is not a mandatory init.ora parameter.
I would think that only the first three are required.

Hemant

At 06:08 AM 13-01-03 -0800, you wrote:
Nirmal - I believe there are four:
 db_name
 control_file
 db_block_size
 compatibility

This is from John Hibbard, a great Oracle Education instructor.
But why not try for yourself? Save off your init.ora, then create a new
init.ora with just the above parameters. If Oracle comes up, then remove
parameters. If there is another parameter, Oracle will tell you.



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

-Original Message-
Sent: Monday, January 13, 2003 4:59 AM
To: Multiple recipients of list ORACLE-L


List,

I'm interested to know the minimum required parameters to startup the
database.

Pls anybody list out that?

Nirmal.,

--
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).

Hemant K Chitale
My web site page is :  http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  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: compatible in 9iRel2

David - What are you trying to accomplish?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


List, 

I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 Solaris
8 database.  If you bounce the instance the 
sqlshow parameter compatible=9.0.0  stays the same, it does not change.  If
you shutdown and reboot the server you get an:

ora-01033: Initialization or shutdown in progressWhich basically states
that you are trying to connect to an instance that is being shutdown down or
starting up.  I believe it goes into NOMOUNT stage and reads the init.ora
and hangs because of the compatible parameter being set to 8i. I saw the
document Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01
stating how to downgrade, but it seems to defeat the purpose of having a 9i
instance.   Is there any way to do this without stripping the 9i database
down to 8i?  


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).
-- 
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: Minimum required init.ora parameters

2003-01-13 Thread K Gopalakrishnan

The only one parameter.. control_files.
Rest are optional.

Best Regards,
K Gopalakrishnan

 


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 7:44 AM
To: Multiple recipients of list ORACLE-L


Hemant - I was just going from my hastily written class notes. Sorry about
misspelling the parameter. However, just tried it and 8.1.6 won't start
without the COMPATIBLE parameter set in init.ora.

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


-Original Message-
Sent: Monday, January 13, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L



compatibility or compatible is not a mandatory init.ora parameter.
I would think that only the first three are required.

Hemant

At 06:08 AM 13-01-03 -0800, you wrote:
Nirmal - I believe there are four:
 db_name
 control_file
 db_block_size
 compatibility

This is from John Hibbard, a great Oracle Education instructor.
But why not try for yourself? Save off your init.ora, then create a new
init.ora with just the above parameters. If Oracle comes up, then remove
parameters. If there is another parameter, Oracle will tell you.



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

-Original Message-
Sent: Monday, January 13, 2003 4:59 AM
To: Multiple recipients of list ORACLE-L


List,

I'm interested to know the minimum required parameters to startup the
database.

Pls anybody list out that?

Nirmal.,

--
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).

Hemant K Chitale
My web site page is :  http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  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://www.orafaq.net
-- 
Author: K Gopalakrishnan
  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: Daramtically improve BCHR with a single statement

2003-01-13 Thread Yechiel Adar

Hello All

I saw that piece of code a few times and still think that even if you can
bump up the BCHR it has it's place. How can you tell that you need more
space in the buffer pool? Bad BCHR is an indication that you need to check
this. It is also an indication that you do a lot of FTS or missing an index
and you SQL reads too many blocks.

Use the BCHR as an indication. When it goes down it is time to check.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 5:08 AM


 To add an example of what Anjo, Morgens and everyone else is talking
about,
 here is a perfect illustration of why focusing on BCHR is like
 concentrating intensely on how fast your tyres rotate in a Tour de France,
 instead of looking of where you are going (probably a lot more useful).
 Another example:
 If I raced (100M sprint) against Maurice Green, and he went off in the
 wrong direction, despite the fact that he is so much faster than me (duh
!)
 , I could lightly jog (as if I have anything else to offer) the 100M in
the
 right direction and beat him. Well, focusing on BCHR alone is like going
at
 full tilt with no direction.
 Also, I have realized that cars have been around for  100 years now, so
 why exactly would I want to sprint again ? :-)

 Check out this example:

 run any number of scripts to look at BCHR. Then run the following
anonymous
 PL/SQL block:

 declare
   jackass number;
 begin
  for jackass in 1..1000 loop
 execute immediate 'select count (*) from solvit.solvit_lic ' ; --
 replace this table with any single row table you like.
  end loop;
 end;
 /

 Check your BCHR again. Wow, amazing ! How much better your BCHR looks now.
 This must be magic. If you would like to purchase other such tools, please
 feel free to drop me a line, I could also sell you a large iceberg, which
 would end your personal water restriction problems.

 Another advantage to the above code is that it eliminates idle capacity
 from my CPU's (I paid for the thing, it should be put to work, right ? )
as
 my laptop has been at 100% CPU utilization for the last 8 minutes as I let
 this piece of crap run before I killed it (Oracle 9 on XP with 512 MB RAM
 [SGA 120 MB], with a bunch of other starved stuff running concurrently).

 Reduction of logical I/O : Now THERE is the holy grail worth pursuing ! I
 am sure we could have a VERY interesting discussion on that one !

 Feel free to use the above example to prove for once and for all that
 concetration on tuning BCHR alone is a fruitless exercise.

 Regards :

 Ferenc Mantfeld

 -Original Message-
 From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
 Sent: Monday, January 13, 2003 11:24 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: BCHR Tuning

 and those people sell a tuning tool hm, I hadn't noticed any
 selling going on here. Or perhaps it's been subliminal?


 --- Jared Still [EMAIL PROTECTED] wrote:
  On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
   Obviously, we don't know what we're talking about. I can see
  there's a
   presentation by Rich Niemich at IOUG-A where he'll address all
  those
   idiots who are saying you should ignore the Cash Hit Ratio (and who
  are
   all just after making big money on their products - I loved that
  one).
Or modify the set up of these tools to take action when BCHR
  falls..
   
 
  Here's the session info:
 
  Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
  Venue: Southern Hemisphere 2, Walt Disney World
 Dolphin, Lake Buena Vista, FL
 
  Abstract: Lately, there has been a big push to ignore your
  hit ratio with claims that it is meaningless. This shallow
  minded view (usually by people who sell a tuning tool) ignores
  why people look at hit ratios and what they are looking for.
  This quick tip talk will show you what to look for and why.
  You will definitely know when, where  why to look at your
  hit ratio in the future.
 
  Show you why your hit ratio matters. How to analyze the
  hit ratio. Fallacies by those who want to sell you products
  and tools instead.
 
 
  Shallow Minded ?!
 
  Jared
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Jared Still
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! Mail Plus - Powerful. Affordable. Sign up now.

Re: BCHR Tuning

2003-01-13 Thread Yechiel Adar

Hello Anjo

I just had a tuning session with Dov Hit, from ACS in Israel.
He used some of the scripts that you showed him 2 years ago when you did
some work for Amdocs.
Anyway, after doing some search on the waits, he checked the BCHR and found
out that this database has only 40%. That led us on further checks and we
found more offending SQL's.

The BCHR has it's place.
Just do not measure yourself JUST by it.


Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 3:03 AM


 Hmm,

 Lately? That actually started publicly in 1998 as far as I am concerned
;-)
 And acutally long before that.

 Anjo.

 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Sunday, January 12, 2003 11:43 PM


  On Friday 10 January 2003 14:48, Mogens Nørgaard wrote:
   Obviously, we don't know what we're talking about. I can see there's a
   presentation by Rich Niemich at IOUG-A where he'll address all those
   idiots who are saying you should ignore the Cash Hit Ratio (and who
are
   all just after making big money on their products - I loved that one).
Or modify the set up of these tools to take action when BCHR
 falls..
   
 
  Here's the session info:
 
  Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
  Venue: Southern Hemisphere 2, Walt Disney World
 Dolphin, Lake Buena Vista, FL
 
  Abstract: Lately, there has been a big push to ignore your
  hit ratio with claims that it is meaningless. This shallow
  minded view (usually by people who sell a tuning tool) ignores
  why people look at hit ratios and what they are looking for.
  This quick tip talk will show you what to look for and why.
  You will definitely know when, where  why to look at your
  hit ratio in the future.
 
  Show you why your hit ratio matters. How to analyze the
  hit ratio. Fallacies by those who want to sell you products
  and tools instead.
 
 
  Shallow Minded ?!
 
  Jared
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Jared Still
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: Anjo Kolk
   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: Yechiel Adar
  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: Minimum required init.ora parameters

2003-01-13 Thread Keith Moore

It seems like I remember misspelling 'control_file' once and discorvering
that even it has a default. Of course with Oracle, it could depend on the
version, O/S, patch level, phase of the moon, etc.

Keith

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 8:54 AM



 compatibility or compatible is not a mandatory init.ora parameter.
 I would think that only the first three are required.

 Hemant

 At 06:08 AM 13-01-03 -0800, you wrote:
 Nirmal - I believe there are four:
  db_name
  control_file
  db_block_size
  compatibility
 
 This is from John Hibbard, a great Oracle Education instructor.
 But why not try for yourself? Save off your init.ora, then create a new
 init.ora with just the above parameters. If Oracle comes up, then remove
 parameters. If there is another parameter, Oracle will tell you.
 
 
 
 Dennis Williams
 DBA, 40%OCP
 Lifetouch, Inc.
 [EMAIL PROTECTED]
 
 -Original Message-
 Sent: Monday, January 13, 2003 4:59 AM
 To: Multiple recipients of list ORACLE-L
 
 
 List,
 
 I'm interested to know the minimum required parameters to startup the
 database.
 
 Pls anybody list out that?
 
 Nirmal.,
 
 --
 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).

 Hemant K Chitale
 My web site page is :  http://hkchital.tripod.com


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Hemant K Chitale
   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).




The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged 
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

RE: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

2003-01-13 Thread Conrad Meertins

ALTER ...REBUILD..

Thanks

COnrad..


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
  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://www.orafaq.net
-- 
Author: Conrad Meertins
  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).

Dead shared server msgs in alert log + core dumps

2003-01-13 Thread [EMAIL PROTECTED]

Has anyone ever seen a similar message to the following showing up in the
alert log and creating dump/trace files:

Mon Jan 13 09:45:22 2003
found dead shared server 'S000', pid = (9, 13)
Mon Jan 13 10:13:46 2003
Errors in file /u01/app/oracle/admin/ndi/bdump/ndi_s000_3556.trc:
ORA-07445: exception encountered: core dump [000100E8EAE0] [SIGBUS]
[Invalid address alignment] [0x812EA10E] [] []


The trace file noted contains (partially) the following:

*** 2003-01-13 10:13:46.200
*** SESSION ID:(55.1326) 2003-01-13 10:13:46.181
Exception signal: 10 (SIGBUS), code: 1 (Invalid address alignment), addr:
0x812ea10e, PC: [0x100e8eae0, 000100E8EAE0]
*** 2003-01-13 10:13:46.202
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [000100E8EAE0] [SIGBUS]
[Invalid address alignment] [0x812EA10E] [] []
Current SQL statement for this session:
 select  . . . . . (and so on.)


I'm not sure if I need to be overly concerned or not as it doesn't seem to
be causing any problems of note at this time.  I haven't seen this
particular thing before.  But, I am curious as to what might be causing the
shared server to die.  any ideas or experience with this? 

There were several similar trace/dump files created over about a 2 hour
period, but it appears to have stopped for now.  BTW, the box is Sun OS
running Oracle 9.2.0.2.

Thanks,
Karen Morton




mail2web - Check your email from the web at
http://mail2web.com/ .


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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).

newbie question - still: please help

2003-01-13 Thread Daniel Wisser

hi!

a DBA inteds to speed up a script that is looping and
sending hundred thousands of sequential update statements like:

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

he suggests copying the table to a file, change it and then
load it into the DB again. i am strongly convinced that this
is nonsense.

what is the best way to go for a script like this, doing tons of
updates? (except convincing him to swith to sell burgers)


thx
daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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: 8.1.6: possible to set role in db's logon trigger?

2003-01-13 Thread Koivu, Lisa

Title: RE: 8.1.6: possible to set role in db's logon trigger?





Hi Roy, 


Note 122230.1 will answer your first question about session id's. 


Lisa Koivu
Oracle Dogbone Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063






-Original Message-
From: Pardee, Roy E [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L
Subject: 8.1.6: possible to set role in db's logon trigger?



Greetings all,


I'm trying to support a COTS application that is back-end agnostic  makes
only minimal use of security on the db. In particular, it requires that
users be granted a default role that has *very* heavy permissions--enough to
do some major mischief should they ever figure out how to use odbc or
sql*plus.


My collegues  I have devised a kludgy method for getting around this
problem, involving a shill startup program that turns the default-ness of
the role on  off in conjunction with users opening  closing the client
program. This works, but is a pain to maintain.


I've recently discovered the v$session.program field  am now wondering
whether it would be possible to use the new-fangled logon system trigger to
set the role only for cases where v$session.program = the COTS client.


Can anybody comment as to whether this is a viable approach on an 8.1.6
database  if not, on a 9i db?


In particular, there are two things I don't know--first, how to select just
the one row in v$session that corresponds to the current connection. If a
user was to start up the COTS client  then connect to the same db via
sql*plus, I would want the role set *only* for the COTS client session. My
best thought so far here is to use the most recently started connection
based on v$session.logon_time.


Second, whether the SET ROLE statement is legal in a logon trigger.


All help will be most welcome.


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: dw tool question

2003-01-13 Thread Bruce A. Bergman

We have a new datawarehouse project. I didn't involve in this project at
beginning. Right now, I was assigned to the group for picking a front
end reporting tool. So far I know the manager prefer brio, cognos and
business intelligent product.

Does anyone familiar with those tools? Can you give me some feedback as
dba standpoint of view, like how report distributed, power user vs end
user, performance and security advange or disadvantage, concerns?

Me! Me! Pick me!  (Oooh, a topic I can answer! :-)

If your manager likes Brio, then that is a plus.  Brio is a good product for any 
reporting from simple to moderately complex.  I've used it extensively and have always 
been impressed with how it is able to tackle tasks that seem to be out of their area 
of comfort.  The tool has a nice Portal-like ability that uses JavaScript internally 
to allow fairly complex customization.  They also have options for distributed 
reporting, push-vs-pull reporting, etc.  About the only negative I've seen with Brio 
is that they locally cache the hypercube.  Thus if you want your hypercube to be 
refreshed automatically, you either need their push technology or a different product. 
 If that doesn't matter to you (and in fact, many see that as a plus, since it makes 
for easy static snapshots), then it works great.  Obviously, their thin-client version 
does not store the hypercube locally.  It may store it on the server, I'm not sure.  
Their pricing is okay, and they do barter for lower prices.

As somone already pointed out, Business Objects is also a good choice for moderate to 
complex projects.  In my mind, the biggest problem with BO is that it takes sooo long 
to get going with their stuff.  You can get a simple portal up and going with a couple 
reports in Brio in a few days, without ever having touched their product before.  Try 
that in BO and you'll be there for at least double the time.  On the positive side, 
once you get proficient, BO scales a lot better, and can handle just about any 
reporting/DW needs you'll ever have.  Price is about the same, and they too barter.

Crystal Analysis is definitely the price-point winner, and a lot of people know how to 
use Crystal Reports, so it makes getting skilled labor easier.  Their product is 
definitely not as capable at the moderate-to-complex end, but for simple reporting 
it's about as easy as it comes.  I've just never been able to trust Seagate all that 
much.  They change their product names and offerings almost yearly, they don't give 
any warm fuzzies for continued existence of their products, and their pricing is 
haphazard and at the whim of whatever sales person you talk to.  I hate that.  Don't 
even ask me about the whole Crystal Info debacle or I'm going to get mad. ;-)

As a side note, I like (and have chosen) Sagent for data warehousing.  It's more than 
just a reporting tool, since it does the ETL side of things, schema operations, 
automation, Portal-like functions, etc.  But it's very powerful and blows the socks 
off of any piecemeal collection of software that does the same thing.  And lest we 
forget, Sagent used to be the engine for Oracle's data warehousing solution as 
recently as two years ago, so even Oracle thinks highly of them.

One last thought: if TWDI has a conference coming up in your area, it's worth going to 
if you can get there.  One of the presenters goes through a terrific in-depth 
evaluation of all the data warehousing tools on the market, including reporting tools. 
 Some say it's worth the cost of the conference in what you save buying products.

Anyhow, hope that helps.

thanks,
bruce

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bruce A. Bergman
  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: 8.1.6: possible to set role in db's logon trigger?

2003-01-13 Thread Thomas Day


To solve your first problem, correctly identifying the current session from
v$session, in your login trigger include:

  client_info_str := 'SOME_LITTERAL_' || LTRIM(dbms_random.value,'.');
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);

Then you can:

  SELECT program, username,
osuser, terminal, machine
  INTO loc_program, loc_username,
loc_osuser,loc_terminal,loc_machine
  FROM V$SESSION
  WHERE client_info=client_info_str;

I've never tried the SET ROLE in a login trigger but I don't know why it
wouldn't work.



   

  Pardee, Roy E  

  roy.e.pardeeTo:  Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  @lmco.com   cc: 

  Sent by: rootSubject: 8.1.6: possible to set role in 
db's logon trigger? 
   

   

  01/13/2003 10:43 

  AM   

  Please respond   

  to ORACLE-L  

   

   





Greetings all,

I'm trying to support a COTS application that is back-end agnostic  makes
only minimal use of security on the db.  In particular, it requires that
users be granted a default role that has *very* heavy permissions--enough
to
do some major mischief should they ever figure out how to use odbc or
sql*plus.

My collegues  I have devised a kludgy method for getting around this
problem, involving a shill startup program that turns the default-ness of
the role on  off in conjunction with users opening  closing the client
program.  This works, but is a pain to maintain.

I've recently discovered the v$session.program field  am now wondering
whether it would be possible to use the new-fangled logon system trigger to
set the role only for cases where v$session.program = the COTS client.

Can anybody comment as to whether this is a viable approach on an 8.1.6
database  if not, on a 9i db?

In particular, there are two things I don't know--first, how to select just
the one row in v$session that corresponds to the current connection.  If a
user was to start up the COTS client  then connect to the same db via
sql*plus, I would want the role set *only* for the COTS client session.  My
best thought so far here is to use the most recently started connection
based on v$session.logon_time.

Second, whether the SET ROLE statement is legal in a logon trigger.

All help will be most welcome.

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: Thomas Day
  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

RE: Minimum required init.ora parameters

Well, I just tried it on 8.1.8, and here is what I receive:

SVRMGR !cat inittest816.ora
control_files = (/oracle8/oradata/test816/control01.ctl,
/oracle8/oradata/te)
SVRMGR startup
ORACLE instance started.
Total System Global Area142688680 bytes
Fixed Size  94632 bytes
Variable Size91721728 bytes
Database Buffers 50331648 bytes
Redo Buffers   540672 bytes
ORA-01506: missing or illegal database name

-Original Message-
Sent: Monday, January 13, 2003 10:20 AM
To: Multiple recipients of list ORACLE-L


The only one parameter.. control_files.
Rest are optional.

Best Regards,
K Gopalakrishnan

 


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 7:44 AM
To: Multiple recipients of list ORACLE-L


Hemant - I was just going from my hastily written class notes. Sorry about
misspelling the parameter. However, just tried it and 8.1.6 won't start
without the COMPATIBLE parameter set in init.ora.

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


-Original Message-
Sent: Monday, January 13, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L



compatibility or compatible is not a mandatory init.ora parameter.
I would think that only the first three are required.

Hemant

At 06:08 AM 13-01-03 -0800, you wrote:
Nirmal - I believe there are four:
 db_name
 control_file
 db_block_size
 compatibility

This is from John Hibbard, a great Oracle Education instructor.
But why not try for yourself? Save off your init.ora, then create a new
init.ora with just the above parameters. If Oracle comes up, then remove
parameters. If there is another parameter, Oracle will tell you.



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

-Original Message-
Sent: Monday, January 13, 2003 4:59 AM
To: Multiple recipients of list ORACLE-L


List,

I'm interested to know the minimum required parameters to startup the
database.

Pls anybody list out that?

Nirmal.,

--
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).

Hemant K Chitale
My web site page is :  http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  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://www.orafaq.net
-- 
Author: K Gopalakrishnan
  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

RE: newbie question - still: please help

Daniel - Can you explain the two alternatives in a little more detail,
especially the difference between the two. What types of systems are
involved (Unix, Windows)? Thanks.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 



-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


hi!

a DBA inteds to speed up a script that is looping and
sending hundred thousands of sequential update statements like:

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

he suggests copying the table to a file, change it and then
load it into the DB again. i am strongly convinced that this
is nonsense.

what is the best way to go for a script like this, doing tons of
updates? (except convincing him to swith to sell burgers)


thx
daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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: newbie question - still: please help

2003-01-13 Thread Stephane Faroult

hi!

a DBA inteds to speed up a script that is looping
and
sending hundred thousands of sequential update
statements like:

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

he suggests copying the table to a file, change it
and then
load it into the DB again. i am strongly convinced
that this
is nonsense.

what is the best way to go for a script like this,
doing tons of
updates? (except convincing him to swith to sell
burgers)


thx
daniel

Would be curious to know your DBA's background. 
The most reasonable thing might be to size rollback segments as needed, and remove the 
loop (I guess your loop is here to enable you to commit regularly). The second best 
option would be (8.1.5 and over) to load PL/SQL arrays and do bulk updates (refer to 
the PL/SQL doc for bulk updates).
Files have their use, but not this one.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  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: 8.1.6: possible to set role in db's logon trigger?

2003-01-13 Thread Jamadagni, Rajendra

Title: RE: 8.1.6: possible to set role in db's logon trigger?





Roy,


this is in 9202 ... check the custom code for hash joins ... it has been working fine for us for  2 months ...


CREATE OR REPLACE TRIGGER SYSTEM.DBT_USERS_LOGON
AFTER LOGON ON DATABASE
--
DECLARE
CURSOR cur_sess IS
SELECT *
FROM v$session
WHERE AUDSID = USERENV('SESSIONID')
AND USERNAME NOT IN ('HEARTBEAT');
--
recSess cur_sess%ROWTYPE;
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
BEGIN
OPEN cur_sess;
FETCH cur_Sess INTO recSess;
CLOSE cur_sess;
--
INSERT INTO USER_LOGON_AUDIT
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM,
TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
VALUES
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser),
recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL);
COMMIT;
--
IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND
UPPER(recSess.machine) = 'IMAPPROD1' THEN
EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false';
END IF;
--
EXCEPTION
WHEN OTHERS THEN
NULL;
END DBT_USERS_LOGON;
/
Raj


__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: compatible in 9iRel2

David - Try COMPATIBLE=8.1.0

See the following link (you may have to patch it back together)
http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96530/migcompa.htm#1
009871

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

-Original Message-
Sent: Monday, January 13, 2003 11:16 AM
To: Multiple recipients of list ORACLE-L


I am trying to apply a fix for some developers here.  They used this fix
in 8 and 8i by changing the compatible parameter and running their sql
script.  I just want to be able to tell them that they need to fix their
application now that we are using 9iRel2.  I tried this morning and got the
ORA-402 and ORA-405 errors.  I don't want to strip away features of 9i just
to get their security script to run.
thanks,
David Ehresmann.

-Original Message-
Sent: Monday, January 13, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L


David - What are you trying to accomplish?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


List, 

I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 Solaris
8 database.  If you bounce the instance the 
sqlshow parameter compatible=9.0.0  stays the same, it does not change.  If
you shutdown and reboot the server you get an:

ora-01033: Initialization or shutdown in progressWhich basically states
that you are trying to connect to an instance that is being shutdown down or
starting up.  I believe it goes into NOMOUNT stage and reads the init.ora
and hangs because of the compatible parameter being set to 8i. I saw the
document Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01
stating how to downgrade, but it seems to defeat the purpose of having a 9i
instance.   Is there any way to do this without stripping the 9i database
down to 8i?  


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).
-- 
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: 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).
-- 
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: compatible in 9iRel2

I am trying to apply a fix for some developers here.  They used this fix
in 8 and 8i by changing the compatible parameter and running their sql
script.  I just want to be able to tell them that they need to fix their
application now that we are using 9iRel2.  I tried this morning and got the
ORA-402 and ORA-405 errors.  I don't want to strip away features of 9i just
to get their security script to run.
thanks,
David Ehresmann.

-Original Message-
Sent: Monday, January 13, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L


David - What are you trying to accomplish?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


List, 

I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 Solaris
8 database.  If you bounce the instance the 
sqlshow parameter compatible=9.0.0  stays the same, it does not change.  If
you shutdown and reboot the server you get an:

ora-01033: Initialization or shutdown in progressWhich basically states
that you are trying to connect to an instance that is being shutdown down or
starting up.  I believe it goes into NOMOUNT stage and reads the init.ora
and hangs because of the compatible parameter being set to 8i. I saw the
document Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01
stating how to downgrade, but it seems to defeat the purpose of having a 9i
instance.   Is there any way to do this without stripping the 9i database
down to 8i?  


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).
-- 
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: 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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

Conrad - I haven't used 7.3.4 in quite some time, so my knowledge is a
little rusty. If you search METALINK, you will find quite a few issues with
this command on various Oracle versions. I believe that the basic principle
is the same - Oracle builds a new index in the background, syncs up any
table changes that have been made since the index build began, then switches
which index is being used for queries. I think I heard somewhere that the
locking/syncing mechanism has been improved in more recent Oracle versions.
Perhaps someone else on the list has some specific knowledge. The database
doesn't need to be in exclusive mode, but common sense would be to avoid
rebuilding when the table is being heavily modified. Once you complete, you
can ANALYZE INDEX VALIDATE STRUCTURE just to be sure you don't have any
problems.

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


-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


ALTER ...REBUILD..

Thanks

COnrad..


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
  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://www.orafaq.net
-- 
Author: Conrad Meertins
  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).

removing duplicate entries from a table

2003-01-13 Thread Mark Warner

Hi List

Is there a tool for removing duplicate entries from a table - except that
the key field is not the same. I ran some data imports more than once and
was hoping someone could point me in the direction of a way to undo my
mistake.

Any help will be most appreciated

Thanks
Mark Warner

__
The information contained in this communication is confidential and
may be legally privileged.  It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it.  If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action
in reliance of the contents of this information is strictly prohibited
and may be unlawful.  Absa is liable neither for the proper, complete
transmission of the information contained in this communication, nor 
for any delay in its receipt, nor for the assurance that it is 
virus-free.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Warner
  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: Daramtically improve BCHR with a single statement

How can you tell that you need more space in the buffer pool?

...You can notice when this is true by observing summarized extended SQL
trace (event 10046) data. If the LIO count for a session is small, but
the PIO count is large, then you have the problem.

So, sure... Checking a session's BCHR is okay. Just don't try to
maximize the BCHR before trying to minimize whatever it is that's
consuming the majority of the session's response time.

I have learned that paying much attention to a *system*'s BCHR (or any
other system-wide statistic for that matter) is a step that is prone to
causing bad decisions.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
Adar
Sent: Monday, January 13, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L

Hello All

I saw that piece of code a few times and still think that even if you
can
bump up the BCHR it has it's place. How can you tell that you need more
space in the buffer pool? Bad BCHR is an indication that you need to
check
this. It is also an indication that you do a lot of FTS or missing an
index
and you SQL reads too many blocks.

Use the BCHR as an indication. When it goes down it is time to check.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 5:08 AM


 To add an example of what Anjo, Morgens and everyone else is talking
about,
 here is a perfect illustration of why focusing on BCHR is like
 concentrating intensely on how fast your tyres rotate in a Tour de
France,
 instead of looking of where you are going (probably a lot more
useful).
 Another example:
 If I raced (100M sprint) against Maurice Green, and he went off in the
 wrong direction, despite the fact that he is so much faster than me
(duh
!)
 , I could lightly jog (as if I have anything else to offer) the 100M
in
the
 right direction and beat him. Well, focusing on BCHR alone is like
going
at
 full tilt with no direction.
 Also, I have realized that cars have been around for  100 years now,
so
 why exactly would I want to sprint again ? :-)

 Check out this example:

 run any number of scripts to look at BCHR. Then run the following
anonymous
 PL/SQL block:

 declare
   jackass number;
 begin
  for jackass in 1..1000 loop
 execute immediate 'select count (*) from solvit.solvit_lic ' ; --
 replace this table with any single row table you like.
  end loop;
 end;
 /

 Check your BCHR again. Wow, amazing ! How much better your BCHR looks
now.
 This must be magic. If you would like to purchase other such tools,
please
 feel free to drop me a line, I could also sell you a large iceberg,
which
 would end your personal water restriction problems.

 Another advantage to the above code is that it eliminates idle
capacity
 from my CPU's (I paid for the thing, it should be put to work, right ?
)
as
 my laptop has been at 100% CPU utilization for the last 8 minutes as I
let
 this piece of crap run before I killed it (Oracle 9 on XP with 512 MB
RAM
 [SGA 120 MB], with a bunch of other starved stuff running
concurrently).

 Reduction of logical I/O : Now THERE is the holy grail worth pursuing
! I
 am sure we could have a VERY interesting discussion on that one !

 Feel free to use the above example to prove for once and for all that
 concetration on tuning BCHR alone is a fruitless exercise.

 Regards :

 Ferenc Mantfeld

 -Original Message-
 From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
 Sent: Monday, January 13, 2003 11:24 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: BCHR Tuning

 and those people sell a tuning tool hm, I hadn't noticed any
 selling going on here. Or perhaps it's been subliminal?


 --- Jared Still [EMAIL PROTECTED] wrote:
  On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
   Obviously, we don't know what we're talking about. I can see
  there's a
   presentation by Rich Niemich at IOUG-A where he'll address all
  those
   idiots who are saying you should ignore the Cash Hit Ratio (and
who
  are
   all just after making big money on their products - I loved that
  one).
Or modify the set up of these tools to take action when BCHR
  falls..
   
 
  Here's the session info:
 
  Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
  Venue: Southern Hemisphere 2, Walt Disney World
 Dolphin, Lake Buena Vista, FL
 
  Abstract: Lately, there has been a big push to ignore your
  hit ratio with claims that it is meaningless. This shallow
  minded view (usually by people who sell a tuning tool) ignores
  why people look at hit ratios and what they are looking for.
  This quick tip talk will show you what to look for and why.
  You will definitely know when, where  why to look at your
  hit ratio in the future.
 
  Show you why your hit ratio matters. How to analyze the
  hit ratio. Fallacies by those who

RE: dw tool question

2003-01-13 Thread Koivu, Lisa

Title: RE: dw tool question





This is something I am currently dealing with. 


We have two products here: Business Objects and BRIO. It depends on what kind of end-user you expect to support. The main difference I see between these two is that Business Objects can easily hide the metadata detail and joins from the L-user. However, this requires the use of a repository to store the data. Note: Access does NOT WORK FOR THIS - there were lock problems. 

Brio requires knowledge of the ERD, and therefore a more skilled user. 


An example: A programmer that didn't understand the ERD wrote a query with Brio and didn't know why she was getting 64 rows per row in another table. Well, it was because she forgot to put a condition on the time stamp. And she missed a couple of other joins as well. Sure, she understood once I explained the ERD to her, and suddenly the query ran just fine, returning the correct results. When we turn this loose to our user community, there will be 3 programmers assigned to Brio reporting. With Business Objects, the user community had been running their own reports for years with a very simple universe. 

So I guess it's dependent upon what kind of end-user you plan to support. If you have some really savvy users, Brio is a good choice. If you have users who expect to just refresh and get their report without wanting to know why and how, then BO fits the bill. 

We are being pushed away from BO to Brio and I don't like it... I just remind myself that it's just a job, sigh and remind myself that every company makes dumb decisions like this. 

Oh well. Another day, another grey hair. 


Bruce, in your experience, has Brio been customized to the extent that I'm describing above with BO? I'd love to hear your comments, on the list or off. 

Thank you


Lisa Koivu
Oracle Babytoy Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063







-Original Message-
From: Bruce A. Bergman [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: dw tool question



We have a new datawarehouse project. I didn't involve in this project at
beginning. Right now, I was assigned to the group for picking a front
end reporting tool. So far I know the manager prefer brio, cognos and
business intelligent product.

Does anyone familiar with those tools? Can you give me some feedback as
dba standpoint of view, like how report distributed, power user vs end
user, performance and security advange or disadvantage, concerns?


Me! Me! Pick me! (Oooh, a topic I can answer! :-)


If your manager likes Brio, then that is a plus. Brio is a good product for any reporting from simple to moderately complex. I've used it extensively and have always been impressed with how it is able to tackle tasks that seem to be out of their area of comfort. The tool has a nice Portal-like ability that uses _javascript_ internally to allow fairly complex customization. They also have options for distributed reporting, push-vs-pull reporting, etc. About the only negative I've seen with Brio is that they locally cache the hypercube. Thus if you want your hypercube to be refreshed automatically, you either need their push technology or a different product. If that doesn't matter to you (and in fact, many see that as a plus, since it makes for easy static snapshots), then it works great. Obviously, their thin-client version does not store the hypercube locally. It may store it on the server, I'm not sure. Their pricing is okay, and they do barter for lower prices.

As somone already pointed out, Business Objects is also a good choice for moderate to complex projects. In my mind, the biggest problem with BO is that it takes sooo long to get going with their stuff. You can get a simple portal up and going with a couple reports in Brio in a few days, without ever having touched their product before. Try that in BO and you'll be there for at least double the time. On the positive side, once you get proficient, BO scales a lot better, and can handle just about any reporting/DW needs you'll ever have. Price is about the same, and they too barter.

Crystal Analysis is definitely the price-point winner, and a lot of people know how to use Crystal Reports, so it makes getting skilled labor easier. Their product is definitely not as capable at the moderate-to-complex end, but for simple reporting it's about as easy as it comes. I've just never been able to trust Seagate all that much. They change their product names and offerings almost yearly, they don't give any warm fuzzies for continued existence of their products, and their pricing is haphazard and at the whim of whatever sales person you talk to. I hate that. Don't even ask me about the whole Crystal Info debacle or I'm going to get mad. ;-)

As a side note, I like (and have chosen) Sagent for data warehousing. It's more than just a reporting tool, since it does the ETL side of

Re: BCHR Tuning

2003-01-13 Thread Rajesh . Rao


I too think the BCHR has its place, as a problem indicator. It can tell me
theres something wrong with my database. Say, I have this database
performing well, the users are happy, the BHR is mostly at 90%, and now it
suddenly shoots down to 70%, or it suddenly increases to 98. Somethings
amiss. Its less tasking, to code for scripts that query v$sysstat to
indicate me of some problems, rather than querying v$sqlarea. Or I need to
code for some intelligent scripts to query v$session_wait or
V$system_event. Or I need to look at the statspack reports every hour. The
point is when do I look at wait events? When the user calls me up?

All the papers out there, asking us rightly, to look at wait events, trash
the BCHR. I think what the authors intended was to tell us that increasing
DB_BLOCK_BUFFERS was not the solution to a low BCHR, and that a BCHR of 99%
does not mean a highly efficient database. Vice Versa, a BCHR of 50% does
not indicate a poorly performing database. Give me a database with a 45%
BHR, and I can get it to 99% by running a few queries. Point well
understood. It does not mean in any way that I should now ignore PIO's and
start tuning LIO's.

I still use BCHR. What you infer from the BCHR is what counts.

Raj





   
 
Yechiel   
 
AdarTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
adar76@intercc:   
 
.net.il Subject: Re: BCHR Tuning  
 
Sent by:   
 
root@fatcity.  
 
com
 
   
 
   
 
January 13,
 
2003 10:58 AM  
 
Please 
 
respond to 
 
ORACLE-L   
 
   
 
   
 




Hello Anjo

I just had a tuning session with Dov Hit, from ACS in Israel.
He used some of the scripts that you showed him 2 years ago when you did
some work for Amdocs.
Anyway, after doing some search on the waits, he checked the BCHR and found
out that this database has only 40%. That led us on further checks and we
found more offending SQL's.

The BCHR has it's place.
Just do not measure yourself JUST by it.


Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 3:03 AM


 Hmm,

 Lately? That actually started publicly in 1998 as far as I am concerned
;-)
 And acutally long before that.

 Anjo.

 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Sunday, January 12, 2003 11:43 PM


  On Friday 10 January 2003 14:48, Mogens Nørgaard wrote:
   Obviously, we don't know what we're talking about. I can see there's
a
   presentation by Rich Niemich at IOUG-A where he'll address all those
   idiots who are saying you should ignore the Cash Hit Ratio (and who
are
   all just after making big money on their products - I loved that
one).
Or modify the set up of these tools to take action when BCHR
 falls..
   
 
  Here's the session info:
 
  Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
  Venue: Southern Hemisphere 2, Walt Disney World
 Dolphin, Lake Buena Vista, FL
 
  Abstract: Lately, there has been a big push to ignore your
  hit ratio with claims that it is meaningless. This shallow
  minded view (usually by people who sell a tuning tool) ignores
  why people look at hit ratios and what they are looking for.
  This quick tip talk will show you what to look for and why.
  You will

RE: Recommendation for SQL Tuning Book

The Pocket Reference has been helpful to many people. The book appears
to be at least a handy summary of some relevant aspects of Oracle's
documentation. However, various colleagues have convinced me that there
are many statements in the book that you can disprove using careful
tests. 


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
David
Sent: Monday, January 13, 2003 3:39 AM
To: Multiple recipients of list ORACLE-L


Question for those who have gone before...

Is there a consensus on the best book to use to teach myself SQL tuning
under the cost-based optimiser, particularly the use of hints.

I have lots of experience under the rule-based optimiser, so I need
hard-core info on use of hints and other features, not the general
principles of SQL.

I have found Oracle SQL Tuning Pocket Reference by Mark Gurry which
looks
like it will fit the bill.  Does anyone have a better suggestion?

Thanks in advance.

David Jones
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jones, 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  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: removing duplicate entries from a table

2003-01-13 Thread MURAT BALKAS


Hi,

  following metalink documents may help you.

Note:65080.1  Using SQL To Delete Duplicate Rows In A Table

PR:1015631.6  HOW TO SELECT DUPLICATE ROWS WITHOUT USING ROWID

PR:1004425.6  HOW TO FIND OR DELETE DUPLICATE ROWS IN TABLE

Murat



   

  Mark Warner

  [EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]   
  o.zacc: 

  Sent by: Subject:  removing duplicate entries 
from a table   
  [EMAIL PROTECTED] 

   

   

  01/13/2003 07:35 

  PM   

  Please respond to

  ORACLE-L 

   

   





Hi List

Is there a tool for removing duplicate entries from a table - except that
the key field is not the same. I ran some data imports more than once and
was hoping someone could point me in the direction of a way to undo my
mistake.

Any help will be most appreciated

Thanks
Mark Warner

__
The information contained in this communication is confidential and
may be legally privileged.  It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it.  If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action
in reliance of the contents of this information is strictly prohibited
and may be unlawful.  Absa is liable neither for the proper, complete
transmission of the information contained in this communication, nor
for any delay in its receipt, nor for the assurance that it is
virus-free.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Warner
  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: MURAT BALKAS
  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: compatible in 9iRel2

2003-01-13 Thread John Shaw




I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
never really needed it so I haven't bothered with metalink/tar.
 [EMAIL PROTECTED] 01/13/03 11:16AM I 
am trying to apply a "fix" for some developers here. They used this 
fixin 8 and 8i by changing the compatible parameter and running their 
sqlscript. I just want to be able to tell them that they need to fix 
theirapplication now that we are using 9iRel2. I tried this morning 
and got theORA-402 and ORA-405 errors. I don't want to strip away 
features of 9i justto get their security script to run.thanks,David 
Ehresmann.-Original Message-Sent: Monday, January 13, 2003 
9:55 AMTo: Multiple recipients of list ORACLE-LDavid - What are 
you trying to accomplish?Dennis WilliamsDBA, 40%OCPLifetouch, 
Inc.[EMAIL PROTECTED] -Original Message-Sent: 
Monday, January 13, 2003 8:44 AMTo: Multiple recipients of list 
ORACLE-LList, I recently tried to set the compatible=8.1.7.0 
parameter in a 9iRel2 Solaris8 database. If you bounce the instance 
the sqlshow parameter compatible=9.0.0 stays the same, it does not 
change. Ifyou shutdown and reboot the server you get 
an:ora-01033: Initialization or shutdown in progress 
Which basically statesthat you are trying to connect to an instance that is 
being shutdown down orstarting up. I believe it goes into NOMOUNT 
stage and reads the init.oraand hangs because of the compatible parameter 
being set to 8i. I saw thedocument Oracle9i Database Migration Release 2 
(9.2) Part Number A96530-01stating how to downgrade, but it seems to defeat 
the purpose of having a 9iinstance. Is there any way to do this 
without stripping the 9i databasedown to 8i? 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.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).-- 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.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).-- 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.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: bitmapped indexes


Andrey,

Perhaps you could tell us what paper, and where to obtain it.

From the context, it sounds like a reference to physical modifications
rather than DML.

Jared

On Monday 13 January 2003 07:54, Andrey Bronfin wrote:
 Dear gurus !
 A ( maybe ) stupid question : I always thought that bitmapped indexes are
 bad for tables that undergo many DMLs agains them.
 Today i have came across an Oracle white paper, which says modifications
 on tables with bitmap indexes can be done a lot faster than modifications
 with B-tree indexes..
 Can you please sched some light on the matter ?
 Thanks a lot.
 Andrey,.


Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information


Though I'm not sure, I *believe* that in 7.3 indexes must
be taken offline during a rebuild.

That being the case, your users queries would not have
executed properly.  i.e.  FTS would take place for queries
that normally used indexed access.

Jared

On Monday 13 January 2003 08:14, Conrad Meertins wrote:
 ALTER ...REBUILD..

 Thanks

 COnrad..


 -Original Message-
 WILLIAMS
 Sent: Monday, January 13, 2003 10:19 AM
 To: Multiple recipients of list ORACLE-L


 Conrad - What command did you use to re-index? Drop index / create index?

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


 -Original Message-
 Sent: Monday, January 13, 2003 8:44 AM
 To: Multiple recipients of list ORACLE-L



 Sorry, I forgot this additional infromation...

 Oracle created invalid objects when we ran rebuild.  The odd thing was that
 the trace file showed an invalid object but the all_objects table showed a
 valid object.



 -Original Message-
 Sent: Monday, January 13, 2003 9:39 AM
 To: [EMAIL PROTECTED]



 Team,

 I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
 While the users were on the system, we Re-Indexed our tables.
 Situation: The users complained, that they were unable to process their
 orders.
 Although, the log file showed that the re-indexing was successful, the
 users were still unable to process their orders.


 QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
 version 7.x?

 Should you have the database in exclusive mode when re-indexing?

 Please assist.

 Thanks

 Conrad Meertins

 [EMAIL PROTECTED]

 DBA Masters


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Conrad Meertins
   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://www.orafaq.net
-- 
Author: Jared Still
  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: Different Backups - A Comparartive analysis

On Monday 13 January 2003 06:03, Tim Gorman wrote:
 Another question:  should SQL BackTrack be included for consideration?

What does SQL BackTrack to that RMAN doesn't do?

Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: DB Triggers vs Stored Procedures


In a nutshell, it is better to put your code
in a stored procedure outside of the trigger.

Why?  Modular code is easier to reuse.  You can't
reuse code blocks that are in a trigger.  There are 
other reasons, but this is the biggy IMO.

As Ferenc recommended, get the Feurstein book.

Jared

On Sunday 12 January 2003 22:03, [EMAIL PROTECTED] 
wrote:
 Hi  All
 I would like to know the difference between using the Stored procedures in
 DB Triggers and writing the code directly in the DB Trigger. Which would be
 better to use and what r the advantages.
 Rgds
 Sathya
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Oracle 7.3.4 Real-Time Re-indexing

2003-01-13 Thread M Rafiq

Have you used parallel clause while rebuilding? What version of 7.3.4 ?

Check alert_SID.log for any possible index corruption
If any , then you have to check for table and all indexes on that. Find 
index/es with multiple keys , save their defination through export , drop 
those indexes and create again...Check for invalids (using dba_objects) and 
recompile

You can analyze table by using 'analyze table table_name  validate structure 
cascade; and check alert_log. for possible corruption of that table 
indexes

If you have any any specific question then please let me know...

Regards
Rafiq







Have y


Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Mon, 13 Jan 2003 06:38:46 -0800


Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
  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).

_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE* 
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: M Rafiq
 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: Daramtically improve BCHR with a single statement


So, if I coerce the developers into writing better code that
does less logical IO, and the BCHR goes down, I should
then investigate and fix the problem?   ;)

Jared

On Monday 13 January 2003 08:09, Yechiel Adar wrote:
 Hello All

 I saw that piece of code a few times and still think that even if you can
 bump up the BCHR it has it's place. How can you tell that you need more
 space in the buffer pool? Bad BCHR is an indication that you need to check
 this. It is also an indication that you do a lot of FTS or missing an index
 and you SQL reads too many blocks.

 Use the BCHR as an indication. When it goes down it is time to check.

 Yechiel Adar
 Mehish
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Monday, January 13, 2003 5:08 AM

  To add an example of what Anjo, Morgens and everyone else is talking

 about,

  here is a perfect illustration of why focusing on BCHR is like
  concentrating intensely on how fast your tyres rotate in a Tour de
  France, instead of looking of where you are going (probably a lot more
  useful). Another example:
  If I raced (100M sprint) against Maurice Green, and he went off in the
  wrong direction, despite the fact that he is so much faster than me (duh

 !)

  , I could lightly jog (as if I have anything else to offer) the 100M in

 the

  right direction and beat him. Well, focusing on BCHR alone is like going

 at

  full tilt with no direction.
  Also, I have realized that cars have been around for  100 years now, so
  why exactly would I want to sprint again ? :-)
 
  Check out this example:
 
  run any number of scripts to look at BCHR. Then run the following

 anonymous

  PL/SQL block:
 
  declare
jackass number;
  begin
   for jackass in 1..1000 loop
  execute immediate 'select count (*) from solvit.solvit_lic ' ; --
  replace this table with any single row table you like.
   end loop;
  end;
  /
 
  Check your BCHR again. Wow, amazing ! How much better your BCHR looks
  now. This must be magic. If you would like to purchase other such tools,
  please feel free to drop me a line, I could also sell you a large
  iceberg, which would end your personal water restriction problems.
 
  Another advantage to the above code is that it eliminates idle capacity
  from my CPU's (I paid for the thing, it should be put to work, right ? )

 as

  my laptop has been at 100% CPU utilization for the last 8 minutes as I
  let this piece of crap run before I killed it (Oracle 9 on XP with 512 MB
  RAM [SGA 120 MB], with a bunch of other starved stuff running
  concurrently).
 
  Reduction of logical I/O : Now THERE is the holy grail worth pursuing ! I
  am sure we could have a VERY interesting discussion on that one !
 
  Feel free to use the above example to prove for once and for all that
  concetration on tuning BCHR alone is a fruitless exercise.
 
  Regards :
 
  Ferenc Mantfeld
 
  -Original Message-
  From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
  Sent: Monday, January 13, 2003 11:24 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: BCHR Tuning
 
  and those people sell a tuning tool hm, I hadn't noticed any
  selling going on here. Or perhaps it's been subliminal?
 
  --- Jared Still [EMAIL PROTECTED] wrote:
   On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
Obviously, we don't know what we're talking about. I can see
  
   there's a
  
presentation by Rich Niemich at IOUG-A where he'll address all
  
   those
  
idiots who are saying you should ignore the Cash Hit Ratio (and who
  
   are
  
all just after making big money on their products - I loved that
  
   one).
  
 Or modify the set up of these tools to take action when BCHR
  
   falls..
  
  
   Here's the session info:
  
   Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
   Venue: Southern Hemisphere 2, Walt Disney World
  Dolphin, Lake Buena Vista, FL
  
   Abstract: Lately, there has been a big push to ignore your
   hit ratio with claims that it is meaningless. This shallow
   minded view (usually by people who sell a tuning tool) ignores
   why people look at hit ratios and what they are looking for.
   This quick tip talk will show you what to look for and why.
   You will definitely know when, where  why to look at your
   hit ratio in the future.
  
   Show you why your hit ratio matters. How to analyze the
   hit ratio. Fallacies by those who want to sell you products
   and tools instead.
  
  
   Shallow Minded ?!
  
   Jared
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Jared Still
 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

Re: compatible in 9iRel2


You need to create the database with compatible set
to 8.1.7.0.  You can't create a database at one version
and then lower the compatible level without downgrading
or recreating.


Jared

On Monday 13 January 2003 09:16, Ehresmann, David wrote:
 I am trying to apply a fix for some developers here.  They used this fix
 in 8 and 8i by changing the compatible parameter and running their sql
 script.  I just want to be able to tell them that they need to fix their
 application now that we are using 9iRel2.  I tried this morning and got the
 ORA-402 and ORA-405 errors.  I don't want to strip away features of 9i just
 to get their security script to run.
 thanks,
 David Ehresmann.

 -Original Message-
 Sent: Monday, January 13, 2003 9:55 AM
 To: Multiple recipients of list ORACLE-L


 David - What are you trying to accomplish?

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


 -Original Message-
 Sent: Monday, January 13, 2003 8:44 AM
 To: Multiple recipients of list ORACLE-L


 List,

 I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2
 Solaris 8 database.  If you bounce the instance the
 sqlshow parameter compatible=9.0.0  stays the same, it does not change. 
 If you shutdown and reboot the server you get an:

 ora-01033: Initialization or shutdown in progressWhich basically states
 that you are trying to connect to an instance that is being shutdown down
 or starting up.  I believe it goes into NOMOUNT stage and reads the
 init.ora and hangs because of the compatible parameter being set to 8i. I
 saw the document Oracle9i Database Migration Release 2 (9.2) Part Number
 A96530-01 stating how to downgrade, but it seems to defeat the purpose of
 having a 9i instance.   Is there any way to do this without stripping the
 9i database down to 8i?


 David Ehresmann
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: newbie question - still: please help

Daniel,

Your gut reaction is right on-target.  It is always a struggle to keep
certain folks from killing the entire village while trying to cure a single
case of the sniffles.  What's worse is that such folks are usually quite
bright and talented.  After, very few mediocre folks can either cure the
sniffles or kill entire villages...  ;-)

Longer response:  This is a common argument that eventually distills down to
something like I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, Java.  What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into problems such
as transaction recoverability (aka rollback), concurrency, and its close
cousin read-consistency.  Oh yeah, and then there is also what my good
friend Gary once called DFB or diddly file build-up (i.e. an excess of
diddly files in a file-system), which very few people see up front but
invariably grows to dominate such approaches.  After some decades of effort
by thousands of developers and designers (very few of whom are stupid), what
results is the modern database engine.  Such people who think they can
outperform database engines without losing such crucial features do not have
any sense of humility about their place in the world.  Ask him to skim
through Gray and Reuter's Principles of Transaction Processing to gain
some of that humility...

Shorter response:  look into using PL/SQL bulk-bind operations (i.e. FORALL,
BULK COLLECT, etc) instead of one-row-at-a-time processing.

I suspect the latter approach will be more effective...  :-)

Hope this helps...and keep up the good work!

-Tim

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 9:14 AM


 hi!

 a DBA inteds to speed up a script that is looping and
 sending hundred thousands of sequential update statements like:

 UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

 he suggests copying the table to a file, change it and then
 load it into the DB again. i am strongly convinced that this
 is nonsense.

 what is the best way to go for a script like this, doing tons of
 updates? (except convincing him to swith to sell burgers)


 thx
 daniel
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Daniel Wisser
   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: Tim Gorman
  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: newbie question - still: please help


Even with this little bit of information, it's obvious your DBA
is on the right track.

Any script that loops and executes 'hundred thousands of 
sequential update statements' is total nonsense.

This will bring most any database to it's knees in a hurry.

Oh yes, I've had developers pull this one on me.  How about
a 10gig text file of INSERT statements?  

Please supply more info, but from what you have here, I 
would say you should start listening to your DBA.

Jared


On Monday 13 January 2003 08:14, Daniel Wisser wrote:
 hi!

 a DBA inteds to speed up a script that is looping and
 sending hundred thousands of sequential update statements like:

 UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

 he suggests copying the table to a file, change it and then
 load it into the DB again. i am strongly convinced that this
 is nonsense.

 what is the best way to go for a script like this, doing tons of
 updates? (except convincing him to swith to sell burgers)


 thx
 daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: 8.1.6: possible to set role in db's logon trigger?

2003-01-13 Thread Pardee, Roy E

Woah--free code!  A thousand thanks--this looks really close to what I'd
like to do.  If I can wrestle some extra privs on our test db I'll report
back as to whether I was able to get this going on 8.1.6.

Thanks also to Lisa  Thomas for responding.

Cheers,

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487 
-Original Message-
Sent: Monday, January 13, 2003 9:05 AM
To: Multiple recipients of list ORACLE-L


Roy, 
this is in 9202 ... check the custom code for hash joins ... it has been
working fine for us for  2 months ... 
CREATE OR REPLACE TRIGGER SYSTEM.DBT_USERS_LOGON 
AFTER LOGON ON DATABASE 
-- 
DECLARE 
CURSOR cur_sess IS 
SELECT * 
FROM v$session 
WHERE AUDSID = USERENV('SESSIONID') 
AND USERNAME NOT IN ('HEARTBEAT'); 
-- 
recSess cur_sess%ROWTYPE; 
-- 
PRAGMA AUTONOMOUS_TRANSACTION; 
-- 
BEGIN 
OPEN cur_sess; 
FETCH cur_Sess INTO recSess; 
CLOSE cur_sess; 
-- 
INSERT INTO USER_LOGON_AUDIT 
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM, 
TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME) 
VALUES 
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser), 
recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL); 
COMMIT; 
-- 
IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND 
UPPER(recSess.machine) = 'IMAPPROD1' THEN 
EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false'; 
END IF; 
-- 
EXCEPTION 
WHEN OTHERS THEN 
NULL; 
END DBT_USERS_LOGON; 
/ 
Raj 
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art! 
-- 
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: Daramtically improve BCHR with a single statement

:)


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
Still
Sent: Monday, January 13, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L


So, if I coerce the developers into writing better code that
does less logical IO, and the BCHR goes down, I should
then investigate and fix the problem?   ;)

Jared

On Monday 13 January 2003 08:09, Yechiel Adar wrote:
 Hello All

 I saw that piece of code a few times and still think that even if you
can
 bump up the BCHR it has it's place. How can you tell that you need
more
 space in the buffer pool? Bad BCHR is an indication that you need to
check
 this. It is also an indication that you do a lot of FTS or missing an
index
 and you SQL reads too many blocks.

 Use the BCHR as an indication. When it goes down it is time to check.

 Yechiel Adar
 Mehish
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Monday, January 13, 2003 5:08 AM

  To add an example of what Anjo, Morgens and everyone else is talking

 about,

  here is a perfect illustration of why focusing on BCHR is like
  concentrating intensely on how fast your tyres rotate in a Tour de
  France, instead of looking of where you are going (probably a lot
more
  useful). Another example:
  If I raced (100M sprint) against Maurice Green, and he went off in
the
  wrong direction, despite the fact that he is so much faster than me
(duh

 !)

  , I could lightly jog (as if I have anything else to offer) the 100M
in

 the

  right direction and beat him. Well, focusing on BCHR alone is like
going

 at

  full tilt with no direction.
  Also, I have realized that cars have been around for  100 years
now, so
  why exactly would I want to sprint again ? :-)
 
  Check out this example:
 
  run any number of scripts to look at BCHR. Then run the following

 anonymous

  PL/SQL block:
 
  declare
jackass number;
  begin
   for jackass in 1..1000 loop
  execute immediate 'select count (*) from solvit.solvit_lic ' ;
--
  replace this table with any single row table you like.
   end loop;
  end;
  /
 
  Check your BCHR again. Wow, amazing ! How much better your BCHR
looks
  now. This must be magic. If you would like to purchase other such
tools,
  please feel free to drop me a line, I could also sell you a large
  iceberg, which would end your personal water restriction problems.
 
  Another advantage to the above code is that it eliminates idle
capacity
  from my CPU's (I paid for the thing, it should be put to work, right
? )

 as

  my laptop has been at 100% CPU utilization for the last 8 minutes as
I
  let this piece of crap run before I killed it (Oracle 9 on XP with
512 MB
  RAM [SGA 120 MB], with a bunch of other starved stuff running
  concurrently).
 
  Reduction of logical I/O : Now THERE is the holy grail worth
pursuing ! I
  am sure we could have a VERY interesting discussion on that one !
 
  Feel free to use the above example to prove for once and for all
that
  concetration on tuning BCHR alone is a fruitless exercise.
 
  Regards :
 
  Ferenc Mantfeld
 
  -Original Message-
  From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
  Sent: Monday, January 13, 2003 11:24 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: BCHR Tuning
 
  and those people sell a tuning tool hm, I hadn't noticed any
  selling going on here. Or perhaps it's been subliminal?
 
  --- Jared Still [EMAIL PROTECTED] wrote:
   On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
Obviously, we don't know what we're talking about. I can see
  
   there's a
  
presentation by Rich Niemich at IOUG-A where he'll address all
  
   those
  
idiots who are saying you should ignore the Cash Hit Ratio (and
who
  
   are
  
all just after making big money on their products - I loved that
  
   one).
  
 Or modify the set up of these tools to take action when BCHR
  
   falls..
  
  
   Here's the session info:
  
   Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
   Venue: Southern Hemisphere 2, Walt Disney World
  Dolphin, Lake Buena Vista, FL
  
   Abstract: Lately, there has been a big push to ignore your
   hit ratio with claims that it is meaningless. This shallow
   minded view (usually by people who sell a tuning tool) ignores
   why people look at hit ratios and what they are looking for.
   This quick tip talk will show you what to look for and why.
   You will definitely know when, where  why to look at your
   hit ratio in the future.
  
   Show you why your hit ratio matters. How to analyze the
   hit ratio. Fallacies by those who want to sell you products
   and tools instead.
  
  
   Shallow Minded ?!
  
   Jared
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Jared Still
 INET: [EMAIL PROTECTED]
  
   Fat City Network

RE: compatible in 9iRel2

2003-01-13 Thread Jamadagni, Rajendra

Title: RE: compatible in 9iRel2





Curious minds want to know what bugs you are trying to fix ??


Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Ehresmann, David [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 12:16 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: compatible in 9iRel2



I am trying to apply a fix for some developers here. They used this fix
in 8 and 8i by changing the compatible parameter and running their sql
script. I just want to be able to tell them that they need to fix their
application now that we are using 9iRel2. I tried this morning and got the
ORA-402 and ORA-405 errors. I don't want to strip away features of 9i just
to get their security script to run.
thanks,
David Ehresmann.



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: compatible in 9iRel2




Were you using any of the new tablespace 
features or using dictionary managed tablespaces?
David Ehresmann

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
   [EMAIL PROTECTED] 01/13/03 11:16AM 
  I am trying to apply a "fix" for some developers here. 
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript. I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2. I 
  tried this morning and got theORA-402 and ORA-405 errors. I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database. If you bounce the instance the sqlshow 
  parameter compatible=9.0.0 stays the same, it does not change. 
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up. I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance. Is there any way to do this without 
  stripping the 9i databasedown to 8i? 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.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).-- 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.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).-- 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.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: compatible in 9iRel2




We are running 9.2.0.1., do you think that 
could be the reason. the .1=component specifiec Release 
Number? Was your 9i 9.2.0.0.0?
David Ehresmann.

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
   [EMAIL PROTECTED] 01/13/03 11:16AM 
  I am trying to apply a "fix" for some developers here. 
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript. I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2. I 
  tried this morning and got theORA-402 and ORA-405 errors. I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database. If you bounce the instance the sqlshow 
  parameter compatible=9.0.0 stays the same, it does not change. 
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up. I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance. Is there any way to do this without 
  stripping the 9i databasedown to 8i? 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.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).-- 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.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).-- 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.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).

How to identify full table scans?

2003-01-13 Thread Govind.Arumugam

List,

We use the following script to identify recent full table scans or full index scans.  
This result set will be used to identify the potential queries that could benefit by 
creating any new indexes or modify the existing index structure as needed.

Our intention is to run this query against X$BH every hour and gather this data.  Do 
you have any suggestions or scripts to accomplish the same?  Are there any issues in 
trying to do this every hour?  

Thanks,
Govind

/* Recent full table scan */
/* Should be run as user SYS */

set serverout on size 100
set verify off
set pagesiz 300
set lin 120

col object_name form a30
col owner form a10

PROMPT Column flag in x$bh table is set to value 0x8, when
PROMPT block was read by a sequential scan.

spool recentfulltablescan.lst

SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows
FROM dba_objects o,x$bh x, dba_tables t
WHERE x.obj=o.object_id
and o.object_name=t.table_name
-- AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)0
AND o.owner'SYS'
group by o.object_name, o.object_type, o.owner, t.num_rows
order by 1 ;

spool off




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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).

USER_TABLESPACES has more rows than DBA_TABLESPACES

2003-01-13 Thread Keith Moore




Has anyone else seen this or can youexplain 
it?

I have 7 tablespaces in USER_TABLESPACES that don't 
exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear 
from USER_TABLESPACES. They have a status of INVALID.

The database is version 8.0.5 (Yeah, I know, we'll 
be going to 9i real soon now)

Keith

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

RE: compatible in 9iRel2

2003-01-13 Thread John Shaw



system is data 
dictionary - the rest lmt's with uniform extents 
[EMAIL PROTECTED] 01/13/03 12:35PM 
Were you using any of the new tablespace 
features or using dictionary managed tablespaces?
David Ehresmann

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
   [EMAIL PROTECTED] 01/13/03 11:16AM 
  I am trying to apply a "fix" for some developers here. 
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript. I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2. I 
  tried this morning and got theORA-402 and ORA-405 errors. I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database. If you bounce the instance the sqlshow 
  parameter compatible=9.0.0 stays the same, it does not change. 
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up. I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance. Is there any way to do this without 
  stripping the 9i databasedown to 8i? 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.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).-- 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.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).-- 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.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: How to identify full table scans?

2003-01-13 Thread Koivu, Lisa

Title: RE: How to identify full table scans?





Govind, 


Just curious why you are attacking the full table scans. I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql. It was very telling and very very useful. 

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063






-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject: How to identify full table scans?



List,


We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed.

Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? 

Thanks,
Govind


/* Recent full table scan */
/* Should be run as user SYS */


set serverout on size 100
set verify off
set pagesiz 300
set lin 120


col object_name form a30
col owner form a10


PROMPT Column flag in x$bh table is set to value 0x8, when
PROMPT block was read by a sequential scan.


spool recentfulltablescan.lst


SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows
FROM dba_objects o,x$bh x, dba_tables t
WHERE x.obj=o.object_id
and o.object_name=t.table_name
-- AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)0
AND o.owner'SYS'
group by o.object_name, o.object_type, o.owner, t.num_rows
order by 1 ;


spool off





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
 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: USER_TABLESPACES has more rows than DBA_TABLESPACES

2003-01-13 Thread Stephane Faroult

 Keith Moore wrote:
 
 Has anyone else seen this or can you explain it?
 
 I have 7 tablespaces in USER_TABLESPACES that don't exist in
 DBA_TABLESPACES. These have been dropped, but somehow did not
 disappear from USER_TABLESPACES. They have a status of INVALID.
 
 The database is version 8.0.5 (Yeah, I know, we'll be going to 9i real
 soon now)
 
 Keith
 

Regular behaviour. Rows are never deleted from sys.ts$ (on which
DBA_TABLESPACES is based).

-- 
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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

2003-01-13 Thread M Rafiq

Dennis,

Here/ in this senario '
'ANALYZE INDEX VALIDATE STRUCTURE' will not help or reveal anything except 
to anlyze table with cascade structure which will reveal any index 
corruption relating to that table. There was a bug in 7.3.4.3 where index 
rebuilding with parallel clause was resulting in corrupt indexes with 
multiple keys. Indexes with single index key were ok...

I think it should be the problem

Regards
Rafiq








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Mon, 13 Jan 2003 09:29:04 -0800

Conrad - I haven't used 7.3.4 in quite some time, so my knowledge is a
little rusty. If you search METALINK, you will find quite a few issues with
this command on various Oracle versions. I believe that the basic principle
is the same - Oracle builds a new index in the background, syncs up any
table changes that have been made since the index build began, then switches
which index is being used for queries. I think I heard somewhere that the
locking/syncing mechanism has been improved in more recent Oracle versions.
Perhaps someone else on the list has some specific knowledge. The database
doesn't need to be in exclusive mode, but common sense would be to avoid
rebuilding when the table is being heavily modified. Once you complete, you
can ANALYZE INDEX VALIDATE STRUCTURE just to be sure you don't have any
problems.

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


-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


ALTER ...REBUILD..

Thanks

COnrad..


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
  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://www.orafaq.net
--
Author: Conrad Meertins
  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

Re[2]: newbie question - still: please help

2003-01-13 Thread dgoulet

Daniel,

After some of the younger folks in the audience have had a say, Let's let
the old fart have one.

While on the one hand I can see what your DBA is croaking on, I can also see
where you are too.  Your approach is easy to code, but can wreck hell on the
database especially if each statement requires a full table scan.

With the DBA's view your update statement turns into a select, followed by a
delete, and then an insert after you've processed the file.  Damn, that's a lot
of work for a simple update and who's to say that the process handling the
interim data file is bug free as well.  As Scotty in Star Trek 3 said The more
you overtake the pluming, the easier it is to stop up the drain.  What happens
when part of your application needs the data record in the middle of your
update?  Or the business logic changes?  Very convoluted pluming, many tight
drains.

But, having a loop as you do that sets a value to a constant for several
document id's in series, come on!!  Why not put those id's into a global temp
table (or suitable substitute) and then use an UPDATE ISIS_DOCAR SET STATUS =
2000 WHERE ID in (select id from temp_table);  One statement, several thousand
rows updated.  Simple pluming, one very large drain.

An interim solution might be to select your data, which obviously you did to
fill in 'n', with the for update clause.  Then your update changes into
UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE current of cursor x;  Less io on the
system, same result.  The pluming gets a little more complicated, but the drain
is still large  free flowing.

Dick Goulet
Senior Oracle DBA  Oracle Certified 8i DBA
Vicor Corporation

Reply Separator
Author: Tim Gorman [EMAIL PROTECTED]
Date:   1/13/2003 9:40 AM

Daniel,

Your gut reaction is right on-target.  It is always a struggle to keep
certain folks from killing the entire village while trying to cure a single
case of the sniffles.  What's worse is that such folks are usually quite
bright and talented.  After, very few mediocre folks can either cure the
sniffles or kill entire villages...  ;-)

Longer response:  This is a common argument that eventually distills down to
something like I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, Java.  What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into problems such
as transaction recoverability (aka rollback), concurrency, and its close
cousin read-consistency.  Oh yeah, and then there is also what my good
friend Gary once called DFB or diddly file build-up (i.e. an excess of
diddly files in a file-system), which very few people see up front but
invariably grows to dominate such approaches.  After some decades of effort
by thousands of developers and designers (very few of whom are stupid), what
results is the modern database engine.  Such people who think they can
outperform database engines without losing such crucial features do not have
any sense of humility about their place in the world.  Ask him to skim
through Gray and Reuter's Principles of Transaction Processing to gain
some of that humility...

Shorter response:  look into using PL/SQL bulk-bind operations (i.e. FORALL,
BULK COLLECT, etc) instead of one-row-at-a-time processing.

I suspect the latter approach will be more effective...  :-)

Hope this helps...and keep up the good work!

-Tim

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 9:14 AM


 hi!

 a DBA inteds to speed up a script that is looping and
 sending hundred thousands of sequential update statements like:

 UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

 he suggests copying the table to a file, change it and then
 load it into the DB again. i am strongly convinced that this
 is nonsense.

 what is the best way to go for a script like this, doing tons of
 updates? (except convincing him to swith to sell burgers)


 thx
 daniel
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Daniel Wisser
   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: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California--

ioug.org web site

2003-01-13 Thread Adams, Matthew (GECP, MABG, 088130)

Title: ioug.org web site





Is anybody else having trouble with the www.ioug.org
web site. I can get to the home page, but cannot
log in, or register for IOUG-A Live.


Is it just me?



Matt Adams - GE Appliances - [EMAIL PROTECTED]
My computer beat me at chess, but I won
when it came to kick boxing.

Question On High Parse to execute ratio

2003-01-13 Thread Jay Wade

Hello:

I have a third party application which seems to have alot of unbound SQL 
statements, several of which do not seem to be very effective queries.  
Unfortunatelly the code is locked.

My Parse to execute ratio is around 72% and I was wondering if anyone has 
any ideas on how I can decrease this?

I figure that I might start with using the parameter CURSOR_SHARING.

Any ideas would be great.

Thanks in advance,
Jay


_
Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* 
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Wade
 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).

index rebuilding performance vs sort_area_size

2003-01-13 Thread Guang Mei

Hi:

Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to 
test index rebuild (with nologging) performance vs sort_area_size. I used 
alter session set sort_area_size =  to set the sort_area_size value. 
Nothing else was changed. The temp tablespace is 8G. There is no other 
active sessions running during the test. I selected two indexes for the 
test. Their sizes are about 20M and 115M respectively so that they were fit 
into their initial extent after the rebuild.

Here is the result:

-- 1. rebuild an index with size of about 20M:
alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next 
20M);

sort_area_size  20971520
Elapsed:00:00:12.49   00:00:11.6800:00:12.18

sort_area_size  80971520
Elapsed:00:00:09.9500:00:09.94  00:00:09.54

-- 2. rebuild an index with size of  about 115M:
alter index mt.TOPIC_INDEX rebuild nologging
STORAGE (INITIAL 114688000  next 114688000);

sort_area_size  20971520
Elapsed:00:00:51.06  00:00:50.4400:00:51.46

sort_area_size  80971520
Elapsed:00:00:52.17  00:00:51.6500:00:51.75

sort_area_size  150971520
Elapsed:   00:00:42.42  00:00:41.81 00:00:41.71

So with this very limited data points, I found

1. In the 1st example, the sort_area_size was increased almost 4 times, but 
we only got about 20% performance improvement.

2. In the 2nd example, we got 20% performance boost when sort_area_size was 
increased from 21M to 151M.

Is what I see here typical? It seems that with the increase of 
sort_area_size, the index rebuild will be faster, but not as fast as I 
hoped. Any comments?

Guang Mei

_
The new MSN 8 is here: Try it free* for 2 months 
http://join.msn.com/?page=dept/dialup

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
 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: Conversion from CLOB to RAW

2003-01-13 Thread Burke, William F (Bill)

I wasn't.  I actually am still trying to look at the varchar2.  The downside
is the huge amount of data they want to keep in a compressed format hence
the RAW or BLOB format.

Regards,

Bill Burke
The Kinder and Gentler DBA
IOUG University Master Class Faculty 2001-2002
iDBA Management, High Performance Infrastructure and HA
IOUG Board of Directors 2000-2002
ODTUG Board of Directors 1996-2000
www.OracleGuru.com
www.KBMotorsports.biz



-Original Message-
Sent: Sunday, January 12, 2003 12:34 AM
To: Multiple recipients of list ORACLE-L


Bill,

If the data length is less than 2K, why not use varchar2? You get all the
functionality like substr(), instr(), like etc. I'm not sure why you are
leaning towards RAW, when the type you are storing is of character based.

HTH.

Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 11, 2003 1:33 PM


 The amount of data being stored is fairly significant @60G of xml
 transaction data.  The developers want to apply a compression routine to
the
 xml string which will save about 70% of the space currently in use.  BLOB
 was my original recommendation, they were pushing to go RAW instead.
We've
 only got a couple of CLOB's out there, but they are taking up huge amounts
 of storage.  On the overkill note, most all of the XML has been parsed to
 less than 2K in length so one of my thoughts was we had introduced LOB
 functionality without really needing it.

 The other aside to this is we will definately need to partition the data
 when we do the conversion as it currently resides in a traditional table
as
 a CLOB.

 Regards,

 Bill Burke
 The Kinder and Gentler DBA
 IOUG University Master Class Faculty 2001-2002
 iDBA Management, High Performance Infrastructure and HA
 IOUG Board of Directors 2000-2002
 ODTUG Board of Directors 1996-2000
 www.OracleGuru.com
 www.KBMotorsports.biz



 -Original Message-
 [mailto:[EMAIL PROTECTED]]
 Sent: Friday, January 10, 2003 4:24 PM
 To: Multiple recipients of list ORACLE-L



 Bill,

 I agree with Michael.  You've already got the data in a suitable
 datatype.  Why move it to a cumbersome, soon-to-be-obsolete datatype?
You
 can use DBMS_LOB functionality on LOBs, not on Raw.

 I'd be so happy if the couple dozen tables in our 3rd party Student
 Information system that have Long or Long Raw columns had CLOB or BLOB
 columns instead.  It would make converting them to partitioned tables much
 easier.

 I definitely vote to keep your CLOBs.

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





   Michael Fontana

   [EMAIL PROTECTED]To:   Multiple
recipients
 of list ORACLE-L
   et   [EMAIL PROTECTED]

   Sent by: cc:

   [EMAIL PROTECTED] Subject:  Re: Conversion
from
 CLOB to RAW




   01/10/2003 02:15

   PM

   Please respond to

   ORACLE-L









 At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote:
   Here's where I get to ask the most likely simple question.

   I've inherited a database where it was built using a CLOB to hold
XML
   data
   but we have now determined that was total overkill and want to move
   it to a
   RAW column or other suitable datatype.

   Looking for conversion issues or other alternatives.


 Since Oracle is moving us away from LONG and RAW datatypes, I assume
 you want to convert from CLOB to BLOB?  BLOB is probably more
 storage-efficient,
 but since XML is made up of character data, I don't really understand the
 issue with
 keeping it a CLOB.

  What do you mean by overkill?




 --
 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: Burke, William F (Bill)
   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

RE: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

Thanks Rafiq. As I said, my 7.3.4 knowledge is too old by now (but man, was
that a great version). 

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


-Original Message-
Sent: Monday, January 13, 2003 1:22 PM
To: Multiple recipients of list ORACLE-L


Dennis,

Here/ in this senario '
'ANALYZE INDEX VALIDATE STRUCTURE' will not help or reveal anything except 
to anlyze table with cascade structure which will reveal any index 
corruption relating to that table. There was a bug in 7.3.4.3 where index 
rebuilding with parallel clause was resulting in corrupt indexes with 
multiple keys. Indexes with single index key were ok...

I think it should be the problem

Regards
Rafiq








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Mon, 13 Jan 2003 09:29:04 -0800

Conrad - I haven't used 7.3.4 in quite some time, so my knowledge is a
little rusty. If you search METALINK, you will find quite a few issues with
this command on various Oracle versions. I believe that the basic principle
is the same - Oracle builds a new index in the background, syncs up any
table changes that have been made since the index build began, then switches
which index is being used for queries. I think I heard somewhere that the
locking/syncing mechanism has been improved in more recent Oracle versions.
Perhaps someone else on the list has some specific knowledge. The database
doesn't need to be in exclusive mode, but common sense would be to avoid
rebuilding when the table is being heavily modified. Once you complete, you
can ANALYZE INDEX VALIDATE STRUCTURE just to be sure you don't have any
problems.

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


-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


ALTER ...REBUILD..

Thanks

COnrad..


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
   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://www.orafaq.net
--
Author: Conrad Meertins
   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

RE: compatible in 9iRel2

David - No. This just ensures you don't use any 9i only features.



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

-Original Message-
Sent: Monday, January 13, 2003 1:22 PM
To: Multiple recipients of list ORACLE-L



I was curious about this parameter

I have an application that is certified on 8.1.7 only

Could I create a 9i database set compatible to 8.1.7 and essentially call it
an 8.1.7 database?

 

-Original Message-
Sent: Monday, January 13, 2003 1:41 PM
To: Multiple recipients of list ORACLE-L

 

We are running 9.2.0.1., do you think that could be the reason.   the
.1=component specifiec Release Number?  Was your 9i  9.2.0.0.0?

David Ehresmann.

-Original Message-
Sent: Monday, January 13, 2003 11:41 AM
To: Multiple recipients of list ORACLE-L

I had a 9.2/solaris database that I could set compatible 8.1.7 on - but
since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either
- never really needed it so I haven't bothered with metalink/tar.


 [EMAIL PROTECTED] 01/13/03 11:16AM 
I am trying to apply a fix for some developers here.  They used this fix
in 8 and 8i by changing the compatible parameter and running their sql
script.  I just want to be able to tell them that they need to fix their
application now that we are using 9iRel2.  I tried this morning and got the
ORA-402 and ORA-405 errors.  I don't want to strip away features of 9i just
to get their security script to run.
thanks,
David Ehresmann.

-Original Message-
Sent: Monday, January 13, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L


David - What are you trying to accomplish?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


List, 

I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 Solaris
8 database.  If you bounce the instance the 
sqlshow parameter compatible=9.0.0  stays the same, it does not change.  If
you shutdown and reboot the server you get an:

ora-01033: Initialization or shutdown in progressWhich basically states
that you are trying to connect to an instance that is being shutdown down or
starting up.  I believe it goes into NOMOUNT stage and reads the init.ora
and hangs because of the compatible parameter being set to 8i. I saw the
document Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01
stating how to downgrade, but it seems to defeat the purpose of having a 9i
instance.   Is there any way to do this without stripping the 9i database
down to 8i?  


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
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
http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
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
http://www.orafaq.net 
-- 
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
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

Re: Question On High Parse to execute ratio

2003-01-13 Thread Igor Neyman

Success with using CURSOR_SHARING depends on your Oracle version.
In earlier versions it's buggy.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 3:00 PM


 Hello:

 I have a third party application which seems to have alot of unbound SQL
 statements, several of which do not seem to be very effective queries.
 Unfortunatelly the code is locked.

 My Parse to execute ratio is around 72% and I was wondering if anyone has
 any ideas on how I can decrease this?

 I figure that I might start with using the parameter CURSOR_SHARING.

 Any ideas would be great.

 Thanks in advance,
 Jay


 _
 Help STOP SPAM: Try the new MSN 8 and get 2 months FREE*
 http://join.msn.com/?page=features/junkmail

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jay Wade
   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: Igor Neyman
  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: ioug.org web site

2003-01-13 Thread Stephen Andert

I don't know if it's just you, but I'm ok.  I just logged in and went to
the Live 2003 page without any problem.  

Check with your network folks.

Stephen

 [EMAIL PROTECTED] 01/13/03 12:49PM 
Is anybody else having trouble with the www.ioug.org 
web site.  I can get to the home page, but cannot
log in, or register for IOUG-A Live.

Is it just me?


Matt Adams - GE Appliances - [EMAIL PROTECTED] 
My computer beat me at chess, but I won
when it came to kick boxing.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Andert
  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: Question On High Parse to execute ratio

Jay - Here are the possibilities:
   1. The vendor is stupid. Or doesn't understand Oracle. 
   2. The product was incorrectly installed. Some products are sophisticated
enough to have a configurable interface, which may be misconfigured or need
tuning over time.
   3. You are missing a key piece of knowledge. 
My point is, learn what you can about alternatives 2 and 3. Read the vendor
documentation on the Oracle interface. Place a support call with the vendor.
Try to locate a knowledgeable resource at the vendor. Find a support list.
But if that fails, then you may be forced to fall back on an Oracle-only
solution. CURSOR_SHARING is a high-risk solution that I would try after
exhausting the simpler solutions.

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


-Original Message-
Sent: Monday, January 13, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Hello:

I have a third party application which seems to have alot of unbound SQL 
statements, several of which do not seem to be very effective queries.  
Unfortunatelly the code is locked.

My Parse to execute ratio is around 72% and I was wondering if anyone has 
any ideas on how I can decrease this?

I figure that I might start with using the parameter CURSOR_SHARING.

Any ideas would be great.

Thanks in advance,
Jay


_
Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* 
http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Wade
  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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

2003-01-13 Thread M Rafiq

Yes, it is as of today...We are still having 7.3.4.5 for our Oracle 
Financials 10.7 Application. This may be the last year for this version...


Regards
Rafiq








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Mon, 13 Jan 2003 12:38:39 -0800

Thanks Rafiq. As I said, my 7.3.4 knowledge is too old by now (but man, was
that a great version).

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


-Original Message-
Sent: Monday, January 13, 2003 1:22 PM
To: Multiple recipients of list ORACLE-L


Dennis,

Here/ in this senario '
'ANALYZE INDEX VALIDATE STRUCTURE' will not help or reveal anything except
to anlyze table with cascade structure which will reveal any index
corruption relating to that table. There was a bug in 7.3.4.3 where index
rebuilding with parallel clause was resulting in corrupt indexes with
multiple keys. Indexes with single index key were ok...

I think it should be the problem

Regards
Rafiq








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Mon, 13 Jan 2003 09:29:04 -0800

Conrad - I haven't used 7.3.4 in quite some time, so my knowledge is a
little rusty. If you search METALINK, you will find quite a few issues with
this command on various Oracle versions. I believe that the basic principle
is the same - Oracle builds a new index in the background, syncs up any
table changes that have been made since the index build began, then switches
which index is being used for queries. I think I heard somewhere that the
locking/syncing mechanism has been improved in more recent Oracle versions.
Perhaps someone else on the list has some specific knowledge. The database
doesn't need to be in exclusive mode, but common sense would be to avoid
rebuilding when the table is being heavily modified. Once you complete, you
can ANALYZE INDEX VALIDATE STRUCTURE just to be sure you don't have any
problems.

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


-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


ALTER ...REBUILD..

Thanks

COnrad..


-Original Message-
WILLIAMS
Sent: Monday, January 13, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Conrad - What command did you use to re-index? Drop index / create index?

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


-Original Message-
Sent: Monday, January 13, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L



Sorry, I forgot this additional infromation...

Oracle created invalid objects when we ran rebuild.  The odd thing was that
the trace file showed an invalid object but the all_objects table showed a
valid object.



-Original Message-
Sent: Monday, January 13, 2003 9:39 AM
To: [EMAIL PROTECTED]



Team,

I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
While the users were on the system, we Re-Indexed our tables.
Situation: The users complained, that they were unable to process their
orders.
Although, the log file showed that the re-indexing was successful, the users
were still unable to process their orders.


QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
version 7.x?

Should you have the database in exclusive mode when re-indexing?

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
   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://www.orafaq.net
--
Author: Conrad Meertins
   INET: [EMAIL PROTECTED]

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

Re: Different Backups - A Comparartive analysis

Don't know really.  Just thought that it should probably be included, then I
was hoping to find out...  :-)

- Original Message -
To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 10:49 AM


 On Monday 13 January 2003 06:03, Tim Gorman wrote:
  Another question:  should SQL BackTrack be included for consideration?

 What does SQL BackTrack to that RMAN doesn't do?

 Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  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: removing duplicate entries from a table

Mark,

About how many rows are we talking about?  Just curious...

Log Miner might be a viable option.  It comes with 8i but if the database in
question is v8.0.x then you can still read them from an 8i database.  From
Log Miner you can extract the UNDO_SQL for the rows you want to delete.

If you are running Oracle7, then Log Miner is not an option at all and all
you've got left is:

select key-col1, key-col2, ..., count(*) dups, 'delete table-name where
rowid = '''||max(rowid)||''';' cmd
from   table-name
group by key-col1, key-col2, ...
having count(*)  1;

If you only have duplicates (i.e. two copies) you can decide if you want to
run the 'cmd'.  After testing things out in SQL*Plus, you can just spool out
the DELETE commands by entering the following SQL*Plus commands first...

col key-col1 noprint
col key-col2 noprint
...
col cnt noprint

which will just spool out the generated DELETE commands...

Be careful!  Hope this helps...

-Tim

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 10:35 AM


 Hi List

 Is there a tool for removing duplicate entries from a table - except that
 the key field is not the same. I ran some data imports more than once and
 was hoping someone could point me in the direction of a way to undo my
 mistake.

 Any help will be most appreciated

 Thanks
 Mark Warner

 __
 The information contained in this communication is confidential and
 may be legally privileged.  It is intended solely for the use of the
 individual or entity to whom it is addressed and others authorised to
 receive it.  If you are not the intended recipient you are hereby
 notified that any disclosure, copying, distribution or taking action
 in reliance of the contents of this information is strictly prohibited
 and may be unlawful.  Absa is liable neither for the proper, complete
 transmission of the information contained in this communication, nor
 for any delay in its receipt, nor for the assurance that it is
 virus-free.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Mark Warner
   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: Tim Gorman
  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: index rebuilding performance vs sort_area_size

2003-01-13 Thread Govind.Arumugam

We have not seen any performance gains after setting the sort_area_size in excess of 
50Mb.  We have set this as a
standard in our re-indexing scripts to set this to 50Mb maximum.

HTH.

-Original Message-
Sent: Monday, January 13, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L


Hi:

Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to 
test index rebuild (with nologging) performance vs sort_area_size. I used 
alter session set sort_area_size =  to set the sort_area_size value. 
Nothing else was changed. The temp tablespace is 8G. There is no other 
active sessions running during the test. I selected two indexes for the 
test. Their sizes are about 20M and 115M respectively so that they were fit 
into their initial extent after the rebuild.

Here is the result:

-- 1. rebuild an index with size of about 20M:
alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next 
20M);

sort_area_size  20971520
Elapsed:00:00:12.49   00:00:11.6800:00:12.18

sort_area_size  80971520
Elapsed:00:00:09.9500:00:09.94  00:00:09.54

-- 2. rebuild an index with size of  about 115M:
alter index mt.TOPIC_INDEX rebuild nologging
STORAGE (INITIAL 114688000  next 114688000);

sort_area_size  20971520
Elapsed:00:00:51.06  00:00:50.4400:00:51.46

sort_area_size  80971520
Elapsed:00:00:52.17  00:00:51.6500:00:51.75

sort_area_size  150971520
Elapsed:   00:00:42.42  00:00:41.81 00:00:41.71

So with this very limited data points, I found

1. In the 1st example, the sort_area_size was increased almost 4 times, but 
we only got about 20% performance improvement.

2. In the 2nd example, we got 20% performance boost when sort_area_size was 
increased from 21M to 151M.

Is what I see here typical? It seems that with the increase of 
sort_area_size, the index rebuild will be faster, but not as fast as I 
hoped. Any comments?

Guang Mei

_
The new MSN 8 is here: Try it free* for 2 months 
http://join.msn.com/?page=dept/dialup

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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: [EMAIL PROTECTED]
  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: ioug.org web site

2003-01-13 Thread Ruth Gramolini

Hi Matt,
I registered last week without a problem, except they insisted on a credit
card number even tho I owed $0.  Looking forward to seeing you again!
Ruth Gramolini
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 2:49 PM


 Is anybody else having trouble with the www.ioug.org
 web site.  I can get to the home page, but cannot
 log in, or register for IOUG-A Live.

 Is it just me?

 
 Matt Adams - GE Appliances - [EMAIL PROTECTED]
 My computer beat me at chess, but I won
 when it came to kick boxing.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ruth Gramolini
  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: Question On High Parse to execute ratio

2003-01-13 Thread Rajesh . Rao


My colleagues call it CURSER_SHARING :)))




   
  
Igor Neyman  
  
ineyman@perceTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
ptron.comcc:  
  
Sent by:  Subject: Re: Question On High Parse to 
execute ratio   
[EMAIL PROTECTED] 
  
om 
  
   
  
   
  
January 13,
  
2003 03:38 PM  
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




Success with using CURSOR_SHARING depends on your Oracle version.
In earlier versions it's buggy.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 3:00 PM


 Hello:

 I have a third party application which seems to have alot of unbound SQL
 statements, several of which do not seem to be very effective queries.
 Unfortunatelly the code is locked.

 My Parse to execute ratio is around 72% and I was wondering if anyone has
 any ideas on how I can decrease this?

 I figure that I might start with using the parameter CURSOR_SHARING.

 Any ideas would be great.

 Thanks in advance,
 Jay


-- 
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: Conversion from CLOB to RAW

Bill - Will you need to search for information that will be compressed? If
yes, then performance could be really, really bad.

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


-Original Message-
Sent: Monday, January 13, 2003 2:07 PM
To: Multiple recipients of list ORACLE-L


I wasn't.  I actually am still trying to look at the varchar2.  The downside
is the huge amount of data they want to keep in a compressed format hence
the RAW or BLOB format.

Regards,

Bill Burke
The Kinder and Gentler DBA
IOUG University Master Class Faculty 2001-2002
iDBA Management, High Performance Infrastructure and HA
IOUG Board of Directors 2000-2002
ODTUG Board of Directors 1996-2000
www.OracleGuru.com
www.KBMotorsports.biz



-Original Message-
Sent: Sunday, January 12, 2003 12:34 AM
To: Multiple recipients of list ORACLE-L


Bill,

If the data length is less than 2K, why not use varchar2? You get all the
functionality like substr(), instr(), like etc. I'm not sure why you are
leaning towards RAW, when the type you are storing is of character based.

HTH.

Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 11, 2003 1:33 PM


 The amount of data being stored is fairly significant @60G of xml
 transaction data.  The developers want to apply a compression routine to
the
 xml string which will save about 70% of the space currently in use.  BLOB
 was my original recommendation, they were pushing to go RAW instead.
We've
 only got a couple of CLOB's out there, but they are taking up huge amounts
 of storage.  On the overkill note, most all of the XML has been parsed to
 less than 2K in length so one of my thoughts was we had introduced LOB
 functionality without really needing it.

 The other aside to this is we will definately need to partition the data
 when we do the conversion as it currently resides in a traditional table
as
 a CLOB.

 Regards,

 Bill Burke
 The Kinder and Gentler DBA
 IOUG University Master Class Faculty 2001-2002
 iDBA Management, High Performance Infrastructure and HA
 IOUG Board of Directors 2000-2002
 ODTUG Board of Directors 1996-2000
 www.OracleGuru.com
 www.KBMotorsports.biz



 -Original Message-
 [mailto:[EMAIL PROTECTED]]
 Sent: Friday, January 10, 2003 4:24 PM
 To: Multiple recipients of list ORACLE-L



 Bill,

 I agree with Michael.  You've already got the data in a suitable
 datatype.  Why move it to a cumbersome, soon-to-be-obsolete datatype?
You
 can use DBMS_LOB functionality on LOBs, not on Raw.

 I'd be so happy if the couple dozen tables in our 3rd party Student
 Information system that have Long or Long Raw columns had CLOB or BLOB
 columns instead.  It would make converting them to partitioned tables much
 easier.

 I definitely vote to keep your CLOBs.

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





   Michael Fontana

   [EMAIL PROTECTED]To:   Multiple
recipients
 of list ORACLE-L
   et   [EMAIL PROTECTED]

   Sent by: cc:

   [EMAIL PROTECTED] Subject:  Re: Conversion
from
 CLOB to RAW




   01/10/2003 02:15

   PM

   Please respond to

   ORACLE-L









 At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote:
   Here's where I get to ask the most likely simple question.

   I've inherited a database where it was built using a CLOB to hold
XML
   data
   but we have now determined that was total overkill and want to move
   it to a
   RAW column or other suitable datatype.

   Looking for conversion issues or other alternatives.


 Since Oracle is moving us away from LONG and RAW datatypes, I assume
 you want to convert from CLOB to BLOB?  BLOB is probably more
 storage-efficient,
 but since XML is made up of character data, I don't really understand the
 issue with
 keeping it a CLOB.

  What do you mean by overkill?




 --
 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: Burke, William F (Bill)
   INET: [EMAIL PROTECTED]

 Fat City

RE: How to identify full table scans?

2003-01-13 Thread Govind.Arumugam

Title: RE: How to identify full table scans?

This
helps to identify the queries that could be tunedfor LIO and/or PIO from a
SQL Tuning perspective. We can give this list to the development or
application teams so that they could independently work off this list
(hopefully!).

-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 2003
2:22 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: How to identify full table scans?
Govind,
Just curious why you are attacking the full table scans.
I implemented something like this in the past utilizing Steve Adams' script
expensive_sql.sql. It was very telling and very very useful.
Lisa Koivu Oracle Database
Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL,
USA 33063
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: How to identify full table scans?
List,
We use the following script to identify recent full table
scans or full index scans. This result set will be used to identify the
potential queries that could benefit by creating any new indexes or modify the
existing index structure as needed.
Our intention is to run this query against X$BH every hour and
gather this data. Do you have any suggestions or scripts to accomplish
the same? Are there any issues in trying to do this every hour?

Thanks, Govind
/* Recent full table scan */ /* Should
be run as user SYS */
set serverout on size 100 set
verify off set pagesiz 300 set
lin 120
col object_name form a30 col owner
form a10
PROMPT Column flag in x$bh table is set to value 0x8,
when PROMPT block was read by a sequential
scan.
spool recentfulltablescan.lst
SELECT count(o.object_name) "COUNT", o.object_name,
o.object_type, o.owner, t.num_rows FROM dba_objects
o,x$bh x, dba_tables t WHERE x.obj=o.object_id
and o.object_name=t.table_name -- AND
o.object_type='TABLE' AND
standard.bitand(x.flag,524288)0 AND
o.owner'SYS' group by o.object_name,
o.object_type, o.owner, t.num_rows order by 1 ;

spool off
-- Please see the official ORACLE-L
FAQ: http://www.orafaq.net -- Author:
[EMAIL PROTECTED] 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: Different Backups - A Comparartive analysis

2003-01-13 Thread VIVEK_SHARMA


Is SQL BackTrack a product like RMAN (from Oracle Corp. or 3rd party )?
 

-Original Message-
Sent: Tuesday, January 14, 2003 2:09 AM
To: Multiple recipients of list ORACLE-L


Don't know really.  Just thought that it should probably be included, then I
was hoping to find out...  :-)

- Original Message -
To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED]
Sent: Monday, January 13, 2003 10:49 AM


 On Monday 13 January 2003 06:03, Tim Gorman wrote:
  Another question:  should SQL BackTrack be included for consideration?

 What does SQL BackTrack to that RMAN doesn't do?

 Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  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: VIVEK_SHARMA
  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).

rdist Examples

2003-01-13 Thread Post, Ethan

rdist is a good way to keep files (sql, tools, tnsnames) synchronized
between servers, in my case I need to replicate 

/db01/foo/* to /u01/foo/*

Going from /db01/foo to /db01/foo on a different server is easy but I can
not find any examples of going to another directory structure.  The docs are
not very clear to me, sorry my IQ really isn't very high (never take an IQ
test with your wife, once she finds out she is smarter things are never the
same), anyway I ramble...

http://docs.sun.com/db/doc/805-3172/6j31br5lf?a=view

My rdist config file looks like this...

HOSTS=( bunnyfoofoo )
FILES=( /db01/foo/* )
(${FILES}) - (${HOSTS})
install -R;

Here is what I run...

rdist -f rdist.txt -y -R

Works great, now how do I go from db01 to u01?

Thanks ahead of time.

- Ethan
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Post, Ethan
  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: Different Backups - A Comparartive analysis


I *think* that RMAN does everything SQL Backtrack does.

Can't be positive, as I haven't used it for awhile.  

RMAN seems to have everything I recall SQL BT having, and then some.

Jared

On Monday 13 January 2003 12:38, Tim Gorman wrote:
 Don't know really.  Just thought that it should probably be included, then
 I was hoping to find out...  :-)

 - Original Message -
 To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED]
 Sent: Monday, January 13, 2003 10:49 AM

  On Monday 13 January 2003 06:03, Tim Gorman wrote:
   Another question:  should SQL BackTrack be included for consideration?
 
  What does SQL BackTrack to that RMAN doesn't do?
 
  Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Minimum required init.ora parameters

2003-01-13 Thread Jesse, Rich

Curious, for which platform is 8.1.8 available?  And *why*?

Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

 -Original Message-
 From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
 Sent: Monday, January 13, 2003 10:55 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Minimum required init.ora parameters
 
 
 Well, I just tried it on 8.1.8, and here is what I receive:
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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).

Was 8.1.6 certified on Solaris 8?

2003-01-13 Thread Miller, Jay

We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of
our boxes.

I want to know if it's possible to do the OS upgrade first and then the
database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so).

The Oracle certification matrix only says that 8.1.6 is desupported and
therefore doesn't list any certified OS versions for it.

Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified?


TIA,
Jay Miller

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Miller, Jay
  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: ioug.org web site

2003-01-13 Thread Ari Kaplan

I spoke with Matt and he is able to register / log in. As it usually
happens, he got it working right after he sent the email.

Take care,

-Ari Kaplan

-Original Message-
Andert
Sent: Monday, January 13, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L


I don't know if it's just you, but I'm ok.  I just logged in and went to
the Live 2003 page without any problem.

Check with your network folks.

Stephen

 [EMAIL PROTECTED] 01/13/03 12:49PM 
Is anybody else having trouble with the www.ioug.org
web site.  I can get to the home page, but cannot
log in, or register for IOUG-A Live.

Is it just me?


Matt Adams - GE Appliances - [EMAIL PROTECTED]
My computer beat me at chess, but I won
when it came to kick boxing.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Andert
  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: Ari Kaplan
  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: index rebuilding performance vs sort_area_size


The increase in performance of course comes from
sorting in memory and avoiding disk writes.

Try running this bit of SQL before and after your tests,
and record the numbers:

select
   stat.sid,
   name.name name,
   stat.value
from v$sesstat stat, v$statname name, v$session sess
where
   stat.sid = sess.sid
   and sess.audsid = sys_context('userenv','sessionid')
   and stat.statistic# = name.statistic#
   and name.name like '%sort%';

You can then see how many sorts to disk are taking 
place.  I suspect you're not eliminating as many as you
had hoped for.

You will need to determine how much memory is required
to actually do the entire sort in memory.  

Something else to monitor is the amount of memory
available on your system.  If increasing the sort_are_size
causes excessive paging to take place, there isn't much
point in changing the value.  

Paging can be monitored vi vmstat on Solaris.

HTH

Jared



On Monday 13 January 2003 12:00, Guang Mei wrote:
 Hi:

 Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to
 test index rebuild (with nologging) performance vs sort_area_size. I used
 alter session set sort_area_size =  to set the sort_area_size value.
 Nothing else was changed. The temp tablespace is 8G. There is no other
 active sessions running during the test. I selected two indexes for the
 test. Their sizes are about 20M and 115M respectively so that they were fit
 into their initial extent after the rebuild.

 Here is the result:

 -- 1. rebuild an index with size of about 20M:
 alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next
 20M);

 sort_area_size  20971520
 Elapsed:00:00:12.49   00:00:11.6800:00:12.18

 sort_area_size  80971520
 Elapsed:00:00:09.9500:00:09.94  00:00:09.54

 -- 2. rebuild an index with size of  about 115M:
 alter index mt.TOPIC_INDEX rebuild nologging
 STORAGE (INITIAL 114688000  next 114688000);

 sort_area_size  20971520
 Elapsed:00:00:51.06  00:00:50.4400:00:51.46

 sort_area_size  80971520
 Elapsed:00:00:52.17  00:00:51.6500:00:51.75

 sort_area_size  150971520
 Elapsed:   00:00:42.42  00:00:41.81 00:00:41.71

 So with this very limited data points, I found

 1. In the 1st example, the sort_area_size was increased almost 4 times, but
 we only got about 20% performance improvement.

 2. In the 2nd example, we got 20% performance boost when sort_area_size was
 increased from 21M to 151M.

 Is what I see here typical? It seems that with the increase of
 sort_area_size, the index rebuild will be faster, but not as fast as I
 hoped. Any comments?

 Guang Mei

 _
 The new MSN 8 is here: Try it free* for 2 months
 http://join.msn.com/?page=dept/dialup
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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).

2 basic Qs. on export

2003-01-13 Thread VIVEK_SHARMA


1) Large Export Dumps , if Directly Exported to TAPE Devices , Can Import be safely 
Done therefrom ?

FILE=Device name

OR is it advisable to compress / Split the Export Dump Files onto Storage Box  
thereafter backup the Same onto Tape ?


2) With Oracle  8i  9i 

Does export backup fired at a certain Time take ALL Objects's Data existing as at that 
point in time ? 

Assuming exp Command is issued to export a Full Database Containing many Tables at 
10:00 hours .
Assuming Update is Done to Some Table which is yet to be Exported at 10:01 , 
Will the export backup contain the Updated OR NON-Updated Data ?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  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: Question On High Parse to execute ratio

2003-01-13 Thread Karen Morton

Jay,

Take a look into using stored outlines.  If the problem is with the vendor's
code not using bind variables, I don't know that this will help, but you may
find some instances where it might be useful.

I heard recently that a book should be written with just one chapter on bind
variables.Chapter 1, Page 1:  Use them!  The End.

Karen

-Original Message-
Sent: Monday, January 13, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L


Hello:

I have a third party application which seems to have alot of unbound SQL
statements, several of which do not seem to be very effective queries.
Unfortunatelly the code is locked.

My Parse to execute ratio is around 72% and I was wondering if anyone has
any ideas on how I can decrease this?

I figure that I might start with using the parameter CURSOR_SHARING.

Any ideas would be great.

Thanks in advance,
Jay


_
Help STOP SPAM: Try the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail

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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Karen Morton
  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: Question On High Parse to execute ratio

2003-01-13 Thread Jesse, Rich

In fact, in 8.1.7.2, it can cause *incorrect* results in certain queries
without reporting an error!  8.1.7.4 is supposed to be a little more stable
(applying that minipack to production as this is typed), and 9iR2 is
reported to be even better.

For us, it was CS=F or die (dead server, die trying to revamp 10Ks of lines
of code, etc).  We chose CS=F.  It definitely is not as good as good code,
but it is a pretty good stopgap, if you are aware of the risks!  See
Metalink for the warnings, especially in the BUG area.

GL!

Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


 -Original Message-
 From: Igor Neyman [mailto:[EMAIL PROTECTED]]
 Sent: Monday, January 13, 2003 2:39 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: Question On High Parse to execute ratio
 
 
 Success with using CURSOR_SHARING depends on your Oracle version.
 In earlier versions it's buggy.
 
 Igor Neyman, OCP DBA
 [EMAIL PROTECTED]
 
 
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Monday, January 13, 2003 3:00 PM
 
 
  Hello:
 
  I have a third party application which seems to have alot 
 of unbound SQL
  statements, several of which do not seem to be very 
 effective queries.
  Unfortunatelly the code is locked.
 
  My Parse to execute ratio is around 72% and I was wondering 
 if anyone has
  any ideas on how I can decrease this?
 
  I figure that I might start with using the parameter CURSOR_SHARING.
 
  Any ideas would be great.
 
  Thanks in advance,
  Jay
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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 timeout

2003-01-13 Thread David Turner

Does anyone have some perl code that will return an error if it take longer
than a certain number of seconds to connect to or return the results from a
database? I'd like to have some of my queries connect to an alternate database
if there is a problem connecting or returning results within 10 seconds. Any 
other suggestions are appreciated.

Dave
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Turner
  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: #of blocks in extent

2003-01-13 Thread Miller, Jay

And of course to calculate you'll need to know your block size and your
extent size.

-Original Message-
Sent: Saturday, January 11, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


UNIFORM SIZE clause of Tablespace, if it is LMT.

--- Igor Neyman [EMAIL PROTECTED] wrote:
 INITIAL, NEXT, PCTINCREASE  -- if it's not LMT
 
 Igor Neyman, OCP DBA
 [EMAIL PROTECTED]
 
 
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Sent: Friday, January 10, 2003 2:54 PM
 
 
  How many blocks are allocated to an extend . what
 parameter decides that .
  Is it some storage param ?
  --
  Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
  --
  Author: BigP
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: Igor Neyman
   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).
 


=
cool 
amar
The best way to express yourself is to be yourself.

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Amar Kumar Padhi
  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: Miller, Jay
  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: Question On High Parse to execute ratio