date:20030107

Ferenc,

There's this new FREE product on the Metalink site. It's called ORA-600
Lookup (I don't bother to bookmark it, just do a search on that and the
first one in the search list is the link to the product). You put in
the version number, the first argument on the ora-600 and then, if
there is any document or anything that nearly matches it, you get links
to the POSSIBLE fixes.

Most of the time you get we don't have anything on that particular
error but we'll add it to the list of ones we will eventually document
but...

sometimes you get a match and it saves time, energy and wear and tear
on the sphincter muscles.  If you don't get a match, THEN you polite
ask Tim (I get 1% of that 10%!)

Rachel
--- mantfield [EMAIL PROTECTED] wrote:
 Sinardy
 
 ORA-0600 is really an encoded message from Oracle that reads :
 
 'Thank you for helping find yet another bug in our software. You can
 now 
 release your sphincter (some herbal tea might help), especially if
 this has 
 cropped up in production. We suggest you do not try to solve this one
 by 
 yourself, which is why the arguments are supplied. Kindly call OWWS
 with 
 your CSI number, tell them exactly which version and platform you ran
 this 
 on, and if we have encountered this previously, there is a small
 chance 
 that we could have a patch. If not, please do not hold your breath
 waiting, 
 while we assign this to an experienced software engineer, or perhaps
 a 
 recent college grad, in which case, you're toast !' However, we will
 ask 
 you to ftp up to our ftp site loads of dumps and traces which might
 prove 
 useful.
 
 Seriously though, ORA-0600 are mysteries. Your best chance is to get
 Tim 
 Gorman to look at it, he has an amazing knowledge of these. And of
 course, 
 Tim should not work for free either. (Tim, it was 10% commission we
 agreed 
 on, right ? ) :-)
 
 Ferenc Mantfeld
 
 -Original Message-
 From: Sinardy Xing [SMTP:[EMAIL PROTECTED]]
 Sent: Tuesday, January 07, 2003 2:34 PM
 To:   Multiple recipients of list ORACLE-L
 Subject:  ORA-00600: [2662]
 
 Hi all,
 
 I hit by this error
 
 ORA-00600: internal error code, arguments: [2662], [0], [54151123],
 [0],
 [54173017], [16781180], [], []
 
 
 Can you help me where to find info about this error
 
 
 
 
 Sinardy
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Sinardy Xing
   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: mantfield
   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.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

RE: MAX Number of Records in Cursor ?

2003-01-07 Thread Jamadagni, Rajendra

Title: RE: MAX Number of Records in Cursor ?





Possibly the some exception is being generated and (possibly) ignored? My best guess it the script bombs at some point and no one knows why ...

Remove any exception handlers in the script and re-run.


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: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 8:09 AM
To: Multiple recipients of list ORACLE-L
Subject: MAX Number of Records in Cursor ?




A Relationship manager needs to broadcast mail to all the Customers .
There are around 102,847 customer 


To achieve this functionality in our code we are opening a cursor, fetching each 
user id from a table and inserting into a mail table for each fetch.


Mail is getting Generated for only 7130 Customers .
There are no oracle errors reported in the log files .


Is there any size limitation in oracle while opening/fetching a cursor as the No. of 
records to be fetched are 102,847 ?


Thanks



*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

OT: Linux and Oracle Cluster File System

2003-01-07 Thread Jos

List,I am a newbie on Linux and Clustering technology, I need to setup a RAC system and have been reading somedocumentation on how to do it. I am a bit confuse about the difference between setting the disk up with Linux raw partition and Oracle cluster file system, I hope someone on the list can give me some hints oruseful reference for reading. The document said for Lunix raw partition I need to setup a partition pertablespace, this is quit a lot considering for Oracle Apps there is about 200 tablespaces. I am wonderingif I am using Oracle Clustering file system (OCFS), can I define one big partition for OCFS and mount it on /u01 and create all the files under this one mount point or the one raw partition per tablespace rule stillapplies.Jos
Yahoo! Greetings
- Send your seasons greetings online this year!

RE: True inner peace

oh yeah, we're alive and kicking. All the *interesting* people are
there :)

(this should give me a run on people to approve for subscription as no
one wants to be thought of as uninteresting G)


--- Boivin, Patrice J [EMAIL PROTECTED] wrote:
 Wow, the OT list is still running...
 
 Patrice Boivin
 Systems Analyst (Oracle Certified DBA)
 
 Systems Admin  Operations | Admin. et Exploit. des systèmes
 Technology Services| Services technologiques
 Informatics Branch | Direction de l'informatique 
 Maritimes Region, DFO  | Région des Maritimes, MPO
 
 E-Mail: [EMAIL PROTECTED]
 
 
 
 
 -Original Message-
 Sent: Monday, January 06, 2003 11:29 PM
 To: Multiple recipients of list ORACLE-L
 
 
 I *knew* I was forgetting something. damn, where did I leave that box
 of chocolates? The rest of your list are already long gone.
 
 :)
 
 Come to the dark side Ferenc, join us on the OT list before Jared and
 the members here get annoyed with the non-technical posts :)
 
 
 --- mantfield [EMAIL PROTECTED] wrote:
  By following the simple advice I read in an article, I have finally
  found inner peace...
  
  It said: The way to achieve inner peace is to finish all the
 things
  I had started.
  
  So, today I have finished one bottle of cognac, two bottles of red
  wine, a bottle of Jack Daniels, my Prozac's, and a box of
 chocolates.
  
  Amazing, I feel better already!
  
  Pass this on to all those in need of Inner Peace!
  
  Ferenc Mantfeld
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: mantfield
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.
 http://mailplus.yahoo.com
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Rachel Carmichael
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Boivin, Patrice J
   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.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

Re: MAX Number of Records in Cursor ?

2003-01-07 Thread tim

There are no limits on the number of rows which can be
retrieved.  Please check the query and the application code
surrounding it more closely.

I'd suggest extracting the query into SQL*Plus and running
it there.  If the behavior is as expected, then the
surrounding application code is at fault.  Else, the query
is faulty...

 
 A Relationship manager needs to broadcast mail to all the
 Customers . There are around 102,847 customer 
 
 To achieve this functionality in our code we are opening a
 cursor, fetching each  user id from a table and inserting
 into a mail table for each fetch. 
 Mail is getting Generated for only 7130 Customers .
 There are no oracle errors reported in the log files .
 
 Is there any size limitation in oracle while
 opening/fetching a cursor as the No. of  records to be 
 fetched are 102,847 ? 
 Thanks
 
 -- 
 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: 
  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).

AW: AW: Unix for oracle dba -- Suggest a book ?

wow, that was stupid of me !!

UNIX System Administration Handbook (3rd Edition)
by Evi Nemeth, Garth Snyder, Scott Seebass, Trent R. Hein

Unix for Oracle DBAs Pocket Reference
by Donald K. Burleson

(I copied the ISBN from Amazon).

Regards,
Stefan



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

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

AW: Unix for oracle dba -- Suggest a book ?

I read it and love it. The only thing I was wondering about is the fact,
that he uses tcl/tk, which I found most people don't use anymore. Nice
surprise.
I wasn't quite sure wether oraora was looking for books that gives more of a
general overview of books that delve into the depth of unix internals.
Anyway, here is my favorite on Unix internals (hence, the name of the book
;):

UNIX Internals: The New Frontiers by Uresh Vahalia

Eventhough it was published in 1995, it gives you a very good understanding
about how things really work and why they work the way they do.

Regards,
Stefan

-Ursprüngliche Nachricht-
Von: Hately, Mike (NESL-IT) [mailto:[EMAIL PROTECTED]]
Gesendet: Montag, 6. Januar 2003 18:04
An: Multiple recipients of list ORACLE-L
Betreff: RE: Unix for oracle dba -- Suggest a book ?


If you want to understand how Oracle uses Unix get a copy of James Morle's
Scaling Oracle.
It's not a beginner's Unix book but it's accurate and detailed.

regards,
Mike Hately

-Original Message-
Sent: 06 January 2003 15:59
To: Multiple recipients of list ORACLE-L



Doesn't anyone read the manuals any more?!

Oracle9i Installation Guide - Unix
  http://download-east.oracle.com/docs/html/A96167_01/toc.htm
Oracle9i Administrator's Reference - Unix
  http://download-east.oracle.com/docs/html/A97297_01/toc.htm




 

  James Damiano

  [EMAIL PROTECTED]To:   Multiple recipients
of list ORACLE-L [EMAIL PROTECTED] 
  .nh.us  cc:

  Sent by: Subject:  Re: Unix for oracle
dba -- Suggest a book ? 
  [EMAIL PROTECTED]

 

 

  01/06/03 06:28 AM

  Please respond to

  ORACLE-L

 

 





I've found a wonderful resource in the following book:

Oracle DBA on Unix and Linux
by Michael Wessler
http://www.samspublishing.com

It covers some of the differences in features between 8i and 9i as well as
handling the specifics of administrating Oracle specifically on Unix
platforms.  Highly recommended (at least by me).

Jim Damiano


 Guys,

 i know a bit of Linux.and not completely a newbie to Unix.

 Can u suggest me a good/best book for Unix ?
 ..Unix for oracle DBA.
 i.e,tuning unix for good performance of oracle.

 any such book available ?
 kindly let me know guys.

 TIA.
 Jp.


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

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






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

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

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

AW: Unix for oracle dba -- Suggest a book ?

Yes. This is also a very good book. I own an older version for Oracle 8.0.x,
but I remember it to be pretty well written and concise.

Another remark on Unix books for Oracle DBAs: My company got a copy of
Oracle 9i Unix Adminstration Handbook by Don Burleson. It starts at the very
beginnings of command line tools and the like, so I already knew that stuff.
I was surprised how badly the book was reviewed by the editors (I guess),
since there were so many (small but still) glitches in there, starting from
the explanation of /etc/passwd to mixing up DOS command line tools and Unix
ones. Nothing big, but if you are a total novice, these kind of things might
be confusing and a professional book at the price of about 50 bucks
shouldn't have that many mistakes. This is very unfortunate, since it is
overall a very neat volumen. That almost reminds me of the Couchman OCP
study guide. Your were ready to be certified by the time you were able to
identify all the errors in the book and to correct them ;).

Regards,
Stefan


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

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

RE: Re: MAX Number of Records in Cursor ?

2003-01-07 Thread Stephane Faroult

Besides, Vivek, have you ever heard about INSERT ... SELECT ? Why, I ask, do you want 
to loop ?

- Original Message -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tue, 07 Jan 2003 05:54:17

There are no limits on the number of rows which can
be
retrieved.  Please check the query and the
application code
surrounding it more closely.

I'd suggest extracting the query into SQL*Plus and
running
it there.  If the behavior is as expected, then the

surrounding application code is at fault.  Else,
the query
is faulty...

 A Relationship manager needs to broadcast mail to
all the
 Customers . There are around 102,847 customer 

 To achieve this functionality in our code we are
opening a
 cursor, fetching each  user id from a table and
inserting
 into a mail table for each fetch. 
 Mail is getting Generated for only 7130 Customers
.
 There are no oracle errors reported in the log
files .

 Is there any size limitation in oracle while
 opening/fetching a cursor as the No. of  records
to be 
 fetched are 102,847 ? 
 Thanks

-- 
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: MAX Number of Records in Cursor ?

That I know of, there's no limit to the number of rows fetched by a
cursor. I've certainly returned more rows than that.

Have you tried the sql statement in SQL*Plus? I know I advocate this a
lot, but it does tell you if the problem is the sql itself. Are you
SURE there are over 100K customers to get the mail? Do a select
count(*) from the mail table. run a counter in your program every time
you fetch from the cursor and another when you do an insert and display
the counters at the end.  Is there a exception that you aren't
handling?

Standard debugging techniques. 


--- VIVEK_SHARMA [EMAIL PROTECTED] wrote:
 
 A Relationship manager needs to broadcast mail to all the Customers .
 There are around 102,847 customer 
 
 To achieve this functionality in our code we are opening a cursor,
 fetching each 
 user id from a table and inserting into a mail table for each fetch.
 
 Mail is getting Generated for only 7130 Customers .
 There are no oracle errors reported in the log files .
 
 Is there any size limitation in oracle while opening/fetching a
 cursor as the No. of 
 records to be  fetched are 102,847 ?
 
 Thanks
 
 -- 
 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).
 


__
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: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

RE: ORA-00600: [2662]

Mogens, 
 You meant K. Gopal (aka X$KG) is on the right track... and not Kirti. ;) 
 I do not recall posting in this thread. 
 
- Kirti 

-Original Message-
Sent: Tuesday, January 07, 2003 1:59 AM
To: Multiple recipients of list ORACLE-L


This response from Oracle is not a good one. Kirti is on the right track 
regarding the SCN stuff as far as I remember. Other than that, I know 
that Peter Gram and Bjorn Engsig (among a lot of other guys) might be 
able to suggest something, so I've CC'ed them.

I have my own law on ora-600s: If you supply all the required 
information (tracefiles, alertlogs, repro-case, etc.) chances are 
Support is not gonna need them. If you miss out just one of those 
things, chances are Support will tell you they cannot proceed without 
that vital piece of information. In other words: There's no hope :-)))

Could also be related to db-links between v7 and v8...

But back to the good questions from Kirti: Have you set the funny 
_-parameters he mentions?


Mogens

PS: I do beleive there are ways around this with an alter system or 
alter session command where you bump the SCN some level, but 
Peter/Bjorn/someone can confirm this.

Sinardy Xing wrote:

Hi guys,


This is what I get from Oracle

==

Hi Sinardy,

I've check several bug looking for the cause of this problem. However, what I've 
found is that Development could not diagnosis the problem other than the possibily 
hat there was some memory corruption that gave a bad SCN. Although, they could 
sometimes determine the root cause prior to the problem occurring by using certain 
parameters were set in the database. 

Those parameters are:

a)_db_block_cache_protect.
b)_db_block_checking
c)DB_BLOCK_CHECKSUM 

They also recommended running dbverify on all datafiles tomake sure all files are 
clean on disk.

IMPORTANT
==
These parameter may help later determine what cause the ORA-600 [2662] (and it's not 
for certain that a cause can be found). They parameters does have some performance 
overhead, but unfortunately this is the only way that can help in catching and we 
have to wait until the problem happens again.

So, the bottomline is there is no list of reason for why this error occuring other 
than the error occurs when a data block SCN is ahead of the current SCN.

Sorry Sinardy, we don't have more information.

Thanks!
Wonda

=

What can I do, help me please...


Sinardy



-Original Message-
Sent: 07 January 2003 13:09
To: Multiple recipients of list ORACLE-L


Sinardy

ORA-0600 is really an encoded message from Oracle that reads :

'Thank you for helping find yet another bug in our software. You can now 
release your sphincter (some herbal tea might help), especially if this has 
cropped up in production. We suggest you do not try to solve this one by 
yourself, which is why the arguments are supplied. Kindly call OWWS with 
your CSI number, tell them exactly which version and platform you ran this 
on, and if we have encountered this previously, there is a small chance 
that we could have a patch. If not, please do not hold your breath waiting, 
while we assign this to an experienced software engineer, or perhaps a 
recent college grad, in which case, you're toast !' However, we will ask 
you to ftp up to our ftp site loads of dumps and traces which might prove 
useful.

Seriously though, ORA-0600 are mysteries. Your best chance is to get Tim 
Gorman to look at it, he has an amazing knowledge of these. And of course, 
Tim should not work for free either. (Tim, it was 10% commission we agreed 
on, right ? ) :-)

Ferenc Mantfeld

-Original Message-
From:  Sinardy Xing [SMTP:[EMAIL PROTECTED]]
Sent:  Tuesday, January 07, 2003 2:34 PM
To:Multiple recipients of list ORACLE-L
Subject:   ORA-00600: [2662]

Hi all,

I hit by this error

ORA-00600: internal error code, arguments: [2662], [0], [54151123], [0],
[54173017], [16781180], [], []


Can you help me where to find info about this error




Sinardy

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sinardy Xing
  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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: [EMAIL PROTECTED]

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

RE: encrypted user/passwd connection


All oracle passwords are encrypted is not a true statement. Failed login
attempts, are retried by sending the password in an unencrypted format.
Atleast, until 8.1.7. To avoid which, ORA_ENCRYPT_LOGIN variable and
DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database link)
should be set to TRUE.

I could stand corrected though.

Raj




   
   
Sony kristanto 
   
Sony@polyfincaTo: Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
nggih.com cc: 
   
Sent by:   Subject: RE: encrypted user/passwd 
connection  
[EMAIL PROTECTED]
   
m  
   
   
   
   
   
January 07,
   
2003 01:53 AM  
   
Please respond 
   
to ORACLE-L
   
   
   
   
   




You're right Jared, all oracle password is encrypted. Btw Andrey if it is
possible how to do it ?

 -Original Message-
 From: Jared Still [SMTP:[EMAIL PROTECTED]]
 Sent: Tuesday, January 07, 2003 11:04 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:   Re: encrypted user/passwd connection


 Andre,

 Oracle does not send passwords across the network
 in clear text, they are encrypted by default.

 Jared

 On Monday 06 January 2003 05:43, Andrey Bronfin wrote:
  Dear list !
  I have just been asked the following question:
  is it possible to make a connection from an Oracle client to an Oracle
  instance (both are 8.1.7) in an encrypted way.
  I.e. if someone is sitting with a sniffer between the server and the
  client, then i don't want him to be able to see the user/passwd i'm
  connecting with. Again , i am NOT asking how store the data in the DB
in
 an
  encrypted way, but how to connect to an instance without showing my
  passwd.
  Thanks a lot!
  Andrey.


-- 
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: 9iRAC on SAN ?

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

The 9iRAC interconnect which comes with the product is specific to the
hardware platform and manages the connectivity failover in concert with the
cluster software.

Regards,

Bill Burke
The Kinder and Gentler DBA
IOUG University Master Class Faculty 2001  2002
iDBA Management, Infrastructure and HA
www.OracleGuru.com
www.KBMotorsports.biz



-Original Message-
Sent: Tuesday, January 07, 2003 3:44 AM
To: Multiple recipients of list ORACLE-L



I have a question from my SysAdmin/Storage guys.

How can an Oracle 9iRAC database run on a SAN ?
A SAN supposedly allows only one node to access
a disk.  Can multiple nodes in a 9iRAC cluster
access database files on a SAN ?

We can understand a dual-hosted (or is it
dual-ported ?) Storage which allows two nodes
to access the disks.

Is there a difference for the SAN implementation
if the DB server is 
HPUX (Database Files on Raw Devices) or 
Tru64 (Database Files on a Cluster File System) ?

Hemant K Chitale
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: 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 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: ORA-00600: [2662]

Here is another product that goes after ORA-600: 
http://www.ubTools.com/cgi-bin/ib/ikonboard.cgi?act=Pages;page=iorabugfinder
The creator, Danisment Unal, used to be on the list. 


- Kirti 

-Original Message-
Sent: Tuesday, January 07, 2003 7:34 AM
To: Multiple recipients of list ORACLE-L


Ferenc,

There's this new FREE product on the Metalink site. It's called ORA-600
Lookup (I don't bother to bookmark it, just do a search on that and the
first one in the search list is the link to the product). You put in
the version number, the first argument on the ora-600 and then, if
there is any document or anything that nearly matches it, you get links
to the POSSIBLE fixes.

Most of the time you get we don't have anything on that particular
error but we'll add it to the list of ones we will eventually document
but...

sometimes you get a match and it saves time, energy and wear and tear
on the sphincter muscles.  If you don't get a match, THEN you polite
ask Tim (I get 1% of that 10%!)

Rachel
--- mantfield [EMAIL PROTECTED] wrote:
 Sinardy
 
 ORA-0600 is really an encoded message from Oracle that reads :
 
 'Thank you for helping find yet another bug in our software. You can
 now 
 release your sphincter (some herbal tea might help), especially if
 this has 
 cropped up in production. We suggest you do not try to solve this one
 by 
 yourself, which is why the arguments are supplied. Kindly call OWWS
 with 
 your CSI number, tell them exactly which version and platform you ran
 this 
 on, and if we have encountered this previously, there is a small
 chance 
 that we could have a patch. If not, please do not hold your breath
 waiting, 
 while we assign this to an experienced software engineer, or perhaps
 a 
 recent college grad, in which case, you're toast !' However, we will
 ask 
 you to ftp up to our ftp site loads of dumps and traces which might
 prove 
 useful.
 
 Seriously though, ORA-0600 are mysteries. Your best chance is to get
 Tim 
 Gorman to look at it, he has an amazing knowledge of these. And of
 course, 
 Tim should not work for free either. (Tim, it was 10% commission we
 agreed 
 on, right ? ) :-)
 
 Ferenc Mantfeld
 
 -Original Message-
 From: Sinardy Xing [SMTP:[EMAIL PROTECTED]]
 Sent: Tuesday, January 07, 2003 2:34 PM
 To:   Multiple recipients of list ORACLE-L
 Subject:  ORA-00600: [2662]
 
 Hi all,
 
 I hit by this error
 
 ORA-00600: internal error code, arguments: [2662], [0], [54151123],
 [0],
 [54173017], [16781180], [], []
 
 
 Can you help me where to find info about this error
 
 
 
 
 Sinardy


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

Exam Cram is back

2003-01-07 Thread DENNIS WILLIAMS

For those of you that enjoyed Exam Cram, I received the following newsletter
and thought I would pass it along.

Dear Exam Cram member,

We're back, and better than ever!  The Exam Cram site you
used to know and love is now Exam Cram 2
(http://www.examcram2.com), produced by a joint effort
between InformIT (http://www.informit.com) and Que
Certification.

::: YOUR EXAM CRAM 2 NEWSLETTERS :::

Exam Cram members with subscriptions to the original
Question of the Day newsletters will begin receiving them
again this month. (Please note that we've had to retire
some of the older exams and will not be sending newsletters
supporting those exams.)

If you don't want to automatically continue your
subscription to these newsletters, please click the
following link:

http://www.informit.com/u.asp?[EMAIL PROTECTED]


::: EXAM CRAM 2 FREE PRACTICE EXAMS :::

The new Exam Cram 2 (http://www.examcram2.com) site is your
source for online practice exams, offering free practice
tests on a wide variety of exam topics with new, high-
quality questions.

Over 15 new practice exams will be added to the site in the
next few weeks, so check back often.

::: INFORMIT CERTIFICATION CENTER :::

Ed Tittel, creator of the original Exam Cram book series,
returns as the Exam Cram 2 series editor and will become
the resident Certification Expert on InformIT. Visit the
Certification Center for articles and discussion on
certification topics. Check it out:

http://www.informit.com/link.asp?link=certcntr

::: EXAM CRAM 2 BOOKS :::

The new Exam Cram 2 book series will continue to provide
the same focused, relevant, and timely coverage of key
certification exam topics, concepts, and study strategies.
Each book is extensively reviewed by industry experts and
holds the CramSession seal of approval. See the books here:

http://www.informit.com/link.asp?link=ec2books

We look forward to serving you at the new Exam Cram 2!

Sincerely,

The Exam Cram 2 Team
Exam Cram 2 - the Smartest Way To Get Certified(TM)!
-- 
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: How to delete duplicate records with condition

2003-01-07 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



   

  roland.skoldblom@

  ica.se   To:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]   
  Sent by: cc: 

  [EMAIL PROTECTED] Subject:  How to delete duplicate 
records with condition
   

   

  01/07/2003 03:28 

  PM   

  Please respond to

  ORACLE-L 

   

   





Hallo,

I have this sql,

SELECT * FROM varukorgtmp
where varukorgid= 120 That makes the result of this testfile.xls


(See attached file: start.xls)

There are two values in EAN-field, which are the same
23324614 in row 2 and 3

Now I want in an sql script to check out which are the duplicates of EAN in
that table. Then then the script will check which is VARUTYP = 3, then
delete the record(s) which have VARUTYP =3.

So the result should be like this, with only two rows left in this case).


(See attached file: result.xls)

I would really appreciate if anyone could help me with this sql  I have
tried several sql, but with no luck.

Thanks in advance

Roland









(See attached file: start.xls)(See attached file: result.xls)





start.xls
Description: application/msexcel


result.xls
Description: application/msexcel

RE: Are too many Foreign Keys in one table bad?

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

A couple of other thoughts depending on the size of the table with the large
number of foreign keys (I may have missed the exact row counts), you might
want to consider bitmaps on the foreign keys in the main table depending on
the uniqueness of the data.  Also, if the foreign key tables are relatively
small another possibility to consider would be an indexed table if the joins
would naturally grab the whole table.

Regards,

Bill Burke
The Kinder and Gentler DBA
Live 2003 Expert Presentation - Where there's smoke there's fire -
Firefighter or Arsonist
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 - All UMC and Conference Presentations are here
www.KBMotorsports.biz



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



And apart from the differences in cost on the
simple test, you also remove the information
about uniqueness and non-nullability if you don't
declare the primary key, and this has an impact
on the optimizer's decision tree.

Bear in mind, also, that Oracle will rarely do a 
tablescan on the inner table of a nested loop -
so you may get a fifteen table hash join if you don't
have any indexes, and this MIGHT go to one of the
two possible extremes of demanding nearly 14 x
hash_area_size in memory, or 14 allocations of 
temporary extents on your temporary tablespace.


Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates: 
(see http://www.jlcomp.demon.co.uk/seminar.html )

England__January 21/23
USA_(CA, TX)_August


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



-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 07 January 2003 02:45




There can be quite a difference between using
an index on a small table, and not using one.



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

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

AW: True inner peace

Are you dead ?!

-Ursprüngliche Nachricht-
Von: mantfield [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 7. Januar 2003 02:59
An: Multiple recipients of list ORACLE-L
Betreff: True inner peace


By following the simple advice I read in an article, I have finally found
inner peace...

It said: The way to achieve inner peace is to finish all the things I had
started.

So, today I have finished one bottle of cognac, two bottles of red wine, a
bottle of Jack Daniels, my Prozac's, and a box of chocolates.

Amazing, I feel better already!

Pass this on to all those in need of Inner Peace!

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

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

RE: AW: Unix for oracle dba -- Suggest a book ?

In addition to all the books already mentioned, I find following reference useful when 
dealing with multiple flavours of UNIX.. 
http://bhami.com/rosetta.html

- Kirti 


-Original Message-
Sent: Tuesday, January 07, 2003 7:59 AM
To: Multiple recipients of list ORACLE-L


wow, that was stupid of me !!

UNIX System Administration Handbook (3rd Edition)
by Evi Nemeth, Garth Snyder, Scott Seebass, Trent R. Hein

Unix for Oracle DBAs Pocket Reference
by Donald K. Burleson

(I copied the ISBN from Amazon).

Regards,
Stefan



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

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



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: Perl DBI/SQL question - For those who use it...

2003-01-07 Thread Markus Reger

in case this is a windog machine - install kind of unix shell. and the unix style 
commands work perfectly - just tried it. either use unix tools for windog or cygwin 
from rh.
or dump the w...
have fun.

 [EMAIL PROTECTED] 01/03/03 18:40 PM 
Hi everyone, 

This may be a stupid question.  If so please humor me with a stupid answer.
However: 

I FINALLY have the fun fun fun chance to change one of my data loads to use
the DBI instead of the procedures I hacked together.  In true ksh style I
had written my loads to fire a sql script (calling a stored proc) that was
stored separately.  It seems to me the DBI wants the text of the sql script
embedded piece by piece in the code.  I have looked around for examples
because even though the DBI seems straightforward, it doesn't take much to
confuse me.  I don't see examples of firing a sql script from the DBI (like
this sqlplus /@dbname  @script.sql  logfile.log ... Gosh do I miss unix,
everything was SO EASY)

So my questions to you, my learned friends, are: 

1. is it not perl-style to store the sql in a separate file?  I understand I
may be missing the opportunity for more specific error handling here but
honestly at this point it does not matter.  The thing fails, I restart the
whole script.

2. Does anyone have an example of firing the DBI and calling a sql script
like I could so easily do in ksh? 

Any and all comments are welcome.  Thank you 

I wish everyone a rested and relaxing weekend. 

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Markus Reger
  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: No logging question

If this is a one time DML activity, one may get creative with TTS and this hidden 
parameter :)  

- Kirti 

-Original Message-
Sent: Tuesday, January 07, 2003 4:39 AM
To: Multiple recipients of list ORACLE-L


In that case, you are pretty much SOL, sorry. nologging option of tables 
only applies to direct path operations:
create as select
direct path Sql*Loader
insert /*+ APPEND */

Other than that, all other DML will get logged in the redo log, and as 
several have pointed out, using the _disable_logging parameter is highly 
risky, and it is a catch-all situation.

HTH. Regards:

Ferenc Mantfeld

-Original Message-
From:   GL2Z/ INF  DBA BENLATRECHE [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, January 07, 2003 7:04 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: No logging question

As I understand, this hidden parameter is for the whole database.
In my case It's just for a few tables (I don't care about recovery of these
tables).

Thank you !

Best Regards
Kamel Benlatreche

-Message d'origine-
De : mantfield [mailto:[EMAIL PROTECTED]]
Envoye : lundi 6 janvier 2003 19:19
A : Multiple recipients of list ORACLE-L
Objet : RE: No logging question


Welcome to the dark side, heh heh heh !

There is a hidden parameter that will achieve this . it is
_disable_logging. Be warned though, if you set it to true, you have lost
your ability to recover, and the only way you will recover is with full
cold backup and would lose all subsequent work (not generally popular with
the users), so the whole point of running in ARCHIVELOG mode becomes moot.

I have used this while still at Siebel, when doing very large installs or
upgrades of large databases in a tight time-window, and the customers
refused to move the redo logs over to solid state storage, I got about a
40% - 100% performance improvment, especially if the clients were conned
into placing the redo logs onto RAID-5 storage (bad for serial
write-intensive operations).

Nevertheless, if you have a specific need to disable all redo generation
for a specific time, this is the way to do it. BTW, it does not work on
Orale for Windows.

You have been warned, don't go trying this in production first thing, it
could cost you your job or more.

Regards :

Ferenc Mantfeld

-Original Message-
From:   GL2Z/ INF  DBA BENLATRECHE [SMTP:[EMAIL PROTECTED]]
Sent:   Monday, January 06, 2003 8:34 PM
To: Multiple recipients of list ORACLE-L
Subject:No logging question

Hi All,


  I want to avoid generation of archivelogs against all the DML applied
to some tables.

Thank you
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: GL2Z/ INF  DBA BENLATRECHE
  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: mantfield
  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: GL2Z/ INF  DBA BENLATRECHE
  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: mantfield
  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]

RE: Caching a huge table's data in memory

2003-01-07 Thread Stephen Lee


Pro: It is in memory

Con: You must buy the memory.  You must be sure you don't cause the box to
start paging to swap ... very bad ... VERY bad.

When you live in the land of bad applications, sometimes the politics of the
situation are such that you just do what you know is the equivalent of
fixing things with duct tape  -- a whole lot of duct tape -- rather than
attempt to engage in a hopeless fight.  Consider the case of an application
that is so bad that a box with more than 50 Gb of RAM and 16 Alpha CPU's are
required to handle from 6 to 10 active connections.  Hey, if they want to
spend the money 


 -Original Message-
 What are the pros and cons of caching a table's data?  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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: Are too many Foreign Keys in one table bad?

2003-01-07 Thread Gregory Norris

Thanks to all for the advice.. 
Ps I am an Oz type living in Canada - just flew here at xmas and yes it aint
cheap but ooh airline food is great!
CIAO

-Original Message-
Sent: Monday, January 06, 2003 10:29 PM
To: Multiple recipients of list ORACLE-L


I'm flattered you put me in the same category as Kirti, Dan, Jared,
Connor and Jonathan.

If I recall the cost of my (one) trip to Australia, it would be cheaper
to import you to IOUG than it would be to get us there!  I'm not sure
about Jared and Connor, the rest of us will be at IOUG. And for the
price of a drink of Black Adder Scotch I'm sure you could get Dan to
expound on Oracle and backup and recovery and blockcentric tuning all
night long. Okay, make that a bottle of BlackAdder and make sure he
isn't presenting first thing the next morning :)


--- Mark Richard [EMAIL PROTECTED] wrote:
 All,
 
 Point well taken (both Rachel's and Jared's).  I should have said
 (and was
 even thinking - although the brain and hands sometimes act
 independently)
 might not be worth indexing.  It sounds like a helpdesk system for
 a
 pretty small customer base so I was assuming that system load isn't
 likely
 to be a problem.  My experience has always been that if the fact is
 1000
 rows and the reference are maybe 3 - 10 then Oracle is going to eat
 it up
 for lunch no matter how it's structured unless a large number of
 concurrent
 user come along.
 
 Now on a more serious note, when is the week-long Rachel Carmichael,
 Dan
 Fink, Jonathan Lewis, Connor, Jared, Kirti, et al How to well and
 truly
 beat Oracle into Submission seminar coming down under to Australia? 
 I
 need to know so that I can start selling my soul to raise enough
 money to
 attend...  With our dollar the way it is a seminar like that would
 cost
 about the same as my house.
 
 
 
  
  
 Rachel   
  
 Carmichael   To: Multiple recipients
 of list ORACLE-L [EMAIL PROTECTED]   
 wisernet100@y   cc: 
  
 ahoo.comSubject: Re: Are too
 many Foreign Keys in one table bad?  
 Sent by: 
  
 [EMAIL PROTECTED]   
  
 om   
  
  
  
  
  
 07/01/2003   
  
 12:33
  
 Please respond   
  
 to ORACLE-L  
  
  
  
  
  
 
 
 
 
 Mark,
 
 Based on the presentation and testing Dan Fink did for the last NYOUG
 meeting, it's possible that the ref tables SHOULD be indexed, and
 that
 it will help performance to index them.
 
 Rachel
 
 --- Mark Richard [EMAIL PROTECTED] wrote:
  Greg,
 
  I don't think Oracle will have a real problem with 15 tables or
 1,000
  rows.
  If the ref tables are quite small then they won't even be worth
  indexing -
  Oracle will just read the entire table at one anyway.  You might
 want
  to
  tell Oracle to CACHE the reference tables, although I don't think
  you'll
  see a performance gain really.  Unfortunately I can't give any
  performance
  suggestions because I am used to the other end of the scale (ie:
 250
  million rows in data)
 
  You probably could store CODE in the main table, but if you are
 going
  to
  need the description frequently then all benefit is lost anyway.
  Either
  way though I'm sure that you'll have more problems getting the 15
  joins
  right when writing the queries than Oracle's CBO will have when
  looking at
  the query - I've seen some real nasty queries get pushed into
  Oracle's
  optimisor and as long at the statistics are valid then it does a
  pretty
  good job.
 
  Cheers,
   Mark.
 
  PS:  Why would the

Re: AW: AW: Unix for oracle dba -- Suggest a book ?

2003-01-07 Thread John Sheraton

Don's book is very good. Highly recommend.

RF
--- Stefan Jahnke [EMAIL PROTECTED] wrote:
 wow, that was stupid of me !!
 
 UNIX System Administration Handbook (3rd Edition)
 by Evi Nemeth, Garth Snyder, Scott Seebass, Trent R.
 Hein
 
 Unix for Oracle DBAs Pocket Reference
 by Donald K. Burleson
 
 (I copied the ISBN from Amazon).
 
 Regards,
 Stefan
 
 
 
  
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Stefan Jahnke
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

-
 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing).
 

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

email out of oracle

2003-01-07 Thread Leonard, George

Hi there

Trying to email out from Oracle.

Utl_smtp is installed, executing procedure currently as a DBA. This sun
Machine does send email notifications out via the crontab to me so I know I
can send email via the exchange smtp server.

Problem, Email packages execute, if I do a print I see code 
SQL print

NP
--
-29540

Package executed with following command:

var np number;
exec send_mail('[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', 'testmsg', :np);

Below is the code of the send_mail package, can anyone see the problem or
know what this error code means.

Thx George


System

Oracle 8.1.6.3 EE 32 Bit
Solaris 2.6

--
-- Sending email out of Oracle using a stored procedure.
--
Create or replace PROCEDURE
  send_mail (senderIN VARCHAR2,
 recipient IN VARCHAR2,
 message   IN VARCHAR2,
 nStatus   OUT NUMBER)
IS
mailhostVARCHAR2(30) := '90.1.1.100';
mail_conn  utl_smtp.connection;

BEGIN
nStatus := 0;
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);

utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
nStatus := SQLCODE;
END send_mail;
/

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Cell: (+27) 82 655 2466
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed  Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!

-Original Message-
Sent: 07 January 2003 14:09 PM
To: Multiple recipients of list ORACLE-L

I read it and love it. The only thing I was wondering about is the fact,
that he uses tcl/tk, which I found most people don't use anymore. Nice
surprise.
I wasn't quite sure wether oraora was looking for books that gives more of a
general overview of books that delve into the depth of unix internals.
Anyway, here is my favorite on Unix internals (hence, the name of the book
;):

UNIX Internals: The New Frontiers by Uresh Vahalia

Eventhough it was published in 1995, it gives you a very good understanding
about how things really work and why they work the way they do.

Regards,
Stefan

-Ursprüngliche Nachricht-
Von: Hately, Mike (NESL-IT) [mailto:[EMAIL PROTECTED]]
Gesendet: Montag, 6. Januar 2003 18:04
An: Multiple recipients of list ORACLE-L
Betreff: RE: Unix for oracle dba -- Suggest a book ?


If you want to understand how Oracle uses Unix get a copy of James Morle's
Scaling Oracle.
It's not a beginner's Unix book but it's accurate and detailed.

regards,
Mike Hately

-Original Message-
Sent: 06 January 2003 15:59
To: Multiple recipients of list ORACLE-L



Doesn't anyone read the manuals any more?!

Oracle9i Installation Guide - Unix
  http://download-east.oracle.com/docs/html/A96167_01/toc.htm
Oracle9i Administrator's Reference - Unix
  http://download-east.oracle.com/docs/html/A97297_01/toc.htm




 

  James Damiano

  [EMAIL PROTECTED]To:   Multiple recipients
of list ORACLE-L [EMAIL PROTECTED] 
  .nh.us  cc:

  Sent by: Subject:  Re: Unix for oracle
dba -- Suggest a book ? 
  [EMAIL PROTECTED]

 

 

  01/06/03 06:28 AM

  Please respond to

  ORACLE-L

 

 





I've found a wonderful resource in the following book:

Oracle DBA on Unix and Linux
by Michael Wessler
http://www.samspublishing.com

It covers some of the differences in features between 8i and 9i as well as
handling the specifics of administrating Oracle specifically on Unix
platforms.  Highly recommended (at least by me).

Jim Damiano


 Guys,

 i know a bit of Linux.and not completely a newbie to Unix.

 Can u suggest me a good/best book for Unix ?
 ..Unix for oracle DBA.
 i.e,tuning unix for good performance of oracle.

 any such book available ?
 kindly let me know guys.

 TIA.
 Jp.


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

RE: Are too many Foreign Keys in one table bad?

Black Adder is an excellent method to get me to shut up and leave!

-Original Message-
Sent: Monday, January 06, 2003 8:29 PM
To: Multiple recipients of list ORACLE-L


I'm flattered you put me in the same category as Kirti, Dan, Jared,
Connor and Jonathan.

If I recall the cost of my (one) trip to Australia, it would be cheaper
to import you to IOUG than it would be to get us there!  I'm not sure
about Jared and Connor, the rest of us will be at IOUG. And for the
price of a drink of Black Adder Scotch I'm sure you could get Dan to
expound on Oracle and backup and recovery and blockcentric tuning all
night long. Okay, make that a bottle of BlackAdder and make sure he
isn't presenting first thing the next morning :)


--- Mark Richard [EMAIL PROTECTED] wrote:
 All,
 
 Point well taken (both Rachel's and Jared's).  I should have said
 (and was
 even thinking - although the brain and hands sometimes act
 independently)
 might not be worth indexing.  It sounds like a helpdesk system for
 a
 pretty small customer base so I was assuming that system load isn't
 likely
 to be a problem.  My experience has always been that if the fact is
 1000
 rows and the reference are maybe 3 - 10 then Oracle is going to eat
 it up
 for lunch no matter how it's structured unless a large number of
 concurrent
 user come along.
 
 Now on a more serious note, when is the week-long Rachel Carmichael,
 Dan
 Fink, Jonathan Lewis, Connor, Jared, Kirti, et al How to well and
 truly
 beat Oracle into Submission seminar coming down under to Australia? 
 I
 need to know so that I can start selling my soul to raise enough
 money to
 attend...  With our dollar the way it is a seminar like that would
 cost
 about the same as my house.
 
 
 
  
  
 Rachel   
  
 Carmichael   To: Multiple recipients
 of list ORACLE-L [EMAIL PROTECTED]   
 wisernet100@y   cc: 
  
 ahoo.comSubject: Re: Are too
 many Foreign Keys in one table bad?  
 Sent by: 
  
 [EMAIL PROTECTED]   
  
 om   
  
  
  
  
  
 07/01/2003   
  
 12:33
  
 Please respond   
  
 to ORACLE-L  
  
  
  
  
  
 
 
 
 
 Mark,
 
 Based on the presentation and testing Dan Fink did for the last NYOUG
 meeting, it's possible that the ref tables SHOULD be indexed, and
 that
 it will help performance to index them.
 
 Rachel
 
 --- Mark Richard [EMAIL PROTECTED] wrote:
  Greg,
 
  I don't think Oracle will have a real problem with 15 tables or
 1,000
  rows.
  If the ref tables are quite small then they won't even be worth
  indexing -
  Oracle will just read the entire table at one anyway.  You might
 want
  to
  tell Oracle to CACHE the reference tables, although I don't think
  you'll
  see a performance gain really.  Unfortunately I can't give any
  performance
  suggestions because I am used to the other end of the scale (ie:
 250
  million rows in data)
 
  You probably could store CODE in the main table, but if you are
 going
  to
  need the description frequently then all benefit is lost anyway.
  Either
  way though I'm sure that you'll have more problems getting the 15
  joins
  right when writing the queries than Oracle's CBO will have when
  looking at
  the query - I've seen some real nasty queries get pushed into
  Oracle's
  optimisor and as long at the statistics are valid then it does a
  pretty
  good job.
 
  Cheers,
   Mark.
 
  PS:  Why would the reference CODE change instead of the
 DESCRIPTION?
  I'm
  guessing the code will be

FULL TABLE SCAN?

2003-01-07 Thread Seema Singh

Hi
How to avoid FULL TABLE SCAN?
Thx
-seema





_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Seema Singh
 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: Database/system Crashing

2003-01-07 Thread Webber Valerie H

Title: RE: Database/system Crashing



Well I relocated the background dest files and I got the 
following error... that was a great idea!

ORA-00206: error in writing (block 3, # blocks 1) of 
controlfileORA-00202: controlfile: 
'/u04/oradata/ERCS/ora_control2'ORA-27063: Message 27063 not found; 
product=RDBMS; facility=ORASVR4 Error: 5: I/O errorAdditional 
information: -1Additional information: 2048error 221 detected in 
background process

The SA's think its a data block corruption. If anyone has 
any additional information, it will be greatly appreciated.
At least now I know why the database crashed to begin with. 
Now the SA's just have to figure out how to fix it.

Thanks for all the help!!
Val

  -Original Message-From: Burke, William F (Bill) 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 2:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Database/system Crashing
  I'd 
  agree with Dan. You need to find the root cause of the crash. If 
  you rebuild to the current state from scratch, the odds are you'll see the 
  same problem reoccur. Secondly, while NFS mounted volumes will work, 
  they should always be a last resort as any network, remote IO load on the 
  server where the NFS mounted volume lives "could" cause IO corruption and 
  panic the host server. I didn't see the start of this thread so these 
  are after the thought comments. Maybe they're 
  helpful.
  
  Regards, 
  Bill Burke "The 
  Kinder and Gentler DBA" www.OracleGuru.com www.KBMotorsports.biz 
  
-Original Message-From: Fink, Dan 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:55 
AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Database/system Crashing
Val,
 Not having an entry in the alert 
log or having trace files is not all that odd. This indicates a hard crash 
of the instance, where the background processes were unable to write to the 
files. This could be a result of the instance being forcefully terminated 
without using the Oracle shutdown process or it could be the result of the 
processes being unable to write to the device containing the log and trace 
files. Try moving the background_dump_dest to another device (preferably 
internally connected to the server).
 I would not reinstall the OS and 
Oracle unless it can be reasonably determined that the OS is causing the 
problem. What are the reasons the SAs say it is the OS? It is a lot of work 
to recreate the system and you have no guarantee that this will solve it. It 
sounds like a more detailed inspection of all the systems is in order 
instead of spinning the 'Wheel Of Blame' to stop on the 'most likely' 
suspect. More troubleshooting is called for, not the drastic step of "wipe 
it clean and start over"

Dan Fink

  -Original Message-From: Webber Valerie H 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 
  9:40 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database/system 
  Crashing
  Yes there are NFS mounts involved. What you said about the 
  OS locks on the audit directory makes a lot of sense. My SA's are back to 
  thinking it's a OS problem because it crashed again with the database shut 
  down. 
  The odd thing is that there is nothing written to the 
  Oracle alert log file nor are there any entries in the trace files. But 
  when the system is rebooted and I bring the db back up, Oracle knows it 
  previously crashed and recovers itself. That's in the alert log file. Its 
  like the system is losing its pointers or something. I suggested 
  reinstalling the OS and Oracle then put my database back and see if that 
  helps. Are there huge risks with this scenario?
  Another odd thing that the SA's can't figure out is there 
  are no entries in the message file nor can they get a dump file to 
  determine why the system crashed. There is nothing. It crashed over the 
  weekend with no activity and they got some sort of i-nodes error. 
  
  Thanks for all your replies. Any ideas are helpful and I 
  will relay them to our SA's... 
  Val

RE: ORA-00600: [2662]

2003-01-07 Thread K Gopalakrishnan

Mogens:

Yes. You are right. You have to bump the SCNs globally
(i.e. across all data files to higher number, say 1 Billion)
using the event 10015 and ADJUST_SCN. Then we can safely
open the database and rebuild that.

KG


Best Regards,
K Gopalakrishnan




-Original Message-
Norgaard
Sent: Monday, January 06, 2003 11:59 PM
To: Multiple recipients of list ORACLE-L


This response from Oracle is not a good one. Kirti is on the right track
regarding the SCN stuff as far as I remember. Other than that, I know
that Peter Gram and Bjorn Engsig (among a lot of other guys) might be
able to suggest something, so I've CC'ed them.

I have my own law on ora-600s: If you supply all the required
information (tracefiles, alertlogs, repro-case, etc.) chances are
Support is not gonna need them. If you miss out just one of those
things, chances are Support will tell you they cannot proceed without
that vital piece of information. In other words: There's no hope :-)))

Could also be related to db-links between v7 and v8...

But back to the good questions from Kirti: Have you set the funny
_-parameters he mentions?


Mogens

PS: I do beleive there are ways around this with an alter system or
alter session command where you bump the SCN some level, but
Peter/Bjorn/someone can confirm this.

Sinardy Xing wrote:

Hi guys,


This is what I get from Oracle

==

Hi Sinardy,

I've check several bug looking for the cause of this problem. However, what
I've found is that Development could not diagnosis the problem other than
the possibily hat there was some memory corruption that gave a bad SCN.
Although, they could sometimes determine the root cause prior to the
problem occurring by using certain parameters were set in the database.

Those parameters are:

a)_db_block_cache_protect.
b)_db_block_checking
c)DB_BLOCK_CHECKSUM

They also recommended running dbverify on all datafiles tomake sure all
files are clean on disk.

IMPORTANT
==
These parameter may help later determine what cause the ORA-600 [2662] (and
it's not for certain that a cause can be found). They parameters does have
some performance overhead, but unfortunately this is the only way that can
help in catching and we have to wait until the problem happens again.

So, the bottomline is there is no list of reason for why this error
occuring other than the error occurs when a data block SCN is ahead of the
current SCN.

Sorry Sinardy, we don't have more information.

Thanks!
Wonda

=

What can I do, help me please...


Sinardy



-Original Message-
Sent: 07 January 2003 13:09
To: Multiple recipients of list ORACLE-L


Sinardy

ORA-0600 is really an encoded message from Oracle that reads :

'Thank you for helping find yet another bug in our software. You can now
release your sphincter (some herbal tea might help), especially if this has
cropped up in production. We suggest you do not try to solve this one by
yourself, which is why the arguments are supplied. Kindly call OWWS with
your CSI number, tell them exactly which version and platform you ran this
on, and if we have encountered this previously, there is a small chance
that we could have a patch. If not, please do not hold your breath waiting,
while we assign this to an experienced software engineer, or perhaps a
recent college grad, in which case, you're toast !' However, we will ask
you to ftp up to our ftp site loads of dumps and traces which might prove
useful.

Seriously though, ORA-0600 are mysteries. Your best chance is to get Tim
Gorman to look at it, he has an amazing knowledge of these. And of course,
Tim should not work for free either. (Tim, it was 10% commission we agreed
on, right ? ) :-)

Ferenc Mantfeld

-Original Message-
From:  Sinardy Xing [SMTP:[EMAIL PROTECTED]]
Sent:  Tuesday, January 07, 2003 2:34 PM
To:Multiple recipients of list ORACLE-L
Subject:   ORA-00600: [2662]

Hi all,

I hit by this error

ORA-00600: internal error code, arguments: [2662], [0], [54151123], [0],
[54173017], [16781180], [], []


Can you help me where to find info about this error




Sinardy

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sinardy Xing
  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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: [EMAIL

RE: Are too many Foreign Keys in one table bad?

Gregory,
There are several things to consider since you are still at the
design phase. This table sounds like a great candidate for denormalization.
Is this an OLTP or OLAP system? How static are the values in the reftables?
If the reftables are static and contain very few values, consider putting
the values into the support_data table. If the data values (not counting the
relationship codes) are small, the storage may be about the same if they are
stored inside or outside of the database.
In addition to the join performance issue, you will have to worry
about insert/update/delete. To prevent locking problems, you will need to
put an index on the FK columns. You now have 15 extra changes to make when
you change a row.
The best method to determine the optimal solution is to create test
cases and measure the performance of various configurations. It will require
a little bit of time right now, but may save a great deal of time in
fixes/outages/redeployments when the system goes live and performance goes
down.

Dan Fink

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



I am designing some tables to store Customer Support Data.  
The main table (SUPPORT_DATA) contains many (up to 15) foreign key links to
other tables.  
Most of the other tables are small lookup REFTABLES (eg Priority Type).  
A few bigger tables store up to 1000 records eg CUSTOMER_DATA.

I am concerned that to get data for one Support record will involve a join
of 15 Tables and possibly more for reports, and that this many tables may
confuse the Cost Based Optimiser.

I am considering storing the CODE in the SUPPORT_DATA table instead of the
ID for the reference tables.  This will reduce the number of joins greatly.

_
Design Proposed

SUPPORT_DATA
  Id (PK)
  reftable_code (FK)  
   support_data_desc


REFTABLE
  reftable_id (PK)
  reftable_code (Unique Constraint) 
  reftable_description
_

The Main problems I see with this are that DATA storage increases (I can
deal with that) and  that I will have to create a trigger to update all
SUPPORT_DATA if one of the CODES in a REFTABLE is updated (this would be
rare and so not a great concern).

Is storing the CODE a sound option? 
Any hints or comments would be appreciated =)

THX Greg
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gregory Norris
  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: Fink, Dan
  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: email out of oracle

2003-01-07 Thread babu . nagarajan


$ oerr ora 29540
29540, 0, class %s does not exist
// *Cause: Java method execution failed to find a class with the indicated
name.
// *Action: Correct the name or add the missing Java class.

Looks like you are missing some Java Class... Do you actually get the email
it sends?

Also if you get the SQLERRM you might get the name of the class

Babu


   
 
  Leonard, George
 
  george.leonard@fTo:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  arnell.com  cc: 
 
  Sent by: Subject:  email out of oracle   
 
  [EMAIL PROTECTED] 
 
   
 
   
 
  01/07/03 11:13 AM
 
  Please respond to
 
  ORACLE-L 
 
   
 
   
 




Hi there

Trying to email out from Oracle.

Utl_smtp is installed, executing procedure currently as a DBA. This sun
Machine does send email notifications out via the crontab to me so I know I
can send email via the exchange smtp server.

Problem, Email packages execute, if I do a print I see code
SQL print

NP
--
-29540

Package executed with following command:

var np number;
exec send_mail('[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', 'testmsg', :np);

Below is the code of the send_mail package, can anyone see the problem or
know what this error code means.

Thx George


System

Oracle 8.1.6.3 EE 32 Bit
Solaris 2.6

--
-- Sending email out of Oracle using a stored procedure.
--
Create or replace PROCEDURE
  send_mail (senderIN VARCHAR2,
 recipient IN VARCHAR2,
 message   IN VARCHAR2,
 nStatus   OUT NUMBER)
IS
mailhostVARCHAR2(30) := '90.1.1.100';
mail_conn  utl_smtp.connection;

BEGIN
nStatus := 0;
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);

utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
nStatus := SQLCODE;
END send_mail;
/

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Cell: (+27) 82 655 2466
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed  Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!

-Original Message-
Sent: 07 January 2003 14:09 PM
To: Multiple recipients of list ORACLE-L

I read it and love it. The only thing I was wondering about is the fact,
that he uses tcl/tk, which I found most people don't use anymore. Nice
surprise.
I wasn't quite sure wether oraora was looking for books that gives more of
a
general overview of books that delve into the depth of unix internals.
Anyway, here is my favorite on Unix internals (hence, the name of the book
;):

UNIX Internals: The New Frontiers by Uresh Vahalia

Eventhough it was published in 1995, it gives you a very good understanding
about how things really work and why they work the way they do.

Regards,
Stefan

-Ursprüngliche Nachricht-
Von: Hately, Mike (NESL-IT) [mailto:[EMAIL PROTECTED]]
Gesendet: Montag, 6. Januar

Delete duplicate records with condition

2003-01-07 Thread roland . skoldblom


Hallo,

I have this sql,

SELECT * FROM varukorgtmp
where varukorgid= 120 That makes the result of this testfile.xls


(See attached file: start.xls)

There are two values in EAN-field, which are the same
23324614 in row 2 and 3

Now I want in an sql script to check out which are the duplicates of EAN in that 
table. Then then the script will check which is VARUTYP = 3, then delete the record(s) 
which have VARUTYP =3.

So the result should be like this, with only two rows left in this case).


(See attached file: result.xls)

I would really appreciate if anyone could help me with this sql  I have tried several 
sql, but with no luck.

Thanks in advance

Roland



start.xls
Description: application/msexcel


result.xls
Description: application/msexcel

RE: Are too many Foreign Keys in one table bad?

okay fine, Talisker then :)


--- Fink, Dan [EMAIL PROTECTED] wrote:
 Black Adder is an excellent method to get me to shut up and leave!
 
 -Original Message-
 Sent: Monday, January 06, 2003 8:29 PM
 To: Multiple recipients of list ORACLE-L
 
 
 I'm flattered you put me in the same category as Kirti, Dan, Jared,
 Connor and Jonathan.
 
 If I recall the cost of my (one) trip to Australia, it would be
 cheaper
 to import you to IOUG than it would be to get us there!  I'm not sure
 about Jared and Connor, the rest of us will be at IOUG. And for the
 price of a drink of Black Adder Scotch I'm sure you could get Dan to
 expound on Oracle and backup and recovery and blockcentric tuning all
 night long. Okay, make that a bottle of BlackAdder and make sure he
 isn't presenting first thing the next morning :)
 
 
 --- Mark Richard [EMAIL PROTECTED] wrote:
  All,
  
  Point well taken (both Rachel's and Jared's).  I should have said
  (and was
  even thinking - although the brain and hands sometimes act
  independently)
  might not be worth indexing.  It sounds like a helpdesk system
 for
  a
  pretty small customer base so I was assuming that system load isn't
  likely
  to be a problem.  My experience has always been that if the fact is
  1000
  rows and the reference are maybe 3 - 10 then Oracle is going to eat
  it up
  for lunch no matter how it's structured unless a large number of
  concurrent
  user come along.
  
  Now on a more serious note, when is the week-long Rachel
 Carmichael,
  Dan
  Fink, Jonathan Lewis, Connor, Jared, Kirti, et al How to well and
  truly
  beat Oracle into Submission seminar coming down under to
 Australia? 
  I
  need to know so that I can start selling my soul to raise enough
  money to
  attend...  With our dollar the way it is a seminar like that would
  cost
  about the same as my house.
  
  
  
 
  
   
  Rachel 
  
   
  Carmichael   To: Multiple
 recipients
  of list ORACLE-L [EMAIL PROTECTED]   
  wisernet100@y   cc:   
  
   
  ahoo.comSubject: Re: Are too
  many Foreign Keys in one table bad?  
  Sent by:   
  
   
  [EMAIL PROTECTED] 
  
   
  om 
  
   
 
  
   
 
  
   
  07/01/2003 
  
   
  12:33  
  
   
  Please respond 
  
   
  to ORACLE-L
  
   
 
  
   
 
  
   
  
  
  
  
  Mark,
  
  Based on the presentation and testing Dan Fink did for the last
 NYOUG
  meeting, it's possible that the ref tables SHOULD be indexed, and
  that
  it will help performance to index them.
  
  Rachel
  
  --- Mark Richard [EMAIL PROTECTED] wrote:
   Greg,
  
   I don't think Oracle will have a real problem with 15 tables or
  1,000
   rows.
   If the ref tables are quite small then they won't even be worth
   indexing -
   Oracle will just read the entire table at one anyway.  You might
  want
   to
   tell Oracle to CACHE the reference tables, although I don't think
   you'll
   see a performance gain really.  Unfortunately I can't give any
   performance
   suggestions because I am used to the other end of the scale (ie:
  250
   million rows in data)
  
   You probably could store CODE in the main table, but if you are
  going
   to
   need the description frequently then all benefit is lost anyway.
   Either
   way though I'm sure that you'll have more problems getting the 15
   joins
   right when writing the queries than Oracle's CBO will have when
   looking at
   the query - I've seen some real nasty queries get pushed into
   Oracle's
   optimisor

RE: Linux and Oracle Cluster File System

2003-01-07 Thread Richard Ji




Jos,

Yes, 
you are right that if you use OCFS then you can just have one mount point and 
create all
data 
files under it, because OCFS is a file system. It makes it a lot easier to 
manage space
and 
you can use commands like: cp, mv, rm etc which you can't do to a raw 
partition.
Backup 
on OCFS is also easier than using raw.

Richard Ji

  -Original Message-From: Jos 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
  8:49 AMTo: Multiple recipients of list ORACLE-LSubject: 
  OT: Linux and Oracle Cluster File SystemList,I am a 
  newbie on Linux and Clustering technology, I need to setup a RAC system and 
  have been reading somedocumentation on how to do it. I am a bit confuse 
  about the difference between setting the disk up with Linux raw partition 
  and Oracle cluster file system, I hope someone on the list can give me some 
  hints oruseful reference for reading. The document said for Lunix raw 
  partition I need to setup a partition pertablespace, this is quit a lot 
  considering for Oracle Apps there is about 200 tablespaces. I am 
  wonderingif I am using Oracle Clustering file system (OCFS), can I define 
  one big partition for OCFS and mount it on /u01 and create all the files 
  under this one mount point or the one raw partition per tablespace rule 
  stillapplies.Jos
  
  
  Yahoo! Greetings- Send your seasons greetings 
  online this year!

RE: FULL TABLE SCAN?

2003-01-07 Thread Koivu, Lisa

Title: RE: FULL TABLE SCAN?





_full_table_scan=FALSE


-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 11:29 AM
To: Multiple recipients of list ORACLE-L
Subject: FULL TABLE SCAN?



Hi
How to avoid FULL TABLE SCAN?
Thx
-seema






_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Seema Singh
 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: Database/system Crashing

Title: RE: Database/system Crashing



Got the same error on NT last week.
Check if there are system backups that backup the control 
files.
When Veritas backup the control file as a regular file the 
database can not write to it and you get this message.

Yechiel AdarMehish

  - Original Message - 
  From: 
  Webber Valerie H 
  To: Multiple recipients of list ORACLE-L 
  Sent: Tuesday, January 07, 2003 6:34 
  PM
  Subject: RE: Database/system 
  Crashing
  
  Well I relocated the background dest files 
  and I got the following error... that was a great idea!
  
  ORA-00206: error in writing (block 3, # 
  blocks 1) of controlfileORA-00202: controlfile: 
  '/u04/oradata/ERCS/ora_control2'ORA-27063: Message 27063 not found; 
  product=RDBMS; facility=ORASVR4 Error: 5: I/O errorAdditional 
  information: -1Additional information: 2048error 221 detected in 
  background process
  
  The SA's think its a data block corruption. 
  If anyone has any additional information, it will be greatly 
  appreciated.
  At least now I know why the database crashed 
  to begin with. Now the SA's just have to figure out how to fix 
  it.
  
  Thanks for all the help!!
  Val
  
-Original Message-From: Burke, William F (Bill) 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 2:49 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Database/system Crashing
I'd agree with Dan. You need to find the root cause of the 
crash. If you rebuild to the current state from scratch, the odds are 
you'll see the same problem reoccur. Secondly, while NFS mounted 
volumes will work, they should always be a last resort as any network, 
remote IO load on the server where the NFS mounted volume lives "could" 
cause IO corruption and panic the host server. I didn't see the start 
of this thread so these are after the thought comments. Maybe they're 
helpful.

Regards, 
Bill Burke "The Kinder and Gentler DBA" www.OracleGuru.com www.KBMotorsports.biz 

  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:55 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  Val,
   Not having an entry in the 
  alert log or having trace files is not all that odd. This indicates a hard 
  crash of the instance, where the background processes were unable to write 
  to the files. This could be a result of the instance being forcefully 
  terminated without using the Oracle shutdown process or it could be the 
  result of the processes being unable to write to the device containing the 
  log and trace files. Try moving the background_dump_dest to another device 
  (preferably internally connected to the server).
   I would not reinstall the OS 
  and Oracle unless it can be reasonably determined that the OS is causing 
  the problem. What are the reasons the SAs say it is the OS? It is a lot of 
  work to recreate the system and you have no guarantee that this will solve 
  it. It sounds like a more detailed inspection of all the systems is in 
  order instead of spinning the 'Wheel Of Blame' to stop on the 'most 
  likely' suspect. More troubleshooting is called for, not the drastic step 
  of "wipe it clean and start over"
  
  Dan Fink
  
-Original Message-From: Webber Valerie H 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 
2003 9:40 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Yes there are NFS mounts involved. What you said about 
the OS locks on the audit directory makes a lot of sense. My SA's are 
back to thinking it's a OS problem because it crashed again with the 
database shut down. 
The odd thing is that there is nothing written to the 
Oracle alert log file nor are there any entries in the trace files. But 
when the system is rebooted and I bring the db back up, Oracle knows it 
previously crashed and recovers itself. That's in the alert log file. 
Its like the system is losing its pointers or something. I suggested 
reinstalling the OS and Oracle then put my database back and see if that 
helps. Are there huge risks with this scenario?
Another odd thing that the SA's can't figure out is 
there are no entries in the message file nor can they get a dump file to 
determine why the system crashed. There is nothing. It crashed over the 
weekend with no activity and they got some sort of i-nodes error. 

Thanks for all your replies. Any ideas are helpful and I 
will relay them to our SA's... 
Val

RE: Database/system Crashing

Title: RE: Database/system Crashing



Val,
 Have you tried copying a known good controlfile in place of 
the bad one? If not, try it and report the result. If it corrupts as well, it 
seems to me that there is a much bigger problem. If it does not corrupt, then 
the question is, why didn't oracle report the corruption in the first place. I 
hate to say this, but I'm not certain you have found the problem, you may only 
be experiencing another symptom.

Dan

  -Original Message-From: Webber Valerie H 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
  9:34 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  Well I relocated the background dest files and I got the 
  following error... that was a great idea!
  
  ORA-00206: error in writing (block 3, # blocks 1) of 
  controlfileORA-00202: controlfile: 
  '/u04/oradata/ERCS/ora_control2'ORA-27063: Message 27063 not found; 
  product=RDBMS; facility=ORASVR4 Error: 5: I/O errorAdditional 
  information: -1Additional information: 2048error 221 detected in 
  background process
  
  The SA's think its a data block corruption. If anyone has 
  any additional information, it will be greatly 
appreciated.
  At least now I know why the database crashed to begin 
  with. Now the SA's just have to figure out how to fix it.
  
  Thanks for all the help!!
  Val
  
-Original Message-From: Burke, William F (Bill) 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 2:49 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Database/system Crashing
I'd agree with Dan. You need to find the root cause of the 
crash. If you rebuild to the current state from scratch, the odds are 
you'll see the same problem reoccur. Secondly, while NFS mounted 
volumes will work, they should always be a last resort as any network, 
remote IO load on the server where the NFS mounted volume lives "could" 
cause IO corruption and panic the host server. I didn't see the start 
of this thread so these are after the thought comments. Maybe they're 
helpful.

Regards, 
Bill Burke "The Kinder and Gentler DBA" www.OracleGuru.com www.KBMotorsports.biz 

  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:55 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  Val,
   Not having an entry in the 
  alert log or having trace files is not all that odd. This indicates a hard 
  crash of the instance, where the background processes were unable to write 
  to the files. This could be a result of the instance being forcefully 
  terminated without using the Oracle shutdown process or it could be the 
  result of the processes being unable to write to the device containing the 
  log and trace files. Try moving the background_dump_dest to another device 
  (preferably internally connected to the server).
   I would not reinstall the OS 
  and Oracle unless it can be reasonably determined that the OS is causing 
  the problem. What are the reasons the SAs say it is the OS? It is a lot of 
  work to recreate the system and you have no guarantee that this will solve 
  it. It sounds like a more detailed inspection of all the systems is in 
  order instead of spinning the 'Wheel Of Blame' to stop on the 'most 
  likely' suspect. More troubleshooting is called for, not the drastic step 
  of "wipe it clean and start over"
  
  Dan Fink
  
-Original Message-From: Webber Valerie H 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 
2003 9:40 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Yes there are NFS mounts involved. What you said about 
the OS locks on the audit directory makes a lot of sense. My SA's are 
back to thinking it's a OS problem because it crashed again with the 
database shut down. 
The odd thing is that there is nothing written to the 
Oracle alert log file nor are there any entries in the trace files. But 
when the system is rebooted and I bring the db back up, Oracle knows it 
previously crashed and recovers itself. That's in the alert log file. 
Its like the system is losing its pointers or something. I suggested 
reinstalling the OS and Oracle then put my database back and see if that 
helps. Are there huge risks with this scenario?
Another odd thing that the SA's can't figure out is 
there are no entries in the message file nor can they get a dump file to 
determine why the system crashed. There is nothing. It crashed over the 
weekend with no activity and they got some sort of i-nodes error. 

Thanks for all your replies. Any ideas are helpful and I

RE: Caching a huge table's data in memory

2003-01-07 Thread Orr, Steve

Title: RE: Caching a huge table's data in memory





I don't think a cache table is actually pinned in memory. It just means
that its blocks stick around once they are read and are not recycled as
much as normal tables. That having been said, due diligence should be
taken to tune the queries and caching large tables should be avoided. What 
good is it if you tune, or rather, speed up one query if it takes resources
away from other queries and slows down the overall system? Sounds like a
good opportunity to educate a DUHveloper. Tune the query and show her/him
before and after tkprof stats. Better yet, teach her/him how to use tkprof
and make them run it on each query before putting it into code. Make sure
your test/development data set reflects production volumes.



Steve Orr
Bozeman, Montana




-Original Message-
From: Stephen Lee [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 8:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Caching a huge table's data in memory



Pro: It is in memory


Con: You must buy the memory. You must be sure you don't cause the box to
start paging to swap ... very bad ... VERY bad.


When you live in the land of bad applications, sometimes the politics of the
situation are such that you just do what you know is the equivalent of
fixing things with duct tape -- a whole lot of duct tape -- rather than
attempt to engage in a hopeless fight. Consider the case of an application
that is so bad that a box with more than 50 Gb of RAM and 16 Alpha CPU's are
required to handle from 6 to 10 active connections. Hey, if they want to
spend the money 



 -Original Message-
 What are the pros and cons of caching a table's data? 
--

Long-running PL/SQL function (long)


Our developers sent me a function which is running quite long to see if I
could give them any advice.   It is written in PL/SQL for version 9.2.0.1
of Oracle on Sun Solaris.   It is going across a database link.  It reads
tables in one database and loads a new table in a datamart table on another
box.   It looks like it will currently run for four or five days to load a
140 million-row table, which is longer than our available window.

I am wondering if anyone can look at the big picture and see if there are
any obvious places for improvement of this overall design.   I am open to
any suggestions that I can relay back to the developers.

My gratitude to anyone who can wade through this and recommend
improvements.

Cherie Machler
Oracle DBA
Gelco Information Network




FUNCTION exp_rpt_sts_load (
in_src_proc_no NUMBER,
in_stt_dt  DATE,
in_stop_dt DATE,
in_commit_interval NUMBER,
in_err_threshold   VARCHAR2,
in_debugging   BOOLEAN )
 RETURN BOOLEAN IS

   TYPE list_array IS VARRAY(200) OF VARCHAR2(2);

   TYPE no_array   IS VARRAY(200) OF NUMBER(10);

   lv_pay_sts_array list_array :=
list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

   lv_sts_cnfr_no no_array :=
no_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

   CURSOR c_exp_rpt_hdr IS
  SELECT a.acct_no,
 a.cnfr_no,
 a.arrv_dt_tm,
 b.pay_type,
 b.status,
 b.wh_mod_dt_tm upd_dt_tm,
 b.wh_date_key,
 b.wh_time_key
  FROM exp_rpt_hdr a,
 exp_rpt_amt_type b
  WHERE b.wh_mod_dt_tm BETWEEN in_stt_dt AND in_stop_dt
 AND trans_type = 'R'
 AND a.cnfr_no = b.cnfr_no
  order by acct_no, cnfr_no;

  -- c_exp_rpt_hdr storage values
  lv_cnfr_no exp_rpt_hdr.cnfr_no%TYPE;
  lv_acct_no acct_pay_type.acct_no%TYPE;
  lv_pay_typeacct_pay_type.pay_type%TYPE;

   CURSOR c_exp_rpt_dtls IS
  SELECT DISTINCT a.cnfr_no,
   b.line_seq_no,
   NVL(b.dtl_seq_no,0) dtl_seq_no,
   NVL(c.alloc_seq_no,0) alloc_seq_no,
   d.descr
  FROM exp_rpt_line_item_hdr a,
 exp_rpt_line_item_dtl b,
 exp_rpt_alloc c,
 acct_pay_type d
  WHERE a.cnfr_no = lv_cnfr_no
 AND b.pay_type = lv_pay_type
 AND a.cnfr_no = b.cnfr_no
 AND b.cnfr_no = c.cnfr_no(+)
 AND b.line_seq_no = c.line_seq_no(+)
 AND b.dtl_seq_no = c.dtl_seq_no(+)
 AND d.acct_no = lv_acct_no
 AND b.pay_type = d.pay_type
   ORDER BY b.line_seq_no, dtl_seq_no, alloc_seq_no;

  -- c_exp_rpt_dtls storage values
  lv_line_seq_no exp_rpt_line_item_dtl.line_seq_no%TYPE;
  lv_dtl_seq_no  exp_rpt_line_item_dtl.dtl_seq_no%TYPE;
  lv_82_descracct_pay_type.descr%TYPE;

  -- Row definitions
  r_exp_rpt_hdr   c_exp_rpt_hdr%ROWTYPE;
  r_exp_rpt_dtls  c_exp_rpt_dtls%ROWTYPE;

  -- Miscellaneous local variables
  lv_mgr_global_user_no acct_user.global_user_no%TYPE;
  lv_eff_dt_in  DATE;
  lv_sql_code   NUMBER;
  lv_sql_msgVARCHAR2(256);
  lv_step_txt   VARCHAR2(160);
  lv_err_txtVARCHAR2(320);
  lv_sysdateDATE;
  lv_char_SYSDATE   VARCHAR2(20);
  lv_handle UTL_FILE.FILE_TYPE;
  lv_status BOOLEAN := TRUE;
  lv_in_cnt NUMBER :=0;
  lv_row_cntNUMBER :=0;
  lv_err_cntNUMBER :=0;
  lv_run_log_no INTEGER :=0;
  lv_in_loopBOOLEAN;
  lv_82 BOOLEAN := FALSE;
  lv_pay_meth   r_exp_rpt_hdr.pay_type%TYPE;
  lv_pay_stsr_exp_rpt_hdr.status%TYPE;
  lv_no_alloc_rec   BOOLEAN;
  lv_ach_amtNUMBER := 0;
  loop_ctr  NUMBER;
  lv_chng_dtDATE;
  lv_arrv_dtDATE;
  lv_loop   NUMBER := 0;
  lv_tran_dtDATE;

  -- Constants
  c_proc_nm VARCHAR2(80) := 'load_edm_exp_rpt_sts';

   BEGIN
  -- File Control
  lv_handle := WHSE_DEBUG_PKG.open_debug_log_file(c_proc_nm);
  lv_err_txt := 'Process '
 || c_proc_nm
 || ', '
 || 'Runtime '
 || SYSDATE;

  lv_status := WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
lv_handle);

  --Run Log Start
  lv_run_log_no :=

Re: FULL TABLE SCAN?



1. Set optimizer_mode to RULE.
2. Make sure all statements have a WHERE clause.
3. Dont use functions in the equality clauses.
4. Create an index on each and every column you have in the database.

Take my advice. I dont use it anyway :)))

Raj




   
  
Seema Singh  
  
oracledbam@hoTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
tmail.comcc:  
  
Sent by:  Subject: FULL TABLE SCAN?
  
[EMAIL PROTECTED] 
  
om 
  
   
  
   
  
January 07,
  
2003 11:28 AM  
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




Hi
How to avoid FULL TABLE SCAN?
Thx
-seema





_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus

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

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





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

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

RE: FULL TABLE SCAN?

At great personal risk, I will tell you some secrets.
There are many options.
1) Don't query data. To really enforce this, remove select, insert, update
and delete privileges from all users.
2) If you must query data, use an index and only an index. Create indexes
that cover all possible combinations of data in the table.
3) If you see a FULL TABLE SCAN coming your way, grab your cellphone and
appear to be in deep conversation with someone and totally oblivious to
everything around you (this is especially good when driving in heavy
traffic).
4) If the FULL TABLE SCAN comes up to you and begins talking, pretend like
you don't speak SQL.

And, just so I am not crucified by those with 0 sense of humor... read the
previous posts on FTS!

-Original Message-
Sent: Tuesday, January 07, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L


Hi
How to avoid FULL TABLE SCAN?
Thx
-seema





_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Seema Singh
  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: Fink, Dan
  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: Exam Cram is back

2003-01-07 Thread KENNETH JANUSZ

I also got this e-mal.  I could not find anything on 9i just 8i.  So, they
have some catching up to do.

Ken Janusz, CPIM

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 07, 2003 9:08 AM


 For those of you that enjoyed Exam Cram, I received the following
newsletter
 and thought I would pass it along.

 Dear Exam Cram member,

 We're back, and better than ever!  The Exam Cram site you
 used to know and love is now Exam Cram 2
 (http://www.examcram2.com), produced by a joint effort
 between InformIT (http://www.informit.com) and Que
 Certification.

 ::: YOUR EXAM CRAM 2 NEWSLETTERS :::

 Exam Cram members with subscriptions to the original
 Question of the Day newsletters will begin receiving them
 again this month. (Please note that we've had to retire
 some of the older exams and will not be sending newsletters
 supporting those exams.)

 If you don't want to automatically continue your
 subscription to these newsletters, please click the
 following link:

 http://www.informit.com/u.asp?[EMAIL PROTECTED]


 ::: EXAM CRAM 2 FREE PRACTICE EXAMS :::

 The new Exam Cram 2 (http://www.examcram2.com) site is your
 source for online practice exams, offering free practice
 tests on a wide variety of exam topics with new, high-
 quality questions.

 Over 15 new practice exams will be added to the site in the
 next few weeks, so check back often.

 ::: INFORMIT CERTIFICATION CENTER :::

 Ed Tittel, creator of the original Exam Cram book series,
 returns as the Exam Cram 2 series editor and will become
 the resident Certification Expert on InformIT. Visit the
 Certification Center for articles and discussion on
 certification topics. Check it out:

 http://www.informit.com/link.asp?link=certcntr

 ::: EXAM CRAM 2 BOOKS :::

 The new Exam Cram 2 book series will continue to provide
 the same focused, relevant, and timely coverage of key
 certification exam topics, concepts, and study strategies.
 Each book is extensively reviewed by industry experts and
 holds the CramSession seal of approval. See the books here:

 http://www.informit.com/link.asp?link=ec2books

 We look forward to serving you at the new Exam Cram 2!

 Sincerely,

 The Exam Cram 2 Team
 Exam Cram 2 - the Smartest Way To Get Certified(TM)!
 --
 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: KENNETH JANUSZ
  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: FULL TABLE SCAN?

2003-01-07 Thread Whittle Jerome Contr NCI

Title: RE: FULL TABLE SCAN?






A Where clause in your SQL and indexes that support the Where clause are a good place to start.


Jerry Whittle

ASIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From: Seema Singh [SMTP:[EMAIL PROTECTED]]

Hi

How to avoid FULL TABLE SCAN?

Thx

-seema

RE: FULL TABLE SCAN?

2003-01-07 Thread Farnsworth, Dave

Create a meaningful index and keep your stats up to date.

Dave
The OT list rules

-Original Message-
Sent: Tuesday, January 07, 2003 10:29 AM
To: Multiple recipients of list ORACLE-L


Hi
How to avoid FULL TABLE SCAN?
Thx
-seema





_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

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

Cant install statspack !

2003-01-07 Thread Bob Metelsky

Hello All

I seem to be missing something very basic as no matter what I do
I cant get statspack to install

This is 8.16 on WIN2kpro

I downloaded the newest version of stataspack and placed it into
%oracle_home%\rdbms\admin

C:\Oracle\Ora81\RDBMS\ADMINdir stats*
02/08/2000  07:36p   1,805 statsauto.sql
02/08/2000  07:36p 891 statscauto.sql
12/30/1999  02:13p   1,832 statscbps.sql
02/08/2000  07:36p 882 statscre.sql
02/08/2000  07:36p  28,088 statsctab.sql
02/08/2000  07:36p  27,879 statsctaba.sql
02/08/2000  07:36p   5,098 statscusr.sql
02/08/2000  07:36p   4,384 statscusra.sql
02/08/2000  07:36p 829 statsdrp.sql
02/08/2000  07:36p   3,344 statsdtab.sql
02/08/2000  07:36p   1,136 statsdusr.sql
02/08/2000  07:34p  28,516 statspack.doc
02/08/2000  07:36p  51,400 statspack.sql
02/08/2000  07:36p  48,205 statsrep.sql
01/19/2000  06:53p  52,610 statsrep80.sql
02/08/2000  07:36p 579 statsuexp.par
  16 File(s)257,478 bytes

I log onto the db sqlplusw internal/pw@instance

SQL@%oracle_home%\rdbms\admin\statscre.sql
the script seems to create the user.. but then blinks out. Apparently
its soupposed to generate .lis files but there are no such files

I tried to spool a log file but It only captures the first line... as
the script blinks out

if I try to 

SQL execute statspack.snap

I get 
PLS-00201: identifier 'STATSPACK.SNAP' must be declared

I try to prefix it with perfstat. or sys. but no joy

what can I be doing wrong??

Ive tried to hack the scripts(so they would stay up) but there are so
many variables that it dosnt seem practical

Thanks
bob
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  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: FULL TABLE SCAN?

2003-01-07 Thread Reginald . W . Bailey


Seema:

Use an index, obviously.  If and index exists, ensure that the query uses
it.  The select columns order should match the order of the index columns.
Or try using a HINT.
Sometimes a full scan is not a bad thing.  Are there less than 100,000 rows
in the table?  Sometimes the optimizer will execute a full table scan
because it is faster that way.

RWB




Seema Singh [EMAIL PROTECTED]@fatcity.com on 01/07/2003 10:28:53
AM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hi
How to avoid FULL TABLE SCAN?
Thx
-seema





_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus

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

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




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

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

RE: Caching a huge table's data in memory

2003-01-07 Thread Stephen Lee


I have never delved into just what gets cache and how permanently it gets
cached when a table is cached.  In the case of a monstrosity of an
application, to cache or not to cache (that is the question) a large table,
is a case of tweedle-dee and tweedle-dum.  But when people are grabbing at
any straw that can be grabbed, you just go with the flow and hope the real
problems and what needs to be done become self-evident.

-Original Message-

I don't think a cache table is actually pinned in memory. It just means 
that its blocks stick around once they are read and are not recycled as 
much as normal tables. That having been said, due diligence should be 
taken to tune the queries and caching large tables should be avoided. What 
good is it if you tune, or rather, speed up one query if it takes
resources 
away from other queries and slows down the overall system? Sounds like a 
good opportunity to educate a DUHveloper. Tune the query and show her/him 
before and after tkprof stats. Better yet, teach her/him how to use tkprof 
and make them run it on each query before putting it into code. Make sure 
your test/development data set reflects production volumes. 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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: Caching a huge table's data in memory

Title: RE: Caching a huge table's data in memory

Read
Cary Millsap's papers on Misunderstandings about Oracle Internals at his site www.hotsos.com. They are
excellent!

-Original Message-From: Orr, Steve
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 9:54
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Caching a huge table's data in memory
I don't think a cache table is actually "pinned" in memory. It
just means that its blocks stick around once they are
read and are not recycled as much as "normal" tables.
That having been said, due diligence should be taken
to tune the queries and caching large tables should be avoided. What
good is it if you "tune", or rather, speed up one
query if it takes resources away from other queries
and slows down the overall system? Sounds like a good
opportunity to educate a DUHveloper. Tune the query and show her/him
before and after tkprof stats. Better yet, teach her/him how
to use tkprof and make them run it on each query
before putting it into code. Make sure your
test/development data set reflects production volumes.
Steve Orr Bozeman, Montana

-Original Message- From:
Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 8:09 AM To:
Multiple recipients of list ORACLE-L Subject: RE:
Caching a huge table's data in memory
Pro: It is in memory
Con: You must buy the memory. You must be sure you don't
cause the box to start paging to swap ... very bad ...
VERY bad.
When you live in the land of bad applications, sometimes the
politics of the situation are such that you just do
what you know is the equivalent of "fixing" things
with duct tape -- a whole lot of duct tape -- rather than
attempt to engage in a hopeless fight. Consider the
case of an application that is so bad that a box with
more than 50 Gb of RAM and 16 Alpha CPU's are required
to handle from 6 to 10 active connections. Hey, if they want to
spend the money
-Original Message-
What are the pros and cons of caching a table's data? --

Re: Long-running PL/SQL function (long)


Cherie,

If network bandwidth is the bottleneck, the use of Fast Refreshable
snapshots will be a great help. Whereby you only pull the rows that have
changed since the last refresh across to the primary.

Raj




   
  
Cherie_Machler 
  
@gelco.comTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
Sent by:  cc:  
  
[EMAIL PROTECTED]Subject: Long-running PL/SQL function 
(long)   
om 
  
   
  
   
  
January 07,
  
2003 12:25 PM  
  
Please respond 
  
to ORACLE-L
  
   
  
   
  





Our developers sent me a function which is running quite long to see if I
could give them any advice.   It is written in PL/SQL for version 9.2.0.1
of Oracle on Sun Solaris.   It is going across a database link.  It reads
tables in one database and loads a new table in a datamart table on another
box.   It looks like it will currently run for four or five days to load a
140 million-row table, which is longer than our available window.

I am wondering if anyone can look at the big picture and see if there are
any obvious places for improvement of this overall design.   I am open to
any suggestions that I can relay back to the developers.

My gratitude to anyone who can wade through this and recommend
improvements.

Cherie Machler
Oracle DBA
Gelco Information Network


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

Pinned packages with multiple reloads

2003-01-07 Thread Jesse, Rich

Hey all,

I have a trigger that pins various packages on the startup of our 8.1.7.2.0
DB on HP/UX 11.0.  I check V$DB_OBJECT_CACHE every so often to see that it's
working and to make any necessary additions/deletions from the list of
pinned packages.

However, the SYS.DBMS_APPLICATION_INFO package currently has 836 loads off
of 97706 execs despite being successfully pinned on startup (we've only been
up for a few days due a 7445 cascade crash).  How is this possible?
Furthermore, does it do any good to pin this package?  And why?

This is sort of a repost from months ago, but I can't find it on fatcity.

TIA!
Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA
-- 
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).

RE: Exam Cram is back

2003-01-07 Thread Boivin, Patrice J

Looks like they are still starting up the new incarnation, the Oracle books
are about the Oracle 8 exams.

I don't understand what happened to Coriolis before, why did it close down?
Not enough sales?

In this area the computer books sections are shrinking in the bookstores, I
suspect people are ordering more and more of these books via the 'net.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin  Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique 
Maritimes Region, DFO  | Région des Maritimes, MPO

E-Mail: [EMAIL PROTECTED]




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


For those of you that enjoyed Exam Cram, I received the following newsletter
and thought I would pass it along.

Dear Exam Cram member,

We're back, and better than ever!  The Exam Cram site you
used to know and love is now Exam Cram 2
(http://www.examcram2.com), produced by a joint effort
between InformIT (http://www.informit.com) and Que
Certification.

::: YOUR EXAM CRAM 2 NEWSLETTERS :::

Exam Cram members with subscriptions to the original
Question of the Day newsletters will begin receiving them
again this month. (Please note that we've had to retire
some of the older exams and will not be sending newsletters
supporting those exams.)

If you don't want to automatically continue your
subscription to these newsletters, please click the
following link:

http://www.informit.com/u.asp?[EMAIL PROTECTED]


::: EXAM CRAM 2 FREE PRACTICE EXAMS :::

The new Exam Cram 2 (http://www.examcram2.com) site is your
source for online practice exams, offering free practice
tests on a wide variety of exam topics with new, high-
quality questions.

Over 15 new practice exams will be added to the site in the
next few weeks, so check back often.

::: INFORMIT CERTIFICATION CENTER :::

Ed Tittel, creator of the original Exam Cram book series,
returns as the Exam Cram 2 series editor and will become
the resident Certification Expert on InformIT. Visit the
Certification Center for articles and discussion on
certification topics. Check it out:

http://www.informit.com/link.asp?link=certcntr

::: EXAM CRAM 2 BOOKS :::

The new Exam Cram 2 book series will continue to provide
the same focused, relevant, and timely coverage of key
certification exam topics, concepts, and study strategies.
Each book is extensively reviewed by industry experts and
holds the CramSession seal of approval. See the books here:

http://www.informit.com/link.asp?link=ec2books

We look forward to serving you at the new Exam Cram 2!

Sincerely,

The Exam Cram 2 Team
Exam Cram 2 - the Smartest Way To Get Certified(TM)!
-- 
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: Boivin, Patrice J
  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: Cant install statspack !

I had some problems with stats pack install today.
There is: 'on error exit' in the scripts. Remove it so the script can
continue. This may solved your problem.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 07, 2003 8:04 PM


 Hello All

 I seem to be missing something very basic as no matter what I do
 I cant get statspack to install

 This is 8.16 on WIN2kpro

 I downloaded the newest version of stataspack and placed it into
 %oracle_home%\rdbms\admin

 C:\Oracle\Ora81\RDBMS\ADMINdir stats*
 02/08/2000  07:36p   1,805 statsauto.sql
 02/08/2000  07:36p 891 statscauto.sql
 12/30/1999  02:13p   1,832 statscbps.sql
 02/08/2000  07:36p 882 statscre.sql
 02/08/2000  07:36p  28,088 statsctab.sql
 02/08/2000  07:36p  27,879 statsctaba.sql
 02/08/2000  07:36p   5,098 statscusr.sql
 02/08/2000  07:36p   4,384 statscusra.sql
 02/08/2000  07:36p 829 statsdrp.sql
 02/08/2000  07:36p   3,344 statsdtab.sql
 02/08/2000  07:36p   1,136 statsdusr.sql
 02/08/2000  07:34p  28,516 statspack.doc
 02/08/2000  07:36p  51,400 statspack.sql
 02/08/2000  07:36p  48,205 statsrep.sql
 01/19/2000  06:53p  52,610 statsrep80.sql
 02/08/2000  07:36p 579 statsuexp.par
   16 File(s)257,478 bytes

 I log onto the db sqlplusw internal/pw@instance

 SQL@%oracle_home%\rdbms\admin\statscre.sql
 the script seems to create the user.. but then blinks out. Apparently
 its soupposed to generate .lis files but there are no such files

 I tried to spool a log file but It only captures the first line... as
 the script blinks out

 if I try to

 SQL execute statspack.snap

 I get
 PLS-00201: identifier 'STATSPACK.SNAP' must be declared

 I try to prefix it with perfstat. or sys. but no joy

 what can I be doing wrong??

 Ive tried to hack the scripts(so they would stay up) but there are so
 many variables that it dosnt seem practical

 Thanks
 bob
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Bob Metelsky
   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: Cant install statspack !

2003-01-07 Thread tim

Bob,

The scripts contain CONNECT statements which likely do not
include the @instance TNS connect-string you are using
initially.  The scripts also contain WHENEVER OSERROR and
WHENEVER SQLERROR directives, so they blow out of SQL*Plus
on error, most likely...

Please consider editing the scripts to add the TNS
connect-strings?

Hope this helps...

-Tim

 Hello All
 
 I seem to be missing something very basic as no matter
 what I do I cant get statspack to install
 
 This is 8.16 on WIN2kpro
 
 I downloaded the newest version of stataspack and placed
 it into %oracle_home%\rdbms\admin
 
 C:\Oracle\Ora81\RDBMS\ADMINdir stats*
 02/08/2000  07:36p   1,805 statsauto.sql
 02/08/2000  07:36p 891 statscauto.sql
 12/30/1999  02:13p   1,832 statscbps.sql
 02/08/2000  07:36p 882 statscre.sql
 02/08/2000  07:36p  28,088 statsctab.sql
 02/08/2000  07:36p  27,879 statsctaba.sql
 02/08/2000  07:36p   5,098 statscusr.sql
 02/08/2000  07:36p   4,384 statscusra.sql
 02/08/2000  07:36p 829 statsdrp.sql
 02/08/2000  07:36p   3,344 statsdtab.sql
 02/08/2000  07:36p   1,136 statsdusr.sql
 02/08/2000  07:34p  28,516 statspack.doc
 02/08/2000  07:36p  51,400 statspack.sql
 02/08/2000  07:36p  48,205 statsrep.sql
 01/19/2000  06:53p  52,610 statsrep80.sql
 02/08/2000  07:36p 579 statsuexp.par
   16 File(s)257,478 bytes
 
 I log onto the db sqlplusw internal/pw@instance
 
 SQL@%oracle_home%\rdbms\admin\statscre.sql
 the script seems to create the user.. but then blinks out.
 Apparently its soupposed to generate .lis files but there
 are no such files 
 I tried to spool a log file but It only captures the first
 line... as the script blinks out
 
 if I try to 
 
 SQL execute statspack.snap
 
 I get 
 PLS-00201: identifier 'STATSPACK.SNAP' must be declared
 
 I try to prefix it with perfstat. or sys. but no joy
 
 what can I be doing wrong??
 
 Ive tried to hack the scripts(so they would stay up) but
 there are so many variables that it dosnt seem practical
 
 Thanks
 bob
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Bob Metelsky
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

Re: Long-running PL/SQL function (long)

I think that you can try 2 things:

1) Run the function in the source db. Selects across links does funny stuff.
2) Write CSV file on the source system and sql loader on the target using
direct.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 07, 2003 7:25 PM



 Our developers sent me a function which is running quite long to see if I
 could give them any advice.   It is written in PL/SQL for version 9.2.0.1
 of Oracle on Sun Solaris.   It is going across a database link.  It reads
 tables in one database and loads a new table in a datamart table on
another
 box.   It looks like it will currently run for four or five days to load a
 140 million-row table, which is longer than our available window.

 I am wondering if anyone can look at the big picture and see if there are
 any obvious places for improvement of this overall design.   I am open to
 any suggestions that I can relay back to the developers.

 My gratitude to anyone who can wade through this and recommend
 improvements.

 Cherie Machler
 Oracle DBA
 Gelco Information Network




 FUNCTION exp_rpt_sts_load (
 in_src_proc_no NUMBER,
 in_stt_dt  DATE,
 in_stop_dt DATE,
 in_commit_interval NUMBER,
 in_err_threshold   VARCHAR2,
 in_debugging   BOOLEAN )
  RETURN BOOLEAN IS

TYPE list_array IS VARRAY(200) OF VARCHAR2(2);

TYPE no_array   IS VARRAY(200) OF NUMBER(10);

lv_pay_sts_array list_array :=
 list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

lv_sts_cnfr_no no_array :=
 no_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

CURSOR c_exp_rpt_hdr IS
   SELECT a.acct_no,
  a.cnfr_no,
  a.arrv_dt_tm,
  b.pay_type,
  b.status,
  b.wh_mod_dt_tm upd_dt_tm,
  b.wh_date_key,
  b.wh_time_key
   FROM exp_rpt_hdr a,
  exp_rpt_amt_type b
   WHERE b.wh_mod_dt_tm BETWEEN in_stt_dt AND in_stop_dt
  AND trans_type = 'R'
  AND a.cnfr_no = b.cnfr_no
   order by acct_no, cnfr_no;

   -- c_exp_rpt_hdr storage values
   lv_cnfr_no exp_rpt_hdr.cnfr_no%TYPE;
   lv_acct_no acct_pay_type.acct_no%TYPE;
   lv_pay_typeacct_pay_type.pay_type%TYPE;

CURSOR c_exp_rpt_dtls IS
   SELECT DISTINCT a.cnfr_no,
b.line_seq_no,
NVL(b.dtl_seq_no,0) dtl_seq_no,
NVL(c.alloc_seq_no,0) alloc_seq_no,
d.descr
   FROM exp_rpt_line_item_hdr a,
  exp_rpt_line_item_dtl b,
  exp_rpt_alloc c,
  acct_pay_type d
   WHERE a.cnfr_no = lv_cnfr_no
  AND b.pay_type = lv_pay_type
  AND a.cnfr_no = b.cnfr_no
  AND b.cnfr_no = c.cnfr_no(+)
  AND b.line_seq_no = c.line_seq_no(+)
  AND b.dtl_seq_no = c.dtl_seq_no(+)
  AND d.acct_no = lv_acct_no
  AND b.pay_type = d.pay_type
ORDER BY b.line_seq_no, dtl_seq_no, alloc_seq_no;

   -- c_exp_rpt_dtls storage values
   lv_line_seq_no exp_rpt_line_item_dtl.line_seq_no%TYPE;
   lv_dtl_seq_no  exp_rpt_line_item_dtl.dtl_seq_no%TYPE;
   lv_82_descracct_pay_type.descr%TYPE;

   -- Row definitions
   r_exp_rpt_hdr   c_exp_rpt_hdr%ROWTYPE;
   r_exp_rpt_dtls  c_exp_rpt_dtls%ROWTYPE;

   -- Miscellaneous local variables
   lv_mgr_global_user_no acct_user.global_user_no%TYPE;
   lv_eff_dt_in  DATE;
   lv_sql_code   NUMBER;
   lv_sql_msgVARCHAR2(256);
   lv_step_txt   VARCHAR2(160);
   lv_err_txtVARCHAR2(320);
   lv_sysdateDATE;
   lv_char_SYSDATE   VARCHAR2(20);
   lv_handle UTL_FILE.FILE_TYPE;
   lv_status BOOLEAN := TRUE;
   lv_in_cnt NUMBER :=0;
   lv_row_cntNUMBER :=0;
   lv_err_cntNUMBER :=0;
   lv_run_log_no INTEGER :=0;
   lv_in_loopBOOLEAN;
   lv_82 BOOLEAN := FALSE;
   lv_pay_meth   r_exp_rpt_hdr.pay_type%TYPE;
   lv_pay_stsr_exp_rpt_hdr.status%TYPE;
   lv_no_alloc_rec   BOOLEAN;
   lv_ach_amtNUMBER := 0;
   loop_ctr  NUMBER;
   lv_chng_dtDATE;
   lv_arrv_dtDATE;
   lv_loop   NUMBER := 0;
   lv_tran_dtDATE;

Re: Cant install statspack !

2003-01-07 Thread Barbara Baker

Bob:
I have a slightly different version. My spcreate.sql involkes 3 scripts: @@spcusr,@@spctab,@@spcpkg. The problem that I had was that spcusr was creating some x_ views that already existed in the database(perhaps from Steve Adams' script). When the spcusr script encounters the errors, it quits and does not proceed with the remainder of the creation.
I had to modify the script and take out all the references to the x_ views (or delete them, I don't remember which) in order to get a clean install.
You should have the equivalent of an spdrop. If so you can run it and start over, but this will not remove the x_ views and synonyms.
(Also make sure you're not using svrmgrl. statspack does not like svrmgrl)
Good luck!
Barb

Bob Metelsky [EMAIL PROTECTED] wrote:
Hello AllI seem to be missing something very basic as no matter what I doI cant get statspack to installThis is 8.16 on WIN2kproI downloaded the newest version of stataspack and placed it into%oracle_home%\rdbms\adminC:\Oracle\Ora81\RDBMS\ADMINdir stats*02/08/2000 07:36p 1,805 statsauto.sql02/08/2000 07:36p 891 statscauto.sql12/30/1999 02:13p 1,832 statscbps.sql02/08/2000 07:36p 882 statscre.sql02/08/2000 07:36p 28,088 statsctab.sql02/08/2000 07:36p 27,879 statsctaba.sql02/08/2000 07:36p 5,098 statscusr.sql02/08/2000 07:36p 4,384 statscusra.sql02/08/2000 07:36p 829 statsdrp.sql02/08/2000 07:36p 3,344 statsdtab.sql02/08/2000 07:36p 1,136 statsdusr.sql02/08/2000 07:34p 28,516 statspack.doc02/08/2000 07:36p 51,400 statspack.sql02/08/2000 07:36p 48,205 statsrep.sql01/19/2000 06:53p 52,610 sta!
tsrep80.sql02/08/2000 07:36p 579 statsuexp.par16 File(s) 257,478 bytesI log onto the db sqlplusw internal/pw@instanceSQL@%oracle_home%\rdbms\admin\statscre.sqlthe script seems to create the user.. but then blinks out. Apparentlyits soupposed to generate .lis files but there are no such filesI tried to spool a log file but It only captures the first line... asthe script blinks outif I try to SQL execute statspack.snapI get PLS-00201: identifier 'STATSPACK.SNAP' must be declaredI try to prefix it with perfstat. or sys. but no joywhat can I be doing wrong??Ive tried to hack the scripts(so they would "stay up") but there are somany variables that it dosnt seem practicalThanksbob-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bob MetelskyINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://w!
ww.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).Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

Re: Long-running PL/SQL function (long)

2003-01-07 Thread Thomas Day


I believe, with 8.1.7, Oracle changed the behavior of selects over a
database link so that it is optimized for snapshot (materialized view)
replication.  We faced a similar situation and the answer was to copy the
source tables over the db_link and then run the PL/SQL against the copied
tables (without using the db_link).  We were able to merge 12G of data in
48 hours.

HTH



   

  Cherie_Machler   

  @gelco.com   To:  Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  Sent by: rootcc: 

   Subject: Long-running PL/SQL function 
(long)
   

  01/07/2003 12:25 

  PM   

  Please respond   

  to ORACLE-L  

   

   






Our developers sent me a function which is running quite long to see if I
could give them any advice.   It is written in PL/SQL for version 9.2.0.1
of Oracle on Sun Solaris.   It is going across a database link.  It reads
tables in one database and loads a new table in a datamart table on another
box.   It looks like it will currently run for four or five days to load a
140 million-row table, which is longer than our available window.

I am wondering if anyone can look at the big picture and see if there are
any obvious places for improvement of this overall design.   I am open to
any suggestions that I can relay back to the developers.

My gratitude to anyone who can wade through this and recommend
improvements.

Cherie Machler
Oracle DBA
Gelco Information Network




FUNCTION exp_rpt_sts_load (
in_src_proc_no NUMBER,
in_stt_dt  DATE,
in_stop_dt DATE,
in_commit_interval NUMBER,
in_err_threshold   VARCHAR2,
in_debugging   BOOLEAN )
 RETURN BOOLEAN IS

   TYPE list_array IS VARRAY(200) OF VARCHAR2(2);

   TYPE no_array   IS VARRAY(200) OF NUMBER(10);

   lv_pay_sts_array list_array :=
list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

   lv_sts_cnfr_no no_array :=
no_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

   CURSOR c_exp_rpt_hdr IS
  SELECT a.acct_no,
 a.cnfr_no,
 a.arrv_dt_tm,
 b.pay_type,
 b.status,
 b.wh_mod_dt_tm upd_dt_tm,
 b.wh_date_key,
 b.wh_time_key
  FROM exp_rpt_hdr a,
 exp_rpt_amt_type b
  WHERE b.wh_mod_dt_tm BETWEEN in_stt_dt AND in_stop_dt
 AND trans_type = 'R'
 AND a.cnfr_no = b.cnfr_no
  order by acct_no, cnfr_no;

  -- c_exp_rpt_hdr storage values
  lv_cnfr_no exp_rpt_hdr.cnfr_no%TYPE;
  lv_acct_no acct_pay_type.acct_no%TYPE;
  lv_pay_typeacct_pay_type.pay_type%TYPE;

   CURSOR c_exp_rpt_dtls IS
  SELECT DISTINCT a.cnfr_no,
   b.line_seq_no,
   NVL(b.dtl_seq_no,0) dtl_seq_no,
   NVL(c.alloc_seq_no,0) alloc_seq_no,
   d.descr
  FROM exp_rpt_line_item_hdr a,
 exp_rpt_line_item_dtl b,
 exp_rpt_alloc c,
 acct_pay_type d
  WHERE a.cnfr_no = lv_cnfr_no
 AND b.pay_type = lv_pay_type
 AND a.cnfr_no = b.cnfr_no
 AND b.cnfr_no = c.cnfr_no(+)
 AND b.line_seq_no = c.line_seq_no(+)
 AND

RE: Long-running PL/SQL function (long)

2003-01-07 Thread Jamadagni, Rajendra

Title: RE: Long-running PL/SQL function (long)





Cherie,


I'd run this function with 2 events separately ...


first 10938 this will give you pl/sql profiling or simply use dbms_profiler package. This will tell you where (and at which line) you are spending most of your time. Metalink has some really good stuff on profiler ...

Then of course 10046 which will give you SQL profiling and you can concentrate on SQLS. I believe here you have to attack this problem on both fronts. I believe pl/sql tuning will be easier (I think) than SQL.

Are you running the code on the souce DB?


How big is acct_pay_type table? If it is less than 2000 rows, can you pre-load it as a pl/sql table so the selects can be avoided? I believe even some of pl/sql can be re-arranged to be a bit faster. But it is all relative. Have your developer sthought about using bulk-inserts and bulk selects?

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.*1

Re: rbs' maxextents in LMT

2003-01-07 Thread Guang Mei

Hi, Arup:

I created lmt rbs this way:

CREATE TABLESPACE RBS
DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 2M;

and then

SQL create rollback segment RBSTest1 storage(initial 2048K next 2048K) 
tablespace rbs;

Rollback segment created.

SQL create rollback segment RBSTest2 storage(initial 2048K next 2048K 
MAXEXTENTS 300) tablespace rbs;

Rollback segment created.

SQL select  SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT,
 2  MIN_EXTENTS,MAX_EXTENTS
 3  from dba_rollback_segs
 4  where SEGMENT_NAME like '%TEST%';

SEGMENT_NAME   INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
-- -- --- ---
MAX_EXTENTS
---
RBSTEST1  4194304 2097152   1
 32765

RBSTEST2  4194304 2097152   1
 32765


It shows that you can not set MAXEXTENTS of a rollback segment when it is 
created in LMT.  What I mean the run away transaction is a transaction 
that keep using rollback segment until it uses up all it's extents. In DMT 
case, we can set the MAXEXTENTS of all the rollback segments so that there 
is no transaction that can use the whole tablespace. But in LMT, it seems a 
run away transaction can eat up the whole rbs tablespace because there 
is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is 
somewhere in Oracle Doc that I can find the answer of my question?

Thanks.

Guang


Date: Mon, 06 Jan 2003 18:10:08 -0500

Guang,

You should use LMTs with UNFORM extent allocation of some size So create the
tablespaces and the rollback segments but not the INITIAL or NEXT.

I am not sure what you meamn by runaway processes. If a transaction needs
rollback segment space, it will need to extend it. You can still specify
MAXEXTENTS to limit the number of extents.

HTH

Arup


_
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

--
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: MAX Number of Records in Cursor ?

2003-01-07 Thread BigP

Are you using a ref cursor (from JDBC ) and sertting some arraysize .

Bp
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 07, 2003 5:08 AM



 A Relationship manager needs to broadcast mail to all the Customers .
 There are around 102,847 customer

 To achieve this functionality in our code we are opening a cursor,
fetching each
 user id from a table and inserting into a mail table for each fetch.

 Mail is getting Generated for only 7130 Customers .
 There are no oracle errors reported in the log files .

 Is there any size limitation in oracle while opening/fetching a cursor as
the No. of
 records to be  fetched are 102,847 ?

 Thanks

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

Re: Cant install statspack !

2003-01-07 Thread Reginald . W . Bailey


Bob:

Try running the scripts in the command line version of SQL Plus.  In NT use
sqlplus instead of sqlplusw.  The statscusr.sql prompts for a couple of
tablespaces.
Start spooling right after logging in and set termout and echo on.  You
should capture some output this way.

RWB




Bob Metelsky [EMAIL PROTECTED]@fatcity.com on 01/07/2003 12:04:45 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hello All

 I seem to be missing something very basic as no matter what I do
I cant get statspack to install

This is 8.16 on WIN2kpro

I downloaded the newest version of stataspack and placed it into
%oracle_home%\rdbms\admin

C:\Oracle\Ora81\RDBMS\ADMINdir stats*
02/08/2000  07:36p   1,805 statsauto.sql
02/08/2000  07:36p 891 statscauto.sql
12/30/1999  02:13p   1,832 statscbps.sql
02/08/2000  07:36p 882 statscre.sql
02/08/2000  07:36p  28,088 statsctab.sql
02/08/2000  07:36p  27,879 statsctaba.sql
02/08/2000  07:36p   5,098 statscusr.sql
02/08/2000  07:36p   4,384 statscusra.sql
02/08/2000  07:36p 829 statsdrp.sql
02/08/2000  07:36p   3,344 statsdtab.sql
02/08/2000  07:36p   1,136 statsdusr.sql
02/08/2000  07:34p  28,516 statspack.doc
02/08/2000  07:36p  51,400 statspack.sql
02/08/2000  07:36p  48,205 statsrep.sql
01/19/2000  06:53p  52,610 statsrep80.sql
02/08/2000  07:36p 579 statsuexp.par
  16 File(s)257,478 bytes

I log onto the db sqlplusw internal/pw@instance

SQL@%oracle_home%\rdbms\admin\statscre.sql
the script seems to create the user.. but then blinks out. Apparently
its soupposed to generate .lis files but there are no such files

I tried to spool a log file but It only captures the first line... as
the script blinks out

if I try to

SQL execute statspack.snap

I get
PLS-00201: identifier 'STATSPACK.SNAP' must be declared

I try to prefix it with perfstat. or sys. but no joy

what can I be doing wrong??

Ive tried to hack the scripts(so they would stay up) but there are so
many variables that it dosnt seem practical

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

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




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

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

Re: Long-running PL/SQL function (long)


Raj,

Thanks for your reply.

This is a one-time-only load to set up a new datamart from our existing
warehouse.We have a separate process which will be doing periodic
refreshes.   However, yes, the inserts are going across the network with
the current design.

Cherie


   
 
Rajesh.Rao@jpm 
 
chase.comTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Re: Long-running PL/SQL function 
(long)   
om 
 
   
 
   
 
01/07/03 12:04 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 





Cherie,

If network bandwidth is the bottleneck, the use of Fast Refreshable
snapshots will be a great help. Whereby you only pull the rows that have
changed since the last refresh across to the primary.

Raj





Cherie_Machler

@gelco.comTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
Sent by:  cc:

[EMAIL PROTECTED]Subject: Long-running PL/SQL
function (long)
om



January 07,

2003 12:25 PM

Please respond

to ORACLE-L








Our developers sent me a function which is running quite long to see if I
could give them any advice.   It is written in PL/SQL for version 9.2.0.1
of Oracle on Sun Solaris.   It is going across a database link.  It reads
tables in one database and loads a new table in a datamart table on another
box.   It looks like it will currently run for four or five days to load a
140 million-row table, which is longer than our available window.

I am wondering if anyone can look at the big picture and see if there are
any obvious places for improvement of this overall design.   I am open to
any suggestions that I can relay back to the developers.

My gratitude to anyone who can wade through this and recommend
improvements.

Cherie Machler
Oracle DBA
Gelco Information Network


--
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: 
  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: Long-running PL/SQL function (long)


Raj,

Just got this code this morning so I haven't had a chance to run a trace
yet.   Will have to schedule that for tonight.

Yes, code is being run on the source DB.

Yes,  ACCT_PAY_TYPE is less than 2000 rows.   You are the second person who
has recommended a PL/SQL table.   I will run a test and see if that will
help here.

No, I don't believe the developers have actively considered bulk selects
and/or bulk inserts.   I forgot to mention that we are using 9.2.0.1 on the
source database and 8.1.7.2 on the target (remote) database.Could we
still do bulk inserts if that is the case?

Thanks for your reply and ideas.   I will investigate and pass them on.

Cherie


   
  
Jamadagni,
  
Rajendra To: Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
Rajendra.Jamadagni   cc:  
  
@espn.comSubject: RE: Long-running PL/SQL 
function (long)   
Sent by:   
  
[EMAIL PROTECTED]   
  
   
  
   
  
01/07/03 12:59 PM  
  
Please respond to  
  
ORACLE-L   
  
   
  
   
  




Cherie,


I'd run this function with 2 events separately ...


first 10938 this will give you pl/sql profiling or simply use dbms_profiler
package. This will tell you where (and at which line) you are spending most
of your time. Metalink has some really good stuff on profiler ...


Then of course 10046 which will give you SQL profiling and you can
concentrate on SQLS. I believe here you have to attack this problem on both
fronts. I believe pl/sql tuning will be easier (I think) than SQL.


Are you running the code on the souce DB?


How big is acct_pay_type table? If it is less than 2000 rows, can you
pre-load it as a pl/sql table so the selects can be avoided? I believe even
some of pl/sql can be re-arranged to be a bit faster. But it is all
relative. Have your developer sthought about using bulk-inserts and bulk
selects?


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! (See
attached file: ESPN_Disclaimer.txt)








ESPN_Disclaimer.txt
Description: Binary data

RE: FULL TABLE SCAN?

2003-01-07 Thread Farnsworth, Dave

Title: RE: FULL TABLE SCAN?



It's a 
bit buggy in 8i!

  -Original Message-From: Deshpande, Kirti 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
  1:09 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: FULL TABLE SCAN?
  Is 
  that backported to 8i and 7.3 ??? :)
  
  ;) 
  
  
  Thanks.
  
  - 
  Kirti
  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
  11:05 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: FULL TABLE SCAN?
  _full_table_scan=FALSE 
  -Original Message- From: Seema 
  Singh [mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: FULL TABLE SCAN? 
  Hi How to avoid FULL TABLE 
  SCAN? Thx -seema 
  
  _ 
  MSN 8 with e-mail virus protection service: 2 months FREE* 
  http://join.msn.com/?page=features/virus 
  -- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.net -- 
  Author: Seema Singh  
  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: Are too many Foreign Keys in one table bad?

2003-01-07 Thread Jared . Still

Thanks, but I don't think I'm in the same class as
some of those names.  I just keep my head down
and keep trying.  :)

Jared





Mark Richard [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/06/2003 06:23 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:Re: Are too many Foreign Keys in one table bad?


All,

Point well taken (both Rachel's and Jared's).  I should have said (and was
even thinking - although the brain and hands sometimes act independently)
might not be worth indexing.  It sounds like a helpdesk system for a
pretty small customer base so I was assuming that system load isn't likely
to be a problem.  My experience has always been that if the fact is 1000
rows and the reference are maybe 3 - 10 then Oracle is going to eat it up
for lunch no matter how it's structured unless a large number of 
concurrent
user come along.

Now on a more serious note, when is the week-long Rachel Carmichael, Dan
Fink, Jonathan Lewis, Connor, Jared, Kirti, et al How to well and truly
beat Oracle into Submission seminar coming down under to Australia?  I
need to know so that I can start selling my soul to raise enough money to
attend...  With our dollar the way it is a seminar like that would cost
about the same as my house.



  
Rachel  
Carmichael   To: Multiple recipients of 
list ORACLE-L [EMAIL PROTECTED] 
wisernet100@y   cc:   
ahoo.comSubject: Re: Are too many 
Foreign Keys in one table bad? 
Sent by:  
[EMAIL PROTECTED]  
om  
  
  
07/01/2003  
12:33  
Please respond  
to ORACLE-L  
  
  




Mark,

Based on the presentation and testing Dan Fink did for the last NYOUG
meeting, it's possible that the ref tables SHOULD be indexed, and that
it will help performance to index them.

Rachel

--- Mark Richard [EMAIL PROTECTED] wrote:
 Greg,

 I don't think Oracle will have a real problem with 15 tables or 1,000
 rows.
 If the ref tables are quite small then they won't even be worth
 indexing -
 Oracle will just read the entire table at one anyway.  You might want
 to
 tell Oracle to CACHE the reference tables, although I don't think
 you'll
 see a performance gain really.  Unfortunately I can't give any
 performance
 suggestions because I am used to the other end of the scale (ie: 250
 million rows in data)

 You probably could store CODE in the main table, but if you are going
 to
 need the description frequently then all benefit is lost anyway.
 Either
 way though I'm sure that you'll have more problems getting the 15
 joins
 right when writing the queries than Oracle's CBO will have when
 looking at
 the query - I've seen some real nasty queries get pushed into
 Oracle's
 optimisor and as long at the statistics are valid then it does a
 pretty
 good job.

 Cheers,
  Mark.

 PS:  Why would the reference CODE change instead of the DESCRIPTION?
 I'm
 guessing the code will be meaningful such as HIGH, CRITICAL, etc
 and
 description might be Must fix within 1 hr.  Even still, I think you
 are
 right when you said that CODE isn't likely to change often, if at
 all.





 Gregory Norris

 GNorris2@work   To: Multiple recipients
 of list ORACLE-L [EMAIL PROTECTED]
 brain.com   cc:

 Sent by: Subject: Are too many
 Foreign Keys in one table bad?
 [EMAIL PROTECTED]

 om





 07/01/2003

 07:03

 Please respond

 to ORACLE-L










 I am designing some tables to store Customer Support Data.
 The main table (SUPPORT_DATA) contains many (up to 15) foreign key
 links to
 other tables.
 Most of the other tables are small lookup REFTABLES (eg Priority
 Type).
 A few bigger tables store up to 1000 records eg CUSTOMER_DATA.

 I am concerned that to get data for one Support record will involve a
 join
 of 15 Tables and possibly more for reports, and that this many tables
 may
 confuse the Cost Based Optimiser.

 I am considering storing the CODE in the SUPPORT_DATA table instead
 of the
 ID for the reference tables.  This will reduce the number of joins
 greatly.

 _
 Design Proposed

 SUPPORT_DATA
   Id (PK)
   reftable_code (FK)
support_data_desc
 

 REFTABLE
   reftable_id (PK)
   reftable_code (Unique Constraint)
   reftable_description
 _

 The Main problems I see with this are that DATA storage increases (I
 can
 deal with that) and  that I will have to create a trigger to update
 all
 SUPPORT_DATA if one of the CODES in a REFTABLE is updated (this would
 be

RE: Cant install statspack !

2003-01-07 Thread Bob Metelsky

HOT DAMM!!

Ive taken everyones suggestions and did the following

Edited all the stats* file to change

whenever sqlerror exit; 
To
whenever sqlerror continue;

And connect perfstat/perfstat
To
connect perfstat/perfstat@instance

I also ran the script from  a cmd window rather than sqlplusw

I believe I looked at this last summer, and did in fact comment out or
alter the files so they would stay Up but its quite possible I was
more determined this go around due to everyones feedback. I diddnt
really think one should have to hack up the 16 scripts just so they
would run grin

Thanks for the suggestions!

bob
 


Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
STATSPACK complete. Please check statspack.lis for any errors.

LOCDB SQL execute statspack.snap

PL/SQL procedure successfully completed.

LOCDB SQL spool off;
not spooling currently
LOCDB SQL



@@@


 I had some problems with stats pack install today.
 There is: 'on error exit' in the scripts. Remove it so the 
 script can continue. This may solved your problem.
 
 Yechiel Adar
 Mehish
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, January 07, 2003 8:04 PM
 
 
  Hello All
 
  I seem to be missing something very basic as no matter what I do I 
  cant get statspack to install
 
  This is 8.16 on WIN2kpro
 
  I downloaded the newest version of stataspack and placed it into 
  %oracle_home%\rdbms\admin
 
  C:\Oracle\Ora81\RDBMS\ADMINdir stats*
  02/08/2000  07:36p   1,805 statsauto.sql
  02/08/2000  07:36p 891 statscauto.sql
  12/30/1999  02:13p   1,832 statscbps.sql
  02/08/2000  07:36p 882 statscre.sql
  02/08/2000  07:36p  28,088 statsctab.sql
  02/08/2000  07:36p  27,879 statsctaba.sql
  02/08/2000  07:36p   5,098 statscusr.sql
  02/08/2000  07:36p   4,384 statscusra.sql
  02/08/2000  07:36p 829 statsdrp.sql
  02/08/2000  07:36p   3,344 statsdtab.sql
  02/08/2000  07:36p   1,136 statsdusr.sql
  02/08/2000  07:34p  28,516 statspack.doc
  02/08/2000  07:36p  51,400 statspack.sql
  02/08/2000  07:36p  48,205 statsrep.sql
  01/19/2000  06:53p  52,610 statsrep80.sql
  02/08/2000  07:36p 579 statsuexp.par
16 File(s)257,478 bytes
 
  I log onto the db sqlplusw internal/pw@instance
 
  SQL@%oracle_home%\rdbms\admin\statscre.sql
  the script seems to create the user.. but then blinks out. 
 Apparently 
  its soupposed to generate .lis files but there are no such files
 
  I tried to spool a log file but It only captures the first 
 line... as 
  the script blinks out
 
  if I try to
 
  SQL execute statspack.snap
 
  I get
  PLS-00201: identifier 'STATSPACK.SNAP' must be declared
 
  I try to prefix it with perfstat. or sys. but no joy
 
  what can I be doing wrong??
 
  Ive tried to hack the scripts(so they would stay up) but 
 there are 
  so many variables that it dosnt seem practical
 
  Thanks
  bob
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Bob Metelsky
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).
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  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

RE: encrypted user/passwd connection

2003-01-07 Thread Jared . Still

Thanks Raj. 

I would think that the default being set to 'always encrypt' would be
more reasonable, 

In checking the parameters via 

select
   a.KSPPINM NAME,
   a.KSPPDESC DESCRIPTION,
   b.KSPPSTVL VALUE,
   b.KSPPSTDF ISDEFAULT
from X$KSPPI a, X$KSPPCV b
where a.indx = b.indx
and a.KSPPINM like '%crypt%'
order by name;

.. I found that only the dblink_encrypt_login parm was available.

This is on 7.3.4, 8.0.6, 8.1.7 and 9.2.0.

Where does ORA_ENCRYPT_LOGIN get applied?

Jared








[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/07/2003 07:03 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: encrypted user/passwd connection



All oracle passwords are encrypted is not a true statement. Failed login
attempts, are retried by sending the password in an unencrypted format.
Atleast, until 8.1.7. To avoid which, ORA_ENCRYPT_LOGIN variable and
DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database link)
should be set to TRUE.

I could stand corrected though.

Raj




  
Sony kristanto   
Sony@polyfincaTo: Multiple recipients of 
list ORACLE-L [EMAIL PROTECTED] 
nggih.com cc:   
Sent by:   Subject: RE: encrypted 
user/passwd connection 
[EMAIL PROTECTED]   
m  
  
  
January 07,  
2003 01:53 AM   
Please respond   
to ORACLE-L  
  
  




You're right Jared, all oracle password is encrypted. Btw Andrey if it is
possible how to do it ?

 -Original Message-
 From: Jared Still [SMTP:[EMAIL PROTECTED]]
 Sent: Tuesday, January 07, 2003 11:04 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:   Re: encrypted user/passwd connection


 Andre,

 Oracle does not send passwords across the network
 in clear text, they are encrypted by default.

 Jared

 On Monday 06 January 2003 05:43, Andrey Bronfin wrote:
  Dear list !
  I have just been asked the following question:
  is it possible to make a connection from an Oracle client to an Oracle
  instance (both are 8.1.7) in an encrypted way.
  I.e. if someone is sitting with a sniffer between the server and the
  client, then i don't want him to be able to see the user/passwd i'm
  connecting with. Again , i am NOT asking how store the data in the DB
in
 an
  encrypted way, but how to connect to an instance without showing my
  passwd.
  Thanks a lot!
  Andrey.


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

Snapshot too old

2003-01-07 Thread Patrick Van der Sande

Dear,

Since a few weeks I am tuning a big conversion batch written
in PL/SQL (millions of lines of code split over 7 batches)

When the job is running, certain batches stop with ORA-1555: Snapshot too old. Other batches run well till
the end.

Bizarre is that not always the same job stops.

When I do a trace I see nothing. With a normal trace I am
pretty sure that I will never see it.

Rollback segments are rarely used. So making the rollbacks
bigger or smaller is not the solution.

They also tried to change the commit rate. That was not the
solution.

When I modified the optimal size to NULL value to avoid
shrinking and cached 3 heavily used sequences some runs went all the way but

since a week it
stops again with the same annoying error.

After that I put an event in the init.ora
file : event = 1555 trace
name processstate forever, level 10

A trace file was generated but I could not find the error in
the trace file.

I am pretty sure that Oracle just dumps all open cursors in
a file. Since there are 100 of cursors opened I do not have a clue which one

is provoking the
error.

I already looked at the batches and I have identified in 5
of them a fetch across commit.

Still they have the error. But in the 2 remaining I can not
find this.(surely the 2 biggest ones, nice !)

So my question is :

How can I know where in the code the error is generated ?

Must I change the definition of the event ?
(I know there are other options but I can not find them right away)

Should I use DBMS_PROFILER ? (it generates massive files !)

Must they write exceptions everywhere in their code?

Can somebody
help me?

Please do not send me an explanation of the snapshot
too old error. I wake up with it and I go asleep with it.

Patrick

Re: Cant install statspack !

2003-01-07 Thread babu . nagarajan


I remember seing a script on Steve Adam's site that will delete the views
created so that STATSPACK can install correctly

Babu


   
 
  Barbara Baker
 
  barbarabbaker@yaTo:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  hoo.com cc: 
 
  Sent by: Subject:  Re: Cant install  statspack ! 
 
  [EMAIL PROTECTED] 
 
   
 
   
 
  01/07/03 01:37 PM
 
  Please respond to
 
  ORACLE-L 
 
   
 
   
 




Bob:


I have a slightly different version.  My spcreate.sql involkes 3 scripts:
@@spcusr,
@@spctab,@@spcpkg.  The problem that I had was that spcusr was creating
some x_ views that already existed in the database (perhaps from Steve
Adams' script).  When the spcusr script encounters the errors, it quits and
does not proceed with the remainder of the creation.


I had to modify the script and take out all the references to the x_ views
(or delete them, I don't remember which) in order to get a clean install.


You should have the equivalent of an spdrop.  If so you can run it and
start over, but this will not remove the x_ views and synonyms.


(Also make sure you're not using svrmgrl.  statspack does not like svrmgrl)


Good luck!


Barb






 Bob Metelsky [EMAIL PROTECTED] wrote:
 Hello All

 I seem to be missing something very basic as no matter what I do
 I cant get statspack to install

 This is 8.16 on WIN2kpro

 I downloaded the newest version of stataspack and placed it into
 %oracle_home%\rdbms\admin

 C:\Oracle\Ora81\RDBMS\ADMINdir stats*
 02/08/2000 07:36p 1,805 statsauto.sql
 02/08/2000 07:36p 891 statscauto.sql
 12/30/1999 02:13p 1,832 statscbps.sql
 02/08/2000 07:36p 882 statscre.sql
 02/08/2000 07:36p 28,088 statsctab.sql
 02/08/2000 07:36p 27,879 statsctaba.sql
 02/08/2000 07:36p 5,098 statscusr.sql
 02/08/2000 07:36p 4,384 statscusra.sql
 02/08/2000 07:36p 829 statsdrp.sql
 02/08/2000 07:36p 3,344 statsdtab.sql
 02/08/2000 07:36p 1,136 statsdusr.sql
 02/08/2000 07:34p 28,516 statspack.doc
 02/08/2000 07:36p 51,400 statspack.sql
 02/08/2000 07:36p 48,205 statsrep.sql
 01/19/2000 06:53p 52,610 sta! tsrep80.sql
 02/08/2000 07:36p 579 statsuexp.par
 16 File(s) 257,478 bytes

 I log onto the db sqlplusw internal/pw@instance

 SQL@%oracle_home%\rdbms\admin\statscre.sql
 the script seems to create the user.. but then blinks out. Apparently
 its soupposed to generate .lis files but there are no such files

 I tried to spool a log file but It only captures the first line... as
 the script blinks out

 if I try to

 SQL execute statspack.snap

 I get
 PLS-00201: identifier 'STATSPACK.SNAP' must be declared

 I try to prefix it with perfstat. or sys. but no joy

 what can I be doing wrong??

 Ive tried to hack the scripts(so they would stay up) but there are so
 many variables that it dosnt seem practical

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

 Fat City Network Services -- 858-538-5051 http://w! ww.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: email out of oracle

2003-01-07 Thread Mercadante, Thomas F

George,

Did you resolve this yet?  The error code indicates that you are missing a
Java class.  Did you load the ORACLE_HOME/rdbms/initplsj.sql file?  this
will load the PL/SQL Java classes needed to send mail.

hope this helps

PS.  Uncomment your exception clause in your procedure so that you can see
the text of the error message.  it should give you a better idea of the java
classes that are missing.

Tom Mercadante
Oracle Certified Professional


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


Hi there

Trying to email out from Oracle.

Utl_smtp is installed, executing procedure currently as a DBA. This sun
Machine does send email notifications out via the crontab to me so I know I
can send email via the exchange smtp server.

Problem, Email packages execute, if I do a print I see code 
SQL print

NP
--
-29540

Package executed with following command:

var np number;
exec send_mail('[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', 'testmsg', :np);

Below is the code of the send_mail package, can anyone see the problem or
know what this error code means.

Thx George


System

Oracle 8.1.6.3 EE 32 Bit
Solaris 2.6

--
-- Sending email out of Oracle using a stored procedure.
--
Create or replace PROCEDURE
  send_mail (senderIN VARCHAR2,
 recipient IN VARCHAR2,
 message   IN VARCHAR2,
 nStatus   OUT NUMBER)
IS
mailhostVARCHAR2(30) := '90.1.1.100';
mail_conn  utl_smtp.connection;

BEGIN
nStatus := 0;
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);

utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
nStatus := SQLCODE;
END send_mail;
/

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Cell: (+27) 82 655 2466
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

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

RE: Pinned packages with multiple reloads

2003-01-07 Thread Johnston, Tim

What about invalidations?  If something invalidates the packages does it
reload?

Just guessing...

I remember this thread and was hoping you would have an answer by now...
Damn...

:-)



-Original Message-
Sent: Tuesday, January 07, 2003 1:18 PM
To: Multiple recipients of list ORACLE-L


Hey all,

I have a trigger that pins various packages on the startup of our 8.1.7.2.0
DB on HP/UX 11.0.  I check V$DB_OBJECT_CACHE every so often to see that it's
working and to make any necessary additions/deletions from the list of
pinned packages.

However, the SYS.DBMS_APPLICATION_INFO package currently has 836 loads off
of 97706 execs despite being successfully pinned on startup (we've only been
up for a few days due a 7445 cascade crash).  How is this possible?
Furthermore, does it do any good to pin this package?  And why?

This is sort of a repost from months ago, but I can't find it on fatcity.

TIA!
Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Johnston, Tim
  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).

SQL Server/ Oracle DBA Needed in Phildelphia area..

2003-01-07 Thread OraStaff

Position: SQL Server DBA..the selected candidate will be crossed trained in
Oracle if not experienced.

Location: Philadelphia, Pennslyvania area (Berwyn)

Salary Range: Low-mid 70s base, outstanding benefits, plus 4-6 weeks paid
vacation first year.


*No relocation but a small sign on bonus may be offered to assist with
moving expenses...
 Local candidates high desired and will be given first consideration.

Please Do Not send your resume unless you have a stable work history.
Candidates whose work history includes frequent job changes connot be
considered.
If you are employed by a consulting company you must have a long term
project history.

This is a full time staff position so no sub-contractors or third parties
please.

No H-1B candidates please.

*Please do not send your resume unless you meet these requirements:
-Bachelor's Degree in Computer Science or related field. 
-3+ years SQL Server DBA experience..preferably including the latest releases.
-Oracle experience is a plus.
-Must be a U.S. citizen or permanent resident.

For  immediate consideration, please send your resume as a Word attachment to:
OraStaff, Inc.
Email: [EMAIL PROTECTED]
Please use job code: One/Philly/SQL Server DBA/Kim

All Submissions are handled in confidence.

*We pay referral fees.
So please contact me if you know of anyone who would be qualified/interested
in the position described above- if it is not a match for your skills.
Thanks,
Bill Law


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: OraStaff
  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: Long-running PL/SQL function (long)

2003-01-07 Thread Jamadagni, Rajendra

Title: RE: Long-running PL/SQL function (long)

Cherie,

Bulk inserts/selects are available since 8i ...
Not being sarcastic but sorry to disappoint you, but your developers haven't used anything new that wasn't in 8i ... you can give them the bad news .. (if you want.)

I think bulk operations will be useful, but you'll have to watch for the memory usage as well, I'd probably process data in chunks of couple of thousands. In or around 8i, pl/sql tables used to leak (I mean really leak) memory after the row length reached a specific length. Also modeling a pl/sql table but using table%rowtype used lot more memory than if it was based on a record etc.

Try it, you'll probably like the results ... I seem to remember of some issue with bulk operations on a remote database, but you can always populate a local table and import/export. I it will be faster than all the operations across db link.

Keep us posted though ...

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: Database/system Crashing

2003-01-07 Thread Webber Valerie H

Title: RE: Database/system Crashing



Dan,

I meant to say that I found out why Oracle crashed. There 
is a bigger problem with the OS since it crashes when the db is down and it 
seems to lose parts of itself if that makes sense. After the OS "sorta crashes" 
or partially crashes, some Unix commands are invalid like CAT or MORE or even 
VI. The SAs are looking into it. I thought about re-creating the control file or 
replacing it with a good one but they like you think that is just a symptom of a 
bigger OS problem.

Val

  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 12:16 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Database/system Crashing
  Val,
   Have you tried copying a known good controlfile in place 
  of the bad one? If not, try it and report the result. If it corrupts as well, 
  it seems to me that there is a much bigger problem. If it does not corrupt, 
  then the question is, why didn't oracle report the corruption in the first 
  place. I hate to say this, but I'm not certain you have found the problem, you 
  may only be experiencing another symptom.
  
  Dan
  
-Original Message-From: Webber Valerie H 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
9:34 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Well I relocated the background dest files and I got 
the following error... that was a great idea!

ORA-00206: error in writing (block 3, # blocks 1) of 
controlfileORA-00202: controlfile: 
'/u04/oradata/ERCS/ora_control2'ORA-27063: Message 27063 not 
found; product=RDBMS; facility=ORASVR4 Error: 5: I/O 
errorAdditional information: -1Additional information: 2048error 
221 detected in background process

The SA's think its a data block corruption. If anyone 
has any additional information, it will be greatly 
appreciated.
At least now I know why the database crashed to begin 
with. Now the SA's just have to figure out how to fix 
it.

Thanks for all the help!!
Val

  -Original Message-From: Burke, William F (Bill) 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 2:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  I'd agree with Dan. You need to find the root cause of the 
  crash. If you rebuild to the current state from scratch, the odds 
  are you'll see the same problem reoccur. Secondly, while NFS mounted 
  volumes will work, they should always be a last resort as any network, 
  remote IO load on the server where the NFS mounted volume lives "could" 
  cause IO corruption and panic the host server. I didn't see the 
  start of this thread so these are after the thought comments. Maybe 
  they're helpful.
  
  Regards, 
  Bill Burke "The Kinder and Gentler DBA" www.OracleGuru.com www.KBMotorsports.biz 
  
-Original Message-From: Fink, Dan 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:55 
AMTo: Multiple recipients of list ORACLE-LSubject: 
RE: Database/system Crashing
Val,
 Not having an entry in the 
alert log or having trace files is not all that odd. This indicates a 
hard crash of the instance, where the background processes were unable 
to write to the files. This could be a result of the instance being 
forcefully terminated without using the Oracle shutdown process or it 
could be the result of the processes being unable to write to the device 
containing the log and trace files. Try moving the background_dump_dest 
to another device (preferably internally connected to the 
server).
 I would not reinstall the OS 
and Oracle unless it can be reasonably determined that the OS is causing 
the problem. What are the reasons the SAs say it is the OS? It is a lot 
of work to recreate the system and you have no guarantee that this will 
solve it. It sounds like a more detailed inspection of all the systems 
is in order instead of spinning the 'Wheel Of Blame' to stop on the 
'most likely' suspect. More troubleshooting is called for, not the 
drastic step of "wipe it clean and start over"

Dan Fink

  -Original Message-From: Webber Valerie H 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 
  2003 9:40 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database/system 
  Crashing
  Yes there are NFS mounts involved. What you said about 
  the OS locks on the audit directory makes a lot of sense. My SA's are 
  back to thinking it's a OS problem because it crashed again with the 
  database shut down. 
  The odd thing is that there

RE: Linux and Oracle Cluster File System

2003-01-07 Thread Jos

Thanks for the clarification, a lot more to read.
Richard Ji [EMAIL PROTECTED] wrote:


Jos,

Yes, you are right that if you use OCFS then you can just have one mount point and create all
data files under it, because OCFS is a file system. It makes it a lot easier to manage space
and you can use commands like: cp, mv, rm etc which you can't do to a raw partition.
Backup on OCFS is also easier than using raw.

Richard Ji

-Original Message-From: Jos [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 8:49 AMTo: Multiple recipients of list ORACLE-LSubject: OT: Linux and Oracle Cluster File SystemList,I am a newbie on Linux and Clustering technology, I need to setup a RAC system and have been reading somedocumentation on how to do it. I am a bit confuse about the difference between setting the disk up with Linux raw partition and Oracle cluster file system, I hope someone on the list can give me some hints oruseful reference for reading. The document said for Lunix raw partition I need to setup a partition pertablespace, this is quit a lot considering for Oracle Apps there is about 200 tablespaces. I am wonderingif I am using Oracle Clustering file system (OCFS), can I define one big partition for OCFS and mount it!
 on /u01 and create all the files under this one mount point or the one raw partition per tablespace rule stillapplies.Jos


Yahoo! Greetings- Send your seasons greetings online this year!
Yahoo! Greetings
- Send your seasons greetings online this year!

Re: Cant install statspack !

I get around the connect problem by setting oracle_sid before invoking
sqlplus (no W) from a dos box in NT.
Of course, this means that I work on the server itself.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 07, 2003 8:37 PM


 Bob,

 The scripts contain CONNECT statements which likely do not
 include the @instance TNS connect-string you are using
 initially.  The scripts also contain WHENEVER OSERROR and
 WHENEVER SQLERROR directives, so they blow out of SQL*Plus
 on error, most likely...

 Please consider editing the scripts to add the TNS
 connect-strings?

 Hope this helps...

 -Tim

  Hello All
 
  I seem to be missing something very basic as no matter
  what I do I cant get statspack to install
 
  This is 8.16 on WIN2kpro
 
  I downloaded the newest version of stataspack and placed
  it into %oracle_home%\rdbms\admin
 
  C:\Oracle\Ora81\RDBMS\ADMINdir stats*
  02/08/2000  07:36p   1,805 statsauto.sql
  02/08/2000  07:36p 891 statscauto.sql
  12/30/1999  02:13p   1,832 statscbps.sql
  02/08/2000  07:36p 882 statscre.sql
  02/08/2000  07:36p  28,088 statsctab.sql
  02/08/2000  07:36p  27,879 statsctaba.sql
  02/08/2000  07:36p   5,098 statscusr.sql
  02/08/2000  07:36p   4,384 statscusra.sql
  02/08/2000  07:36p 829 statsdrp.sql
  02/08/2000  07:36p   3,344 statsdtab.sql
  02/08/2000  07:36p   1,136 statsdusr.sql
  02/08/2000  07:34p  28,516 statspack.doc
  02/08/2000  07:36p  51,400 statspack.sql
  02/08/2000  07:36p  48,205 statsrep.sql
  01/19/2000  06:53p  52,610 statsrep80.sql
  02/08/2000  07:36p 579 statsuexp.par
16 File(s)257,478 bytes
 
  I log onto the db sqlplusw internal/pw@instance
 
  SQL@%oracle_home%\rdbms\admin\statscre.sql
  the script seems to create the user.. but then blinks out.
  Apparently its soupposed to generate .lis files but there
  are no such files
  I tried to spool a log file but It only captures the first
  line... as the script blinks out
 
  if I try to
 
  SQL execute statspack.snap
 
  I get
  PLS-00201: identifier 'STATSPACK.SNAP' must be declared
 
  I try to prefix it with perfstat. or sys. but no joy
 
  what can I be doing wrong??
 
  Ive tried to hack the scripts(so they would stay up) but
  there are so many variables that it dosnt seem practical
 
  Thanks
  bob
  --
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net --
  Author: Bob Metelsky
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com San Diego, California--
  Mailing list and web hosting services
  --
  --- To REMOVE yourself from this mailing list,
  send an E-Mail message to: [EMAIL PROTECTED] (note
  EXACT spelling of 'ListGuru') and in the message BODY,
  include a line containing: UNSUB ORACLE-L (or the name of
  mailing list you want to be removed from).  You may also
  send the HELP command for other information (like
  subscribing).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author:
   INET: [EMAIL PROTECTED]

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


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

clustering Oracle9i db

2003-01-07 Thread Greg Faktor

Hi All!
Need advice:
We have two Windows 2000 servers with MSCS installed on it.
Now I need to install Oracle9i.
I read about Real Application cluster for 9i and Oracle fail safe:
MSCS will take care about load balancing and failover do I need to instal RAC on top?

Thanks.
Greg.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Greg Faktor
  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: Database/system Crashing

2003-01-07 Thread Webber Valerie H

Title: RE: Database/system Crashing





"PS.. do we all get a virtual 
"pass" on a future audit for helping? :)"

ABSOLUTELY!! 
;)

  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
  3:52 PMTo: '[EMAIL PROTECTED]'Cc: 
  '[EMAIL PROTECTED]'Subject: RE: Database/system 
  Crashing
  Val,
  
  if 
  the unix commands are disappearing, then it sounds like you are either losing 
  disk directories, or the paths that point at them.
  
  when 
  I first read your post last week, I had a sneaky feeling that this was an OS 
  problem and not an Oracle one. but not having anything solid to offer 
  you, I just lurked until someone with better unix experience could 
  help.
  
  glad 
  you are "on your way" to figuring it out.
  
  
  PS.. do we all get a virtual "pass" on a future audit for 
  helping? :)
  Tom Mercadante Oracle Certified Professional 
  
-Original Message-From: Webber Valerie H 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 
3:00 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Dan,

I meant to say that I found out why Oracle crashed. 
There is a bigger problem with the OS since it crashes when the db is down 
and it seems to lose parts of itself if that makes sense. After the OS 
"sorta crashes" or partially crashes, some Unix commands are invalid like 
CAT or MORE or even VI. The SAs are looking into it. I thought about 
re-creating the control file or replacing it with a good one but they like 
you think that is just a symptom of a bigger OS problem.

Val

  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 12:16 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  Val,
   Have you tried copying a known good controlfile in 
  place of the bad one? If not, try it and report the result. If it corrupts 
  as well, it seems to me that there is a much bigger problem. If it does 
  not corrupt, then the question is, why didn't oracle report the corruption 
  in the first place. I hate to say this, but I'm not certain you have found 
  the problem, you may only be experiencing another 
  symptom.
  
  Dan
  
-Original Message-From: Webber Valerie H 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 
2003 9:34 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Well I relocated the background dest files and I 
got the following error... that was a great idea!

ORA-00206: error in writing (block 3, # blocks 1) 
of controlfileORA-00202: controlfile: 
'/u04/oradata/ERCS/ora_control2'ORA-27063: Message 27063 not 
found; product=RDBMS; facility=ORASVR4 Error: 5: I/O 
errorAdditional information: -1Additional information: 
2048error 221 detected in background process

The SA's think its a data block corruption. If 
anyone has any additional information, it will be greatly 
appreciated.
At least now I know why the database crashed to 
begin with. Now the SA's just have to figure out how to fix 
it.

Thanks for all the help!!
Val

  -Original Message-From: Burke, William F 
  (Bill) [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 
  2003 2:49 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database/system 
  Crashing
  I'd agree with Dan. You need to find the root cause of 
  the crash. If you rebuild to the current state from scratch, the 
  odds are you'll see the same problem reoccur. Secondly, while 
  NFS mounted volumes will work, they should always be a last resort as 
  any network, remote IO load on the server where the NFS mounted volume 
  lives "could" cause IO corruption and panic the host server. I 
  didn't see the start of this thread so these are after the thought 
  comments. Maybe they're helpful.
  
  Regards, 
  Bill Burke "The Kinder and Gentler DBA" www.OracleGuru.com www.KBMotorsports.biz 
  
-Original Message-From: Fink, Dan 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 
11:55 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Val,
 Not having an entry in 
the alert log or having trace files is not all that odd. This 
indicates a hard crash of the instance, where the background 
processes were unable to write to the files. This could be a result 
of the instance being forcefully

Re: Long-running PL/SQL function (short)


Stephane,

Thanks for your reply.

I don't think it's a possibility to get another resource to rewrite the
PL/SQL.   Our shop is more java-based and PL/SQL is not our developers
language of choice.

Do you mean to move as many statements inside of the loop to outside of the
loop as possible?   If yes, I'll pursue that.

I already made recommendations to remove the DISTINCT and ORDER BY clauses
in most SQL statements.   I was told that the distinct is necessary to
remove redundant data and do some additional  clean-up.   I believe that
they want the order by to make their QA effort easier and confirm that the
data is still valid and matches across to other databases.   However, I am
trying to push to get the ORDER BYs out at least.

Thanks again for your feedback.

Cherie


   
 
Stephane   
 
Faroult  To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
sfaroult@orio   cc:   
 
le.com  Subject: Re: Long-running PL/SQL function 
(short)  
Sent by:   
 
[EMAIL PROTECTED] 
 
om 
 
   
 
   
 
01/07/03 01:39 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Have the procedure rewritten by somebody with a little experience.
Getting rid of statements inside loops, DISTINCT and ORDER BY which are
rarely necessary when moving data from a table to another table would be
good places to start.

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





-- 
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: encrypted user/passwd connection