RE: Database/system Crashing

2003-01-13 Thread Richard Ji 
Title: RE: Database/system Crashing



Sorry 
for getting back to you late on this.  In our case, the server crashes each 
time with CPU panic message
in 
/var/adm/messages.  We used adb to analyze the core dump and saw in each 
instance of the crash, NFS
lead 
to it.  It was trying to free memory twice which caused the panic.  
And talking to Sun confirmed that there
was a 
bug in NFS.  If you are interested in it I can send you the patch 
number.

  -Original Message-From: Webber Valerie H 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 
  5:26 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database/system Crashing
  Richard, 
   
  Yes this is a SUN platform. One SA now 
  believes it is independent of the database since it happens when the database 
  isn't running. I moved the background dest files to the other disk (other than 
  the root disk.) Normally it would have crashed by now after starting the 
  database and using Designer but so far so good.
   
  There are no error messages in 
  /var/adm/messages. There's no core dump file either. Its like the system gets 
  corrupt before it can write to the /var/adm/messages file.
   
  What were the symptoms of your Sun NFS 
  related crashed and how did you diagnois the problem and what was the solution 
  (if it were that simple)
   
  Val
  
-Original Message-From: Richard Ji 
[mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 
2003 4:19 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database/system 
Crashing
Val,
 
Sorry I missed the previous messages.  Was 
this a Sun platform?  Did the system crash with a CPU 
panic
in 
/var/adm/messages?  We resovled a Sun NFS related crashes a couple of 
months ago.
 
Richard Ji

  -Original Message-From: Webber Valerie H 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 
  11: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 
  -Original Message- From: 
  Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 10:09 AM To: 
  Multiple recipients of list ORACLE-L Subject: RE: 
  Database/system Crashing 
  I wonder if a file lock is being left in place when the 
  instance crashes, and the OS does not clear the 
  lock until a reboot.  I would think the OS should clear this without a reboot, but stranger things have been 
  seen with OS's ... even Unix.  This doesn't 
  explain why the instance crashes.  I wonder 
  if fuser would show anything.  Are there any NFS mounts 
  involved? 
  -Original Message- Yes, 
  you're correct and it can write the file to 
  $ORACLE_HOME/rdbms/audit once the system is 
  rebooted. Its just that when the database crashes, it can't write to that location until its rebooted. Is it possible that I need to beef up my init.ora 
  parameters? -- 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: Oracle 7.3.4 Real-Time Re-indexing - Additional Information

2003-01-13 Thread Nikunj Gupta 

Were you analyzing while rebuilding Indexes ?

If that is the case.. there are chances of possible corruption.

Try to give
select count(*) from table_name;

and

select * from table_name;

Hope this may provide you with some answer.

HTH
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, January 13, 2003 06:43 AM


>
> Sorry, I forgot this additional infromation...
>
> Oracle created invalid objects when we ran rebuild.  The odd thing was
that
> the trace file showed an invalid object but the all_objects table showed a
> valid object.
>
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 9:39 AM
> To: [EMAIL PROTECTED]
>
>
>
> Team,
>
> I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
> While the users were on the system, we Re-Indexed our tables.
> Situation: The users complained, that they were unable to process their
> orders.
> Although, the log file showed that the re-indexing was successful, the
users
> were still unable to process their orders.
>
>
> QUESTION: Are they any known issues/pit-falls when re-indexing real-time
in
> version 7.x?
>
> Should you have the database in exclusive mode when re-indexing?
>
> Please assist.
>
> Thanks
>
> Conrad Meertins
>
> [EMAIL PROTECTED]
>
> DBA Masters
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Conrad Meertins
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nikunj Gupta
  INET: [EMAIL PROTECTED]

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

2003-01-13 Thread Sony kristanto 
BTW rajesh, where can I find 'ORA_ENCRYPT_LOGIN variable and
DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database
link)' ? Pls respond ...



> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, January 07, 2003 10:02 PM
> To:   [EMAIL PROTECTED]
> Cc:   [EMAIL PROTECTED]
> 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
> 
>  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: Sony kristanto
  INET: [EMAIL PROTECTED]

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

2003-01-13 Thread Jared Still 

Gee Dave, I know of a book that has scripts that
already do this.  ;)

Here's an untested bit of code to demonstrate.

   my $dbh;

   eval {

  local $SIG{ALRM} = sub {
 die "connection timeout\n";
  };

  alarm 60;

   $dbh = DBI->connect(
 'dbi:Oracle:' . $db,
 $username, $password,
 {
 RaiseError => 1,
 AutoCommit => 0,
  }
   
   );

   };

   # the alarm reset must be outside the eval{}
   alarm 0;

I say untested cuz I simplified it a bit after lifting
it from a script.

HTH,

Jared

On Monday 13 January 2003 15:49, David Turner wrote:
> Does anyone have some perl code that will return an error if it take longer
> than a certain number of seconds to connect to or return the results from a
> database? I'd like to have some of my queries connect to an alternate
> database if there is a problem connecting or returning results within 10
> seconds. Any other suggestions are appreciated.
>
> Dave
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

Re: Oracle 9i release 3 coming?

2003-01-13 Thread Jared Still 

Since 9iR2 is 9.2.0, I don't think that 9.0.3 will be Release 3.

Jared

On Monday 13 January 2003 18:23, Boivin, Patrice J wrote:
> Oracle started posting items with the 9.0.3. number, does this imply they
> plan to release a 9i Release 3?
>
> 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]


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 

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

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

Re: Oracle 9i release 3 coming?

2003-01-13 Thread Rachel Carmichael 
doubtful, they've said that release 2 is the final release for 9i

--- "Boivin, Patrice J" <[EMAIL PROTECTED]> wrote:
> Oracle started posting items with the 9.0.3. number, does this imply
> they
> plan to release a 9i Release 3?
>  
> 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] 
> 
> 
> 


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

2003-01-13 Thread Jared Still 

Yes, well, I've heard that claim too.

It may actually work now, but a very capable DBA ( 2 actually )
spent quite a bit of time testing this with no good results.

And then there was the SQL BT glitch that left one of my former
coworkers without a certain TBS in the DW for 6 weeks.

Yes, 6 weeks.

And you complain about Oracle Support.  ;)

Jared

On Monday 13 January 2003 18:29, Deshpande, Kirti wrote:
> I think SQLBackTrack can get the table data back from the backup file
> without the need to create a partial  database on another server and then
> export the table from it.
>
> I remember seeing a demo where a dropped table was recovered by the BMC
> rep.
>
> - Kirti
>
> -Original Message-
> Sent: Monday, January 13, 2003 6:15 PM
> To: Multiple recipients of list ORACLE-L
>
>
> RMAN Does everything that SQL Backtrack does, for free. :-)
> In fact, last I heard, BMC was planning on altering SQL Backtrack so that
> it
>
> is really nothing more than a nice fancy front end to RMAN. When I was
> at CSX we moved away from SQL Backtrack to RMAN and never had any regrets.
> The only issue with SQL Backtrack vs. RMAN was support for 7.x databases.
> RMAN does not support anything < 8.0.
>
> RF
>
> Robert G. Freeman
> Technical Management Consultant
> TUSC - The Oracle Experts www.tusc.com
> 904.708.5076 Cell (it's everywhere that I am!)
> Author of several books you can find on Amazon.com!
>
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 4:39 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> I *think* that RMAN does everything SQL Backtrack does.
>
> Can't be positive, as I haven't used it for awhile.
>
> RMAN seems to have everything I recall SQL BT having, and then some.
>
> Jared
>
> On Monday 13 January 2003 12:38, Tim Gorman wrote:
> > Don't know really.  Just thought that it should probably be included,
> > then I was hoping to find out...  :-)
> >
> > - Original Message -
> > To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> > Sent: Monday, January 13, 2003 10:49 AM
> >
> > > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > > Another question:  should SQL BackTrack be included for
> > > > consideration?
> > >
> > > What does SQL BackTrack to that RMAN doesn't do?
> > >
> > > Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

RE: BCHR Tuning

2003-01-13 Thread mantfield 
Morgens is very correct in saying tha all sorts of measurements have their 
place. Actually, the length-of-skirt measurement works very well for me. 
Here is the algorithm:

Heat-by-day in inversely proportional to length of skirt which again is 
inversely proportional to my desire to have lunch outdoors at sidewalk 
cafe, but which directly affects my enjoyment of the day and inversely 
affects my productivity (much like this posting).

Right now (Jan / Feb), we are experiencing our heatwaves (30+ Celsius by 
day, 20 by night = absolute bliss), so I get a lot of opportunity to 
streamline the algorithm and processes. Melbourne is the place to be, 
unless you can get to work on Bondi beach in Sydney, where the skirt length 
= zero.

Of course, if you study the skirts or lack of it TOO intensely, this leads 
a high jealous-boyfriend-hit-ratio, which inversely affects my overall 
well-being and morale, so you need to find that optimal balance between 
appreciation and blatant gawking or technically put : maximum benefit 
within minimized response time.

Ferenc Mantfeld

-Original Message-
From:   Mogens Norgaard [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, January 14, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: BCHR Tuning

Something here doesn't compute. If you tried to use time-based
measurements and didn't find out where the time went - well, bad for
you. If you then stumbled on something, say the database
startup/database shutdown ratio (would normally be fairly close to 1,
but could vary) or the log file switch/archive log file ratio (again,
could be close to 1 or 100% or something - or could vary) or the ratio
of blocks from a certain index found in the permanent pool versus the
number of PIO's required for that statement - or whatever - it would
still be guesswork, checklist tuning, or what you'd prefer to call it.

All sorts of measures have their place. All sorts of measures could
prove interesting. When I went to school the famous example was the wolf
population of Canada which seemed to follow the birthrate of children in
Denmark. Or the length of skirts versus economic prosperity in the
Western world, which also proved rather closely matched.

If you want to measure response time (what else?) it just might be of
interest to find out where the time is spent.

The BCHR, the x/y, the DBStarup/DBShutdown ratio or other ratios or
measurements might be important to find out symptoms of things - but to
say that that kind of guesswork still has it's place is like saying that
we should still carefully watch the wolf population of Canada or the
skirt length in the Western World...because you never know.

And that just might be the case: You never (will) know until you adopt
an approach that is hierachical (spelling?) and which you can use to
prioritize and quantify your efforts (try that with the BCHR - the
x$kcbrbh, etc. of course are grossly wrong in those respects).

Yep, I've been there, I've used it all, I've tried to use all the notes
and articles regarding the wonderful statistics available in bstat/estat
- I've been through the stages of collecting more and more queries and
numbers and ratios until my file with scripts and queries was bigger
than Holland. Yet it never gave me solutions, just a lot of things to
check and change and fiddle with - without knowing which one to choose
first, and how much it would help.

The YAPP method works. There are cases where it is not 100% accurate. In
most cases it's spot on. Watch where the monitoring tools are going.
Spotlight in the latest edition have the YAPP method built in. Let's see
what Oracle does in 10i. Precise has it. Steve Adams' scripts has it.

This is not about the BCHR being low or high or in between. This is
about using a METHOD instead of 100s of different numbers that don't
mean anything.

Mogens

[EMAIL PROTECTED] wrote:

>I too think the BCHR has its place, as a problem indicator. It can tell me
>theres something wrong with my database. Say, I have this database
>performing well, the users are happy, the BHR is mostly at 90%, and now it
>suddenly shoots down to 70%, or it suddenly increases to 98. Somethings
>amiss. Its less tasking, to code for scripts that query v$sysstat to
>indicate me of some problems, rather than querying v$sqlarea. Or I need to
>code for some intelligent scripts to query v$session_wait or
>V$system_event. Or I need to look at the statspack reports every hour. The
>point is when do I look at wait events? When the user calls me up?
>
>All the papers out there, asking us rightly, to look at wait events, trash
>the BCHR. I think what the authors intended was to tell us that increasing
>DB_BLOCK_BUFFERS was not the solution to a low BCHR, and that a BCHR of 
99%
>does not mean a highly efficient database. Vice Versa, a BCHR of 50% does
>not indicate a poorly performing database. Give me a database with a 45%
>BHR, and I can get it to 99% by running a few queries. Point well
>underst

RE: 2 basic Qs. on export

2003-01-13 Thread Deshpande, Kirti 
Here is my suggestion:

1.
  Use file= and filesize= option (8i Rel 2?) to create multiple disk files, copy them 
to tape. Reload when  importing. 

2a. 
  Export should not be considered a database backup. (IMHO). In future releases, 
full=y may not be available. 
2b.
  Depends. If export starts at 10:00 with option consistent=y then no updates after 
10:00 will be seen by export. You will get a read consistent image of the database (as 
of 10:00).  With consistent=n (default), updates to rows not yet read by exp will be 
included in the exported data.  

There is a good discussion about this in the Oracle8i/9i Utilities Guide. 

- Kirti 



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



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

FILE=

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


2) With Oracle  8i & 9i 

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

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


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

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



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

2003-01-13 Thread Deshpande, Kirti 
SQLBackTrack does not have 'block' recovery, something I think is in the latest RMAN 
version (?).

I don't use either of these, but just remember a few things from demos, written 
material.

- Kirti 

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



Yes, but I think less capable than RMAN.

Just do a google search for it.  Owned by BMC I believe.

Jared

On Monday 13 January 2003 14:34, VIVEK_SHARMA wrote:
> Is SQL BackTrack a product like RMAN (from Oracle Corp. or 3rd party )?
>
>
> -Original Message-
> Sent: Tuesday, January 14, 2003 2:09 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Don't know really.  Just thought that it should probably be included, then
> I was hoping to find out...  :-)
>
> - Original Message -
> To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 10:49 AM
>
> > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > Another question:  should SQL BackTrack be included for consideration?
> >
> > What does SQL BackTrack to that RMAN doesn't do?
> >
> > Jared




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

2003-01-13 Thread Deshpande, Kirti 
Ethan,

Here is one my 'dist' scripts.
This sends files from HP Server to Sun Servers.
Check that last couple of lines.

HTH,

- Kirti 

#
# File Name: dist_all_to_SUN.rdist
#
# This is the distfile for 'rdist' to distribute scripts to SUN servers.
#
# by Kirti 
#

# HOSTS to contain all UNIX servers running Oracle Databases


HOSTS = (is008 sn024 sn050 snprod02)

#
# File names (with full path of repository directory) of the file(s) to distribute 
# from local machine
#

FILES = ( /home/oracle/tools/backups/DIST/db_cloner.ksh
  /home/oracle/tools/backups/DIST/db_cold_backup.ksh)

#
# Send files to target 'production' directories on the remote hosts 
#
hosts: ${FILES} -> ${HOSTS}
   install /vz/prod_scripts; 

#-- End of file

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


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

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

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

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

My rdist config file looks like this...

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

Here is what I run...

rdist -f rdist.txt -y -R

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

Thanks ahead of time.

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

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



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

2003-01-13 Thread Deshpande, Kirti 
I think SQLBackTrack can get the table data back from the backup file without the need 
to create a partial  database on another server and then export the table from it.

I remember seeing a demo where a dropped table was recovered by the BMC rep. 

- Kirti 

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


RMAN Does everything that SQL Backtrack does, for free. :-)
In fact, last I heard, BMC was planning on altering SQL Backtrack so that it

is really nothing more than a nice fancy front end to RMAN. When I was
at CSX we moved away from SQL Backtrack to RMAN and never had any regrets.
The only issue with SQL Backtrack vs. RMAN was support for 7.x databases.
RMAN does not support anything < 8.0.

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



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



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

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

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

Jared

On Monday 13 January 2003 12:38, Tim Gorman wrote:
> Don't know really.  Just thought that it should probably be included, then
> I was hoping to find out...  :-)
>
> - Original Message -
> To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 10:49 AM
>
> > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > Another question:  should SQL BackTrack be included for consideration?
> >
> > What does SQL BackTrack to that RMAN doesn't do?
> >
> > Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

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

Oracle 9i release 3 coming?

2003-01-13 Thread Boivin, Patrice J 
Title: Internet file system



Oracle 
started posting items with the 9.0.3. number, does this imply they plan to 
release a 9i Release 3?
 
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]

RE: RE : RMAN Repository

2003-01-13 Thread Johnston, Tim 
Title: RE: RE : RMAN Repository



I do 
have one scenario where you really have to use a recovery catalog...  When 
attempting to backup a VLDB that uses read only tablespaces and extremely 
infrequent backups for most of the data (i.e. a large datawarehouse), a recovery 
catalog is very important...  You just may not have the time to perform 
full backups of your multi terabyte database...  In this case, you better 
hope that the backup of your read only tablespace from 15 months ago is still 
around...  Since the CONTROL_FILE_RECORD_KEEP parameter only goes to 1 
year, you may be stuck unless you have that data in a recovery 
catalog...
 
Tim

  -Original Message-From: Freeman Robert - IL 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 09, 2003 12:35 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  RE : RMAN Repository
  
  The only things you can't do with 
  controlfile RMAN/database metadata is: 
  1) use previous "incarnations" of the 
  database for recovery; 
  Actually, you can, it's just a manual process. This 
  is documented in the RMAN book. Also, in 9i you can do automated 
  backup/restore of control files of your database in RMAN without the recovery 
  catalog, and you can manually recover a control file if that is required using 
  dbms_backup_restore.
   
  Robert
  Robert G. FreemanTechnical 
  Management ConsultantTUSC - The Oracle Experts 
  www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of 
  several books you can find on Amazon.com! 
  
-Original Message-From: Orr, Steve 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 09, 2003 10:46 
AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
RE : RMAN Repository
If you aren't using a repository all you have to do is make 
sure control file backups are part of the routine. There are 2 ways to 
backup the backup metadata: 1) the RMAN repository database; 2) backup 
controlfiles. 
Functionally and operationally they're pretty much the same. 
The only things you can't do with controlfile RMAN/database metadata is: 1) 
use previous "incarnations" of the database for recovery; 2) use database 
stored scripts. No big deal as far as I'm concerned.
When RMAN first came out a separate repository database was 
a requirement. Subsequent releases added some functionality for using 
controlfiles. The vulnerability of losing the repository or losing the 
backup controlfile is about equivalent. The overhead of the repository 
database is more. With the initial releases of RMAN (EBU) Oracle was rightly 
criticized for the fact that you had to backup the database that holds 
information about the database you want to backup. Getting rid of this 
silliness seems reasonable to me.
Steve Orr-man for RMAN, Bozeman, 
Montana 
-Original Message- From: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, January 08, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Subject: RE : RMAN Repository Importance: 
High 
And how does one go about restoring a database when all 
control files are lost, and the only recovery data 
is stored in the control file? 
This doesn't sound very reasonable. 
Jared 
"Deshpande, Kirti" 
<[EMAIL PROTECTED]> Sent by: 
[EMAIL PROTECTED]  01/08/2003 11:44 AM 
 Please respond to ORACLE-L       
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>     cc:     
Subject:    RE : RMAN 
Repository 
Joe,  That's what I have heard 
(from 2 Oracle University Professors/Lecturers/Demonstrators). But no one would tell me when it 
may happen. We do not use RMAN (yet) so I did not 
pursue it further. 
- Kirti 
-Original Message- Sent: 
Wednesday, January 08, 2003 1:08 PM To: Multiple 
recipients of list ORACLE-L 
 
Obilgatory oracle statement/question:  rumor has it by 
some instructors that RMAN repository is going away 
and only control file recoveries will be possible, 
truth or fiction? 
joe

Re: Question On High Parse to execute ratio

2003-01-13 Thread Jay 
Thank you all for your help.
I will have to review the metalink data as you suggested.
We have asked that they Bind their SQL Statement but where given no patch
date so it looks like we are out in the cold for awhile.

Once again thanks for all your help.
Jay

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


> Jay - Here are the possibilities:
>1. The vendor is stupid. Or doesn't understand Oracle.
>2. The product was incorrectly installed. Some products are
sophisticated
> enough to have a configurable interface, which may be misconfigured or
need
> tuning over time.
>3. You are missing a key piece of knowledge.
> My point is, learn what you can about alternatives 2 and 3. Read the
vendor
> documentation on the Oracle interface. Place a support call with the
vendor.
> Try to locate a knowledgeable resource at the vendor. Find a support list.
> But if that fails, then you may be forced to fall back on an Oracle-only
> solution. CURSOR_SHARING is a high-risk solution that I would try after
> exhausting the simpler solutions.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 2:00 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello:
>
> I have a third party application which seems to have alot of unbound SQL
> statements, several of which do not seem to be very effective queries.
> Unfortunatelly the code is locked.
>
> My Parse to execute ratio is around 72% and I was wondering if anyone has
> any ideas on how I can decrease this?
>
> I figure that I might start with using the parameter CURSOR_SHARING.
>
> Any ideas would be great.
>
> Thanks in advance,
> Jay
>
>
> _
> Help STOP SPAM: Try the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jay Wade
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay
  INET: [EMAIL PROTECTED]

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

Re: Question On High Parse to execute ratio

2003-01-13 Thread Mogens Nørgaard 
And/or get Bjorn Engsigs article (I think it's on MiracleAS.dk under 
Technical Information - called cursor.pdf) about some interesting 
results regarding cursor sharing.

Mogens

Cary Millsap wrote:

CURSOR_SHARING won't reduce the "parse count (total)" count; only
eliminating parse calls from your application can do that. Using
CURSOR_SHARING will reduce the "parse count (hard)" count (and maybe
some shared pool latch contention), but at the expense of more user-mode
CPU consumed during each parse.

CURSOR_SHARING is an okay workaround for the interim time while you're
fixing an application, but to really fix a parsing problem in a
permanent way (if you have a parsing problem that needs fixing) requires
changing the application--either by changing its source code, or by
intercepting its db parse calls and changing their behavior. Hello,
Anjo?

For more information, hit asktom.oracle.com.


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

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


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

Hello:

I have a third party application which seems to have alot of unbound SQL

statements, several of which do not seem to be very effective queries.  
Unfortunatelly the code is locked.

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

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

Any ideas would be great.

Thanks in advance,
Jay


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

 



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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[2]: newbie question - still: please help

2003-01-13 Thread Mark Richard 
I think Dick is on the right track...

If you have a list of accounts which require an update, load that list into
the database - if it isn't already in the database (please don't tell me
you are creating the script from within Oracle - please don't tell me
that).  You could load this list either as a pile of inserts (barely better
than the original solution, although at least the critical update command
will run fast) or SQL*Load them.

As Dick said, you can then do a single update to the main table.

As far as performance goes...  If you are updating a significant proportion
of the ISIS_DOCAR table then an index won't be likely to benefit (a single
FTS would be used).  On the other hand, if only a smallish subset of
ISIS_DOCAR is being updated than an index on the ID column could be useful.

Cheers,
 Mark.




   

[EMAIL PROTECTED] 

om   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Sent by: cc:   

[EMAIL PROTECTED]   Subject: Re[2]: newbie question - still: 
please help  
om 

   

   

14/01/2003 

06:31  

Please respond 

to ORACLE-L

   

   





Daniel,

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

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

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

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

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

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

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

Daniel,

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

Longer response:  This is a common argument that eventually distills down
to
something like "I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, Java."  What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into pro

Re: Daramtically improve BCHR with a single statement

2003-01-13 Thread Mogens Nørgaard 




Time to check what? If it goes down it could be because your statement(s)
were optimized to use less resources (as documented so well). If it goes
up it could be because your statements became more or less efficient. And
you'd have to check if it was because of an index, a table, a sql statement,
something happening in one of the pools, an optimisation, a change of parameters
and who cares, as long as the end user response time is where it should be?
Time would seem to be a better unit of measurement than number of this or
number of that?!

Mogens

Yechiel Adar wrote:

  Hello All

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

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

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


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

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

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

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

  
  at
  
  
full tilt with no direction.
Also, I have realized that cars have been around for > 100 years now, so
why exactly would I want to sprint again ? :-)

Check out this example:

run any number of scripts to look at BCHR. Then run the following

  
  anonymous
  
  
PL/SQL block:

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

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

Another advantage to the above code is that it eliminates idle capacity
from my CPU's (I paid for the thing, it should be put to work, right ? )

  
  as
  
  
my laptop has been at 100% CPU utilization for the last 8 minutes as I let
this piece of crap run before I killed it (Oracle 9 on XP with 512 MB RAM
[SGA 120 MB], with a bunch of other starved stuff running concurrently).

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

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

Regards :

Ferenc Mantfeld

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

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


--- Jared Still <[EMAIL PROTECTED]> wrote:


  On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
  
  
Obviously, we don't know what we're talking about. I can see

  
  there's a
  
  
presentation by Rich Niemich at IOUG-A where he'll address all

  
  those
  
  
idiots who are saying you should ignore the Cash Hit Ratio (and who

  
  are
  
  
all just after making big money on their products - I loved that

  
  one).
  
  

  Or modify the set up of these tools to take action when BCHR
  

  
  falls..
  
  Here's the session info:

Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
Venue: Southern Hemisphere 2, Walt Disney World
   Dolphin, Lake Buena Vista, FL

Abstract: Lately, there has been a big push to ignore your
hit ratio with claims that it is meaningless. This shallow
minded view (usually by people who sell a tuning tool) ignores
why people look at hit ratios and what they are looking for.
This quick tip talk will show you what to look for and why.
You will definitely know when, where & why to look at your
hit ratio in the future.

Show you why your hit ratio matters. How to analyze the
hit ratio. Fallacies by those who want to sell you products
and tools instead.


Shallow Minded ?!

Jared
--
Please see the official

Re: BCHR Tuning

2003-01-13 Thread Mogens Nørgaard 




Something here doesn't compute. If you tried to use time-based measurements
and didn't find out where the time went - well, bad for you. If you then
stumbled on something, say the database startup/database shutdown ratio (would
normally be fairly close to 1, but could vary) or the log file switch/archive
log file ratio (again, could be close to 1 or 100% or something - or could
vary) or the ratio of blocks from a certain index found in the permanent
pool versus the number of PIO's required for that statement - or whatever
- it would still be guesswork, checklist tuning, or what you'd prefer to
call it.

All sorts of measures have their place. All sorts of measures could prove
interesting. When I went to school the famous example was the wolf population
of Canada which seemed to follow the birthrate of children in Denmark. Or
the length of skirts versus economic prosperity in the Western world, which
also proved rather closely matched.

If you want to measure response time (what else?) it just might be of interest
to find out where the time is spent.

The BCHR, the x/y, the DBStarup/DBShutdown ratio or other ratios or measurements
might be important to find out symptoms of things - but to say that that
kind of guesswork still has it's place is like saying that we should still
carefully watch the wolf population of Canada or the skirt length in the
Western World...because you never know.

And that just might be the case: You never (will) know until you adopt an
approach that is hierachical (spelling?) and which you can use to prioritize
and quantify your efforts (try that with the BCHR - the x$kcbrbh, etc. of
course are grossly wrong in those respects).

Yep, I've been there, I've used it all, I've tried to use all the notes and
articles regarding the wonderful statistics available in bstat/estat - I've
been through the stages of collecting more and more queries and numbers and
ratios until my file with scripts and queries was bigger than Holland. Yet
it never gave me solutions, just a lot of things to check and change and
fiddle with - without knowing which one to choose first, and how much it
would help.

The YAPP method works. There are cases where it is not 100% accurate. In
most cases it's spot on. Watch where the monitoring tools are going. Spotlight
in the latest edition have the YAPP method built in. Let's see what Oracle
does in 10i. Precise has it. Steve Adams' scripts has it. 

This is not about the BCHR being low or high or in between. This is about
using a METHOD instead of 100s of different numbers that don't mean anything.

Mogens

[EMAIL PROTECTED] wrote:

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

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

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

Raj






"Yechiel
Adar"To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
 Subject: Re: BCHR Tuning   
Sent by:
root@fatcity.   
com 


January 13,

Parallel Replication of Single Table

2003-01-13 Thread Larry Elkins 
Listers,

How can one use parallelism when fast refreshing a *single* table?

Pulling my hair out on this one. We want to use parallelism when replicating
updates/inserts from a single partitioned table between DB's -- partitioned
on both DB's. Both are 8.1.7.4 Solaris 8 64 bit. Using fast refresh and
primary key method. Have the MV in the target, have the source along with
the associated MLOG$. The job queues, parallel parameters, etc are all way
up there. We manually pull on occasion when doing restructuring or mass mods
and have no problems getting parallelism on each side. But we have to
initiate from the target side as a pull to get that. Have set degree 8 on
MLOG$, source, and target.

Specifically, when I crank up the refresh, with only updates, the update on
the target serializes. Info in the docs is a little confusing. Much is made
of partitioning a table in the DW guide to allow parallelism of the refresh.
But it is unclear if they are talking an MV based on an object in the same
DB, or, based on a remote object, or both. In the distributed manual it
makes a comment about DML serializing when doing remote operations. Now
someone looked into this a while back and opened a TAR to get an
explanation -- the analyst said to simply use the parallelism parameter of
the DBMS_SNAPSHOT.REFRESH. Well this didn't do it. Then he comes back and
says it is possible to replicate a single table using parallelism if you use
advanced replication (or maybe we read that somewhere). Guess I'll create a
second 8.1.7 DB on my home machine and give that a go.

But I'm probably missing something obvious here. So it's back to a simple
question -- how can one use parallelism to refresh a single table? We can
write our own routines to do this, but I would rather use native
capabilities as opposed to re-inventing the wheel. Feeling pretty stupid
here. Oh well, time to setup and test advanced replication.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

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

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

RE: Different Backups - A Comparartive analysis

2003-01-13 Thread Freeman Robert - IL 
SQL BackTrack is a BMC product.

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



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



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

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


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

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


> On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > Another question:  should SQL BackTrack be included for consideration?
>
> What does SQL BackTrack to that RMAN doesn't do?
>
> Jared

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

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

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

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

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

Replicating Replicated Data.

2003-01-13 Thread MacGregor, Ian A. 
Some of our data is stored in Oracle databases dedicated to Peoplesoft.  We have other 
databases which require a subset of this information.  These other databases are not 
all on the same machine.  They are either directly tied to physics experiments or they 
are subject to certification requirements  from other third-party vendors.  The 
separation is to prevent requirements specific to one database from affecting the 
upgrade of others.

Security's dream would be to have none of these databases communicate with our 
Peoplesoft databases, but they realize that won't fly.  They have vouchsafed that one 
such server be capable.  All other servers would  get their data from this sainted 
one.  If I use materialized views to replicate the data from Peoplesoft to the 
venerated server, any further use of matrialized views from that server to the others 
requires complete refreshes.   It would seem the best course to use something other 
than matrialized views to get the data out of Peoplesoft.   This method would use 
homegrown log tables so only the changed data would be replicated.  I have done this 
to overcome the inability of Oracle to handle longs in materialized views.  

I wonder however if I am overlooking something?  Why does Oracle have  the complete 
refresh restriction in the first place?


Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED] 


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

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

Re: Different Backups - A Comparartive analysis

2003-01-13 Thread Jared Still 

Yes, but I think less capable than RMAN.

Just do a google search for it.  Owned by BMC I believe.

Jared

On Monday 13 January 2003 14:34, VIVEK_SHARMA wrote:
> Is SQL BackTrack a product like RMAN (from Oracle Corp. or 3rd party )?
>
>
> -Original Message-
> Sent: Tuesday, January 14, 2003 2:09 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Don't know really.  Just thought that it should probably be included, then
> I was hoping to find out...  :-)
>
> - Original Message -
> To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 10:49 AM
>
> > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > Another question:  should SQL BackTrack be included for consideration?
> >
> > What does SQL BackTrack to that RMAN doesn't do?
> >
> > Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

RE: Different Backups - A Comparative analysis

2003-01-13 Thread Freeman Robert - IL 
RMAN Does everything that SQL Backtrack does, for free. :-)
In fact, last I heard, BMC was planning on altering SQL Backtrack so that it

is really nothing more than a nice fancy front end to RMAN. When I was
at CSX we moved away from SQL Backtrack to RMAN and never had any regrets.
The only issue with SQL Backtrack vs. RMAN was support for 7.x databases.
RMAN does not support anything < 8.0.

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



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



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

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

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

Jared

On Monday 13 January 2003 12:38, Tim Gorman wrote:
> Don't know really.  Just thought that it should probably be included, then
> I was hoping to find out...  :-)
>
> - Original Message -
> To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 10:49 AM
>
> > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > Another question:  should SQL BackTrack be included for consideration?
> >
> > What does SQL BackTrack to that RMAN doesn't do?
> >
> > Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

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

2003-01-13 Thread John Kanagaraj 
Hi Govind,
 
Just a thought: The value of _small_table_threshold - which is currently 2%
of the DB_BLOCK_BUFFERS. Any table undergoing FTS will be placed at the MRU
end rather than the LRU end and would thus live longer (and either cause
problems or alleviate it as the case may be!). Keep this in mind if you are
looking at FTS...
 
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end
of your journey in this earth?

** The opinions and statements above are entirely my own and not those of my
employer or clients **


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


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

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



Govind, 

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

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





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


List, 

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

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

Thanks, 
Govind 

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

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

col object_name form a30 
col owner form a10 

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

spool recentfulltablescan.lst 

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

spool off 




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

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

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

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

RE: Question On High Parse to execute ratio

2003-01-13 Thread Cary Millsap 
CURSOR_SHARING won't reduce the "parse count (total)" count; only
eliminating parse calls from your application can do that. Using
CURSOR_SHARING will reduce the "parse count (hard)" count (and maybe
some shared pool latch contention), but at the expense of more user-mode
CPU consumed during each parse.

CURSOR_SHARING is an okay workaround for the interim time while you're
fixing an application, but to really fix a parsing problem in a
permanent way (if you have a parsing problem that needs fixing) requires
changing the application--either by changing its source code, or by
intercepting its db parse calls and changing their behavior. Hello,
Anjo?

For more information, hit asktom.oracle.com.


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

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


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

Hello:

I have a third party application which seems to have alot of unbound SQL

statements, several of which do not seem to be very effective queries.  
Unfortunatelly the code is locked.

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

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

Any ideas would be great.

Thanks in advance,
Jay


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

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

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

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

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

RE: #of blocks in extent

2003-01-13 Thread Miller, Jay 
And of course to calculate you'll need to know your block size and your
extent size.

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


UNIFORM SIZE clause of Tablespace, if it is LMT.

--- Igor Neyman <[EMAIL PROTECTED]> wrote:
> INITIAL, NEXT, PCTINCREASE  -- if it's not LMT
> 
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
> 
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Friday, January 10, 2003 2:54 PM
> 
> 
> > How many blocks are allocated to an extend . what
> parameter decides that .
> > Is it some storage param ?
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> > --
> > Author: BigP
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> > San Diego, California-- Mailing list and
> web hosting services
> >
>
-
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from).  You may
> > also send the HELP command for other information
> (like subscribing).
> >
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Igor Neyman
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 


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

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Amar Kumar Padhi
  INET: [EMAIL PROTECTED]

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

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

perl timeout

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

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

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

RE: Question On High Parse to execute ratio

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

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

GL!

Rich


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


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

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

RE: Question On High Parse to execute ratio

2003-01-13 Thread Karen Morton 
Jay,

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

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

Karen

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


Hello:

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

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

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

Any ideas would be great.

Thanks in advance,
Jay


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

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

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

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

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

2 basic Qs. on export

2003-01-13 Thread VIVEK_SHARMA 

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

FILE=

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


2) With Oracle  8i & 9i 

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

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


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

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

Re: index rebuilding performance vs sort_area_size

2003-01-13 Thread Jared Still 

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

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

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

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

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

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

Paging can be monitored vi vmstat on Solaris.

HTH

Jared



On Monday 13 January 2003 12:00, Guang Mei wrote:
> Hi:
>
> Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to
> test index rebuild (with nologging) performance vs sort_area_size. I used
> "alter session set sort_area_size = " to set the sort_area_size value.
> Nothing else was changed. The temp tablespace is 8G. There is no other
> active sessions running during the test. I selected two indexes for the
> test. Their sizes are about 20M and 115M respectively so that they were fit
> into their initial extent after the rebuild.
>
> Here is the result:
>
> -- 1. rebuild an index with size of about 20M:
> alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next
> 20M);
>
> sort_area_size  20971520
> Elapsed:00:00:12.49   00:00:11.6800:00:12.18
>
> sort_area_size  80971520
> Elapsed:00:00:09.9500:00:09.94  00:00:09.54
>
> -- 2. rebuild an index with size of  about 115M:
> alter index mt.TOPIC_INDEX rebuild nologging
> STORAGE (INITIAL 114688000  next 114688000);
>
> sort_area_size  20971520
> Elapsed:00:00:51.06  00:00:50.4400:00:51.46
>
> sort_area_size  80971520
> Elapsed:00:00:52.17  00:00:51.6500:00:51.75
>
> sort_area_size  150971520
> Elapsed:   00:00:42.42  00:00:41.81 00:00:41.71
>
> So with this very limited data points, I found
>
> 1. In the 1st example, the sort_area_size was increased almost 4 times, but
> we only got about 20% performance improvement.
>
> 2. In the 2nd example, we got 20% performance boost when sort_area_size was
> increased from 21M to 151M.
>
> Is what I see here typical? It seems that with the increase of
> sort_area_size, the index rebuild will be faster, but not as fast as I
> hoped. Any comments?
>
> Guang Mei
>
> _
> The new MSN 8 is here: Try it free* for 2 months
> http://join.msn.com/?page=dept/dialup
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

RE: ioug.org web site

2003-01-13 Thread Ari Kaplan 
I spoke with Matt and he is able to register / log in. As it usually
happens, he got it working right after he sent the email.

Take care,

-Ari Kaplan

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


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

Check with your network folks.

Stephen

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

Is it just me?


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

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

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


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

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

Was 8.1.6 certified on Solaris 8?

2003-01-13 Thread Miller, Jay 
We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of
our boxes.

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

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

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


TIA,
Jay Miller

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

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

RE: Minimum required init.ora parameters

2003-01-13 Thread Jesse, Rich 
Curious, for which platform is 8.1.8 available?  And *why*?

Rich


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

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

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

Re: Different Backups - A Comparartive analysis

2003-01-13 Thread Jared Still 

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

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

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

Jared

On Monday 13 January 2003 12:38, Tim Gorman wrote:
> Don't know really.  Just thought that it should probably be included, then
> I was hoping to find out...  :-)
>
> - Original Message -
> To: <[EMAIL PROTECTED]>; "Tim Gorman" <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 10:49 AM
>
> > On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > > Another question:  should SQL BackTrack be included for consideration?
> >
> > What does SQL BackTrack to that RMAN doesn't do?
> >
> > Jared
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

rdist Examples

2003-01-13 Thread Post, Ethan 
rdist is a good way to keep files (sql, tools, tnsnames) synchronized
between servers, in my case I need to replicate 

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

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

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

My rdist config file looks like this...

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

Here is what I run...

rdist -f rdist.txt -y -R

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

Thanks ahead of time.

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

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

RE: Different Backups - A Comparartive analysis

2003-01-13 Thread VIVEK_SHARMA 

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

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


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

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


> On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > Another question:  should SQL BackTrack be included for consideration?
>
> What does SQL BackTrack to that RMAN doesn't do?
>
> Jared

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

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

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

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

RE: How to identify full table scans?

2003-01-13 Thread Govind.Arumugam 
Title: RE: How to identify full table scans?



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

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 2003 
  2:22 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: How to identify full table scans?
  Govind, 
  Just curious why you are attacking the full table scans.  
  I implemented something like this in the past utilizing Steve Adams' script 
  expensive_sql.sql.  It was very telling and very very useful. 
  Lisa Koivu Oracle Database 
  Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, 
  USA  33063 
  -Original Message- From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
  Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: How to identify full table scans? 
  List, 
  We use the following script to identify recent full table 
  scans or full index scans.  This result set will be used to identify the 
  potential queries that could benefit by creating any new indexes or modify the 
  existing index structure as needed.
  Our intention is to run this query against X$BH every hour and 
  gather this data.  Do you have any suggestions or scripts to accomplish 
  the same?  Are there any issues in trying to do this every hour?  
  
  Thanks, Govind 
  /* Recent full table scan */ /* Should 
  be run as user SYS */ 
  set serverout on size 100 set 
  verify off set pagesiz 300 set 
  lin 120 
  col object_name form a30 col owner 
  form a10 
  PROMPT Column flag in x$bh table is set to value 0x8, 
  when PROMPT block was read by a sequential 
  scan. 
  spool recentfulltablescan.lst 
  SELECT count(o.object_name) "COUNT", o.object_name, 
  o.object_type, o.owner, t.num_rows FROM dba_objects 
  o,x$bh x, dba_tables t WHERE x.obj=o.object_id 
  and o.object_name=t.table_name -- AND 
  o.object_type='TABLE' AND 
  standard.bitand(x.flag,524288)>0 AND 
  o.owner<>'SYS' group by o.object_name, 
  o.object_type, o.owner, t.num_rows order by 1 ; 
  
  spool off 
  -- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.net -- Author: 
  <[EMAIL PROTECTED]   INET: 
  [EMAIL PROTECTED] 
  Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
  San Diego, 
  California    -- Mailing list and web 
  hosting services - 
  To REMOVE yourself from this mailing list, send an E-Mail 
  message to: [EMAIL PROTECTED] (note EXACT spelling 
  of 'ListGuru') and in the message BODY, include a line 
  containing: UNSUB ORACLE-L (or the name of mailing 
  list you want to be removed from).  You may also 
  send the HELP command for other information (like subscribing).

RE: Conversion from CLOB to RAW

2003-01-13 Thread DENNIS WILLIAMS 
Bill - Will you need to search for information that will be compressed? If
yes, then performance could be really, really bad.

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


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


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

Regards,

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



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


Bill,

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

HTH.

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


> The amount of data being stored is fairly significant @60G of xml
> transaction data.  The developers want to apply a compression routine to
the
> xml string which will save about 70% of the space currently in use.  BLOB
> was my original recommendation, they were pushing to go RAW instead.
We've
> only got a couple of CLOB's out there, but they are taking up huge amounts
> of storage.  On the overkill note, most all of the XML has been parsed to
> less than 2K in length so one of my thoughts was we had introduced LOB
> functionality without really needing it.
>
> The other aside to this is we will definately need to partition the data
> when we do the conversion as it currently resides in a traditional table
as
> a CLOB.
>
> Regards,
>
> Bill Burke
> "The Kinder and Gentler DBA"
> IOUG University Master Class Faculty 2001-2002
> "iDBA Management, High Performance Infrastructure and HA"
> IOUG Board of Directors 2000-2002
> ODTUG Board of Directors 1996-2000
> www.OracleGuru.com
> www.KBMotorsports.biz
>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 10, 2003 4:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Bill,
>
> I agree with Michael.  You've already got the data in a "suitable
> datatype".  Why move it to a cumbersome, soon-to-be-obsolete datatype?
You
> can use DBMS_LOB functionality on LOBs, not on Raw.
>
> I'd be so happy if the couple dozen tables in our 3rd party Student
> Information system that have Long or Long Raw columns had CLOB or BLOB
> columns instead.  It would make converting them to partitioned tables much
> easier.
>
> I definitely vote to keep your CLOBs.
>
> Jack C. Applewhite
> Database Administrator
> Austin Independent School District
> Austin, Texas
> 512.414.9715 (wk)
> 512.935.5929 (pager)
> [EMAIL PROTECTED]
>
>
>
>
>
>   Michael Fontana
>
>   <[EMAIL PROTECTED]To:   Multiple
recipients
> of list ORACLE-L
>   et>   <[EMAIL PROTECTED]>
>
>   Sent by: cc:
>
>   [EMAIL PROTECTED] Subject:  Re: Conversion
from
> CLOB to RAW
>
>
>
>
>   01/10/2003 02:15
>
>   PM
>
>   Please respond to
>
>   ORACLE-L
>
>
>
>
>
>
>
>
>
> At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote:
>   Here's where I get to ask the most likely simple question.
>
>   I've inherited a database where it was built using a CLOB to hold
XML
>   data
>   but we have now determined that was total overkill and want to move
>   it to a
>   RAW column or other suitable datatype.
>
>   Looking for conversion issues or other alternatives.
>
>
> Since Oracle is moving us away from LONG and RAW datatypes, I assume
> you want to convert from CLOB to BLOB?  BLOB is probably more
> storage-efficient,
> but since XML is made up of character data, I don't really understand the
> issue with
> keeping it a CLOB.
>
>  What do you mean by overkill?
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

Re: Question On High Parse to execute ratio

2003-01-13 Thread Rajesh . Rao 

My colleagues call it CURSER_SHARING :)))




   
  
"Igor Neyman"  
  

ptron.com>cc:  
  
Sent by:  Subject: Re: Question On High Parse to 
execute ratio   
[EMAIL PROTECTED] 
  
om 
  
   
  
   
  
January 13,
  
2003 03:38 PM  
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




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

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



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


> Hello:
>
> I have a third party application which seems to have alot of unbound SQL
> statements, several of which do not seem to be very effective queries.
> Unfortunatelly the code is locked.
>
> My Parse to execute ratio is around 72% and I was wondering if anyone has
> any ideas on how I can decrease this?
>
> I figure that I might start with using the parameter CURSOR_SHARING.
>
> Any ideas would be great.
>
> Thanks in advance,
> Jay


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

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

Re: ioug.org web site

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


> Is anybody else having trouble with the www.ioug.org
> web site.  I can get to the home page, but cannot
> log in, or register for IOUG-A Live.
>
> Is it just me?
>
> 
> Matt Adams - GE Appliances - [EMAIL PROTECTED]
> My computer beat me at chess, but I won
> when it came to kick boxing.
>

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

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

RE: index rebuilding performance vs sort_area_size

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

HTH.

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


Hi:

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

Here is the result:

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

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

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

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

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

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

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

So with this very limited data points, I found

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

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

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

Guang Mei

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

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

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

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

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

Re: removing duplicate entries from a table

2003-01-13 Thread Tim Gorman 
Mark,

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

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

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

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

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

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

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

Be careful!  Hope this helps...

-Tim

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


> Hi List
>
> Is there a tool for removing duplicate entries from a table - except that
> the key field is not the same. I ran some data imports more than once and
> was hoping someone could point me in the direction of a way to undo my
> mistake.
>
> Any help will be most appreciated
>
> Thanks
> Mark Warner
>
> __
> "The information contained in this communication is confidential and
> may be legally privileged.  It is intended solely for the use of the
> individual or entity to whom it is addressed and others authorised to
> receive it.  If you are not the intended recipient you are hereby
> notified that any disclosure, copying, distribution or taking action
> in reliance of the contents of this information is strictly prohibited
> and may be unlawful.  Absa is liable neither for the proper, complete
> transmission of the information contained in this communication, nor
> for any delay in its receipt, nor for the assurance that it is
> virus-free."
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Warner
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

Re: Different Backups - A Comparartive analysis

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

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


> On Monday 13 January 2003 06:03, Tim Gorman wrote:
> > Another question:  should SQL BackTrack be included for consideration?
>
> What does SQL BackTrack to that RMAN doesn't do?
>
> Jared

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

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

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

2003-01-13 Thread M Rafiq 
Yes, it is as of today...We are still having 7.3.4.5 for our Oracle 
Financials 10.7 Application. This may be the last year for this version...


Regards
Rafiq








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

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

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


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


Dennis,

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

I think it should be the problem

Regards
Rafiq








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

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

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


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


ALTER ...REBUILD..

Thanks

COnrad..


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


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

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


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



Sorry, I forgot this additional infromation...

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



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



Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

RE: Question On High Parse to execute ratio

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

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


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


Hello:

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

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

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

Any ideas would be great.

Thanks in advance,
Jay


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

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

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

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

Re: ioug.org web site

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

Check with your network folks.

Stephen

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

Is it just me?


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

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

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

Re: Question On High Parse to execute ratio

2003-01-13 Thread Igor Neyman 
Success with using CURSOR_SHARING depends on your Oracle version.
In earlier versions it's buggy.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



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


> Hello:
>
> I have a third party application which seems to have alot of unbound SQL
> statements, several of which do not seem to be very effective queries.
> Unfortunatelly the code is locked.
>
> My Parse to execute ratio is around 72% and I was wondering if anyone has
> any ideas on how I can decrease this?
>
> I figure that I might start with using the parameter CURSOR_SHARING.
>
> Any ideas would be great.
>
> Thanks in advance,
> Jay
>
>
> _
> Help STOP SPAM: Try the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jay Wade
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

RE: compatible in 9iRel2

2003-01-13 Thread DENNIS WILLIAMS 
David - No. This just ensures you don't use any "9i" only features.



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

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



I was curious about this parameter

I have an application that is certified on 8.1.7 only

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

 

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

 

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

David Ehresmann.

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

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


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

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


David - What are you trying to accomplish?

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


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


List, 

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

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


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

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

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

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

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



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

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

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

2003-01-13 Thread DENNIS WILLIAMS 
Thanks Rafiq. As I said, my 7.3.4 knowledge is too old by now (but man, was
that a great version). 

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


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


Dennis,

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

I think it should be the problem

Regards
Rafiq








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

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

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


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


ALTER ...REBUILD..

Thanks

COnrad..


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


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

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


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



Sorry, I forgot this additional infromation...

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



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



Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

RE: Conversion from CLOB to RAW

2003-01-13 Thread Burke, William F (Bill) 
I wasn't.  I actually am still trying to look at the varchar2.  The downside
is the huge amount of data they want to keep in a compressed format hence
the RAW or BLOB format.

Regards,

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



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


Bill,

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

HTH.

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


> The amount of data being stored is fairly significant @60G of xml
> transaction data.  The developers want to apply a compression routine to
the
> xml string which will save about 70% of the space currently in use.  BLOB
> was my original recommendation, they were pushing to go RAW instead.
We've
> only got a couple of CLOB's out there, but they are taking up huge amounts
> of storage.  On the overkill note, most all of the XML has been parsed to
> less than 2K in length so one of my thoughts was we had introduced LOB
> functionality without really needing it.
>
> The other aside to this is we will definately need to partition the data
> when we do the conversion as it currently resides in a traditional table
as
> a CLOB.
>
> Regards,
>
> Bill Burke
> "The Kinder and Gentler DBA"
> IOUG University Master Class Faculty 2001-2002
> "iDBA Management, High Performance Infrastructure and HA"
> IOUG Board of Directors 2000-2002
> ODTUG Board of Directors 1996-2000
> www.OracleGuru.com
> www.KBMotorsports.biz
>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 10, 2003 4:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Bill,
>
> I agree with Michael.  You've already got the data in a "suitable
> datatype".  Why move it to a cumbersome, soon-to-be-obsolete datatype?
You
> can use DBMS_LOB functionality on LOBs, not on Raw.
>
> I'd be so happy if the couple dozen tables in our 3rd party Student
> Information system that have Long or Long Raw columns had CLOB or BLOB
> columns instead.  It would make converting them to partitioned tables much
> easier.
>
> I definitely vote to keep your CLOBs.
>
> Jack C. Applewhite
> Database Administrator
> Austin Independent School District
> Austin, Texas
> 512.414.9715 (wk)
> 512.935.5929 (pager)
> [EMAIL PROTECTED]
>
>
>
>
>
>   Michael Fontana
>
>   <[EMAIL PROTECTED]To:   Multiple
recipients
> of list ORACLE-L
>   et>   <[EMAIL PROTECTED]>
>
>   Sent by: cc:
>
>   [EMAIL PROTECTED] Subject:  Re: Conversion
from
> CLOB to RAW
>
>
>
>
>   01/10/2003 02:15
>
>   PM
>
>   Please respond to
>
>   ORACLE-L
>
>
>
>
>
>
>
>
>
> At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote:
>   Here's where I get to ask the most likely simple question.
>
>   I've inherited a database where it was built using a CLOB to hold
XML
>   data
>   but we have now determined that was total overkill and want to move
>   it to a
>   RAW column or other suitable datatype.
>
>   Looking for conversion issues or other alternatives.
>
>
> Since Oracle is moving us away from LONG and RAW datatypes, I assume
> you want to convert from CLOB to BLOB?  BLOB is probably more
> storage-efficient,
> but since XML is made up of character data, I don't really understand the
> issue with
> keeping it a CLOB.
>
>  What do you mean by overkill?
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Burke, William F (Bill)
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -

index rebuilding performance vs sort_area_size

2003-01-13 Thread Guang Mei 
Hi:

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

Here is the result:

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

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

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

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

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

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

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

So with this very limited data points, I found

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

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

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

Guang Mei

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

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

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

Question On High Parse to execute ratio

2003-01-13 Thread Jay Wade 
Hello:

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

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

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

Any ideas would be great.

Thanks in advance,
Jay


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

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

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

ioug.org web site

2003-01-13 Thread Adams, Matthew (GECP, MABG, 088130) 
Title: ioug.org web site





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


Is it just me?



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

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

2003-01-13 Thread dgoulet 
Daniel,

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

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

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

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

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

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

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

Daniel,

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

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

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

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

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

-Tim

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


> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Wisser
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


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

Fat City Network Services-- 858-538-5051

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

2003-01-13 Thread M Rafiq 
Dennis,

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

I think it should be the problem

Regards
Rafiq








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

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

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


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


ALTER ...REBUILD..

Thanks

COnrad..


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


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

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


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



Sorry, I forgot this additional infromation...

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



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



Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

Fat City Network Services-- 858-538-5051 http://www.fat

Re: USER_TABLESPACES has more rows than DBA_TABLESPACES

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

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

-- 
Regards,

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

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

RE: compatible in 9iRel2

2003-01-13 Thread david hill 









I was curious about this parameter

I have an application that is certified on
8.1.7 only

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

 

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

 



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





David Ehresmann.





-Original
Message-
From: John Shaw
[mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003
11:41 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: compatible in 9iRel2



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






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

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


David - What are you trying to accomplish?

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


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


List, 

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

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


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

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

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

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

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

RE: How to identify full table scans?

2003-01-13 Thread Koivu, Lisa 
Title: RE: How to identify full table scans?





Govind, 


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

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






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



List,


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

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

Thanks,
Govind


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


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


col object_name form a30
col owner form a10


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


spool recentfulltablescan.lst


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


spool off





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


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

RE: compatible in 9iRel2

2003-01-13 Thread John Shaw 


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

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
  >>> [EMAIL PROTECTED] 01/13/03 11:16AM 
  >>>I am trying to apply a "fix" for some developers here.  
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript.  I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2.  I 
  tried this morning and got theORA-402 and ORA-405 errors.  I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database.  If you bounce the instance the sql>show 
  parameter compatible=9.0.0  stays the same, it does not change.  
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress    Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up.  I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance.   Is there any way to do this without 
  stripping the 9i databasedown to 8i?  David 
  Ehresmann-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the official 
  ORACLE-L FAQ: http://www.orafaq.net-- 
  Author: DENNIS WILLIAMS  INET: [EMAIL PROTECTED]Fat 
  City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

USER_TABLESPACES has more rows than DBA_TABLESPACES

2003-01-13 Thread Keith Moore 



Has anyone else seen this or can you explain 
it?
 
I have 7 tablespaces in USER_TABLESPACES that don't 
exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear 
from USER_TABLESPACES. They have a status of INVALID.
 
The database is version 8.0.5 (Yeah, I know, we'll 
be going to 9i real soon now)
 
Keith
 
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

How to identify full table scans?

2003-01-13 Thread Govind.Arumugam 
List,

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

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

Thanks,
Govind

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

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

col object_name form a30
col owner form a10

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

spool recentfulltablescan.lst

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

spool off




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

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

RE: compatible in 9iRel2

2003-01-13 Thread Ehresmann, David 



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

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
  >>> [EMAIL PROTECTED] 01/13/03 11:16AM 
  >>>I am trying to apply a "fix" for some developers here.  
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript.  I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2.  I 
  tried this morning and got theORA-402 and ORA-405 errors.  I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database.  If you bounce the instance the sql>show 
  parameter compatible=9.0.0  stays the same, it does not change.  
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress    Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up.  I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance.   Is there any way to do this without 
  stripping the 9i databasedown to 8i?  David 
  Ehresmann-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the official 
  ORACLE-L FAQ: http://www.orafaq.net-- 
  Author: DENNIS WILLIAMS  INET: [EMAIL PROTECTED]Fat 
  City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

RE: compatible in 9iRel2

2003-01-13 Thread Ehresmann, David 



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

  -Original Message-From: John Shaw 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 
  2003 11:41 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: compatible in 9iRel2
  I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
  since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
  never really needed it so I haven't bothered with metalink/tar.
  >>> [EMAIL PROTECTED] 01/13/03 11:16AM 
  >>>I am trying to apply a "fix" for some developers here.  
  They used this fixin 8 and 8i by changing the compatible parameter and 
  running their sqlscript.  I just want to be able to tell them that 
  they need to fix theirapplication now that we are using 9iRel2.  I 
  tried this morning and got theORA-402 and ORA-405 errors.  I don't 
  want to strip away features of 9i justto get their security script to 
  run.thanks,David Ehresmann.-Original Message-Sent: 
  Monday, January 13, 2003 9:55 AMTo: Multiple recipients of list 
  ORACLE-LDavid - What are you trying to accomplish?Dennis 
  WilliamsDBA, 40%OCPLifetouch, Inc.[EMAIL PROTECTED] 
  -Original Message-Sent: Monday, January 13, 2003 8:44 
  AMTo: Multiple recipients of list ORACLE-LList, I 
  recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2 
  Solaris8 database.  If you bounce the instance the sql>show 
  parameter compatible=9.0.0  stays the same, it does not change.  
  Ifyou shutdown and reboot the server you get an:ora-01033: 
  Initialization or shutdown in progress    Which basically 
  statesthat you are trying to connect to an instance that is being shutdown 
  down orstarting up.  I believe it goes into NOMOUNT stage and reads 
  the init.oraand hangs because of the compatible parameter being set to 8i. 
  I saw thedocument Oracle9i Database Migration Release 2 (9.2) Part Number 
  A96530-01stating how to downgrade, but it seems to defeat the purpose of 
  having a 9iinstance.   Is there any way to do this without 
  stripping the 9i databasedown to 8i?  David 
  Ehresmann-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the official 
  ORACLE-L FAQ: http://www.orafaq.net-- 
  Author: DENNIS WILLIAMS  INET: [EMAIL PROTECTED]Fat 
  City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Ehresmann, David  INET: [EMAIL PROTECTED]Fat City 
  Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California    -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

RE: compatible in 9iRel2

2003-01-13 Thread Jamadagni, Rajendra 
Title: RE: compatible in 9iRel2





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


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



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



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



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

RE: Daramtically improve BCHR with a single statement

2003-01-13 Thread Cary Millsap 
:)


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

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


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


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

Jared

On Monday 13 January 2003 08:09, Yechiel Adar wrote:
> Hello All
>
> I saw that piece of code a few times and still think that even if you
can
> bump up the BCHR it has it's place. How can you tell that you need
more
> space in the buffer pool? Bad BCHR is an indication that you need to
check
> this. It is also an indication that you do a lot of FTS or missing an
index
> and you SQL reads too many blocks.
>
> Use the BCHR as an indication. When it goes down it is time to check.
>
> Yechiel Adar
> Mehish
> - Original Message -
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 5:08 AM
>
> > To add an example of what Anjo, Morgens and everyone else is talking
>
> about,
>
> > here is a perfect illustration of why focusing on BCHR is like
> > concentrating intensely on how fast your tyres rotate in a Tour de
> > France, instead of looking of where you are going (probably a lot
more
> > useful). Another example:
> > If I raced (100M sprint) against Maurice Green, and he went off in
the
> > wrong direction, despite the fact that he is so much faster than me
(duh
>
> !)
>
> > , I could lightly jog (as if I have anything else to offer) the 100M
in
>
> the
>
> > right direction and beat him. Well, focusing on BCHR alone is like
going
>
> at
>
> > full tilt with no direction.
> > Also, I have realized that cars have been around for > 100 years
now, so
> > why exactly would I want to sprint again ? :-)
> >
> > Check out this example:
> >
> > run any number of scripts to look at BCHR. Then run the following
>
> anonymous
>
> > PL/SQL block:
> >
> > declare
> >   jackass number;
> > begin
> >  for jackass in 1..1000 loop
> > execute immediate 'select count (*) from solvit.solvit_lic ' ;
--
> > replace this table with any single row table you like.
> >  end loop;
> > end;
> > /
> >
> > Check your BCHR again. Wow, amazing ! How much better your BCHR
looks
> > now. This must be magic. If you would like to purchase other such
tools,
> > please feel free to drop me a line, I could also sell you a large
> > iceberg, which would end your personal water restriction problems.
> >
> > Another advantage to the above code is that it eliminates idle
capacity
> > from my CPU's (I paid for the thing, it should be put to work, right
? )
>
> as
>
> > my laptop has been at 100% CPU utilization for the last 8 minutes as
I
> > let this piece of crap run before I killed it (Oracle 9 on XP with
512 MB
> > RAM [SGA 120 MB], with a bunch of other starved stuff running
> > concurrently).
> >
> > Reduction of logical I/O : Now THERE is the holy grail worth
pursuing ! I
> > am sure we could have a VERY interesting discussion on that one !
> >
> > Feel free to use the above example to prove for once and for all
that
> > concetration on tuning BCHR alone is a fruitless exercise.
> >
> > Regards :
> >
> > Ferenc Mantfeld
> >
> > -Original Message-
> > From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
> > Sent: Monday, January 13, 2003 11:24 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: BCHR Tuning
> >
> > and those people "sell a tuning tool" hm, I hadn't noticed any
> > selling going on here. Or perhaps it's been subliminal?
> >
> > --- Jared Still <[EMAIL PROTECTED]> wrote:
> > > On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
> > > > Obviously, we don't know what we're talking about. I can see
> > >
> > > there's a
> > >
> > > > presentation by Rich Niemich at IOUG-A where he'll address all
> > >
> > > those
> > >
> > > > idiots who are saying you should ignore the Cash Hit Ratio (and
who
> > >
> > > are
> > >
> > > > all just after making big money on their products - I loved that
> > >
> > > one).
> > >
> > > > > Or modify the set up of these tools to take action when BCHR
> > >
> > > falls..
> > >
> > >
> > > Here's the session info:
> > >
> > > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > > Venue: Southern Hemisphere 2, Walt Disney World
> > >Dolphin, Lake Buena Vista, FL
> > >
> > > Abstract: Lately, there has been a big push to ignore your
> > > hit ratio with claims that it is meaningless. This shallow
> > > minded view (usually by people who sell a tuning tool) ignores
> > > why people look at hit ratios and what they are looking for.
> > > This quick tip talk will show you what to look for and why.
> > > You will definitely know when, where & why to look at your
> > > hit ratio in the future.
> > >
> > > Show you why your hit ratio matters. How to analyze the
> >

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

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

Thanks also to Lisa & Thomas for responding.

Cheers,

-Roy

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


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

QOTD: Any clod can have facts, but having an opinion is an art! 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

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

Re: newbie question - still: please help

2003-01-13 Thread Jared Still 

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

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

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

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

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

Jared


On Monday 13 January 2003 08:14, Daniel Wisser wrote:
> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

Re: newbie question - still: please help

2003-01-13 Thread Tim Gorman 
Daniel,

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

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

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

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

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

-Tim

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


> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Wisser
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


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

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

Re: compatible in 9iRel2

2003-01-13 Thread Jared Still 

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


Jared

On Monday 13 January 2003 09:16, Ehresmann, David wrote:
> I am trying to apply a "fix" for some developers here.  They used this fix
> in 8 and 8i by changing the compatible parameter and running their sql
> script.  I just want to be able to tell them that they need to fix their
> application now that we are using 9iRel2.  I tried this morning and got the
> ORA-402 and ORA-405 errors.  I don't want to strip away features of 9i just
> to get their security script to run.
> thanks,
> David Ehresmann.
>
> -Original Message-
> Sent: Monday, January 13, 2003 9:55 AM
> To: Multiple recipients of list ORACLE-L
>
>
> David - What are you trying to accomplish?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 8:44 AM
> To: Multiple recipients of list ORACLE-L
>
>
> List,
>
> I recently tried to set the compatible=8.1.7.0 parameter in a 9iRel2
> Solaris 8 database.  If you bounce the instance the
> sql>show parameter compatible=9.0.0  stays the same, it does not change. 
> If you shutdown and reboot the server you get an:
>
> ora-01033: Initialization or shutdown in progressWhich basically states
> that you are trying to connect to an instance that is being shutdown down
> or starting up.  I believe it goes into NOMOUNT stage and reads the
> init.ora and hangs because of the compatible parameter being set to 8i. I
> saw the document Oracle9i Database Migration Release 2 (9.2) Part Number
> A96530-01 stating how to downgrade, but it seems to defeat the purpose of
> having a 9i instance.   Is there any way to do this without stripping the
> 9i database down to 8i?
>
>
> David Ehresmann
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

Re: Daramtically improve BCHR with a single statement

2003-01-13 Thread Jared Still 

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

Jared

On Monday 13 January 2003 08:09, Yechiel Adar wrote:
> Hello All
>
> I saw that piece of code a few times and still think that even if you can
> bump up the BCHR it has it's place. How can you tell that you need more
> space in the buffer pool? Bad BCHR is an indication that you need to check
> this. It is also an indication that you do a lot of FTS or missing an index
> and you SQL reads too many blocks.
>
> Use the BCHR as an indication. When it goes down it is time to check.
>
> Yechiel Adar
> Mehish
> - Original Message -
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent: Monday, January 13, 2003 5:08 AM
>
> > To add an example of what Anjo, Morgens and everyone else is talking
>
> about,
>
> > here is a perfect illustration of why focusing on BCHR is like
> > concentrating intensely on how fast your tyres rotate in a Tour de
> > France, instead of looking of where you are going (probably a lot more
> > useful). Another example:
> > If I raced (100M sprint) against Maurice Green, and he went off in the
> > wrong direction, despite the fact that he is so much faster than me (duh
>
> !)
>
> > , I could lightly jog (as if I have anything else to offer) the 100M in
>
> the
>
> > right direction and beat him. Well, focusing on BCHR alone is like going
>
> at
>
> > full tilt with no direction.
> > Also, I have realized that cars have been around for > 100 years now, so
> > why exactly would I want to sprint again ? :-)
> >
> > Check out this example:
> >
> > run any number of scripts to look at BCHR. Then run the following
>
> anonymous
>
> > PL/SQL block:
> >
> > declare
> >   jackass number;
> > begin
> >  for jackass in 1..1000 loop
> > execute immediate 'select count (*) from solvit.solvit_lic ' ; --
> > replace this table with any single row table you like.
> >  end loop;
> > end;
> > /
> >
> > Check your BCHR again. Wow, amazing ! How much better your BCHR looks
> > now. This must be magic. If you would like to purchase other such tools,
> > please feel free to drop me a line, I could also sell you a large
> > iceberg, which would end your personal water restriction problems.
> >
> > Another advantage to the above code is that it eliminates idle capacity
> > from my CPU's (I paid for the thing, it should be put to work, right ? )
>
> as
>
> > my laptop has been at 100% CPU utilization for the last 8 minutes as I
> > let this piece of crap run before I killed it (Oracle 9 on XP with 512 MB
> > RAM [SGA 120 MB], with a bunch of other starved stuff running
> > concurrently).
> >
> > Reduction of logical I/O : Now THERE is the holy grail worth pursuing ! I
> > am sure we could have a VERY interesting discussion on that one !
> >
> > Feel free to use the above example to prove for once and for all that
> > concetration on tuning BCHR alone is a fruitless exercise.
> >
> > Regards :
> >
> > Ferenc Mantfeld
> >
> > -Original Message-
> > From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
> > Sent: Monday, January 13, 2003 11:24 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: BCHR Tuning
> >
> > and those people "sell a tuning tool" hm, I hadn't noticed any
> > selling going on here. Or perhaps it's been subliminal?
> >
> > --- Jared Still <[EMAIL PROTECTED]> wrote:
> > > On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
> > > > Obviously, we don't know what we're talking about. I can see
> > >
> > > there's a
> > >
> > > > presentation by Rich Niemich at IOUG-A where he'll address all
> > >
> > > those
> > >
> > > > idiots who are saying you should ignore the Cash Hit Ratio (and who
> > >
> > > are
> > >
> > > > all just after making big money on their products - I loved that
> > >
> > > one).
> > >
> > > > > Or modify the set up of these tools to take action when BCHR
> > >
> > > falls..
> > >
> > >
> > > Here's the session info:
> > >
> > > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > > Venue: Southern Hemisphere 2, Walt Disney World
> > >Dolphin, Lake Buena Vista, FL
> > >
> > > Abstract: Lately, there has been a big push to ignore your
> > > hit ratio with claims that it is meaningless. This shallow
> > > minded view (usually by people who sell a tuning tool) ignores
> > > why people look at hit ratios and what they are looking for.
> > > This quick tip talk will show you what to look for and why.
> > > You will definitely know when, where & why to look at your
> > > hit ratio in the future.
> > >
> > > Show you why your hit ratio matters. How to analyze the
> > > hit ratio. Fallacies by those who want to sell you products
> > > and tools instead.
> > >
> > >
> > > Shallow Minded ?!
> > >
> > > Jared
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Jared Still
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat

Re: Oracle 7.3.4 Real-Time Re-indexing

2003-01-13 Thread M Rafiq 
Have you used parallel clause while rebuilding? What version of 7.3.4 ?

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

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

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

Regards
Rafiq







Have y


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


Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

Re: DB Triggers vs Stored Procedures

2003-01-13 Thread Jared Still 

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

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

As Ferenc recommended, get the Feurstein book.

Jared

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

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

Re: Different Backups - A Comparartive analysis

2003-01-13 Thread Jared Still 
On Monday 13 January 2003 06:03, Tim Gorman wrote:
> Another question:  should SQL BackTrack be included for consideration?

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

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

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

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

2003-01-13 Thread Jared Still 

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

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

Jared

On Monday 13 January 2003 08:14, Conrad Meertins wrote:
> ALTER ...REBUILD..
>
> Thanks
>
> COnrad..
>
>
> -Original Message-
> WILLIAMS
> Sent: Monday, January 13, 2003 10:19 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Conrad - What command did you use to re-index? Drop index / create index?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 8:44 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Sorry, I forgot this additional infromation...
>
> Oracle created invalid objects when we ran rebuild.  The odd thing was that
> the trace file showed an invalid object but the all_objects table showed a
> valid object.
>
>
>
> -Original Message-
> Sent: Monday, January 13, 2003 9:39 AM
> To: [EMAIL PROTECTED]
>
>
>
> Team,
>
> I have a Oracle 7.3.4 database on a AIX box that is in Archive Log Mode.
> While the users were on the system, we Re-Indexed our tables.
> Situation: The users complained, that they were unable to process their
> orders.
> Although, the log file showed that the re-indexing was successful, the
> users were still unable to process their orders.
>
>
> QUESTION: Are they any known issues/pit-falls when re-indexing real-time in
> version 7.x?
>
> Should you have the database in exclusive mode when re-indexing?
>
> Please assist.
>
> Thanks
>
> Conrad Meertins
>
> [EMAIL PROTECTED]
>
> DBA Masters
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Conrad Meertins
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

Re: bitmapped indexes

2003-01-13 Thread Jared Still 

Andrey,

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

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

Jared

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


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

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

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

RE: compatible in 9iRel2

2003-01-13 Thread John Shaw 



I had a 9.2/solaris database that I could set compatible 8.1.7 on - but 
since I have done the 9.2.0.2. upgrade I can't set compatible below 9 either - 
never really needed it so I haven't bothered with metalink/tar.
>>> [EMAIL PROTECTED] 01/13/03 11:16AM >>>I 
am trying to apply a "fix" for some developers here.  They used this 
fixin 8 and 8i by changing the compatible parameter and running their 
sqlscript.  I just want to be able to tell them that they need to fix 
theirapplication now that we are using 9iRel2.  I tried this morning 
and got theORA-402 and ORA-405 errors.  I don't want to strip away 
features of 9i justto get their security script to run.thanks,David 
Ehresmann.-Original Message-Sent: Monday, January 13, 2003 
9:55 AMTo: Multiple recipients of list ORACLE-LDavid - What are 
you trying to accomplish?Dennis WilliamsDBA, 40%OCPLifetouch, 
Inc.[EMAIL PROTECTED] -Original Message-Sent: 
Monday, January 13, 2003 8:44 AMTo: Multiple recipients of list 
ORACLE-LList, I recently tried to set the compatible=8.1.7.0 
parameter in a 9iRel2 Solaris8 database.  If you bounce the instance 
the sql>show parameter compatible=9.0.0  stays the same, it does not 
change.  Ifyou shutdown and reboot the server you get 
an:ora-01033: Initialization or shutdown in progress    
Which basically statesthat you are trying to connect to an instance that is 
being shutdown down orstarting up.  I believe it goes into NOMOUNT 
stage and reads the init.oraand hangs because of the compatible parameter 
being set to 8i. I saw thedocument Oracle9i Database Migration Release 2 
(9.2) Part Number A96530-01stating how to downgrade, but it seems to defeat 
the purpose of having a 9iinstance.   Is there any way to do this 
without stripping the 9i databasedown to 8i?  David 
Ehresmann-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
Ehresmann, David  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).-- Please see the official ORACLE-L 
FAQ: http://www.orafaq.net-- Author: 
DENNIS WILLIAMS  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).-- Please see the official 
ORACLE-L FAQ: http://www.orafaq.net-- 
Author: Ehresmann, David  INET: [EMAIL PROTECTED]Fat 
City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).

Re: removing duplicate entries from a table

2003-01-13 Thread MURAT BALKAS 

Hi,

  following metalink documents may help you.

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

PR:1015631.6  HOW TO SELECT DUPLICATE ROWS WITHOUT USING ROWID

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

Murat



   

  "Mark Warner"

  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
  o.za>cc: 

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

   

   

  01/13/2003 07:35 

  PM   

  Please respond to

  ORACLE-L 

   

   





Hi List

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

Any help will be most appreciated

Thanks
Mark Warner

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

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






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

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

RE: Recommendation for SQL Tuning Book

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


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

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


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


Question for those who have gone before...

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

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

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

Thanks in advance.

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

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

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

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

Re: BCHR Tuning

2003-01-13 Thread Rajesh . Rao 

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

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

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

Raj





   
 
"Yechiel   
 
Adar"To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
 Subject: Re: BCHR Tuning  
 
Sent by:   
 
root@fatcity.  
 
com
 
   
 
   
 
January 13,
 
2003 10:58 AM  
 
Please 
 
respond to 
 
ORACLE-L   
 
   
 
   
 




Hello Anjo

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

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


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


> Hmm,
>
> Lately? That actually started publicly in 1998 as far as I am concerned
;-)
> And acutally long before that.
>
> Anjo.
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Sunday, January 12, 2003 11:43 PM
>
>
> > On Friday 10 January 2003 14:48, Mogens Nørgaard wrote:
> > > Obviously, we don't know what we're talking about. I can see there's
a
> > > presentation by Rich Niemich at IOUG-A where he'll address all those
> > > idiots who are saying you should ignore the Cash Hit Ratio (and who
are
> > > all just after making big money on their products - I loved that
one).
> > > > Or modify the set up of these tools to take action when BCHR
> falls..
> > > >
> >
> > Here's the session info:
> >
> > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > Venue: Southern Hemisphere 2, Walt Disney World
> >Dolphin, Lake Buena Vista, FL
> >
> > Abstract: Lately, there has been a big push to ignore your
> > hit ratio with claims that it is meaningless. This shallow
> > minded view (usually by people who sell a tuning tool) ignores
> > why people look at hit ratios and what they are looking for.
> > This quick tip talk will show you what to look for and why.
> > You will definitely know when, where & why to look at your
>

RE: dw tool question

2003-01-13 Thread Koivu, Lisa 
Title: RE: dw tool question





This is something I am currently dealing with. 


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

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


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

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

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

Oh well.  Another day, another grey hair. 


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

Thank you


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







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



>We have a new datawarehouse project. I didn't involve in this project at
>beginning. Right now, I was assigned to the group for picking a front
>end reporting tool. So far I know the manager prefer brio, cognos and
>business intelligent product.
>
>Does anyone familiar with those tools? Can you give me some feedback as
>dba standpoint of view, like how report distributed, power user vs end
>user, performance and security advange or disadvantage, concerns?


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


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

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

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

As a side note, I like (and have chosen) Sagent for data warehousing.  It's more than just a

RE: Daramtically improve BCHR with a single statement

2003-01-13 Thread Cary Millsap 
"How can you tell that you need more space in the buffer pool?"

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

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

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


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

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


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

Hello All

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

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

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


> To add an example of what Anjo, Morgens and everyone else is talking
about,
> here is a perfect illustration of why focusing on BCHR is like
> concentrating intensely on how fast your tyres rotate in a Tour de
France,
> instead of looking of where you are going (probably a lot more
useful).
> Another example:
> If I raced (100M sprint) against Maurice Green, and he went off in the
> wrong direction, despite the fact that he is so much faster than me
(duh
!)
> , I could lightly jog (as if I have anything else to offer) the 100M
in
the
> right direction and beat him. Well, focusing on BCHR alone is like
going
at
> full tilt with no direction.
> Also, I have realized that cars have been around for > 100 years now,
so
> why exactly would I want to sprint again ? :-)
>
> Check out this example:
>
> run any number of scripts to look at BCHR. Then run the following
anonymous
> PL/SQL block:
>
> declare
>   jackass number;
> begin
>  for jackass in 1..1000 loop
> execute immediate 'select count (*) from solvit.solvit_lic ' ; --
> replace this table with any single row table you like.
>  end loop;
> end;
> /
>
> Check your BCHR again. Wow, amazing ! How much better your BCHR looks
now.
> This must be magic. If you would like to purchase other such tools,
please
> feel free to drop me a line, I could also sell you a large iceberg,
which
> would end your personal water restriction problems.
>
> Another advantage to the above code is that it eliminates idle
capacity
> from my CPU's (I paid for the thing, it should be put to work, right ?
)
as
> my laptop has been at 100% CPU utilization for the last 8 minutes as I
let
> this piece of crap run before I killed it (Oracle 9 on XP with 512 MB
RAM
> [SGA 120 MB], with a bunch of other starved stuff running
concurrently).
>
> Reduction of logical I/O : Now THERE is the holy grail worth pursuing
! I
> am sure we could have a VERY interesting discussion on that one !
>
> Feel free to use the above example to prove for once and for all that
> concetration on tuning BCHR alone is a fruitless exercise.
>
> Regards :
>
> Ferenc Mantfeld
>
> -Original Message-
> From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, January 13, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: BCHR Tuning
>
> and those people "sell a tuning tool" hm, I hadn't noticed any
> selling going on here. Or perhaps it's been subliminal?
>
>
> --- Jared Still <[EMAIL PROTECTED]> wrote:
> > On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
> > > Obviously, we don't know what we're talking about. I can see
> > there's a
> > > presentation by Rich Niemich at IOUG-A where he'll address all
> > those
> > > idiots who are saying you should ignore the Cash Hit Ratio (and
who
> > are
> > > all just after making big money on their products - I loved that
> > one).
> > > > Or modify the set up of these tools to take action when BCHR
> > falls..
> > > >
> >
> > Here's the session info:
> >
> > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > Venue: Southern Hemisphere 2, Walt Disney World
> >Dolphin, Lake Buena Vista, FL
> >
> > Abstract: Lately, there has been a big push to ignore your
> > hit ratio with claims that it is meaningless. This shallow
> > minded view (usually by people who sell a tuning tool) ignores
> > why people look at hit ratios and what they are looking for.
> > This quick tip talk will show you what to look for and why.
> > You will definitely know when, where & why to look at y

removing duplicate entries from a table

2003-01-13 Thread Mark Warner 
Hi List

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

Any help will be most appreciated

Thanks
Mark Warner

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

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

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

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

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


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


ALTER ...REBUILD..

Thanks

COnrad..


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


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

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


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



Sorry, I forgot this additional infromation...

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



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



Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

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

RE: compatible in 9iRel2

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

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


David - What are you trying to accomplish?

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


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


List, 

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

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


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

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

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

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

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

RE: compatible in 9iRel2

2003-01-13 Thread DENNIS WILLIAMS 
David - Try COMPATIBLE=8.1.0

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

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

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


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

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


David - What are you trying to accomplish?

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


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


List, 

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

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


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

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

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

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

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

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

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

2003-01-13 Thread Jamadagni, Rajendra 
Title: RE: 8.1.6: possible to set role in db's logon trigger?





Roy,


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


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


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



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

RE: newbie question - still: please help

2003-01-13 Thread Stephane Faroult 
>hi!
>
>a DBA inteds to speed up a script that is looping
>and
>sending hundred thousands of sequential update
>statements like:
>
>UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
>he suggests copying the table to a file, change it
>and then
>load it into the DB again. i am strongly convinced
>that this
>is nonsense.
>
>what is the best way to go for a script like this,
>doing tons of
>updates? (except convincing him to swith to sell
>burgers)
>
>
>thx
>daniel

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

Regards,

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

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

RE: newbie question - still: please help

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



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


hi!

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

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

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

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


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

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

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

RE: Minimum required init.ora parameters

2003-01-13 Thread DENNIS WILLIAMS 
Well, I just tried it on 8.1.8, and here is what I receive:

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

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


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

Best Regards,
K Gopalakrishnan

 


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


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

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


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



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

Hemant

At 06:08 AM 13-01-03 -0800, you wrote:
>Nirmal - I believe there are four:
> db_name
> control_file
> db_block_size
> compatibility
>
>This is from John Hibbard, a great Oracle Education instructor.
>But why not try for yourself? Save off your init.ora, then create a new
>init.ora with just the above parameters. If Oracle comes up, then remove
>parameters. If there is another parameter, Oracle will tell you.
>
>
>
>Dennis Williams
>DBA, 40%OCP
>Lifetouch, Inc.
>[EMAIL PROTECTED]
>
>-Original Message-
>Sent: Monday, January 13, 2003 4:59 AM
>To: Multiple recipients of list ORACLE-L
>
>
>List,
>
>I'm interested to know the minimum required parameters to startup the
>database.
>
>Pls anybody list out that?
>
>Nirmal.,
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

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


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

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

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


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

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

Fat Ci

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

2003-01-13 Thread Thomas Day 

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

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

Then you can:

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

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



   

  "Pardee, Roy E"  

  
  @lmco.com>   cc: 

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

   

  01/13/2003 10:43 

  AM   

  Please respond   

  to ORACLE-L  

   

   





Greetings all,

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

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

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

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

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

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

All help will be most welcome.

Thanks!

-Roy

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

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






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

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

Re: dw tool question

2003-01-13 Thread Bruce A. Bergman 
>We have a new datawarehouse project. I didn't involve in this project at
>beginning. Right now, I was assigned to the group for picking a front
>end reporting tool. So far I know the manager prefer brio, cognos and
>business intelligent product.
>
>Does anyone familiar with those tools? Can you give me some feedback as
>dba standpoint of view, like how report distributed, power user vs end
>user, performance and security advange or disadvantage, concerns?

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

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

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

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

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

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

Anyhow, hope that helps.

thanks,
bruce

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

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

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

2003-01-13 Thread Koivu, Lisa 
Title: RE: 8.1.6: possible to set role in db's logon trigger?





Hi Roy, 


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


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






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



Greetings all,


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


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


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


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


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


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


All help will be most welcome.


Thanks!


-Roy


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


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

newbie question - still: please help

2003-01-13 Thread Daniel Wisser 
hi!

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

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

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

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


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

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

Dead shared server msgs in alert log + core dumps

2003-01-13 Thread [EMAIL PROTECTED] 
Has anyone ever seen a similar message to the following showing up in the
alert log and creating dump/trace files:

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


The trace file noted contains (partially) the following:

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


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

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

Thanks,
Karen Morton




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


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

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

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

2003-01-13 Thread Conrad Meertins 
ALTER ...REBUILD..

Thanks

COnrad..


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


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

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


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



Sorry, I forgot this additional infromation...

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



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



Team,

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


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

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

Please assist.

Thanks

Conrad Meertins

[EMAIL PROTECTED]

DBA Masters


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

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

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

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

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

Re: Minimum required init.ora parameters

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

Keith

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


>
> "compatibility" or "compatible" is not a mandatory init.ora parameter.
> I would think that only the first three are required.
>
> Hemant
>
> At 06:08 AM 13-01-03 -0800, you wrote:
> >Nirmal - I believe there are four:
> > db_name
> > control_file
> > db_block_size
> > compatibility
> >
> >This is from John Hibbard, a great Oracle Education instructor.
> >But why not try for yourself? Save off your init.ora, then create a new
> >init.ora with just the above parameters. If Oracle comes up, then remove
> >parameters. If there is another parameter, Oracle will tell you.
> >
> >
> >
> >Dennis Williams
> >DBA, 40%OCP
> >Lifetouch, Inc.
> >[EMAIL PROTECTED]
> >
> >-Original Message-
> >Sent: Monday, January 13, 2003 4:59 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >List,
> >
> >I'm interested to know the minimum required parameters to startup the
> >database.
> >
> >Pls anybody list out that?
> >
> >Nirmal.,
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: DENNIS WILLIAMS
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> >San Diego, California-- Mailing list and web hosting services
> >-
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from).  You may
> >also send the HELP command for other information (like subscribing).
>
> Hemant K Chitale
> My web site page is :  http://hkchital.tripod.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hemant K Chitale
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>


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

Re: BCHR Tuning

2003-01-13 Thread Yechiel Adar 
Hello Anjo

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

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


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


> Hmm,
>
> Lately? That actually started publicly in 1998 as far as I am concerned
;-)
> And acutally long before that.
>
> Anjo.
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Sunday, January 12, 2003 11:43 PM
>
>
> > On Friday 10 January 2003 14:48, Mogens Nørgaard wrote:
> > > Obviously, we don't know what we're talking about. I can see there's a
> > > presentation by Rich Niemich at IOUG-A where he'll address all those
> > > idiots who are saying you should ignore the Cash Hit Ratio (and who
are
> > > all just after making big money on their products - I loved that one).
> > > > Or modify the set up of these tools to take action when BCHR
> falls..
> > > >
> >
> > Here's the session info:
> >
> > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > Venue: Southern Hemisphere 2, Walt Disney World
> >Dolphin, Lake Buena Vista, FL
> >
> > Abstract: Lately, there has been a big push to ignore your
> > hit ratio with claims that it is meaningless. This shallow
> > minded view (usually by people who sell a tuning tool) ignores
> > why people look at hit ratios and what they are looking for.
> > This quick tip talk will show you what to look for and why.
> > You will definitely know when, where & why to look at your
> > hit ratio in the future.
> >
> > Show you why your hit ratio matters. How to analyze the
> > hit ratio. Fallacies by those who want to sell you products
> > and tools instead.
> >
> >
> > Shallow Minded ?!
> >
> > Jared
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jared Still
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Anjo Kolk
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

Re: Daramtically improve BCHR with a single statement

2003-01-13 Thread Yechiel Adar 
Hello All

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

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

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


> To add an example of what Anjo, Morgens and everyone else is talking
about,
> here is a perfect illustration of why focusing on BCHR is like
> concentrating intensely on how fast your tyres rotate in a Tour de France,
> instead of looking of where you are going (probably a lot more useful).
> Another example:
> If I raced (100M sprint) against Maurice Green, and he went off in the
> wrong direction, despite the fact that he is so much faster than me (duh
!)
> , I could lightly jog (as if I have anything else to offer) the 100M in
the
> right direction and beat him. Well, focusing on BCHR alone is like going
at
> full tilt with no direction.
> Also, I have realized that cars have been around for > 100 years now, so
> why exactly would I want to sprint again ? :-)
>
> Check out this example:
>
> run any number of scripts to look at BCHR. Then run the following
anonymous
> PL/SQL block:
>
> declare
>   jackass number;
> begin
>  for jackass in 1..1000 loop
> execute immediate 'select count (*) from solvit.solvit_lic ' ; --
> replace this table with any single row table you like.
>  end loop;
> end;
> /
>
> Check your BCHR again. Wow, amazing ! How much better your BCHR looks now.
> This must be magic. If you would like to purchase other such tools, please
> feel free to drop me a line, I could also sell you a large iceberg, which
> would end your personal water restriction problems.
>
> Another advantage to the above code is that it eliminates idle capacity
> from my CPU's (I paid for the thing, it should be put to work, right ? )
as
> my laptop has been at 100% CPU utilization for the last 8 minutes as I let
> this piece of crap run before I killed it (Oracle 9 on XP with 512 MB RAM
> [SGA 120 MB], with a bunch of other starved stuff running concurrently).
>
> Reduction of logical I/O : Now THERE is the holy grail worth pursuing ! I
> am sure we could have a VERY interesting discussion on that one !
>
> Feel free to use the above example to prove for once and for all that
> concetration on tuning BCHR alone is a fruitless exercise.
>
> Regards :
>
> Ferenc Mantfeld
>
> -Original Message-
> From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, January 13, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: BCHR Tuning
>
> and those people "sell a tuning tool" hm, I hadn't noticed any
> selling going on here. Or perhaps it's been subliminal?
>
>
> --- Jared Still <[EMAIL PROTECTED]> wrote:
> > On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
> > > Obviously, we don't know what we're talking about. I can see
> > there's a
> > > presentation by Rich Niemich at IOUG-A where he'll address all
> > those
> > > idiots who are saying you should ignore the Cash Hit Ratio (and who
> > are
> > > all just after making big money on their products - I loved that
> > one).
> > > > Or modify the set up of these tools to take action when BCHR
> > falls..
> > > >
> >
> > Here's the session info:
> >
> > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > Venue: Southern Hemisphere 2, Walt Disney World
> >Dolphin, Lake Buena Vista, FL
> >
> > Abstract: Lately, there has been a big push to ignore your
> > hit ratio with claims that it is meaningless. This shallow
> > minded view (usually by people who sell a tuning tool) ignores
> > why people look at hit ratios and what they are looking for.
> > This quick tip talk will show you what to look for and why.
> > You will definitely know when, where & why to look at your
> > hit ratio in the future.
> >
> > Show you why your hit ratio matters. How to analyze the
> > hit ratio. Fallacies by those who want to sell you products
> > and tools instead.
> >
> >
> > Shallow Minded ?!
> >
> > Jared
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jared Still
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other informatio

  1   2   >