date:20030724

Title: Computing resource consumption









Raj,

 

A pretty common (and actually pretty
accurate) chargeback unit is the LIO. You can get this information by using the
standard AUDIT CONNECT feature, and using the LREAD value as your basis. If you
wanted to get fancy, you might also charge by the parse call (which
you’re already collecting in your V$SESSTAT query). The reason I’d
focus on these two metrics is because these are the two operations on an Oracle
system that absolutely prevent the system from scaling.

 

You could count physical I/Os as well, but
that would be redundant if you’re already catching LIO call counts.

 



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

Upcoming events:
- Hotsos Clinic 101
in Denver, Sydney
- Hotsos Symposium 2004
March 7–10 Dallas
- Visit www.hotsos.com for schedule
details...



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni,
Rajendra
Sent: Thursday, July 24, 2003
12:04 PM
To: Multiple recipients of list
ORACLE-L
Subject: Computing resource
consumption

 

Does anyone know any papers or techniques to compute resource
consumption by users in a DB systems? This may or may not be for computing
charge-back to the client, but my questions are 

1. What do you compute? 
2.
are there any standard methods and or standard formula? 

I am collecting v$sesstat when a session exits, but is data
alone from session stats sufficient? How about the work done by background
processes on user's behalf? 

Do you do anything like this at your workplace? This is
something that might be coming down the line, so I have been asked to start
looking for related stuff.

Thanks in advance 
Raj



Rajendra
dot Jamadagni at nospamespn dot com 
All
Views expressed in this email are strictly personal. 
QOTD:
Any clod can have facts, having an opinion is an art !

RE: Timestamps in trace files (and other trace file oddities)

I absolutely agree with Wolfgang. This pattern of behavior in the trace file
is common for applications that do an excessive amount of LIO processing.
(LIO = "logical I/O," or a fetch of a buffer's content from the database
buffer cache.)

Henry, you don't show the database call whose line was eventually written to
the trace file (unless you killed the job before it completed). As Wolfgang
pointed out, a line emits to the trace data only after the event it
represents completed. There's probably a FETCH line (if this was a SELECT)
or an EXEC line (if it was an INSERT, UPDATE, DELETE, or MERGE) that came
out eventually with a whopper of a cr or cu field value (or both).

If you were to query V$SESS_IO repeatedly while this trace file is being
generated, you would probably see an enormous number of BLOCK_GETS or
CONSISTENT_GETS racking up to the session you were tracing.

The verdict: Inefficient SQL. I can tell you this with 100% certainty. The
only way out is to tune it. No init.ora parameter, and no amount of memory,
CPU capacity, or faster I/O devices can fix this.

By the way... Even though the WAIT events you see in the trace file
constitute a miniscule percentage of your response time, their very
existence correlates strongly with the verdict I just proposed. The
occasional 'db file scattered read' is an indication of the kernel
periodically executing multiblock reads upon a database file descriptor. The
fact that they're only occasional is a good indication that your inefficient
SQL is revisiting the same blocks over and over again. That's great for your
cache hit ratio, but horrible for performance, because every LIO typically
consumes something on the order of 50 microseconds per call. (That's 50
seconds of execution time for every million LIOs your program executes.)

The 'latch free' wait indicates competition for an acquisition of latch #66
(p2) on your system, which--just guessing--is probably your 'cache buffers
chains' latch. (Check your V$LATCHNAME to be sure.) An acquisition of one of
these latches is required for every single LIO your system ever does.
Acquiring this latch also burns CPU (see "Why you should focus on LIOs
instead of PIOs" at www.hotsos.com/catalog for details). When concurrency is
high, competition for these latches leads to "latch free" waits such as the
one you see here. If you were to watch V$LATCH while this thing runs, you'll
also notice that GETS for the latch with LATCH#=66 is probably spinning
through the roof, just like your V$SESS_IO stuff was.

It's all a part of the same big, very common pattern.

Hope this helps...

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

Upcoming events:
- Hotsos Clinic 101 in Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-Original Message-
Wolfgang Breitling
Sent: Thursday, July 24, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L

My interpretation would be as follows:

The wait entries are written whenever a wait ends, so at 15:40:59.149 the 
session has just been waiting .00 seconds for a scattered read of 18 
blocks. At 15:46:06.340 it just had been waiting on a latch free event. For 
the almost seven minutes between, it had not been waiting on any of the 
established wait events. It had either been processing the blocks returned, 
or it could have been waiting in the OS scheduler queue waiting for a cpu 
to become available - or both intermittendly. After coming out of the latch 
free wait, it found that the blocks it had previously read had been flushed 
from the buffer (not surprising after 7 minutes) and needed to read them
again.

At 07:04 AM 7/24/2003 -0800, you wrote:
>(Tried sending this yesterday. I'll try again)
>
>Dan,
>I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
>today and got some really odd results in my trace file (8.1.7).
>
>*** 2003-07-23 15:40:59.149
>WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
>*** 2003-07-23 15:46:06.340
>WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
>*** 2003-07-23 15:47:53.851
>WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
>
>Two things struck me (three if it takes me too long to write this and I get
>home late). First, the timestamps show an elapsed time of ~7 minutes, but
>the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
>closer to reality. Huh???
>
>Secondly, the first scattered read reads 18 blocks starting at 6041. Why
>does the next scattered read start at block# 6042?
>
>Any ideas?

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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

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

Re: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread jkstill

Jonathan,

I've used MINUS heavily in sql scripts and pl/sql
to determine the differences in schemas: both 
structure and data.

Of interest to DBA's and developers, and least when I
did it it was for the developers.

Jared

On Thu, 24 Jul 2003, Jonathan Gennick wrote:

> I'm doing research for an article on union queries. I'm
> interested in finding examples of problems that were solved
> using UNION, UNION ALL, INTERSECT, or MINUS, with the latter
> two being of special interest because I don't see them used
> very often. If you can think of an interesting problem
> you've solved using one of these keywords, I'd love to hear
> about it.
> 
> Best regards,
> 
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
> 
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by 
> email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
> or send email to [EMAIL PROTECTED] and 
> include the word "subscribe" in either the subject or body.
> 
> 

-- 
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: Triggers - How expensive when set on heavily loaded tables ?

2003-07-24 Thread jkstill


Brian,

Were those row level or statement level triggers?

Jared

On Wed, 23 Jul 2003 [EMAIL PROTECTED] wrote:

> 
> I did some trigger timings about a year ago on v8.1.7.4.  I did the testing
> using a trigger with only 1 statement in it, and that statements was
> "null;".  I found that my transaction timing increased by 100% for the
> first trigger added, and 25% for every trigger after that.  So that math
> breaks down this way:
> 
>   The base transaction took 60 seconds;
>   With one before insert trigger with a "null;" statement in it, it now
> took 120 seconds;
>   I added one more after insert trigger with a "null" statement in it,
> the total time was 150 seconds;
> 
> Kind of makes ya rethink/realize what the true cost of all those before
> insert/update triggers we have added over the years, that do nothing more
> than update the add_date and change_date columns.  I know I'll never do it
> again.
> 
> Now in your case, maybe the trigger is the right answer.  But we can't make
> that decision for you.YOU HAVE TO TEST IT FOR YOURSELF!
> 
> 
> 
> 
> 
> 
>  
> 
>   "VIVEK_SHARMA" 
> 
>   <[EMAIL PROTECTED]To:   Multiple recipients of 
> list ORACLE-L <[EMAIL PROTECTED]> 
>   osys.com>cc:   
> 
>   Sent by: Subject:  Triggers - How expensive 
> when set on heavily loaded tables ?
>   [EMAIL PROTECTED]  
> 
>   .com   
> 
>  
> 
>  
> 
>   07/23/2003 09:14   
> 
>   PM 
> 
>   Please respond to  
> 
>   ORACLE-L   
> 
>  
> 
>  
> 
> 
> 
> 
> 
> 
> Qs 1) Whenever an Account ID is transacted - INSERT occurs into a table
> TRAN_TBL as part of the transaction , Additionally the Account ID is
> checked for existence in another table say TABLE_1 . If found , a record is
> inserted into yet another  table say TABLE_2 .
> 
> Qs. Operations involving TABLE_1 & TABLE_2 if managed using triggers , How
> expensive in CPU & performance will it be ?
> 
> NOTE - TRAN_TBL undergoes very large volumes of Concurrent OLTP INSERT
> transaction .
> 
> 
> Qs 2) If an alert is to be raised on addition of a new record to TABLE_2 :-
> a) Can trigger be used ?
> b) Should a cron job running every 5 min. look at TABLE_2 & based on the
> time criteria generates the alert ?
> 
> Thanks
> 
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: VIVEK_SHARMA
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> 
> 
> 
> 
> 

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

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

Re: Index Usage ?!

2003-07-24 Thread Prem Khanna J

Thanx Breitling.

Leave the index un-analyzed if it works for you

as u said,index IDX_PROFILE_SHINKI is not analyzed now.
that works fine.

but there are seperate indexes on column PREF, FLAG and SEX.
when those indexes are used ,cost=999.

whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC,
FLAG,SEX,PREF,ENTDAY) is used , cost=7.

1.whys there is a huge difference in COST ?
2.does it mean that a composite index is better than individual ones ?

Kindly throw some light on this.

Regards,
Jp.





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

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

too much oracm process

2003-07-24 Thread zhu chao

hi, friends:
I have a rac running linux as2.1/kernel 2.4.9-e25/oracle 9.2.0.3(with
some intermit patches). The server switched from old rac server/storage to
current server/storage on July 16.
But on July 24, there is some new oracm process running, too many oracm
process than I have ever seen.
I checked cm.log, there is no special log during July 24, all of them
look like:(>WARNING:  ReadCommPort:  socket closed by peer on recv()., tid =
11305018 file = unixinc.c, line = 754 {Thu Jul 24 02:50:19 2003 }^). I
noticed that all new oracm process is started by pid:1249(the first oracm
process).
My question is:
1. Have anyone noticed similiar too many oracm problem before?
2. Under what situation will the new oracm process be started?

[EMAIL PROTECTED] admin]$ ps -ef|grep oracm
root  1247 1  0 Jul16 ?00:00:00 oracm
root  1249  1247  0 Jul16 ?00:00:00 oracm
root  1250  1249  0 Jul16 ?00:00:03 oracm
root  1251  1249  0 Jul16 ?00:00:00 oracm
root  1252  1249  0 Jul16 ?00:00:47 oracm
root  1253  1249  0 Jul16 ?00:00:00 oracm
root  1254  1249  0 Jul16 ?00:00:00 oracm
root  1255  1249  0 Jul16 ?00:00:01 oracm
root  1256  1249  0 Jul16 ?00:00:00 oracm
root  1260  1249  0 Jul16 ?00:00:01 oracm
root  1695  1249  0 Jul16 ?00:00:04 oracm
root  1718  1249  0 Jul16 ?00:00:10 oracm
root  1725  1249  0 Jul16 ?00:00:00 oracm
root  1726  1249  0 Jul16 ?00:00:00 oracm
root  1727  1249  0 Jul16 ?00:00:00 oracm
root  1728  1249  0 Jul16 ?00:00:00 oracm
root  1729  1249  0 Jul16 ?00:00:00 oracm
root  1730  1249  0 Jul16 ?00:00:00 oracm
root  1734  1249  0 Jul16 ?00:00:01 oracm
root 21754  1249  0 Jul24 ?00:00:00 oracm
root 21756  1249  0 Jul24 ?00:00:00 oracm
root 21758  1249  0 Jul24 ?00:00:00 oracm
root 21760  1249  0 Jul24 ?00:00:00 oracm
root 21762  1249  0 Jul24 ?00:00:00 oracm
root 22048  1249  0 Jul24 ?00:00:00 oracm
root 22050  1249  0 Jul24 ?00:00:00 oracm
root 22052  1249  0 Jul24 ?00:00:00 oracm
root 22329  1249  0 Jul24 ?00:00:00 oracm
root 22331  1249  0 Jul24 ?00:00:00 oracm
root 22335  1249  0 Jul24 ?00:00:00 oracm
root 22337  1249  0 Jul24 ?00:00:00 oracm
root 22339  1249  0 Jul24 ?00:00:00 oracm
root 22384  1249  0 Jul24 ?00:00:00 oracm
root 22525  1249  0 Jul24 ?00:00:00 oracm
root 22619  1249  0 Jul24 ?00:00:00 oracm
root 22621  1249  0 Jul24 ?00:00:00 oracm
root 22758  1249  0 Jul24 ?00:00:00 oracm
root 22915  1249  0 Jul24 ?00:00:00 oracm
root 22917  1249  0 Jul24 ?00:00:00 oracm
root 22919  1249  0 Jul24 ?00:00:00 oracm
root 22921  1249  0 Jul24 ?00:00:00 oracm
root 23195  1249  0 Jul24 ?00:00:00 oracm
root 23746  1249  0 Jul24 ?00:00:00 oracm
root 25424  1249  0 Jul24 ?00:00:00 oracm
root 25426  1249  0 Jul24 ?00:00:00 oracm
root 26539  1249  0 Jul24 ?00:00:00 oracm
root 27089  1249  0 Jul24 ?00:00:00 oracm
root 27381  1249  0 Jul24 ?00:00:00 oracm
root 28896  1249  0 Jul24 ?00:00:00 oracm
root 29171  1249  0 Jul24 ?00:00:00 oracm
root 29312  1249  0 Jul24 ?00:00:00 oracm
root 29591  1249  0 Jul24 ?00:00:00 oracm
root 30911  1249  0 Jul24 ?00:00:00 oracm
oracle   19240 17165  0 09:56 pts/100:00:00 grep oracm

Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org

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

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

If the sort requires disk you'll the direct write/reads in the trace. They 
do not count towards LIO and PIO as they are not using the buffer pool.

At 03:49 PM 7/24/2003 -0800, you wrote:

Another significant area of processing can be sorting. I don't know if 
sort processing is counted along with LIOs or PIOs.

Dan
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Managing Archived Redo Logs

2003-07-24 Thread Ukrit . K

Hello,
Yes , you sure need to delete the old archive files becuase if you don't, it will eat up your space. I keep my arch files for 1 week. the arch files oder than that, I just have a job to delete it. But besure to backup it first
UK

"Farnsworth, Dave" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/24/2003 09:29 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Managing Archived Redo Logs

I finally get to put our 8.1.7 on NT databases into archivelog mode. We have a third party app vendor that would not support us if I did this but I finally convinced them that is the way to go and it should not effect the app. Anyway, I am reading chapter 7 from the Administrators Guide, Managing Archived Redo Logs. I know I have to set the parameters in the init.ora to achieve automatic archiving;

log_archive_start=true
log_archive_dest_1 = "location=my\disk\drive"
log_archive_format=%%ORACLE_SID%%T%T%S.ARC -or somthing like that

One thing I don't see in TFM is, do these archived redo logs just keep accumulating in the destination directory set in the log_archive_dest_1 parameter? Do I need to create a process to get them to tape and then once on tape, delete these old archive redo logs through my process?
I'm just excited to be able to finally go to archivelog mode. Once I get the basics down then I want to investigate using RMAN.

Thanks,

Dave

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

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

Re: Timestamps in trace files (and other trace file oddities)

2003-07-24 Thread Daniel Fink

10046 Trace files are a peak into how an application and Oracle operate. In looking at 
one generated by a web server, I identified that the application (user) was sorting to 
a temporary tablespace that was dictionary/permanent without at the data dictionary. I 
saw a query performing DML against fet$, uet$ and seg$. You can see every step that 
the application performs, how Oracle responds. You can even see what scripts are 
called from a sql*plus session! COOL! (then again, I like to read
formatted block dumps to determine the undo chain!)

Another significant area of processing can be sorting. I don't know if sort processing 
is counted along with LIOs or PIOs.

Dan

Freeman Robert - IL wrote:
> 
> If you have a 10046 trace file, and the means to process/analyze it, then it
> will tell you what you need to know. If it's IO, you will see IO related
> waits. If they are not there, and no other significant wait events, then
> it's all processing time, which probably equaits to LIO's. Thats my take.
> 
> Love 10046's just had a project where they really helped me figure out
> what was going on.
> 
> RFbegin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

Re: Timestamps in trace files (and other trace file oddities)

2003-07-24 Thread Gudmundur Josepsson

> If you have a 10046 trace file, and the means to process/analyze it

For processing and analyzing a 10046 trace file I think nothing compares to
the Hotsos Profiler.  It's very reasonably priced, too.

Gudmundur

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

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

2003-07-24 Thread Mark Richard


Jonathan,

I can't think of any specific examples but the four operators all have
their place:

UNION - A quick way to merge result sets.  If, for example, you have actual
financial data in one table and budget financial data in another table and
need to spool all data to a file then UNION is an easy way to merge the two
tables into a single cursor.  I guess a natural alternative would be a view
- but this then breaks the statement up into two statements SELECT (with
selection criteria) and VIEW (which will effectively hide the underlying
tables from the main query) - making maintenance worse but could be useful
if the tables are joined often.

UNION ALL - More significant when you may be deliberately creating
duplicate records and need to show both records or when you know that no
duplicates will be created and can therefore save on a sort operation.  My
first example would be better implemented as union all since "actual" and
"budget" being extracted as constants from each table ensures no overlap.

INTERSECT - Can often be used in the same scenario's as "WHERE EXISTS" or
"IN" but may allow more complex conditions to be compared.

MINUS - Can often be used to implement complex "WHERE NOT EXISTS" or "NOT
IN".  For example, a "student" table may hold "number_of_enrolled_subjects"
and a "studentsubject" table may map students to subjects...  If you need
to return the students which have this attribute set incorrectly (ie:
corrupt data) then a simple MINUS query can compare the attribute to the
COUNT(*) from "studentsubject".

I think the important thing to remember is that all of these operations can
normally be accomplished using different SQL syntax.  The decision comes
down to a couple of factors:

1) Maintenance - some ways of writing a query may represent the underlying
logic much easier.  MINUS, for example, can break a complex statement down
into two simpler queries which may make their purpose easier to understand.
UNION may negate the need for a view - which can be a good or bad thing
depending on other factors.

2) Execution approach.  Often the above operators are resolved using a sort
- the volume of records in each side of the query and configuration of your
database may make this desirable, or it may not.  "WHERE EXISTS", on the
other hand will normally be resolved using nested loops or hash joins.
With small recordsets (not necessarily the final resultset since two of
these operators are effectively data filters) the approach probably doesn't
matter, but as data volumes and performance demands increase the decision
can be significant.

Hopefully this has added some food for thought.





   
   
  Jonathan Gennick 
   
  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
  .com>cc: 
   
  Sent by: Subject:  Union  quries: INTERSECT, 
MINUS, etc 
  [EMAIL PROTECTED]
   
  .com 
   
   
   
   
   
  24/07/2003 23:04 
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




I'm doing research for an article on union queries. I'm
interested in finding examples of problems that were solved
using UNION, UNION ALL, INTERSECT, or MINUS, with the latter
two being of special interest because I don't see them used
very often. If you can think of an interesting problem
you've solved using one of these keywords, I'd love to hear
about it.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gen

RE: Timestamps in trace files (and other trace file oddities)

2003-07-24 Thread Freeman Robert - IL

If you have a 10046 trace file, and the means to process/analyze it, then it
will tell you what you need to know. If it's IO, you will see IO related
waits. If they are not there, and no other significant wait events, then
it's all processing time, which probably equaits to LIO's. Thats my take.

Love 10046's just had a project where they really helped me figure out
what was going on.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 7/24/2003 4:54 PM

Wolfgang,
There are 4 cpu's, and file# 65 block# 6041 is from the driving
table of
the 5 table join (all NL joins). I will take a look at v$bh to see what
blocks from the other tables are in memory next time I run this. Aside
from
this indirect approach, any other suggestions on confirming your
plausible
hypothesis? Is there a way to breakdown the workload of individual
cpu's?

Thanks.

Henry

-Original Message-
Wolfgang Breitling
Sent: Thursday, July 24, 2003 5:19 PM
To: Multiple recipients of list ORACLE-L

20% idle = 80% busy? How many cpus?

If it is 20% idle that should lay to rest the theory that  the process
may
wait for a cpu. But, if it is a 5-way join, maybe it is reading a set of
blocks from file 65 and then collects the corresponding rows from the
other
4 tables, which must be all pure logical IO since there are no further
waits. But if you have a few nested NL joins, they can easily churn
through
millions of LIOs taking minutes of cpu. And if you have multiple cpus,
only
one of them would be 100% busy during that time, leaving some overall
idle
%. Since the corresponding rows of the other tables get heavily
referenced,
they stay virtually "pinned" in the buffer ( in quotes because pinned in
the buffer pool context has a different meaning than the ususal
connotation
) offering an explanation why the already read 17 blocks need to be
re-read
once the session is done with block 6041 and wants to move on to block
6042.

At 12:34 PM 7/24/2003 -0800, you wrote:
>Dan,
> No reason to stay in the corner (unless that's where the
fridge
> is). I'm
>trying to decide where to look on the OS. When I looked at vmstat,
there
was
>no paging, and ~20% idle cpu. Maybe set up a cron to take snapshots of
>something(??) every few minutes? Support from the Sys Admin side is
minimal,
>so I am kind of on my own. I also have more experience with Sun than
AIX.
>
>Henry
>
>
>-Original Message-
>Daniel Fink
>Sent: Thursday, July 24, 2003 4:14 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Henry,
> I completely agree, something did not feel right (hence my
obvious
>reluctance). I did a very poor job of trying to explain that we cannot
>extrapolate system waits/activity by looking at database
waits/activity. I
>should have learned my lesson (bad dba...go sit in the corner) from a
long
>email discussion with another list member where I was trying to
understand
a
>session issue by examining a single processes trace file.
>
> Wolgang's answer really nails the issue. What I think it comes
> down to is
>that wait events are only valid for what Oracle is waiting for
internally.
>It cannot see what is happening at the system level. Some of the system
>activity it can determine by the call it is making. For example, when a
>request is sent to disk, it knows that it is waiting for the data to be
>returned. However, it does not know what is consuming the wait time. It
>could be that the disk service time is high or that there are
>so many requests for the disk that they are being queued up. All the
process
>knows is that the data has not been returned. As Wolfgang illustrated,
the
>same thing happens at the CPU. I doubt that all 7 minutes were CPU,
most
>likely there was a lot of intervening activity. All Oracle know was
that it
>was not waiting on something to happen internally.
>
> I hope I've done better the second time around.
>
>Dan
>
>Henry Poras wrote:
> >
> > Dan,
> > I see what you are saying but something doesn't feel right
(see,
>say, feel.
> > Should be some pun in here about not making sense). CPU time of 7
minutes
> > after a single read??? This is an ugly INSERT on a 5 table join, but
>that's
> > about it. Unfortunately I don't think there is a way to measure
service
>time
> > at this granularity. Isn't it recorded in sesstat at the end of the
> > transactions? Guess I'll go and double check that.
> > Also, I remember an old paper by Craig Shallahamer that
shows
you
>don't get
> > a full multi_block_read_count if a block is already in buffer cache
(e.g.
> > mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is
already
in
> > memory. The cached block is already reflected in the trace numbers.)
> > There is something very strange hear but I don't know what.
Might
>be an
> > interesting case to track db/os relationships (running on AIX 4.3)
> >
> > Henry
> >
> > -Original Message-
> > Daniel Fink
> > Sent: Thursday, July 24, 2003 11:49 AM
>

RE: Recall: Datafile sizing

2003-07-24 Thread Freeman Robert - IL

Recall request denied. Resubmit in 30 days for further disapproval.


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 7/24/2003 5:29 PM

Biddell, Ian would like to recall the message, "Datafile sizing".
-- 

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

Recall: Datafile sizing

Biddell, Ian would like to recall the message, "Datafile sizing".
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Biddell, Ian
  INET: [EMAIL PROTECTED]

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

Sorry I was still setting it all up, the datafiles will be numbered 1
thru 5 :-)
I was more concerned if I should add the bitmap header overhead to each
datafile or just the first one.

-Original Message-
Sent: Thursday, 24 July 2003 11:49 PM
To: Multiple recipients of list ORACLE-L


You'll want to modify your statement first to have different file names
for each datafile.  Just pointing it out in case the obvious was
overlooked...

BTW, anyone know what that statement would do?  Would it error out?
Gotta get another playground for testing...

Rich

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


> -Original Message-
> From: Biddell, Ian [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 12:34 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Datafile sizing
> 
> 
> Hi All,
> 
> I have seen previous discussions on datafile sizing and adding 64k to 
> the size for the bitmap header. Also other people saying that they 
> just go for 2001MB when they want a 2000mb datafile.
> My question is does each datafile require this within the 
> tablespace or
> is it just one per tablespace to facilitate LMT ?
> 
> So if I have this statement
> CREATE TABLESPACE csis_l_tables LOGGING 
>   DATAFILE 'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,  
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 4000M
> 
>   extent management local uniform size 64m SEGMENT SPACE
MANAGEMENT 
> AUTO;
> 
> Do I add 1mb to each datafile or just the first?
> 
> Thanks
> Ian
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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

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

Recall: Datafile sizing

Biddell, Ian would like to recall the message, "Datafile sizing".
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Biddell, Ian
  INET: [EMAIL PROTECTED]

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

Sorry I was still setting it all up, the datafiles will be numbered 1
thru 5 :-)
I was more concerned if I should add the bitmap header overhead to each
datafile or just the first one.

-Original Message-
Sent: Thursday, 24 July 2003 11:59 PM
To: Multiple recipients of list ORACLE-L


I tried it.

I got the message "error creating the file, file exists". Tablespace was
not created (as expected). However the FILE itself also was not created,
as if Oracle cleaned up after itself.


--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> You'll want to modify your statement first to have different file 
> names for each datafile.  Just pointing it out in case the obvious was
> overlooked...
> 
> BTW, anyone know what that statement would do?  Would it error out?
> Gotta
> get another playground for testing...
> 
> Rich
> 
> Rich Jesse   System/Database Administrator
> [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
> 
> 
> > -Original Message-
> > From: Biddell, Ian [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 24, 2003 12:34 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Datafile sizing
> > 
> > 
> > Hi All,
> > 
> > I have seen previous discussions on datafile sizing and adding 64k
> to
> > the size for the bitmap header.
> > Also other people saying that they just go for 2001MB when they
> want a
> > 2000mb datafile.
> > My question is does each datafile require this within the
> > tablespace or
> > is it just one per tablespace to facilitate LMT ?
> > 
> > So if I have this statement
> > CREATE TABLESPACE csis_l_tables LOGGING 
> > DATAFILE 'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,  
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 4000M
> > 
> > extent management local uniform size 64m SEGMENT SPACE
MANAGEMENT 
> > AUTO;
> > 
> > Do I add 1mb to each datafile or just the first?
> > 
> > Thanks
> > Ian
> --
> 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).
> 


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

Hi!

How does the list server here operate?
I sent a reply to one message several hours ago (see below), but it never
reached my mailbox again. Also, sometimes I get the reply first to my
mailbox than the original post (I mean several minutes before, it couldn't
be a SMTP latency issue).
Is this list moderated somehow or what could be the cause? Or could it be
related to timestamp of sent message (My morning arrives 7-9 hours earlier
than in US).

Kind of confused,
Tanel.

- Original Message -
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 6:06 PM


> Hi!
>
> About the second one, you see the first read started from 6041 and was 18
> blocks, the second one which was executed 7 minutes later, started from
6042
> and was 17 blocks. Probably the extent boundary is at block 6059.
Multiblock
> reads don't cross extent boundaries.
>
> So, probably your query acquired block 6041 with FTS and by the time it
> needed next block, the blocks were already out of buffer cache (LRU list)
> and another multiblock read was needed. Maybe you should look at keep
buffer
> pool, but since the time interval was long, it might not be necessary
> anyway...
>
> Tanel.
> - Original Message -
> From: "Henry Poras" <[EMAIL PROTECTED]>
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Thursday, July 24, 2003 6:04 PM
> Subject: RE: Timestamps in trace files (and other trace file oddities)
>
>
> > (Tried sending this yesterday. I'll try again)
> >
> > Dan,
> > I was running a 10046 (level 12) trace on an awful piece of PeopleSoft
SQL
> > today and got some really odd results in my trace file (8.1.7).
> >
> > *** 2003-07-23 15:40:59.149
> > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
> > *** 2003-07-23 15:46:06.340
> > WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
> > *** 2003-07-23 15:47:53.851
> > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
> >
> > Two things struck me (three if it takes me too long to write this and I
> get
> > home late). First, the timestamps show an elapsed time of ~7 minutes,
but
> > the trace file has ela=1 (one onehundredth of a second). The 7 minutes
is
> > closer to reality. Huh???
> >
> > Secondly, the first scattered read reads 18 blocks starting at 6041. Why
> > does the next scattered read start at block# 6042?
> >
> > Any ideas?
> >
> > Henry
> >
> >
> >
> > -Original Message-
> > Daniel Fink
> > Sent: Wednesday, July 23, 2003 11:04 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Gudmundur,
> > Where is this documented (so I can RTFM)?
> > The one piece of this I don't quite understand is that the timestamp is
> not
> > emitted twice in a row. If the long time is the triggering event, why do
I
> > see a gap of 90 minutes (in another trace file)?
> >
> > Daniel
> >
> > Gudmundur Bjarni Josepsson wrote:
> > >
> > > Daniel,
> > >
> > > Perhaps someone else can explain this better but the documentation
I've
> > > got on this says that the Oracle kernel emits timestamps when a long
> > > time has elapsed since the last line was emitted to the trace file.
> > > Long time is defined as tens of seconds.
> > >
> > > Gudmundur
> > >
> > > > -Original Message-
> > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > > > Behalf Of Daniel Fink
> > > > Sent: 22. júlí 2003 21:19
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: Timestamps in trace files
> > > >
> > > >
> > > > I was perusing a 10046 trace file and I noticed that
> > > > timestamps are written to the trace file. Sometimes they were
> > > > very regular (3 minutes apart give or take 30 seconds) while
> > > > other times they were hours apart. I have noticed that two
> > > > timestamps are never written without any intervening
> > > > activity. Anyone have any idea on the reasoning behind the
> > > > timestamps and the 'triggering event'?
> > > >
> > > > Daniel
> > > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Gudmundur Bjarni Josepsson
> > >   INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > > San Diego, California-- Mailing list and web hosting services
> > > -
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (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: Henry Poras
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > ---

RE: Timestamps in trace files (and other trace file oddities)

It's been about a decade that I have been working with AIX. There must be 
tools that give you a breakdown by cpu. Else you can do a ps -ef or ps aux 
and watch your process. If I'mm right you should see it consuming 99% cpu 
all the time.

I have been looking after Peoplesoft systems for several years and I know 
those cascading NLs. They can drive up logical reads and cpu usage to 
astronomical heights. Do you by any chance have optimizer_index_cost_adj or 
optimize_index_caching changed from their defaults? Care to send me the sql 
and a 10053 trace of the explain? A warning though. I will be out camping 
the next three days, so the earliest I will be able to look at it is Monday 
(july 28th).

At 01:54 PM 7/24/2003 -0800, you wrote:
Wolfgang,
There are 4 cpu's, and file# 65 block# 6041 is from the driving 
table of
the 5 table join (all NL joins). I will take a look at v$bh to see what
blocks from the other tables are in memory next time I run this. Aside from
this indirect approach, any other suggestions on confirming your plausible
hypothesis? Is there a way to breakdown the workload of individual cpu's?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 and QA

2003-07-24 Thread DENNIS WILLIAMS

Gabriel
   QA is a really broad subject. How this applies to Oracle will vary a lot,
depending on how your site implements QA. For some sites, this means test,
staging, and production databases. Other sites would be happy if they just
had a test system. If you don't have any more to go on, you could surf
Google. Typing Oracle and QA yielded 83,600 sites. If you added other terms
specific to your site, you could trim that down to a reasonable number.

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


-Original Message-
Sent: Thursday, July 24, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


Hi guys, I'm looking for some orientation about
Quality Assurance for Oracle and databases, anyone has
experience with this? This issue about QA is
completely new to me, I know the OFA document and how
to aplly it, but don't know what else to do, maybe
something about QA for SQL statements?

Comments are welcome.

Thanks
Gabriel Aragon

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gabriel Aragon
  INET: [EMAIL PROTECTED]

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

Wolfgang,
There are 4 cpu's, and file# 65 block# 6041 is from the driving table of
the 5 table join (all NL joins). I will take a look at v$bh to see what
blocks from the other tables are in memory next time I run this. Aside from
this indirect approach, any other suggestions on confirming your plausible
hypothesis? Is there a way to breakdown the workload of individual cpu's?

Thanks.

Henry


-Original Message-
Wolfgang Breitling
Sent: Thursday, July 24, 2003 5:19 PM
To: Multiple recipients of list ORACLE-L


20% idle = 80% busy? How many cpus?

If it is 20% idle that should lay to rest the theory that  the process may
wait for a cpu. But, if it is a 5-way join, maybe it is reading a set of
blocks from file 65 and then collects the corresponding rows from the other
4 tables, which must be all pure logical IO since there are no further
waits. But if you have a few nested NL joins, they can easily churn through
millions of LIOs taking minutes of cpu. And if you have multiple cpus, only
one of them would be 100% busy during that time, leaving some overall idle
%. Since the corresponding rows of the other tables get heavily referenced,
they stay virtually "pinned" in the buffer ( in quotes because pinned in
the buffer pool context has a different meaning than the ususal connotation
) offering an explanation why the already read 17 blocks need to be re-read
once the session is done with block 6041 and wants to move on to block 6042.

At 12:34 PM 7/24/2003 -0800, you wrote:
>Dan,
> No reason to stay in the corner (unless that's where the fridge
> is). I'm
>trying to decide where to look on the OS. When I looked at vmstat, there
was
>no paging, and ~20% idle cpu. Maybe set up a cron to take snapshots of
>something(??) every few minutes? Support from the Sys Admin side is
minimal,
>so I am kind of on my own. I also have more experience with Sun than AIX.
>
>Henry
>
>
>-Original Message-
>Daniel Fink
>Sent: Thursday, July 24, 2003 4:14 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Henry,
> I completely agree, something did not feel right (hence my obvious
>reluctance). I did a very poor job of trying to explain that we cannot
>extrapolate system waits/activity by looking at database waits/activity. I
>should have learned my lesson (bad dba...go sit in the corner) from a long
>email discussion with another list member where I was trying to understand
a
>session issue by examining a single processes trace file.
>
> Wolgang's answer really nails the issue. What I think it comes
> down to is
>that wait events are only valid for what Oracle is waiting for internally.
>It cannot see what is happening at the system level. Some of the system
>activity it can determine by the call it is making. For example, when a
>request is sent to disk, it knows that it is waiting for the data to be
>returned. However, it does not know what is consuming the wait time. It
>could be that the disk service time is high or that there are
>so many requests for the disk that they are being queued up. All the
process
>knows is that the data has not been returned. As Wolfgang illustrated, the
>same thing happens at the CPU. I doubt that all 7 minutes were CPU, most
>likely there was a lot of intervening activity. All Oracle know was that it
>was not waiting on something to happen internally.
>
> I hope I've done better the second time around.
>
>Dan
>
>Henry Poras wrote:
> >
> > Dan,
> > I see what you are saying but something doesn't feel right (see,
>say, feel.
> > Should be some pun in here about not making sense). CPU time of 7
minutes
> > after a single read??? This is an ugly INSERT on a 5 table join, but
>that's
> > about it. Unfortunately I don't think there is a way to measure service
>time
> > at this granularity. Isn't it recorded in sesstat at the end of the
> > transactions? Guess I'll go and double check that.
> > Also, I remember an old paper by Craig Shallahamer that shows
you
>don't get
> > a full multi_block_read_count if a block is already in buffer cache
(e.g.
> > mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is already
in
> > memory. The cached block is already reflected in the trace numbers.)
> > There is something very strange hear but I don't know what.
Might
>be an
> > interesting case to track db/os relationships (running on AIX 4.3)
> >
> > Henry
> >
> > -Original Message-
> > Daniel Fink
> > Sent: Thursday, July 24, 2003 11:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Henry,
> > I'll make an attempt, but I am still learning a great deal about
>wait
> > events and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang,
>et.al.
> > are better authorities, so any corrections are very welcome.
> > The time between waits is the elapsed time. If we equate elapsed
>time to
> > response time, we must look at the two components of RT, wait time and
> > service time. If the time differential

RE: is it ever a good idea to multiplex the system tablespace?

2003-07-24 Thread DENNIS WILLIAMS

Ryan - The only time I did was when I needed to expand the SYSTEM tablespace
because it was too small. Normally you can just expand the datafile. For
some reason I couldn't do that, it was awhile ago, probably the Oracle
version. Creating another data file went just fine. 
The more important issue is keeping stuff out of the SYSTEM tablespace
so it doesn't grow. Watch very carefully any objects residing in the SYSTEM
tablespace that aren't owned by SYS. Make sure nobody is using SYSTEM as
their temp or default tablespace, etc. The problem is that shrinking the
SYSTEM tablespace is very hard to do.

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



-Original Message-
Sent: Thursday, July 24, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L


Would you ever want to use multiple data files for the system tablespace? 
-- 
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: Timestamps in trace files (and other trace file oddities)

20% idle = 80% busy? How many cpus?

If it is 20% idle that should lay to rest the theory that  the process may 
wait for a cpu. But, if it is a 5-way join, maybe it is reading a set of 
blocks from file 65 and then collects the corresponding rows from the other 
4 tables, which must be all pure logical IO since there are no further 
waits. But if you have a few nested NL joins, they can easily churn through 
millions of LIOs taking minutes of cpu. And if you have multiple cpus, only 
one of them would be 100% busy during that time, leaving some overall idle 
%. Since the corresponding rows of the other tables get heavily referenced, 
they stay virtually "pinned" in the buffer ( in quotes because pinned in 
the buffer pool context has a different meaning than the ususal connotation 
) offering an explanation why the already read 17 blocks need to be re-read 
once the session is done with block 6041 and wants to move on to block 6042.

At 12:34 PM 7/24/2003 -0800, you wrote:
Dan,
No reason to stay in the corner (unless that's where the fridge 
is). I'm
trying to decide where to look on the OS. When I looked at vmstat, there was
no paging, and ~20% idle cpu. Maybe set up a cron to take snapshots of
something(??) every few minutes? Support from the Sys Admin side is minimal,
so I am kind of on my own. I also have more experience with Sun than AIX.

Henry

-Original Message-
Daniel Fink
Sent: Thursday, July 24, 2003 4:14 PM
To: Multiple recipients of list ORACLE-L
Henry,
I completely agree, something did not feel right (hence my obvious
reluctance). I did a very poor job of trying to explain that we cannot
extrapolate system waits/activity by looking at database waits/activity. I
should have learned my lesson (bad dba...go sit in the corner) from a long
email discussion with another list member where I was trying to understand a
session issue by examining a single processes trace file.
Wolgang's answer really nails the issue. What I think it comes 
down to is
that wait events are only valid for what Oracle is waiting for internally.
It cannot see what is happening at the system level. Some of the system
activity it can determine by the call it is making. For example, when a
request is sent to disk, it knows that it is waiting for the data to be
returned. However, it does not know what is consuming the wait time. It
could be that the disk service time is high or that there are
so many requests for the disk that they are being queued up. All the process
knows is that the data has not been returned. As Wolfgang illustrated, the
same thing happens at the CPU. I doubt that all 7 minutes were CPU, most
likely there was a lot of intervening activity. All Oracle know was that it
was not waiting on something to happen internally.

I hope I've done better the second time around.

Dan

Henry Poras wrote:
>
> Dan,
> I see what you are saying but something doesn't feel right (see,
say, feel.
> Should be some pun in here about not making sense). CPU time of 7 minutes
> after a single read??? This is an ugly INSERT on a 5 table join, but
that's
> about it. Unfortunately I don't think there is a way to measure service
time
> at this granularity. Isn't it recorded in sesstat at the end of the
> transactions? Guess I'll go and double check that.
> Also, I remember an old paper by Craig Shallahamer that shows you
don't get
> a full multi_block_read_count if a block is already in buffer cache (e.g.
> mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is already in
> memory. The cached block is already reflected in the trace numbers.)
> There is something very strange hear but I don't know what. Might
be an
> interesting case to track db/os relationships (running on AIX 4.3)
>
> Henry
>
> -Original Message-
> Daniel Fink
> Sent: Thursday, July 24, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
> Henry,
> I'll make an attempt, but I am still learning a great deal about
wait
> events and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang,
et.al.
> are better authorities, so any corrections are very welcome.
> The time between waits is the elapsed time. If we equate elapsed
time to
> response time, we must look at the two components of RT, wait time and
> service time. If the time differential is 7 minutes, x minutes is service
> time and y minutes is wait time. It is possible that only .01 seconds was
> wait time, while the remaining 6:59:99 was service time (i.e. cpu)
> I can't explain the scattered read issue. However, it is
interesting to
> note that the # of blocks to be read is offset by 1 in the second wait
> event. Perhaps, the read request was issued, but the 65.6041 block was
> already in memory, so the read request was reissued minus that block. I'm
> not sure how the latch free enters into the picture, but I have a feeling
> that this wait is integral in the sequence of events.
>
> Dan
>
--
Please see

V$SESSSTAT stat 3 vs V$OPEN_CURSOR

2003-07-24 Thread Jesse, Rich

Hey all,

We recently had a problem with a 3rd-party app getting "ORA-1000 max open
cursors exceeded" on their 8.1.7.4 DB.  Since OPEN_CURSORS is set to 500 in
the init.ora -- should be more than generous for a tiny app on a tiny DB --
we started looking into how many cursors the app actually has open at any
given time.

While investigating this, I see that the number of rows in V$OPEN_CURSORS
isn't consistent with the value of stat 3 ("opened cursors current") of
V$SESSSTAT.  Of the two processes I looked at, each had a V$SESSSTAT value
of "3" for stat 3, while the first had a single entry in V$OPEN_CURSOR and
the second had four.

Should these values match?  I looked on Metalink but was unable to find any
reference to the relationship between these, other than a forum article
where the OraSupport person was extraordinarily unhelpful.

TIA,
Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

RE: Timestamps in trace files (and other trace file oddities)

Dan,
No reason to stay in the corner (unless that's where the fridge is). I'm
trying to decide where to look on the OS. When I looked at vmstat, there was
no paging, and ~20% idle cpu. Maybe set up a cron to take snapshots of
something(??) every few minutes? Support from the Sys Admin side is minimal,
so I am kind of on my own. I also have more experience with Sun than AIX.

Henry

-Original Message-
Daniel Fink
Sent: Thursday, July 24, 2003 4:14 PM
To: Multiple recipients of list ORACLE-L

Henry,
I completely agree, something did not feel right (hence my obvious
reluctance). I did a very poor job of trying to explain that we cannot
extrapolate system waits/activity by looking at database waits/activity. I
should have learned my lesson (bad dba...go sit in the corner) from a long
email discussion with another list member where I was trying to understand a
session issue by examining a single processes trace file.

Wolgang's answer really nails the issue. What I think it comes down to is
that wait events are only valid for what Oracle is waiting for internally.
It cannot see what is happening at the system level. Some of the system
activity it can determine by the call it is making. For example, when a
request is sent to disk, it knows that it is waiting for the data to be
returned. However, it does not know what is consuming the wait time. It
could be that the disk service time is high or that there are
so many requests for the disk that they are being queued up. All the process
knows is that the data has not been returned. As Wolfgang illustrated, the
same thing happens at the CPU. I doubt that all 7 minutes were CPU, most
likely there was a lot of intervening activity. All Oracle know was that it
was not waiting on something to happen internally.

I hope I've done better the second time around.

Dan

Henry Poras wrote:
>
> Dan,
> I see what you are saying but something doesn't feel right (see,
say, feel.
> Should be some pun in here about not making sense). CPU time of 7 minutes
> after a single read??? This is an ugly INSERT on a 5 table join, but
that's
> about it. Unfortunately I don't think there is a way to measure service
time
> at this granularity. Isn't it recorded in sesstat at the end of the
> transactions? Guess I'll go and double check that.
> Also, I remember an old paper by Craig Shallahamer that shows you
don't get
> a full multi_block_read_count if a block is already in buffer cache (e.g.
> mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is already in
> memory. The cached block is already reflected in the trace numbers.)
> There is something very strange hear but I don't know what. Might
be an
> interesting case to track db/os relationships (running on AIX 4.3)
>
> Henry
>
> -Original Message-
> Daniel Fink
> Sent: Thursday, July 24, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
> Henry,
> I'll make an attempt, but I am still learning a great deal about
wait
> events and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang,
et.al.
> are better authorities, so any corrections are very welcome.
> The time between waits is the elapsed time. If we equate elapsed
time to
> response time, we must look at the two components of RT, wait time and
> service time. If the time differential is 7 minutes, x minutes is service
> time and y minutes is wait time. It is possible that only .01 seconds was
> wait time, while the remaining 6:59:99 was service time (i.e. cpu)
> I can't explain the scattered read issue. However, it is
interesting to
> note that the # of blocks to be read is offset by 1 in the second wait
> event. Perhaps, the read request was issued, but the 65.6041 block was
> already in memory, so the read request was reissued minus that block. I'm
> not sure how the latch free enters into the picture, but I have a feeling
> that this wait is integral in the sequence of events.
>
> Dan
>

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

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

2003-07-24 Thread Weaver, Walt

Title: bad press for oracle









Hasn’t he already settled once with
SCO? I read somewhere about SCO buying the license rights to some obscure
version of DOS and then suing Microsoft. There was some kind of settlement
made.

 

--Walt Weaver

  Bozeman, Montana

 



-Original Message-
From: Goulet, Dick
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 24, 2003 2:19
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: bad press for
oracle

 



What the heck, why not.  Then Mr
Gates can figure out what to do about SCO.  Of cource, buy them as well!!!





 



Dick
Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 



-Original Message-
From: Gogala, Mladen
[mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 2:54
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: bad press for
oracle



Microsoft is buying IBM?





 



Mladen
Gogala 
Oracle
DBA 
Phone:(203)
459-6855 
Email:[EMAIL PROTECTED]




-Original Message-
From: Goulet, Dick
[mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 2:29
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: bad press for
oracle



Lets see, if memory is serving, Dial is
signing an IT outsourcing deal with EDS.  EDS & Oracle are NOT friends. 
EDS & IBM are.  Oracle apps do not run on DB2, SAP does.  SAP
& EDS are friends.  Do we see a connection there??





 



Dick
Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 



-Original Message-
From: Chris Stephens
[mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 12:50 PM
To: Multiple recipients of list
ORACLE-L
Subject: bad press for oracle

http://rss.com.com/2110-1017_3-5053453.html?type=pt&part=rss&tag=feed&subj=news

RE: bad press for oracle....

2003-07-24 Thread Goulet, Dick

Title: bad press for oracle



What 
the heck, why not.  Then Mr Gates can figure out what to do about 
SCO.  Of cource, buy them as well!!!
 
Dick GouletSenior Oracle DBAOracle Certified 8i 
DBA 

  -Original Message-From: Gogala, Mladen 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 2:54 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  bad press for oracle
  Microsoft is buying IBM?
   
  Mladen Gogala Oracle DBA Phone:(203) 
  459-6855 Email:[EMAIL PROTECTED] 
  
-Original Message-From: Goulet, Dick 
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 2:29 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
bad press for oracle
Lets see, if memory is serving, Dial is signing an IT outsourcing 
deal with EDS.  EDS & Oracle are NOT friends.  EDS & IBM 
are.  Oracle apps do not run on DB2, SAP does.  SAP & EDS are 
friends.  Do we see a connection there??
 
Dick GouletSenior Oracle DBAOracle Certified 8i 
DBA 

  -Original Message-From: Chris Stephens 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 
  12:50 PMTo: Multiple recipients of list 
  ORACLE-LSubject: bad press for oracle
  http://rss.com.com/2110-1017_3-5053453.html?type=pt&part=rss&tag=feed&subj=news

Re: Oracle and QA

2003-07-24 Thread Pete Finnigan

Hi Gabriel,

for the database or applications as well?, 

a suggestion - you could do worse than follow Steven Feuersteins
standards for PL/SQL - see the many books he has written on PL/SQL for
O'Reilly.

hth

kind regards

Pete

-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

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

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

2003-07-24 Thread Daniel Fink

Henry,
I completely agree, something did not feel right (hence my obvious 
reluctance). I did a very poor job of trying to explain that we cannot extrapolate 
system waits/activity by looking at database waits/activity. I should have learned my 
lesson (bad dba...go sit in the corner) from a long email discussion with another list 
member where I was trying to understand a session issue by examining a single 
processes trace file.

Wolgang's answer really nails the issue. What I think it comes down to is that 
wait events are only valid for what Oracle is waiting for internally. It cannot see 
what is happening at the system level. Some of the system activity it can determine by 
the call it is making. For example, when a request is sent to disk, it knows that it 
is waiting for the data to be returned. However, it does not know what is consuming 
the wait time. It could be that the disk service time is high or that there are
so many requests for the disk that they are being queued up. All the process knows is 
that the data has not been returned. As Wolfgang illustrated, the same thing happens 
at the CPU. I doubt that all 7 minutes were CPU, most likely there was a lot of 
intervening activity. All Oracle know was that it was not waiting on something to 
happen internally.

I hope I've done better the second time around.

Dan

Henry Poras wrote:
> 
> Dan,
> I see what you are saying but something doesn't feel right (see, say, feel.
> Should be some pun in here about not making sense). CPU time of 7 minutes
> after a single read??? This is an ugly INSERT on a 5 table join, but that's
> about it. Unfortunately I don't think there is a way to measure service time
> at this granularity. Isn't it recorded in sesstat at the end of the
> transactions? Guess I'll go and double check that.
> Also, I remember an old paper by Craig Shallahamer that shows you don't get
> a full multi_block_read_count if a block is already in buffer cache (e.g.
> mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is already in
> memory. The cached block is already reflected in the trace numbers.)
> There is something very strange hear but I don't know what. Might be an
> interesting case to track db/os relationships (running on AIX 4.3)
> 
> Henry
> 
> -Original Message-
> Daniel Fink
> Sent: Thursday, July 24, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
> 
> Henry,
> I'll make an attempt, but I am still learning a great deal about wait
> events and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang, et.al.
> are better authorities, so any corrections are very welcome.
> The time between waits is the elapsed time. If we equate elapsed time to
> response time, we must look at the two components of RT, wait time and
> service time. If the time differential is 7 minutes, x minutes is service
> time and y minutes is wait time. It is possible that only .01 seconds was
> wait time, while the remaining 6:59:99 was service time (i.e. cpu)
> I can't explain the scattered read issue. However, it is interesting to
> note that the # of blocks to be read is offset by 1 in the second wait
> event. Perhaps, the read request was issued, but the 65.6041 block was
> already in memory, so the read request was reissued minus that block. I'm
> not sure how the latch free enters into the picture, but I have a feeling
> that this wait is integral in the sequence of events.
> 
> Dan
>begin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

Re: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread JApplewhite


Jonathon,

I've got a query for you that uses all 3 set operators at once!

I wrote it to compare two different versions of our 3rd Party Student
Information System (SASI) in two different databases.  We were getting
ready to upgrade Production, having already upgraded a Test instance.  The
query hit the local schema, as well as the remote schema across a DB Link.
The results of this query and a couple of others that showed brand-new
tables/columns and dropped tables/columns helped our programmers figure out
which of their reports, etc. needed modifications.

I was impressed at performance, considering it queried across a DB Link,
but mainly because this horrendous mess of an application has over 50,000
tables (User_Tab_Columns has over 1.4 million rows!).  One of the DBs is on
HP-UX, but the Production DB is on Win2k.

Anyway, hope this is interesting enough. ;-)

BTW, if you can find a way to improve it, please let me know.  I sort of
"threw it together", knowing it would be a one-time thing, so it could
probably be made better with some expert critique.

/* Get a list of columns that have changed from SASI 4.5 to 5.0
   for tables that are present in both versions only for the
   current school year.  List only the first 4 characters of
   the table names, since all campuses will be the same.
*/

Spool SASI_45_50_Table_Compare.txt

(
Select Substr(TABLE_NAME,1,4)  "Table"  -- New 5.0 Columns
  ,COLUMN_NAME  "Column"
  ,'5.0'"Ver"
  ,DATA_TYPE"DType"
  ,DATA_LENGTH  "DLn"
  ,DATA_PRECISION   "DPr"
  ,DATA_SCALE   "DSc"
  ,NULLABLE "N?"
>From   User_Tab_Columns
Where  SubStr(Table_Name,5,1)  = '2'
AndSubStr(Table_Name,6,1) <> 'D'
AndTable_Name In
(
 Select Table_Name  --...for Tables in both 4.5 and 5.0
 From   User_Tables
 Intersect
 Select Table_Name
 From   [EMAIL PROTECTED]
)
Minus   --...remove unchanged columns
 (
  Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'5.0'  -- Constant allows Minus to work
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
  From   User_Tab_Columns
  Where  SubStr(Table_Name,5,1)  = '2'
  AndSubStr(Table_Name,6,1) <> 'D'
  Intersect
  Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'5.0'  -- Constant allows Minus to work
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
  From   [EMAIL PROTECTED]
  Where  SubStr(Table_Name,5,1)  = '2'
  AndSubStr(Table_Name,6,1) <> 'D'
 )
)
Union
(
Select Substr(TABLE_NAME,1,4) "Table"-- Old 4.5 columns...
  ,COLUMN_NAME  "Column"
  ,'4.5'"Ver"
  ,DATA_TYPE"DType"
  ,DATA_LENGTH  "DLn"
  ,DATA_PRECISION   "DPr"
  ,DATA_SCALE   "DSc"
  ,NULLABLE "N?"
>From   [EMAIL PROTECTED]
Where  SubStr(Table_Name,5,1)  = '2'
AndSubStr(Table_Name,6,1) <> 'D'
AndTable_Name In
(
 Select Table_Name
 From   User_Tables
 Intersect
 Select Table_Name
 From   [EMAIL PROTECTED]
)
Minus
 (
  Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'4.5'
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
  From   User_Tab_Columns
  Where  SubStr(Table_Name,5,1)  = '2'
  AndSubStr(Table_Name,6,1) <> 'D'
  Intersect
  Select Substr(TABLE_NAME,1,4)
,COLUMN_NAME
,'4.5'
,DATA_TYPE
,DATA_LENGTH
,DATA_PRECISION
,DATA_SCALE
,NULLABLE
  From   [EMAIL PROTECTED]
  Where  SubStr(Table_Name,5,1)  = '2'
  AndSubStr(Table_Name,6,1) <> 'D'
 )
)
/

Spool Off


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



   
  
  Jonathan Gennick 
  
  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]> 
  .com>cc: 
  
  Sent by: Subject:  Union  quries: INTERSECT, 
MINUS, etc
  [EMAIL PROTECTED]
  
  .com

RE: bad press for oracle....

2003-07-24 Thread Gogala, Mladen

Title: bad press for oracle



Microsoft is buying IBM?
 
Mladen Gogala Oracle DBA Phone:(203) 459-6855 
Email:[EMAIL PROTECTED] 

  -Original Message-From: Goulet, Dick 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 2:29 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  bad press for oracle
  Lets 
  see, if memory is serving, Dial is signing an IT outsourcing deal with 
  EDS.  EDS & Oracle are NOT friends.  EDS & IBM are.  
  Oracle apps do not run on DB2, SAP does.  SAP & EDS are 
  friends.  Do we see a connection there??
   
  Dick GouletSenior Oracle DBAOracle Certified 8i 
  DBA 
  
-Original Message-From: Chris Stephens 
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 
12:50 PMTo: Multiple recipients of list 
ORACLE-LSubject: bad press for oracle
http://rss.com.com/2110-1017_3-5053453.html?type=pt&part=rss&tag=feed&subj=news

RE: JDBC application causes intermittent high load and latch wait

2003-07-24 Thread JayMiller

Don't really know.  I'll pass this on to the developer and the main DBA on
that box.  Thanks!

Jay Miller
Sr. Oracle DBA
x68355


-Original Message-
Sent: Wednesday, July 23, 2003 6:39 PM
To: Multiple recipients of list ORACLE-L

Jay - In Java parlance, bind variables == PreparedStatement. Is it possible
that the Web server received a bunch of requests and flooded your DB?



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

-Original Message-
Sent: Wednesday, July 23, 2003 5:15 PM
To: Multiple recipients of list ORACLE-L



Oracle 8.1.5.1

Solaris 2.6

 

Hi there,

 

For the last few days one of our servers will suddenly experience
ridiculously high load averages (50-65) and all applications grind to a
halt.

 

Restarting the database solves the problem and shutting down a specific apps
servers solves the problem.  This apps server generally has about 2-3 JDBC
connections to the database.  I just started looking at it today (it's not
one of my databases) and when it recurred I started checking wait events.

 

The top 10 wait events for about a 2 minute period were as follows:

 

EVENTDelta Waits  Delta Timeouts 



latch free  317376  185591


SQL*Net message from client  66435   0 

SQL*Net message to client  66419   0 

db file sequential read   21709   0 

SQL*Net more data to client18066   0 

file open   12230  

rdbms ipc message   576 170 

log file sync   519 1  

log file parallel write  374 0  

log file sequential read   343 0  

 

 

It looks like a latch contention problem.  By the time I drilled down to
specific sessions that were experiencing the "latch free" wait the apps
server had been restarted.  My query showed most waiting on the "process
allocation" latch but I don't know for sure that this was the latch that was
causing the problem during the high load period.

 

I prepared this query to run the next time the problem occurs in order to
identify which latches are being waited on:

 

select s.sid,s.username,s.program,s.status,

se.event,se.total_waits,se.total_timeouts,

se.time_waited,se.average_wait,sw.p2,v.name

from v$session s, v$session_event se, v$session_wait sw,v$latch v

 where s.sid=se.sid

 and s.sid=sw.sid

 and sw.p2=l.latch#

 and s.status='ACTIVE'

 AND S.USERNAME IS NOT NULL

 and se.event='latch free';

 

Does this make sense?  Any suggestions for other things to check?  Some
reading suggests that latch contention is often caused by not using bind
variables and that increasing db_block_lru_latches (currently = 2) can
alleviate the problem while the code is being fixed.

 

But the question is why this problem suddenly happens and the problem
doesn't go away until the apps server is shut down.  The old version of the
application (which didn't use JDBC) did not have this problem.  I'm
theorizing that perhaps JDBC handles its connections differently and after
waiting for some period of time resends the transaction and keeps resending
it faster than Oracle can clean things up?   

 

Has anyone experienced a similar problem or have other suggestions of where
to go from here?

 

 

Thanks,

Jay Miller

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

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

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

Anybody using Silverrun ?

2003-07-24 Thread Stephane Paquette

Hi,

We're using Silverrun data modeling tool (RDM).
I know that the Enterprise version has a central repository that allow to
share the data models and other component.

I'm used to Oracle Designer and Sybase PowerDesiger and liked those
products.

To the user of Silverrun Enterprise version, what are your feedbacks about
it ?

Thanks


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



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

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

RE: bad press for oracle....

2003-07-24 Thread Goulet, Dick

Title: bad press for oracle



Lets 
see, if memory is serving, Dial is signing an IT outsourcing deal with 
EDS.  EDS & Oracle are NOT friends.  EDS & IBM are.  
Oracle apps do not run on DB2, SAP does.  SAP & EDS are friends.  
Do we see a connection there??
 
Dick GouletSenior Oracle DBAOracle Certified 8i 
DBA 

  -Original Message-From: Chris Stephens 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 
  12:50 PMTo: Multiple recipients of list ORACLE-LSubject: 
  bad press for oracle
  http://rss.com.com/2110-1017_3-5053453.html?type=pt&part=rss&tag=feed&subj=news

Does mts affect explain plans of queries

2003-07-24 Thread Rodrigues, Bryan

Hi everyone,

I have a development, test and production databases (They are on HP-UX 11.0
with Oracle 8.1.7.4). The development and test databases are setup for Multi
Threaded Server (MTS) and production is not (We are still considering
whether or not to put it on production, another story). But during the
normal day, code is built, changed and tuned. I don't have any particular
example, but some of the code that works fine in development and test works
very differently in production. The data that exists in development and test
is comparable to what is in production. The only difference I see is that
development and test are using MTS and production is not.

The questions I see are: Would mts affect explain plans of queries to such a
degree that they would run differently in a non MTS database? Does the
optimizer care whether mts is being used?

TIA,

Bryan Rodrigues
DBA
Elcom, Inc.


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

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

2003-07-24 Thread Michael Boligan





I agree sqlnet.expire_time does only effect the server, but normally when the
server kills the connection the client side will drop.  But in this case it
won't because there is no network for the client to get the dead connection
timeout from the server.  It sounds like Hemant is running OPS which AFAIK
(version 7.3) he is getting the expected behavior, to get the client to
seemlessly switch over to the other instance he needs RAC (again AFAIK).




   

  "Tanel Poder"

 
  mail.ee> cc: 

  Sent by: Subject:  Re: Does the client "hang" or 
"disconnect" when the server's  
  [EMAIL PROTECTED]

  .com 

   

   

  07/24/2003 12:19 

  PM   

  Please respond to

  ORACLE-L 

   

   





Hi!

This sqlnet.expire_time affects server, not sqlplus itself AFAIK. The issue
is likely, that sqlplus has sent a sqlnet request to server, but hasn't got
any response since you pulled the network cable. Thus it'll wait until TCP
connection gets timeout.
Also, sqlplus is different from normal telnet, because it tries to send a
request only when it is on it's prompt, thus issuing a command in sqlplus
session, when it is waiting for response from previous command, doesn't send
any network packets.

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 6:54 PM


>
>
>
>
> Hemant,
>
>   You are right it does get a timeout from the TCP stack, but the
default
> for the connection timeout is 30 minutes.  There is a sqlnet parameter
where you
> can modify the timeout to make it shorter (sqlnet.expire_time?)  Check
metalink
> note 151972.1 for more info.
>
> HTH
> Mike
>
>
>
>
>   Hemant K Chitale
>   <[EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
>   .com.sg> cc:
>   Sent by: Subject:  Does the client
"hang" or "disconnect" when the server's
>   [EMAIL PROTECTED]
>   .com

>
>
>   07/24/2003 11:24
>   AM
>   Please respond to
>   ORACLE-L
>
>
>
>
>
>
>
>
> I'm sorry, but I really can't remember how an Oracle SQLPlus client is
supposed
> to behave when the database server's network interface is "pulled out".
> We were testing a new Sun Cluster and tests for Storage FC Path Failure,
> Instance Failure,
> Node Failure, HeartBeat failure and single Network Interface failure all
> went through.
>
> However, when the engineer pulled out *both* network interfaces of the
running
> database server node, the SQLPlus client that had been running INSERT
> statements
> just "hung".  It did not exit or error out till we killed the job.  At the
> next restart, of course,
> it connected to the database which had already failed-over to the second
node.
>
> The question is : Why does the SQLPlus client "hang" ?  Telnet sessions
> also might
> "hang" [couldnt' test this out as the Servers are Secured and we didn't
> have SSH on the
> client], till the user hits the ENTER key, but shouldn't SQLPlus get
server
> disconnect
> from the TCP stack ?
>
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> My personal web site is :  http://hkchi

is it ever a good idea to multiplex the system tablespace?

2003-07-24 Thread rgaffuri

Would you ever want to use multiple data files for the system tablespace? 
Title: Computing resource consumption





Does anyone know any papers or techniques to compute resource consumption by users in a DB systems? This may or may not be for computing charge-back to the client, but my questions are 

1. What do you compute?
2. are there any standard methods and or standard formula?


I am collecting v$sesstat when a session exits, but is data alone from session stats sufficient? How about the work done by background processes on user's behalf? 

Do you do anything like this at your workplace? This is something that might be coming down the line, so I have been asked to start looking for related stuff.

Thanks in advance
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

RE: Millsap and Timestamps in trace files

2003-07-24 Thread Johnson, Michael

Fair enough then.   Good luck with it.

-Original Message-
Sent: Thursday, July 24, 2003 1:54 AM
To: Multiple recipients of list ORACLE-L


Thanks for the suggestion, Mike.  I had hoped that the smiley had indicated
that this was put forward in a (supposedly)  humorous way.  I had the great
pleasure to spend some time with Cary during his stay in Iceland and I would
not have posted my remark if I thought there was any danger of him being
offended by it.

But again, thanks for the reminder.

Gudmundur

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 12:39 AM


> Just lurking today 
>
> I suspect if you ask in a more gracious and pleasant manner you may get a
> response.
>
> fwiw, Mike
>
> -Original Message-
> Sent: Wednesday, July 23, 2003 2:10 PM
> To: Multiple recipients of list ORACLE-L
>
>
> In the draft of Cary Millsap's upcoming book.  You get a copy of it when
you
> attend the Hotsos Clinic 101.  Cary's supposed to be in DC right now
> teaching this course so I suggest he better make damn sure that he
explains
> this properly (both there and on ORACLE-L :)
>
> Gudmundur
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, July 23, 2003 3:04 PM
>
>
> Gudmundur,
> Where is this documented (so I can RTFM)?
> The one piece of this I don't quite understand is that the timestamp is
not
> emitted twice in a row. If the long time is the triggering event, why do I
> see a gap of 90 minutes (in another trace file)?
>
> Daniel
>
> Gudmundur Bjarni Josepsson wrote:
> >
> > Daniel,
> >
> > Perhaps someone else can explain this better but the documentation I've
> > got on this says that the Oracle kernel emits timestamps when a long
> > time has elapsed since the last line was emitted to the trace file.
> > Long time is defined as tens of seconds.
> >
> > Gudmundur
> >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > > Behalf Of Daniel Fink
> > > Sent: 22. júlí 2003 21:19
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Timestamps in trace files
> > >
> > >
> > > I was perusing a 10046 trace file and I noticed that
> > > timestamps are written to the trace file. Sometimes they were
> > > very regular (3 minutes apart give or take 30 seconds) while
> > > other times they were hours apart. I have noticed that two
> > > timestamps are never written without any intervening
> > > activity. Anyone have any idea on the reasoning behind the
> > > timestamps and the 'triggering event'?
> > >
> > > Daniel
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Gudmundur Bjarni Josepsson
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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: Gudmundur Josepsson
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Johnson, Michael
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Gudmundur Josepsson
  INET: [EMAIL PROTECTED]

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

Oracle and QA

2003-07-24 Thread Gabriel Aragon

Hi guys, I'm looking for some orientation about
Quality Assurance for Oracle and databases, anyone has
experience with this? This issue about QA is
completely new to me, I know the OFA document and how
to aplly it, but don't know what else to do, maybe
something about QA for SQL statements?

Comments are welcome.

Thanks
Gabriel Aragon

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gabriel Aragon
  INET: [EMAIL PROTECTED]

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

Dan,
I see what you are saying but something doesn't feel right (see, say, feel.
Should be some pun in here about not making sense). CPU time of 7 minutes
after a single read??? This is an ugly INSERT on a 5 table join, but that's
about it. Unfortunately I don't think there is a way to measure service time
at this granularity. Isn't it recorded in sesstat at the end of the
transactions? Guess I'll go and double check that.
Also, I remember an old paper by Craig Shallahamer that shows you don't get
a full multi_block_read_count if a block is already in buffer cache (e.g.
mbrc=8; p3=8,p3=8,p3=3,...The 3 is because the fourth block is already in
memory. The cached block is already reflected in the trace numbers.)
There is something very strange hear but I don't know what. Might be an
interesting case to track db/os relationships (running on AIX 4.3)

Henry

-Original Message-
Daniel Fink
Sent: Thursday, July 24, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L

Henry,
I'll make an attempt, but I am still learning a great deal about wait
events and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang, et.al.
are better authorities, so any corrections are very welcome.
The time between waits is the elapsed time. If we equate elapsed time to
response time, we must look at the two components of RT, wait time and
service time. If the time differential is 7 minutes, x minutes is service
time and y minutes is wait time. It is possible that only .01 seconds was
wait time, while the remaining 6:59:99 was service time (i.e. cpu)
I can't explain the scattered read issue. However, it is interesting to
note that the # of blocks to be read is offset by 1 in the second wait
event. Perhaps, the read request was issued, but the 65.6041 block was
already in memory, so the read request was reissued minus that block. I'm
not sure how the latch free enters into the picture, but I have a feeling
that this wait is integral in the sequence of events.

Dan

Henry Poras wrote:
>
> (Tried sending this yesterday. I'll try again)
>
> Dan,
> I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
> today and got some really odd results in my trace file (8.1.7).
>
> *** 2003-07-23 15:40:59.149
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
> *** 2003-07-23 15:46:06.340
> WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
> *** 2003-07-23 15:47:53.851
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
>
> Two things struck me (three if it takes me too long to write this and I
get
> home late). First, the timestamps show an elapsed time of ~7 minutes, but
> the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
> closer to reality. Huh???
>
> Secondly, the first scattered read reads 18 blocks starting at 6041. Why
> does the next scattered read start at block# 6042?
>
> Any ideas?
>
> Henry

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

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

2003-07-24 Thread Ron Thomas


>> and 7.3.4 clients won't talk to a
>> 9.2 database,

> False

This is sort-a false.  If the 9.2 database is in a new characterset such as UTF8, the 
old sqlnet
clients will have difficulties if the client machine is also not in UTF8.

Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan


   
   
  [EMAIL PROTECTED]
   
  E.COMTo:   [EMAIL PROTECTED] 

  Sent by: cc: 
   
  [EMAIL PROTECTED]Subject:  RE: backwards export  
   
  .com 
   
   
   
   
   
  07/24/2003 10:14 
   
  AM   
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   








> I'm not sure of the export compatibility, but you should know that 9i
> clients won't talk to a 7.3.4 database,


  True


> and 7.3.4 clients won't talk to a
> 9.2 database,

 False


  A Oracle7 client can, will, and does talk to a 9.2.0 database
with no problem.


> which could be a problem.  At least, they're not certified
> to talk to each other, which isn't quite the same thing.


  See note 172179.1 on Metalink.  To quote:


"In short, any version of SQL*Net's version 2 Client (2.3.x) and
up to Net 9.0.x, will be able to connect to any version of
Oracle Database version 7.3.x and up to 9.2.x, with certain
compatibility issues that are explained below"





Matt Adams

Matt Adams - GE Appliances - [EMAIL PROTECTED]
It will make sense when you stop thinking logically,
and start thinking Oracle-ly  -  Jim Droppa


-Original Message-
Sent: Wednesday, July 23, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L





I'm not sure of the export compatibility, but you should know that 9i
clients won't talk to a 7.3.4 database, and 7.3.4 clients won't talk to a
9.2 database, which could be a problem.  At least, they're not certified
to talk to each other, which isn't quite the same thing.


Cheers
Simon Anderson









Jeroen van Sluisdam <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
23/07/2003 12:49
Please respond to ORACLE-L



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





Hi,

We are investigating possibilities for migration of an oracle 7.3.4 to
oracle 9i. We want to separate our
production DB from the app-logic such that we can migrate from oracle
7.3.4 on hp-ux 10.20 to oracle 9i
on hp-ux 11. Problem is our development and test-environment because we
cannot separate the logic at the
same time here also from the db (costs). Does anyone have any experience
with exporting from oracle 9i
to oracle 7.3.4 ?

I found the underlying note on metalink about the same problem but with
oracle 8. I have limited experience on oracle8
and none on oracle 9. So I hope you can give some tips.
..
You need to run the 7.3 version of export against your 8.0 database via
sqlnet/net8. Beforehand, you need to run rdbms/admin/catexp7.sql against
your 8.0 database. This creates the 7.3 export views required. Note that
if you have used any 8.0 specific features (e.g. objects) these will not
be exported for obvious reasons.


Tia,

Jeroen





--
Please see the official ORACLE-L FAQ: http://www.or

RE: PL/SQL statement help

2003-07-24 Thread Shamita Singh

For the error: SP2-0158: unknown COLUMN option "line", "column total line" must be one word.. no spaces allowed. Can be total_line or just total.
 
Shamita 
 
Shamita "Nelson, Allan" <[EMAIL PROTECTED]> wrote:





quantity is neither a column name or a valid column alias.  That's what your error is telling you.  
 
Allan


-Original Message-From: Milton C. Craighead, Jr. [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL statement help
I'm having no luck resolving the following PL/SQL errors for a final exam class project. We do not have access to metalink nor do we have access to Oracle PL/SQL documentation. We are being taught from a PL/SQL text book that does not provide ora error message information or where to find them... Any and all assistance will be greatly appreciated. 
 
1) where am I going wrong
 
SQL> SELECT order#, customer#, address, city, state, zip,  2   orderdate, shipdate, shipstreet, shipcity, shipstate,  3   shipzip, item#, isbn, quantity  4  FROM customers NATURAL JOIN orders; shipzip, item#, isbn, quantity   *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 
2) I'm trying to format a report where the output should look like the following:
    I'm able to format the item#, isbn, quanty, retail, and total line with out error but Im getting an error message in my veiw...
 
 
Order#
Customer#
Customer Address
Customer City, State, Zip
 
Order Date
Ship Date
Ship Street
Ship City, Ship State, Ship Zip
 
ITEM#    ISBN        QUANTY        RETAIL    TOTAL LINE
ITEM#ISBN        QUANTY        RETAIL        TOTAL LINE
 
 
SET SERVEROUTPUT ONSQL> set linesize 300SQL> set pagesize 20SQL> ttitle CENTER 'The Law Report' SKIP 2SQL> btitle '(Confidential)' SKIP 2SQL> SQL> column item# heading 'ITEM#' format a10 truncateSQL> column isbn heading 'ISBN' format a15 truncateSQL> column quantity heading 'QUANTITY' format a6 truncateSQL> column retail heading 'RETAIL' format 990.00SQL> column total line heading 'TOTAL|LINE' format 990.00SP2-0158: unknown COLUMN option "line"SQL> SQL> Drop view bookPub;
 
View dropped.
 
SQL> Create view bookPub  2  AS select count(b.title) as bookNum,  b.cost, b.pubid  3  from books b natural join orderitems o  4  group by b.pubid, b.cost;
 
View created.
 
SQL> SQL> SQL> SQL> SQL> Drop view bookRev;
 
View dropped.
 
SQL> Create view  bookRev  2  As select count(b.title) as bkNum,  sum(b.retail - b.cost) as rev, o.customer#  3  from orderitems i natural join books b natural join orders o  4  group by o.customer#;
 
View created.
 
SQL> SQL> SQL> SQL> Drop view orderInfo;
 
View dropped.
 
SQL> Create view  orderInfo  2  As  3  Select  c.customer#, o.order#, c.lastname, c.firstname, c.address,  4  o.shipdate, o.shipstreet, o.shipcity, o.shipstate,  5  o.shipzip, i.item#, i.isbn, i.quantity  6  from customers c, orders o, orderitems i Where c.customer# = o.customer#(+)  7  and o.order# = i.order#;
 
View created.
 
SQL> SQL> CLEAR BREAKbreaks clearedSQL> CLEAR COLUMNcolumns clearedSQL> SQL> SELECT order#, customer#, address, city, state, zip,  2   orderdate, shipdate, shipstreet, shipcity, shipstate,  3   shipzip, item#, isbn, quantity  4  FROM customers NATURAL JOIN orders; shipzip, item#, isbn, quantity   *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 
 
SQL> SQL> SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total Line"  2  FROM customers NATURAL JOIN orders NATURAL JOIN orderitems  3   NATURAL JOIN books  4  GROUP BY item#, isbn, quantity, retail;
3) last but not least I can not figure out how to create this statement at all:
 
Deletes are not allowed, but you will be allowed to de-activate orders. You will need to add a column to the orders table to 
maintain the status, and then only allow updates of that colum through a procedure. 
 
Again thanks for any and all assiatance
 
Regards,Milton C. Craighead, Jr.
__This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]Shamita
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software

Computing resource consumption

Title: Computing resource consumption





Does anyone know any papers or techniques to compute resource consumption by users in a DB systems? This may or may not be for computing charge-back to the client, but my questions are 

1. What do you compute?
2. are there any standard methods and or standard formula?


I am collecting v$sesstat when a session exits, but is data alone from session stats sufficient? How about the work done by background processes on user's behalf? 

Do you do anything like this at your workplace? This is something that might be coming down the line, so I have been asked to start looking for related stuff.

Thanks in advance
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, 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: PL/SQL statement help

2003-07-24 Thread Joe Testa

Milton, do yourself a favor and sign up for OTN(http://otn.oracle.com) 
you got access to full oracle docs online.

joe

Milton C. Craighead, Jr. wrote:

I'm having no luck resolving the following PL/SQL errors for a final 
exam class project. We do not have access to metalink nor do we have 
access to Oracle PL/SQL documentation. We are being taught from a 
PL/SQL text book that does not provide ora error message information 
or where to find them... Any and all assistance will be greatly 
appreciated. 
 
1) where am I going wrong
 
SQL> SELECT order#, customer#, address, city, state, zip,
  2   orderdate, shipdate, shipstreet, shipcity, shipstate,
  3   shipzip, item#, isbn, quantity
  4  FROM customers NATURAL JOIN orders;
 shipzip, item#, isbn, quantity
   *
ERROR at line 3:
ORA-00904: "QUANTITY": invalid identifier
2) I'm trying to format a report where the output should look like the 
following:
I'm able to format the item#, isbn, quanty, retail, and total line 
with out error but Im getting an error message in my veiw...
 
 
Order#
Customer#
Customer Address
Customer City, State, Zip
 
Order Date
Ship Date
Ship Street
Ship City, Ship State, Ship Zip
 
ITEM#ISBNQUANTYRETAILTOTAL LINE
ITEM#ISBNQUANTYRETAILTOTAL LINE
 
 
SET SERVEROUTPUT ON
SQL> set linesize 300
SQL> set pagesize 20
SQL> ttitle CENTER 'The Law Report' SKIP 2
SQL> btitle '(Confidential)' SKIP 2
SQL>
SQL> column item# heading 'ITEM#' format a10 truncate
SQL> column isbn heading 'ISBN' format a15 truncate
SQL> column quantity heading 'QUANTITY' format a6 truncate
SQL> column retail heading 'RETAIL' format 990.00
SQL> column total line heading 'TOTAL|LINE' format 990.00
SP2-0158: unknown COLUMN option "line"
SQL>
SQL> Drop view bookPub;
 
View dropped.
 
SQL> Create view bookPub
  2  AS select count(b.title) as bookNum,  b.cost, b.pubid
  3  from books b natural join orderitems o
  4  group by b.pubid, b.cost;
 
View created.
 
SQL>
SQL>
SQL>
SQL>
SQL> Drop view bookRev;
 
View dropped.
 
SQL> Create view  bookRev
  2  As select count(b.title) as bkNum,  sum(b.retail - b.cost) as 
rev, o.customer#
  3  from orderitems i natural join books b natural join orders o
  4  group by o.customer#;
 
View created.
 
SQL>
SQL>
SQL>
SQL> Drop view orderInfo;
 
View dropped.
 
SQL> Create view  orderInfo
  2  As
  3  Select  c.customer#, o.order#, c.lastname, c.firstname, c.address,
  4  o.shipdate, o.shipstreet, o.shipcity, o.shipstate,
  5  o.shipzip, i.item#, i.isbn, i.quantity
  6  from customers c, orders o, orderitems i Where c.customer# = 
o.customer#(+)
  7  and o.order# = i.order#;
 
View created.
 
SQL>
SQL> CLEAR BREAK
breaks cleared
SQL> CLEAR COLUMN
columns cleared
SQL>
SQL> SELECT order#, customer#, address, city, state, zip,
  2   orderdate, shipdate, shipstreet, shipcity, shipstate,
  3   shipzip, item#, isbn, quantity
  4  FROM customers NATURAL JOIN orders;
 shipzip, item#, isbn, quantity
   *
ERROR at line 3:
ORA-00904: "QUANTITY": invalid identifier
 

SQL>
SQL> SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total 
Line"
  2  FROM customers NATURAL JOIN orders NATURAL JOIN orderitems
  3   NATURAL JOIN books
  4  GROUP BY item#, isbn, quantity, retail;
3) last but not least I can not figure out how to create this 
statement at all:
 
Deletes are not allowed, but you will be allowed to de-activate 
orders. You will need to add a column to the orders table to
maintain the status, and then only allow updates of that colum through 
a procedure.
 
Again thanks for any and all assiatance
 
Regards,
Milton C. Craighead, Jr.


--
Joseph S Testa
Chief Technology Officer 
Data Management Consulting
p: 614-791-9000
f: 614-791-9001

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

RE: PL/SQL statement help

2003-07-24 Thread Nelson, Allan

Title: Message



quantity is neither a column name or a valid column alias.  That's 
what your error is telling you.  
 
Allan

  
  -Original Message-From: Milton C. 
  Craighead, Jr. [mailto:[EMAIL PROTECTED] Sent: Thursday, 
  July 24, 2003 11:39 AMTo: Multiple recipients of list 
  ORACLE-LSubject: PL/SQL statement help
  I'm having no luck resolving the following PL/SQL 
  errors for a final exam class project. We do not have 
  access to metalink nor do we have access to Oracle PL/SQL documentation. 
  We are being taught from a PL/SQL text book that does not provide ora error 
  message information or where to find them... Any and all assistance 
  will be greatly appreciated. 
   
  1) where am I going wrong
   
  SQL> SELECT order#, customer#, address, city, 
  state, zip,  2   orderdate, shipdate, shipstreet, shipcity, 
  shipstate,  3   shipzip, item#, isbn, quantity  
  4  FROM customers NATURAL JOIN orders; shipzip, item#, isbn, 
  quantity   
  *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 
  
  2) I'm trying to format a report where the output 
  should look like the following:
      I'm able to format the item#, 
  isbn, quanty, retail, and total line with out error but Im getting an error 
  message in my veiw...
   
   
  Order#
  Customer#
  Customer Address
  Customer City, State, Zip
   
  Order Date
  Ship Date
  Ship Street
  Ship City, Ship State, Ship Zip
   
  ITEM#    
  ISBN        QUANTY    
      RETAIL    TOTAL 
  LINE
  ITEM#ISBN    
      QUANTY        
  RETAIL        TOTAL LINE
   
   
  SET SERVEROUTPUT ONSQL> set linesize 
  300SQL> set pagesize 20SQL> ttitle CENTER 'The Law Report' SKIP 
  2SQL> btitle '(Confidential)' SKIP 2SQL> SQL> column 
  item# heading 'ITEM#' format a10 truncateSQL> column isbn heading 
  'ISBN' format a15 truncateSQL> column quantity heading 'QUANTITY' 
  format a6 truncateSQL> column retail heading 'RETAIL' format 
  990.00SQL> column total line heading 'TOTAL|LINE' format 
  990.00SP2-0158: unknown COLUMN option "line"SQL> SQL> Drop 
  view bookPub;
   
  View dropped.
   
  SQL> Create view bookPub  2  AS 
  select count(b.title) as bookNum,  b.cost, b.pubid  3  from 
  books b natural join orderitems o  4  group by b.pubid, 
  b.cost;
   
  View created.
   
  SQL> SQL> SQL> SQL> 
  SQL> Drop view bookRev;
   
  View dropped.
   
  SQL> Create view  bookRev  
  2  As select count(b.title) as bkNum,  sum(b.retail - b.cost) as 
  rev, o.customer#  3  from orderitems i natural join books b 
  natural join orders o  4  group by o.customer#;
   
  View created.
   
  SQL> SQL> SQL> SQL> Drop 
  view orderInfo;
   
  View dropped.
   
  SQL> Create view  orderInfo  
  2  As  3  Select  c.customer#, o.order#, c.lastname, 
  c.firstname, c.address,  4  o.shipdate, o.shipstreet, 
  o.shipcity, o.shipstate,  5  o.shipzip, i.item#, i.isbn, 
  i.quantity  6  from customers c, orders o, orderitems i Where 
  c.customer# = o.customer#(+)  7  and o.order# = 
  i.order#;
   
  View created.
   
  SQL> SQL> CLEAR BREAKbreaks 
  clearedSQL> CLEAR COLUMNcolumns clearedSQL> SQL> 
  SELECT order#, customer#, address, city, state, zip,  2   
  orderdate, shipdate, shipstreet, shipcity, shipstate,  3   
  shipzip, item#, isbn, quantity  4  FROM customers NATURAL JOIN 
  orders; shipzip, item#, isbn, 
  quantity   
  *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 
  
   
  SQL> SQL> SELECT item#, isbn, 
  quantity, retail, sum(retail*quantity) "Total Line"  2  FROM 
  customers NATURAL JOIN orders NATURAL JOIN orderitems  3   
  NATURAL JOIN books  4  GROUP BY item#, isbn, quantity, 
  retail;
  3) last but not least I can not figure out how to 
  create this statement at all:
   
  Deletes are not allowed, but you will be allowed 
  to de-activate orders. You will need to add a column to the orders table to 
  
  maintain the status, and then only allow updates 
  of that colum through a procedure. 
   
  Again thanks for any and all 
  assiatance
   
  Regards,Milton C. Craighead, 
Jr.

__
This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information.  Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer.  This email may have been monitored for policy compliance.  [021216]

Re: Update through a DBLink

2003-07-24 Thread John Shaw



There is or at least was a 
problem if you had a autonomous transaction across a dblink with a 
commit.>>> [EMAIL PROTECTED] 7/24/2003 10:59:27 AM 
>>>
One of or developers is work on a routine that will trigger a 
procedurethat will use a dblink to update a table on another database.  
It seemslike I remember that something about a problem with procedures, 
dblinks,updates?  Does anyone remember anything about 
that?Thanks!Ron-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Smith, 
Ron L.  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).

bad press for oracle....

2003-07-24 Thread Chris Stephens

Title: bad press for oracle





http://rss.com.com/2110-1017_3-5053453.html?type=pt&part=rss&tag=feed&subj=news

RE: Update through a DBLink

2003-07-24 Thread Richard Ji

If the remote table uses object types, you will have trouble
updating it from your local procedure.

Richard

-Original Message-
Sent: Thursday, July 24, 2003 11:59 AM
To: Multiple recipients of list ORACLE-L


One of or developers is work on a routine that will trigger a procedure
that will use a dblink to update a table on another database.  It seems
like I remember that something about a problem with procedures, dblinks,
updates?  Does anyone remember anything about that?

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

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

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

PL/SQL statement help

2003-07-24 Thread Milton C. Craighead, Jr.




I'm having no luck resolving the following PL/SQL 
errors for a final exam class project. We do not have access 
to metalink nor do we have access to Oracle PL/SQL documentation. We are 
being taught from a PL/SQL text book that does not provide ora error message 
information or where to find them... Any and all assistance will be 
greatly appreciated. 
 
1) where am I going wrong
 
SQL> SELECT order#, customer#, address, city, 
state, zip,  2   orderdate, shipdate, shipstreet, shipcity, 
shipstate,  3   shipzip, item#, isbn, quantity  
4  FROM customers NATURAL JOIN orders; shipzip, item#, isbn, 
quantity   
*ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 

2) I'm trying to format a report where the output 
should look like the following:
    I'm able to format the item#, 
isbn, quanty, retail, and total line with out error but Im getting an error 
message in my veiw...
 
 
Order#
Customer#
Customer Address
Customer City, State, Zip
 
Order Date
Ship Date
Ship Street
Ship City, Ship State, Ship Zip
 
ITEM#    
ISBN        QUANTY    
    RETAIL    TOTAL 
LINE
ITEM#ISBN    
    QUANTY        
RETAIL        TOTAL LINE
 
 
SET SERVEROUTPUT ONSQL> set linesize 
300SQL> set pagesize 20SQL> ttitle CENTER 'The Law Report' SKIP 
2SQL> btitle '(Confidential)' SKIP 2SQL> SQL> column item# 
heading 'ITEM#' format a10 truncateSQL> column isbn heading 'ISBN' format 
a15 truncateSQL> column quantity heading 'QUANTITY' format a6 
truncateSQL> column retail heading 'RETAIL' format 990.00SQL> 
column total line heading 'TOTAL|LINE' format 990.00SP2-0158: unknown COLUMN 
option "line"SQL> SQL> Drop view bookPub;
 
View dropped.
 
SQL> Create view bookPub  2  AS 
select count(b.title) as bookNum,  b.cost, b.pubid  3  from 
books b natural join orderitems o  4  group by b.pubid, 
b.cost;
 
View created.
 
SQL> SQL> SQL> SQL> 
SQL> Drop view bookRev;
 
View dropped.
 
SQL> Create view  bookRev  2  
As select count(b.title) as bkNum,  sum(b.retail - b.cost) as rev, 
o.customer#  3  from orderitems i natural join books b natural 
join orders o  4  group by o.customer#;
 
View created.
 
SQL> SQL> SQL> SQL> Drop 
view orderInfo;
 
View dropped.
 
SQL> Create view  orderInfo  
2  As  3  Select  c.customer#, o.order#, c.lastname, 
c.firstname, c.address,  4  o.shipdate, o.shipstreet, o.shipcity, 
o.shipstate,  5  o.shipzip, i.item#, i.isbn, i.quantity  
6  from customers c, orders o, orderitems i Where c.customer# = 
o.customer#(+)  7  and o.order# = i.order#;
 
View created.
 
SQL> SQL> CLEAR BREAKbreaks 
clearedSQL> CLEAR COLUMNcolumns clearedSQL> SQL> SELECT 
order#, customer#, address, city, state, zip,  2   orderdate, 
shipdate, shipstreet, shipcity, shipstate,  3   shipzip, 
item#, isbn, quantity  4  FROM customers NATURAL JOIN 
orders; shipzip, item#, isbn, 
quantity   
*ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 
 
SQL> SQL> SELECT item#, isbn, 
quantity, retail, sum(retail*quantity) "Total Line"  2  FROM 
customers NATURAL JOIN orders NATURAL JOIN orderitems  3   
NATURAL JOIN books  4  GROUP BY item#, isbn, quantity, 
retail;
3) last but not least I can not figure out how to 
create this statement at all:
 
Deletes are not allowed, but you will be allowed to 
de-activate orders. You will need to add a column to the orders table to 

maintain the status, and then only allow updates of 
that colum through a procedure. 
 
Again thanks for any and all 
assiatance
 
Regards,Milton C. Craighead, 
Jr.

RE: Timestamps in trace files (and other trace file oddities)

My interpretation would be as follows:

The wait entries are written whenever a wait ends, so at 15:40:59.149 the 
session has just been waiting .00 seconds for a scattered read of 18 
blocks. At 15:46:06.340 it just had been waiting on a latch free event. For 
the almost seven minutes between, it had not been waiting on any of the 
established wait events. It had either been processing the blocks returned, 
or it could have been waiting in the OS scheduler queue waiting for a cpu 
to become available - or both intermittendly. After coming out of the latch 
free wait, it found that the blocks it had previously read had been flushed 
from the buffer (not surprising after 7 minutes) and needed to read them again.

At 07:04 AM 7/24/2003 -0800, you wrote:
(Tried sending this yesterday. I'll try again)

Dan,
I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
today and got some really odd results in my trace file (8.1.7).
*** 2003-07-23 15:40:59.149
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
*** 2003-07-23 15:46:06.340
WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
*** 2003-07-23 15:47:53.851
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
Two things struck me (three if it takes me too long to write this and I get
home late). First, the timestamps show an elapsed time of ~7 minutes, but
the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
closer to reality. Huh???
Secondly, the first scattered read reads 18 blocks starting at 6041. Why
does the next scattered read start at block# 6042?
Any ideas?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: backwards export

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

Title: RE: backwards export







> I'm not sure of the export compatibility, but you should know that 9i 
> clients won't talk to a 7.3.4 database, 


  True


> and 7.3.4 clients won't talk to a 
> 9.2 database, 
  
 False


  A Oracle7 client can, will, and does talk to a 9.2.0 database 
with no problem.


> which could be a problem.  At least, they're not certified 
> to talk to each other, which isn't quite the same thing.


  See note 172179.1 on Metalink.  To quote:


"In short, any version of SQL*Net's version 2 Client (2.3.x) and 
up to Net 9.0.x, will be able to connect to any version of 
Oracle Database version 7.3.x and up to 9.2.x, with certain 
compatibility issues that are explained below"



Matt Adams

Matt Adams - GE Appliances - [EMAIL PROTECTED]
It will make sense when you stop thinking logically,
and start thinking Oracle-ly  -  Jim Droppa


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 23, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: backwards export



I'm not sure of the export compatibility, but you should know that 9i 
clients won't talk to a 7.3.4 database, and 7.3.4 clients won't talk to a 
9.2 database, which could be a problem.  At least, they're not certified 
to talk to each other, which isn't quite the same thing.


Cheers
Simon Anderson







Jeroen van Sluisdam <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
23/07/2003 12:49
Please respond to ORACLE-L


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



Hi,
 
We are investigating possibilities for migration of an oracle 7.3.4 to 
oracle 9i. We want to separate our
production DB from the app-logic such that we can migrate from oracle 
7.3.4 on hp-ux 10.20 to oracle 9i
on hp-ux 11. Problem is our development and test-environment because we 
cannot separate the logic at the
same time here also from the db (costs). Does anyone have any experience 
with exporting from oracle 9i 
to oracle 7.3.4 ? 
 
I found the underlying note on metalink about the same problem but with 
oracle 8. I have limited experience on oracle8
and none on oracle 9. So I hope you can give some tips.
..
You need to run the 7.3 version of export against your 8.0 database via 
sqlnet/net8. Beforehand, you need to run rdbms/admin/catexp7.sql against 
your 8.0 database. This creates the 7.3 export views required. Note that 
if you have used any 8.0 specific features (e.g. objects) these will not 
be exported for obvious reasons. 

 
Tia,
 
Jeroen



-- 
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: Does the client "hang" or "disconnect" when the server's

Hi!

This sqlnet.expire_time affects server, not sqlplus itself AFAIK. The issue
is likely, that sqlplus has sent a sqlnet request to server, but hasn't got
any response since you pulled the network cable. Thus it'll wait until TCP
connection gets timeout.
Also, sqlplus is different from normal telnet, because it tries to send a
request only when it is on it's prompt, thus issuing a command in sqlplus
session, when it is waiting for response from previous command, doesn't send
any network packets.

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 6:54 PM


>
>
>
>
> Hemant,
>
>   You are right it does get a timeout from the TCP stack, but the
default
> for the connection timeout is 30 minutes.  There is a sqlnet parameter
where you
> can modify the timeout to make it shorter (sqlnet.expire_time?)  Check
metalink
> note 151972.1 for more info.
>
> HTH
> Mike
>
>
>
>
>   Hemant K Chitale
>   <[EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
>   .com.sg> cc:
>   Sent by: Subject:  Does the client
"hang" or "disconnect" when the server's
>   [EMAIL PROTECTED]
>   .com

>
>
>   07/24/2003 11:24
>   AM
>   Please respond to
>   ORACLE-L
>
>
>
>
>
>
>
>
> I'm sorry, but I really can't remember how an Oracle SQLPlus client is
supposed
> to behave when the database server's network interface is "pulled out".
> We were testing a new Sun Cluster and tests for Storage FC Path Failure,
> Instance Failure,
> Node Failure, HeartBeat failure and single Network Interface failure all
> went through.
>
> However, when the engineer pulled out *both* network interfaces of the
running
> database server node, the SQLPlus client that had been running INSERT
> statements
> just "hung".  It did not exit or error out till we killed the job.  At the
> next restart, of course,
> it connected to the database which had already failed-over to the second
node.
>
> The question is : Why does the SQLPlus client "hang" ?  Telnet sessions
> also might
> "hang" [couldnt' test this out as the Servers are Secured and we didn't
> have SSH on the
> client], till the user hits the ENTER key, but shouldn't SQLPlus get
server
> disconnect
> from the TCP stack ?
>
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> My personal web site 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: Michael Boligan
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Tanel Poder
  INET: [EMAIL PROTECTED]

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

Title: RE: Update through a DBLink





No ..


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 24, 2003 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Update through a DBLink



One of or developers is work on a routine that will trigger a procedure
that will use a dblink to update a table on another database.  It seems
like I remember that something about a problem with procedures, dblinks,
updates?  Does anyone remember anything about that?


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


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



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: Re: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread Nuno Souto

- Original Message - 


> what do you mean by 'arc'?
> > 

have a look:
http://www.docm.mmu.ac.uk/online/SAD/T07/erd2.htm
much better explanation than I can give here.

Cheers
Nuno Souto
[EMAIL PROTECTED]

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

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

2003-07-24 Thread Stephen Lee


You can have a single disk array shared by two computers.  Each box has its
own OS file system(s) which can be on the disk array or on private drives
not in the disk array; but all the Oracle stuff is shared on a single set of
file systems mounted only by one box at a time.  If the primary computer
goes down, you can have the secondary mount the database file systems and
start the database.  The most important thing is to write your failover,
startup, and shutdown scripts to make sure that both boxes do not try to run
the database at the same time; that will destroy your database.  I think the
most common setup for automated failover will use a pair of private network
connections (i.e. crossover cables) between the two boxes over which the
secondary box will perform some sort of test of the primary box; for
example, ping.  If you don't have a script writer capable of this level
robustness, another option is to have scripts to startup and shutdown, and
run the scripts manually after verifying that only one box will be running
the database.

-Original Message-

Hi! 
We are looking into establishing some sort of high availability solution
here. We are running 9.2.0 on Sun Fire 280 (2 processors).
Since we are on a tight budget, we are looking into various solutions for
HA. 
One option would be to use Sun Cluster Server or Veritas Cluster Server. If
one box fails, the db just fails over to the other node. The problem is that
we don't have a cluster guy here...
The other Option would be to use RAC, but this is the most expensive
solution, I guess... 
Does anybody use any other HA solution that is affordable? Failover time
should be less than 15 minutes, although "frequent" outages (i.e. once a
month or so) are tolerable.
Don't blame me for these requirements; it was not my idea... 
This is 9.2.0 on Sun Solaris. 
Thanks, 
Helmut 
-- 
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: Extproc setup Q?

2003-07-24 Thread Hallas, John, Tech Dev

Jack,
This is a post from a few weeks ago that gives a setup
TNSNAMES:
 
extproc_connection_data.world =
(DESCRIPTION =
(ADDRESS =
  (PROTOCOL = IPC)
   (KEY = extproc_agent)  <--- Key1
)
  (CONNECT_DATA = (SID = 11)  <--- Key2
  (server=dedicated))
)
 
Listener:
 
EXTPROC_LISTENER =
  (ADDRESS_LIST =
(ADDRESS =
  (PROTOCOL = IPC)
  (KEY = extproc_agent) < Key1
)
  )
SID_LIST_EXTPROC_LISTENER =
  (SID_LIST =
(SID_DESC =
   (SID_NAME = 11)  < Key2
   (ORACLE_HOME = /ora1/81764)
   (PROGRAM = extproc)
)
  )
 
BTW: Oracle's recommendation is to use a seperate listener for extproc calls.
-Original Message-
Sent: 24 July 2003 15:10
To: Multiple recipients of list ORACLE-L



  

Tried that still the same error message.


Any other hints & tips for me to try? 
 
Just curious though ,why is it DLLS if I'm working on LINUX
 
 
Jack

-Original Message-   
Sent: Thursday, July 24, 2003 2:30 PM
To: Multiple recipients of list ORACLE-L


Please add the line in red to your listener.ora file. Extproc has been made secure in 
Oracle 9i. The following line has to be added to execute your own external procedures.

LISTENER = 
  (DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) 
  ) 

SID_LIST_LISTENER = 
  (SID_LIST = 
(SID_DESC = 
  (SID_NAME = plsextproc) 
  (ORACLE_HOME = /oracle/app/product/9.2.0) 
  (PROGRAM = extproc) 

(ENVS="EXTPROC_DLLS=ANY")
) 
(SID_DESC = 
  (GLOBAL_DBNAME = Ora92) 
  (ORACLE_HOME = /oracle/app/product/9.2.0) 
  (SID_NAME = ora92) 
) 
  ) 


Regards

Munish Bajaj

-Original Message-
Sent: Thursday, July 24, 2003 16:24
To: Multiple recipients of list ORACLE-L



Hi All, 

I am trying to setup EXTPROC but keep getting ORA-28575 (Check your tnsnames.ora & 
listener.ora) 

Attached are my files that look OK to me 

Anybody?? 


 

[EMAIL PROTECTED] admin]$ cat tnsnames.ora 
# TNSNAMES.ORA Network Configuration File: 
/oracle/app/product/9.2.0/network/admin/tnsnames.ora 

ORA92 = 
  (DESCRIPTION = 
(ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) 
) 
(CONNECT_DATA = 
  (SERVICE_NAME = Ora92) 
) 
  ) 
extproc_connection_data = 
  (DESCRIPTION = 
(ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) 
  (CONNECT_DATA = (SID = plsextproc)) 
) 
) 


[EMAIL PROTECTED] admin]$ cat listener.ora 
# LISTENER.ORA Network Configuration File: 
/oracle/app/product/9.2.0/network/admin/listener.ora 


LISTENER = 
  (DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) 
  ) 

SID_LIST_LISTENER = 
  (SID_LIST = 
(SID_DESC = 
  (SID_NAME = plsextproc) 
  (ORACLE_HOME = /oracle/app/product/9.2.0) 
  (PROGRAM = extproc) 
) 
(SID_DESC = 
  (GLOBAL_DBNAME = Ora92) 
  (ORACLE_HOME = /oracle/app/product/9.2.0) 
  (SID_NAME = ora92) 
) 
  ) 

 

TIA 



Jack van Zanen 


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

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

Hi!

About the second one, you see the first read started from 6041 and was 18
blocks, the second one which was executed 7 minutes later, started from 6042
and was 17 blocks. Probably the extent boundary is at block 6059. Multiblock
reads don't cross extent boundaries.

So, probably your query acquired block 6041 with FTS and by the time it
needed next block, the blocks were already out of buffer cache (LRU list)
and another multiblock read was needed. Maybe you should look at keep buffer
pool, but since the time interval was long, it might not be necessary
anyway...

Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 6:04 PM


> (Tried sending this yesterday. I'll try again)
>
> Dan,
> I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
> today and got some really odd results in my trace file (8.1.7).
>
> *** 2003-07-23 15:40:59.149
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
> *** 2003-07-23 15:46:06.340
> WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
> *** 2003-07-23 15:47:53.851
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
>
> Two things struck me (three if it takes me too long to write this and I
get
> home late). First, the timestamps show an elapsed time of ~7 minutes, but
> the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
> closer to reality. Huh???
>
> Secondly, the first scattered read reads 18 blocks starting at 6041. Why
> does the next scattered read start at block# 6042?
>
> Any ideas?
>
> Henry
>
>
>
> -Original Message-
> Daniel Fink
> Sent: Wednesday, July 23, 2003 11:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Gudmundur,
> Where is this documented (so I can RTFM)?
> The one piece of this I don't quite understand is that the timestamp is
not
> emitted twice in a row. If the long time is the triggering event, why do I
> see a gap of 90 minutes (in another trace file)?
>
> Daniel
>
> Gudmundur Bjarni Josepsson wrote:
> >
> > Daniel,
> >
> > Perhaps someone else can explain this better but the documentation I've
> > got on this says that the Oracle kernel emits timestamps when a long
> > time has elapsed since the last line was emitted to the trace file.
> > Long time is defined as tens of seconds.
> >
> > Gudmundur
> >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > > Behalf Of Daniel Fink
> > > Sent: 22. júlí 2003 21:19
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Timestamps in trace files
> > >
> > >
> > > I was perusing a 10046 trace file and I noticed that
> > > timestamps are written to the trace file. Sometimes they were
> > > very regular (3 minutes apart give or take 30 seconds) while
> > > other times they were hours apart. I have noticed that two
> > > timestamps are never written without any intervening
> > > activity. Anyone have any idea on the reasoning behind the
> > > timestamps and the 'triggering event'?
> > >
> > > Daniel
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Gudmundur Bjarni Josepsson
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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: Henry Poras
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Tanel Poder
  INET: [EMAIL PROTECTED]

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

RE: do I have block corruption?

2003-07-24 Thread Munish Bajaj

I think that the table is synonym created in your schema on some other table
on other schema. You don't have rights  on this table except select rights.

This is my opinion. Please check again..

Regards
Munish Bajaj

-Original Message-
Sent: Thursday, July 24, 2003 19:34
To: Multiple recipients of list ORACLE-L


 1  CREATE  INDEX myIndex
  2   ON myTab (myCol)
  3PCTFREE  1
  4STORAGE (
  5   INITIAL 5M
  6   NEXT5M
  7   MINEXTENTS  1
  8   MAXEXTENTS  UNLIMITED
  9*  PCTINCREASE 0)
 10  /
 ON POS (ACCT_NO)
*
ERROR at line 2:
ORA-08103: object no longer exists

i try to create a table with a join off of this column and I get the same
error. I ran alter table validate structure and didnt get any errors(do
these errors log to a table? I didnt see any docs on OTN about this?)

not much on metalink

I was able to do a 'create table as' and create a duplicate
of the table which suprised me... 

-- 
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: Munish Bajaj
  INET: [EMAIL PROTECTED]

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

2003-07-24 Thread Michael Boligan





Hemant,

  You are right it does get a timeout from the TCP stack, but the default
for the connection timeout is 30 minutes.  There is a sqlnet parameter where you
can modify the timeout to make it shorter (sqlnet.expire_time?)  Check metalink
note 151972.1 for more info.

HTH
Mike



   

  Hemant K Chitale 

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

  Sent by: Subject:  Does the client "hang" or 
"disconnect" when the server's  
  [EMAIL PROTECTED]

  .com 

   

   

  07/24/2003 11:24 

  AM   

  Please respond to

  ORACLE-L 

   

   







I'm sorry, but I really can't remember how an Oracle SQLPlus client is supposed
to behave when the database server's network interface is "pulled out".
We were testing a new Sun Cluster and tests for Storage FC Path Failure,
Instance Failure,
Node Failure, HeartBeat failure and single Network Interface failure all
went through.

However, when the engineer pulled out *both* network interfaces of the running
database server node, the SQLPlus client that had been running INSERT
statements
just "hung".  It did not exit or error out till we killed the job.  At the
next restart, of course,
it connected to the database which had already failed-over to the second node.

The question is : Why does the SQLPlus client "hang" ?  Telnet sessions
also might
"hang" [couldnt' test this out as the Servers are Secured and we didn't
have SSH on the
client], till the user hits the ENTER key, but shouldn't SQLPlus get server
disconnect
from the TCP stack ?


Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site 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: Michael Boligan
  INET: [EMAIL PROTECTED]

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

Update through a DBLink

2003-07-24 Thread Smith, Ron L.

One of or developers is work on a routine that will trigger a procedure
that will use a dblink to update a table on another database.  It seems
like I remember that something about a problem with procedures, dblinks,
updates?  Does anyone remember anything about that?

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

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

2003-07-24 Thread Daniel Fink

Henry,
I'll make an attempt, but I am still learning a great deal about wait events 
and trace files. Cary, Mogens, Anjo, Tim, Jonathan, Wolfgang, et.al. are better 
authorities, so any corrections are very welcome.
The time between waits is the elapsed time. If we equate elapsed time to 
response time, we must look at the two components of RT, wait time and service time. 
If the time differential is 7 minutes, x minutes is service time and y minutes is wait 
time. It is possible that only .01 seconds was wait time, while the remaining 6:59:99 
was service time (i.e. cpu)
I can't explain the scattered read issue. However, it is interesting to note 
that the # of blocks to be read is offset by 1 in the second wait event. Perhaps, the 
read request was issued, but the 65.6041 block was already in memory, so the read 
request was reissued minus that block. I'm not sure how the latch free enters into the 
picture, but I have a feeling that this wait is integral in the sequence of events.

Dan

Henry Poras wrote:
> 
> (Tried sending this yesterday. I'll try again)
> 
> Dan,
> I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
> today and got some really odd results in my trace file (8.1.7).
> 
> *** 2003-07-23 15:40:59.149
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
> *** 2003-07-23 15:46:06.340
> WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
> *** 2003-07-23 15:47:53.851
> WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
> 
> Two things struck me (three if it takes me too long to write this and I get
> home late). First, the timestamps show an elapsed time of ~7 minutes, but
> the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
> closer to reality. Huh???
> 
> Secondly, the first scattered read reads 18 blocks starting at 6041. Why
> does the next scattered read start at block# 6042?
> 
> Any ideas?
> 
> Henrybegin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

Re: Recommendation for "cheap" HA solution

Title: Recommendation for "cheap" HA solution



Hi!
 
When not having any cluster manager, there's 3 main 
issues what you have to deal with:
 
1) hearbeat - verifying whether primary node (or 
other nodes) are alive
2) storage - making failed node's storage 
accessible to backup node

3) connectivity - allowing clients to transparently 
connect to another node (usually IP address transfer)
 
1) If you got max 15 min switchover time, it could 
be done with a script on secondary or monitoring node, which actually verifies 
whether your Oracle database is running (the easiest is 
just connect and select * from dual in a script every minute). If either 
connect or select fails, you take appropriate steps described below
2) storage - both of your servers have to see 
the disks of course. If you got SAN over fibre, it's no problem to share 
disks between several servers. With some external SCSI arrays it shouldn't be a 
problem either to share between 2 servers. 
When your heartbeat mechanism detects that your 
primary Oracle service isn't running, it first tries to kill Oracle and unmount 
file systems on primary server - two nodes writing to the same data without 
coordination will cause a mess. The kill & unmount could be done by 
secondary or monitoring server using rsh, ssh or whatever remote exec mechanism. 
If remote exec doesn't work, then we rely on ping, to see whether the primary 
host is alive. When it isn't alive, we are free to mount file system on 
secondary node and start the instance (of course the primary node should not 
have automatic instance startup scripts in it's rc.d). The problematic issue is, 
when ping show primary instance as alive, but remote exec to 
shutdown&unmount fails. This is the place, where cluster managers should be 
better than home-made high-availability solutions.
3) For connectivity to be directed to backup node 
you either try to transfer the IP in a script (I don't know solaris commands by 
heart, you could just have two sets of network config files as well). Other 
solution would be to play around with tnsnames.ora entries, which always have 
primary host IP first in address list and secondary host as second. Also you got 
to set fail_over or smth like that parameter in tnsnames.
 
If you do have lot's of storage space or do not 
have sharable storage, then go with standby databases (can be done with standard 
ed. too) and forget about issues in point 2.
 
So, you got to do some planning and scripting for 
that, but cheap HA for simple systems is very possible. I personally 
like these simple solutions over expensive software packages, guards, agents, 
which often bring an additional layer of complexity to sysadmins jobs and don't 
always work as expected themselves. But of course, these home-made one-weekend 
solutions aren't appropriate everywhere...
 
Tanel.

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Thursday, July 24, 2003 5:49 
  PM
  Subject: Recommendation for "cheap" HA 
  solution
  
  Hi! 
  We are looking into establishing some sort of high 
  availability solution here. We are running 9.2.0 on Sun Fire 280 (2 
  processors).
  Since we are on a tight budget, we are looking into various 
  solutions for HA. 
  One option would be to use Sun Cluster Server or Veritas 
  Cluster Server. If one box fails, the db just fails over to the other node. 
  The problem is that we don't have a cluster guy here...
  The other Option would be to use RAC, but this is the most 
  expensive solution, I guess... 
  Does anybody use any other HA solution that is affordable? 
  Failover time should be less than 15 minutes, although "frequent" outages 
  (i.e. once a month or so) are tolerable.
  Don't blame me for these requirements; it was not my 
  idea... 
  This is 9.2.0 on Sun Solaris. 
  Thanks, Helmut

RE: Oracle 9i (9.2.0.4)- RH AS 2.1 HP Itanium

2003-07-24 Thread Gogala, Mladen

Oracle RDBMS 9.2.0.4??? I thought that the latest version was 9.2.0.3?
Where did you get 9.2.0.4?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, July 24, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L


Hi there

Who is running this.

Would like to hear opinion and experiences.

George

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




This email and all contents are subject to the following disclaimer:

"http://www.didata.com/disclaimer.asp";

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

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

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

Re: Recommendation for "cheap" HA solution

2003-07-24 Thread kathy . robb

Do you have Oracle Enterprise or Standard?

If Enterprise, you are licensed for Oracle's Adv.
Replication feature. You may want to consider this as an
option since it could provide an HA solution without
spending any additional $$$ except for duplicating your
current db server.  You don't need clustering software here,
just a good DBA and a good test environment.  Once you get
it working, your fail over time is less than a hardware
clustered solution.

If you go with one of the two clustering software choices -
Sun Cluster or Veritas, I personally prefer VCS.  I think
it's easier.

Much luck.

Kathy

> Hi!
> 
> We are looking into establishing some sort of high
> availability solution here. We are running 9.2.0 on Sun
> Fire 280 (2 processors). Since we are on a tight budget,
> we are looking into various solutions for HA.
> 
> One option would be to use Sun Cluster Server or Veritas
> Cluster Server. If one box fails, the db just fails over
> to the other node. The problem is that we don't have a
> cluster guy here... 
> The other Option would be to use RAC, but this is the most
> expensive solution, I guess...
> 
> Does anybody use any other HA solution that is affordable?
> Failover time should be less than 15 minutes, although
> "frequent" outages (i.e. once a month or so) are
> tolerable. 
> Don't blame me for these requirements; it was not my
> idea... 
> This is 9.2.0 on Sun Solaris.
> 
> Thanks,
> Helmut
> 
> 
-- 
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).

Oracle 9i (9.2.0.4)- RH AS 2.1 HP Itanium

2003-07-24 Thread George Leonard (ZA)

Hi there

Who is running this.

Would like to hear opinion and experiences.

George

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




This email and all contents are subject to the following disclaimer:

"http://www.didata.com/disclaimer.asp";

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

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

Title: RE: Trigger Firing and Execution of Body Code





brad,


you have got it right  but to prove your assumptions .. it is very easy ...


alter session set events '10046 trace name context forever, level 4'
/
run some dml on the table
alter session set events '10046 trace name context off'
/


Then look through trace file, it will show you the logic used to fire or skip the trigger execution.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Odland, Brad [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 24, 2003 11:10 AM
To: Multiple recipients of list ORACLE-L
Subject: Trigger Firing and Execution of Body Code



Rich noticed at a large number of executions for a trigger on a table the
header looks like this and now we are a bit confused. Consider the
following:



AFTER INSERT OR UPDATE ON BLAH.PARTMASTER
 FOR EACH ROW
 WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
NULL)
BEGIN


(Trigger with many executions)



ON another trigger with a OF clause


 AFTER INSERT OR UPDATE OF commodity_code ON blah.partmaster
FOR EACH ROW
WHEN (new.commodity_code != old.commodity_code)
BEGIN


(Trigger with few executions)



Is the following statement true?


A trigger can fire but will not always execute the code in the body.


I believe it is based on the examples above. The top trigger will fire on
every insert and update of any column in the table but will not run code in
the body and just exit thus incrementing an execution stat. The second
trigger will execute when the commodity code column is updated. Keep in mind
I am saying here EXECUTE not FIRE the BODY.


So a trigger is said to execute when the condition for the trigger (AFTER
INSERT OR UPDATE in this case) is met.


The interesting issue to is that the first trigger grabs 8k of memory every
time it fires and it has executed 900,000 time in the past month and a half.
I suspect that a trigger is a continuous package of compiled code that when
the initial condition of ON INSERT OR UPDATE ON TABLE executes the trigger
code. The WHEN clause in the code is simple logical branching condition like
an IF statement. When the condition is not met it skips the body and
continues to the end resulting in the trigger "executing" or "firing" even
though the body was not processed.


questions, comments, suggestions?
thoughts, feelings, ideas?
statements, opinions, conjecture?
Am I nuts, should I care?



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


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



*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

Histograms 7.2

2003-07-24 Thread Greg Faktor

Hi All!

I tried create histogram in Oracle 7.2 with the following statement:

SQL> ANALYZE TABLE nai_parameters COMPUTE STATISTICS FOR COLUMNs prompt
SIZE 75; 

ANALYZE TABLE nai_parameters COMPUTE STATISTICS FOR COLUMNS prompt SIZE
75
 *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Is it different syntax in 7.2?


Thanks.

Greg



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

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

AW: dba age

2003-07-24 Thread Stefan Jahnke

Aha. Big guy ;). 34yrs, 6'6'', 225lbs. The 5 miles still work and at least
due to my height, I'm able to cheat at the chin-up bar ;).
Don't panic.

Stefan


-Ursprüngliche Nachricht-
Von: Gogala, Mladen [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 23. Juli 2003 20:14
An: Multiple recipients of list ORACLE-L
Betreff: RE: dba age


I was born on 1/10/1961 in Zagreb, Croatia, which makes me 42 years
of age. My horoscope sign is Ursus (6'4", 260 lbs, used to do greko-roman
wrestling, I can still run 5 miles and lift myself on a chin-up bar). People
sometimes confuse my age with the answer to certain question from the Doglas
Adamses "Hitchiker's Guiide To Galaxy". Don't panic!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, July 23, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L


Tanel.

I am personally impressed with your pratical knowledge, based on your 
responses to this list. Mostly DBA's particpating on this list is 35+ (may 
in the range of 40-45). Only one DBA is 42 years old for the last 5 years. 
Guess who Gogala Mladen(don't shoot me as I am your fan). That is his 
favorite/magic figure 42. However, he can tell his real age if he wants.


Regards
Rafiq




Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 23 Jul 2003 08:09:56 -0800

Hi!

Age isn't the most important criteria for good DBA.
Ability to analyze issues and foresee possible consequences of your actions 
are the most important ones, at least from my point of view.

Personally, I just turned 25. Whether I'm experienced or not, I won't 
speculate, but so far I've lost no data and have kept all my customers happy

;)

Tanel.
   - Original Message -
   From: AK
   To: Multiple recipients of list ORACLE-L
   Sent: Wednesday, July 23, 2003 6:54 PM
   Subject: dba age


   Now this one is difficult folks ..

   what is average age of an experienced oracle dba ?

   -ak

_
The new MSN 8: advanced junk mail protection and 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

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


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

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

RE: Timestamps in trace files (and other trace file oddities)

(Tried sending this yesterday. I'll try again)

Dan,
I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
today and got some really odd results in my trace file (8.1.7).

*** 2003-07-23 15:40:59.149
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
*** 2003-07-23 15:46:06.340
WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
*** 2003-07-23 15:47:53.851
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17

Two things struck me (three if it takes me too long to write this and I get
home late). First, the timestamps show an elapsed time of ~7 minutes, but
the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
closer to reality. Huh???

Secondly, the first scattered read reads 18 blocks starting at 6041. Why
does the next scattered read start at block# 6042?

Any ideas?

Henry

-Original Message-
Daniel Fink
Sent: Wednesday, July 23, 2003 11:04 AM
To: Multiple recipients of list ORACLE-L

Gudmundur,
Where is this documented (so I can RTFM)?
The one piece of this I don't quite understand is that the timestamp is not
emitted twice in a row. If the long time is the triggering event, why do I
see a gap of 90 minutes (in another trace file)?

Daniel

Gudmundur Bjarni Josepsson wrote:
>
> Daniel,
>
> Perhaps someone else can explain this better but the documentation I've
> got on this says that the Oracle kernel emits timestamps when a long
> time has elapsed since the last line was emitted to the trace file.
> Long time is defined as tens of seconds.
>
> Gudmundur
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > Behalf Of Daniel Fink
> > Sent: 22. júlí 2003 21:19
> > To: Multiple recipients of list ORACLE-L
> > Subject: Timestamps in trace files
> >
> >
> > I was perusing a 10046 trace file and I noticed that
> > timestamps are written to the trace file. Sometimes they were
> > very regular (3 minutes apart give or take 30 seconds) while
> > other times they were hours apart. I have noticed that two
> > timestamps are never written without any intervening
> > activity. Anyone have any idea on the reasoning behind the
> > timestamps and the 'triggering event'?
> >
> > Daniel
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gudmundur Bjarni Josepsson
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Henry Poras
  INET: [EMAIL PROTECTED]

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

Does the client "hang" or "disconnect" when the server's

2003-07-24 Thread Hemant K Chitale



I'm sorry, but I really can't remember how an Oracle SQLPlus client is supposed
to behave when the database server's network interface is "pulled out".
We were testing a new Sun Cluster and tests for Storage FC Path Failure, 
Instance Failure,
Node Failure, HeartBeat failure and single Network Interface failure all 
went through.

However, when the engineer pulled out *both* network interfaces of the running
database server node, the SQLPlus client that had been running INSERT 
statements
just "hung".  It did not exit or error out till we killed the job.  At the 
next restart, of course,
it connected to the database which had already failed-over to the second node.

The question is : Why does the SQLPlus client "hang" ?  Telnet sessions 
also might
"hang" [couldnt' test this out as the Servers are Secured and we didn't 
have SSH on the
client], till the user hits the ENTER key, but shouldn't SQLPlus get server 
disconnect
from the TCP stack ?

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

RE: Oracle on linux vs. on windows

2003-07-24 Thread Stephen Lee


There is a marketing term in the USA (at least) called "bait and switch".

"Yeah, sure!  We have Linux.  Here, take a look at our Linux."
"But, while you are doing that, you might as well look at AIX."

-Original Message-

Heh-heh, Burleson talking about running Oracle on WINE and IBM dropping
AIX..

What a joke...

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

Trigger Firing and Execution of Body Code

2003-07-24 Thread Odland, Brad

Rich noticed at a large number of executions for a trigger on a table the
header looks like this and now we are a bit confused. Consider the
following:


AFTER INSERT OR UPDATE ON BLAH.PARTMASTER
 FOR EACH ROW
 WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
NULL)
BEGIN

(Trigger with many executions)


ON another trigger with a OF clause

 AFTER INSERT OR UPDATE OF commodity_code ON blah.partmaster
FOR EACH ROW
WHEN (new.commodity_code != old.commodity_code)
BEGIN

(Trigger with few executions)


Is the following statement true?

A trigger can fire but will not always execute the code in the body.

I believe it is based on the examples above. The top trigger will fire on
every insert and update of any column in the table but will not run code in
the body and just exit thus incrementing an execution stat. The second
trigger will execute when the commodity code column is updated. Keep in mind
I am saying here EXECUTE not FIRE the BODY.

So a trigger is said to execute when the condition for the trigger (AFTER
INSERT OR UPDATE in this case) is met.

The interesting issue to is that the first trigger grabs 8k of memory every
time it fires and it has executed 900,000 time in the past month and a half.
I suspect that a trigger is a continuous package of compiled code that when
the initial condition of ON INSERT OR UPDATE ON TABLE executes the trigger
code. The WHEN clause in the code is simple logical branching condition like
an IF statement. When the condition is not met it skips the body and
continues to the end resulting in the trigger "executing" or "firing" even
though the body was not processed.

questions, comments, suggestions?
thoughts, feelings, ideas?
statements, opinions, conjecture?
Am I nuts, should I care?


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

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

Re[2]: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread Jonathan Gennick

Thursday, July 24, 2003, 10:09:25 AM, Dave wrote:
DP> Compared actual result set
DP> with expected result set via minus.

I've done that too. I need to search the cobwebs of my
memory a bit, but I recall having use MINUS both ways to be
sure:

results MINUS expected_results
tells you whether the real results included any rows
that are unexpected

expected_results MINUS results
tells you whether results omitted any expected rows

I seem to recall once having to use GROUP BY and COUNT to
ensure that the *right quantity* of each row was in the
result set.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to [EMAIL PROTECTED] and 
include the word "subscribe" in either the subject or body.

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

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

The trace seems to be from when the index is not analyzed. The CBO then 
uses defaults for the index statistics - leaf_blocks=25 and clustering 
factor=800. These defaults are much lower than when the index is analyzed 
and the resulting cost for using the index is very low (7 compared to 1676 
for a full scan). When you analyze the index, the statistics will be orders 
of magnitude larger - I estimate that the clustering factor will be > 
300,000, and therefore the cost of using the index exceeds that of the full 
scan ( still 1676 ).

There are two things you can do
Leave the index un-analyzed if it works for you ( I have a few tables where 
I use that "trick")
Set optimizer_index_cost_adj to a value lower than 100 - again if it works 
for you. Test that it does not adversely affect other queries. Many 
advocate that it should be set lower but I have not had any luck with it.

At 04:24 AM 7/24/2003 -0800, you wrote:
Hi Tanel,


did you analyze your table in addition to index as well?
first time you were probably using RBO, which always counts index access
better than table access.

i have analyzed PROFILE table also and hope it's CBO by default in 9i.
anyway,it is CBO right from the beginning in my case here.
SQL>select num_rows,avg_row_len,chain_cnt from user_Tables where 
table_name='PROFILE';
  NUM_ROWS AVG_ROW_LEN  CHAIN_CNT  BLOCKS
   ---  -  --
736820 168 42  17407

because of optimizer_index_cost_adj and optimizer_index_caching 
parameters.

optimizer_index_cost_adj = 100
optimizer_index_caching = 0
db_file_multiblock_read_count = 16
it's called index skip scanning
Thanx for the info Tanel. I was not knowing this.
As u said ,I have attached the Trace file also.

Kindly throw some light on this Tanel.

Regards,
Jp.
Content-Disposition: attachment;
filename==?iso-2022-jp?Q?memb=5Fora=5F2400.trc?=
Content-Type: application/octet-stream
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Recommendation for "cheap" HA solution

2003-07-24 Thread Daiminger, Helmut

Title: Recommendation for "cheap" HA solution





Hi!


We are looking into establishing some sort of high availability solution here. We are running 9.2.0 on Sun Fire 280 (2 processors).

Since we are on a tight budget, we are looking into various solutions for HA.


One option would be to use Sun Cluster Server or Veritas Cluster Server. If one box fails, the db just fails over to the other node. The problem is that we don't have a cluster guy here...

The other Option would be to use RAC, but this is the most expensive solution, I guess...


Does anybody use any other HA solution that is affordable? Failover time should be less than 15 minutes, although "frequent" outages (i.e. once a month or so) are tolerable.

Don't blame me for these requirements; it was not my idea...


This is 9.2.0 on Sun Solaris.


Thanks,
Helmut

Re: Oracle on linux vs. on windows

In the middle of the article:

"Using WINE, you can run Oracle for Windows, using Linux as the underlying
operating system"

I think Burleson mixed up Wine with WMWare, WINE isn't even able to run
notepad correctly, not to talk about complex multithreaded app like
Oracle...

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 5:24 PM


> Tanel,
>  I read it differently. Don talked about the different os that you
> could run and when talking about WINE it was in reference to the client
> os being Linux and being able to run the standard office packages that
> Windows provides. I found no reference to running Oracle on WINE.
> Ron
>
> >>> [EMAIL PROTECTED] 07/24/03 09:34AM >>>
> Extproc setup Q?Heh-heh, Burleson talking about running Oracle on WINE
> and IBM dropping AIX..
>
> What a joke...
>
> Tanel.
>   - Original Message -
>   From: Boivin, Patrice J
>   To: Multiple recipients of list ORACLE-L
>   Sent: Thursday, July 24, 2003 3:34 PM
>   Subject: Oracle on linux vs. on windows
>
>
>   I found this item on the 'net this morning:
>
>   http://rootprompt.org/article.php3?article=5475
>
>   Patrice.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ron Rogers
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>


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

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

2003-07-24 Thread Rachel Carmichael

I tried it.

I got the message "error creating the file, file exists". Tablespace
was not created (as expected). However the FILE itself also was not
created, as if Oracle cleaned up after itself.


--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> You'll want to modify your statement first to have different file
> names for
> each datafile.  Just pointing it out in case the obvious was
> overlooked...
> 
> BTW, anyone know what that statement would do?  Would it error out? 
> Gotta
> get another playground for testing...
> 
> Rich
> 
> Rich Jesse   System/Database Administrator
> [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
> 
> 
> > -Original Message-
> > From: Biddell, Ian [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 24, 2003 12:34 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Datafile sizing
> > 
> > 
> > Hi All,
> > 
> > I have seen previous discussions on datafile sizing and adding 64k
> to
> > the size for the bitmap header.
> > Also other people saying that they just go for 2001MB when they
> want a
> > 2000mb datafile.
> > My question is does each datafile require this within the 
> > tablespace or
> > is it just one per tablespace to facilitate LMT ?
> > 
> > So if I have this statement
> > CREATE TABLESPACE csis_l_tables LOGGING 
> > DATAFILE 'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,  
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
> >  'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 4000M
> > 
> > extent management local uniform size 64m SEGMENT SPACE
> > MANAGEMENT AUTO;
> > 
> > Do I add 1mb to each datafile or just the first?
> > 
> > Thanks
> > Ian
> -- 
> 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).
> 


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.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: Oracle on linux vs. on windows

2003-07-24 Thread Ron Rogers

Tanel,
 I read it differently. Don talked about the different os that you
could run and when talking about WINE it was in reference to the client
os being Linux and being able to run the standard office packages that
Windows provides. I found no reference to running Oracle on WINE.
Ron

>>> [EMAIL PROTECTED] 07/24/03 09:34AM >>>
Extproc setup Q?Heh-heh, Burleson talking about running Oracle on WINE
and IBM dropping AIX..

What a joke...

Tanel.
  - Original Message - 
  From: Boivin, Patrice J 
  To: Multiple recipients of list ORACLE-L 
  Sent: Thursday, July 24, 2003 3:34 PM
  Subject: Oracle on linux vs. on windows


  I found this item on the 'net this morning:

  http://rootprompt.org/article.php3?article=5475 

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

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

RE: Datafile sizing

2003-07-24 Thread Jesse, Rich

You'll want to modify your statement first to have different file names for
each datafile.  Just pointing it out in case the obvious was overlooked...

BTW, anyone know what that statement would do?  Would it error out?  Gotta
get another playground for testing...

Rich

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


> -Original Message-
> From: Biddell, Ian [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 12:34 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Datafile sizing
> 
> 
> Hi All,
> 
> I have seen previous discussions on datafile sizing and adding 64k to
> the size for the bitmap header.
> Also other people saying that they just go for 2001MB when they want a
> 2000mb datafile.
> My question is does each datafile require this within the 
> tablespace or
> is it just one per tablespace to facilitate LMT ?
> 
> So if I have this statement
> CREATE TABLESPACE csis_l_tables LOGGING 
>   DATAFILE 'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,  
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 8000M,
>'D:\oracle\oradata\gcdev\tables_l01.dbf' SIZE 4000M
> 
>   extent management local uniform size 64m SEGMENT SPACE
> MANAGEMENT AUTO;
> 
> Do I add 1mb to each datafile or just the first?
> 
> Thanks
> Ian
-- 
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: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread Dave Phillips

At a previous job, I used MINUS as part of a package to perform
automated testing of transaction processing. Compared actual result set
with expected result set via minus. IF rows returned then if failed and
returned rows were written to error table for review. Worked well for
what we needed it to do.

David Phillips
Support DBA
Gasper Corp

-Original Message-
Sent: Thursday, July 24, 2003 9:04 AM
To: Multiple recipients of list ORACLE-L


I'm doing research for an article on union queries. I'm
interested in finding examples of problems that were solved
using UNION, UNION ALL, INTERSECT, or MINUS, with the latter
two being of special interest because I don't see them used
very often. If you can think of an interesting problem
you've solved using one of these keywords, I'd love to hear
about it.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to [EMAIL PROTECTED] and 
include the word "subscribe" in either the subject or body.

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

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

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

do i have block corruption?

2003-07-24 Thread rgaffuri

 1  CREATE  INDEX myIndex
  2   ON myTab (myCol)
  3PCTFREE  1
  4STORAGE (
  5   INITIAL 5M
  6   NEXT5M
  7   MINEXTENTS  1
  8   MAXEXTENTS  UNLIMITED
  9*  PCTINCREASE 0)
 10  /
 ON POS (ACCT_NO)
*
ERROR at line 2:
ORA-08103: object no longer exists

i try to create a table with a join off of this column and I get the same error. I ran 
alter table validate structure and didnt get any errors(do these errors log to a 
table? I didnt see any docs on OTN about this?)

not much on metalink

I was able to do a 'create table as' and create a duplicate
of the table which suprised me... 

-- 
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: Extproc setup Q?

2003-07-24 Thread Jack van Zanen

Title: Message




Tried that still the same error 
message.
Any other hints & tips for me to try? 
 
Just curious though ,why is it DLLS if I'm working on 
LINUX
 
 
Jack

  
  -Original Message-  
   From: Munish Bajaj [mailto:[EMAIL PROTECTED] 
  Sent: Thursday, July 24, 2003 2:30 PMTo: Multiple 
  recipients of list ORACLE-LSubject: RE: Extproc setup 
  Q?
  
  Please add the line in red to your listener.ora file. 
  Extproc has been made secure in Oracle 9i. The following line has to be added 
  to execute your own external procedures.
  LISTENER = 
    (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 
  Linux)(PORT = 1521))     
  (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent))   ) 
  SID_LIST_LISTENER 
  =   (SID_LIST =     (SID_DESC =   (SID_NAME = 
  plsextproc)   
  (ORACLE_HOME = /oracle/app/product/9.2.0)   (PROGRAM = 
  extproc) 
      
  (ENVS="EXTPROC_DLLS=ANY")    )     (SID_DESC =   (GLOBAL_DBNAME = 
  Ora92)   
  (ORACLE_HOME = /oracle/app/product/9.2.0)   (SID_NAME = ora92) 
      )   ) 
  Regards
  Munish Bajaj
  
-Original Message-From: Jack van Zanen 
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 
16:24To: Multiple recipients of list ORACLE-LSubject: 
Extproc setup Q?
Hi All, 
I am trying to setup EXTPROC but keep 
getting ORA-28575 (Check your tnsnames.ora & listener.ora) 
Attached are my files that look OK to 
me 
Anybody?? 
 

[EMAIL PROTECTED] admin]$ cat 
tnsnames.ora # TNSNAMES.ORA 
Network Configuration File: 
/oracle/app/product/9.2.0/network/admin/tnsnames.ora 
ORA92 =   (DESCRIPTION =     (ADDRESS_LIST = 
  (ADDRESS 
= (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521))     )     (CONNECT_DATA = 
  
(SERVICE_NAME = Ora92)     )   ) extproc_connection_data =   (DESCRIPTION =     (ADDRESS_LIST =   (ADDRESS = 
(PROTOCOL = IPC)(KEY = extproc_agent))   (CONNECT_DATA = (SID = 
plsextproc))     
)     ) 

[EMAIL PROTECTED] admin]$ cat 
listener.ora # LISTENER.ORA 
Network Configuration File: 
/oracle/app/product/9.2.0/network/admin/listener.ora 
LISTENER =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = 
TCP)(HOST = Linux)(PORT = 1521))     (ADDRESS = (PROTOCOL = IPC)(KEY = 
extproc_agent))   ) 

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =   (SID_NAME = 
plsextproc)   (ORACLE_HOME = 
/oracle/app/product/9.2.0)   (PROGRAM = extproc)     )     (SID_DESC =   (GLOBAL_DBNAME = 
Ora92)   (ORACLE_HOME = 
/oracle/app/product/9.2.0)   (SID_NAME = ora92)     )   )  

TIA 
Jack van Zanen

Re: Re: Union quries: INTERSECT, MINUS, etc

2003-07-24 Thread rgaffuri

what do you mean by 'arc'?
> 
> From: "Nuno Souto" <[EMAIL PROTECTED]>
> Date: 2003/07/24 Thu AM 09:39:29 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: Union  quries: INTERSECT, MINUS, etc
> 
> - Original Message - 
> 
> 
> > I'm doing research for an article on union queries. I'm
> > interested in finding examples of problems that were solved
> > using UNION, UNION ALL, INTERSECT, or MINUS, with the latter
> > two being of special interest because I don't see them used
> > very often. If you can think of an interesting problem
> > you've solved using one of these keywords, I'd love to hear
> > about it.
> > 
> 
> UNION is useful to implement arcs.
> INTERSECT I've used very successfully
> with two CONNECT BY queries to retrieve all 
> possible paths of travel between two points 
> A and B in a table that implements flight legs.
> So don't go around saying it isn't used: I need
> it or the RAAF can't book people to flights. ;)
> 
> Cheers
> Nuno Souto
> [EMAIL PROTECTED]
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Nuno Souto
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Oracle 9i Release 2 and Oracle 8i

Title: RE: Oracle 9i Release 2 and Oracle 8i

We experience not many problems on 9202 database we have had very
good uptime. Touch wood ...
Raj

Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
are strictly personal. QOTD: Any clod
can have facts, having an opinion is an art !

-Original Message-From: April Wells
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 9:34
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Oracle 9i Release 2 and Oracle 8i
We have had some... opportunities... but so far the worst one
was connected to APPS and depreciation. It was fixed in
9.2.0.3
April Wells Oracle DBA/Oracle Apps
DBA Corporate Systems Amarillo
Texas
You will recognize your own path when you come upon it,
because you will suddenly have all the energy and imagination you will ever
need.
~ Jerry Gillies ~
-Original Message- From:
Boivin, Patrice J [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 24, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Subject: Oracle 9i Release 2 and Oracle 8i
There is a rumour going around here that 9i Release 2 is not
as "finished" as 8i Release 3 is.
i.e. that there are still problems with 9i Release 2.

I have been running it on Tru64 for months now for our OEM
repository, no problems.
I have been running it on my WinXP Pro workstation for months
for testing and iDS9iR2... no problems there
either.
Did you encounter any problems with 9i Release 2 so far that
haven't been fixed in 9i Release 2?
How do you find 9i Release2 performing?
Patrice.
-- Please see the official ORACLE-L
FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web
hosting services -
To REMOVE yourself from this mailing list, send an E-Mail
message to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (or the name of mailing
list you want to be removed from). You may also
send the HELP command for other information (like subscribing).

The information contained in this communication, including attachments, is strictly
confidential and for the intended use of the addressee only; it may also contain
proprietary, price sensitive, or legally privileged information. Notice is hereby given that
any disclosure, distribution, dissemination, use, or copying of the information by anyone
other than the intended recipient is strictly prohibited and may be illegal. If you have
received this communication in error, please notify the sender immediately by reply e-mail,
delete this communication, and destroy all copies.

Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to
this e-mail has been swept for viruses. We specifically disclaim all liability and will
accept no responsibility for any damage sustained as a result of software viruses and advise
you to carry out your own virus checks before opening any attachment.

*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: Timestamps in trace files

Ah. I think that the kernel will only emit a timestamp line immediately
before writing a db call or wait event to the trace data. This is a similar
trigger to the one that the kernel uses for writing a PARSING IN CURSOR
(PIC) section to the trace stream. You'll only see the PIC section
immediately preceding a database call line. You'll see timestamps
immediately preceding either a database call line or a WAIT line.

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

Upcoming events:
- Hotsos Clinic 101 in Washington, Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-Original Message-
Daniel Fink
Sent: Wednesday, July 23, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L

Gudmundur,
Where is this documented (so I can RTFM)?
The one piece of this I don't quite understand is that the timestamp
is not emitted twice in a row. If the long time is the triggering event, why
do I see a gap of 90 minutes (in another trace file)?

Daniel

Gudmundur Bjarni Josepsson wrote:
> 
> Daniel,
> 
> Perhaps someone else can explain this better but the documentation I've
> got on this says that the Oracle kernel emits timestamps when a long
> time has elapsed since the last line was emitted to the trace file.
> Long time is defined as tens of seconds.
> 
> Gudmundur
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > Behalf Of Daniel Fink
> > Sent: 22. júlí 2003 21:19
> > To: Multiple recipients of list ORACLE-L
> > Subject: Timestamps in trace files
> >
> >
> > I was perusing a 10046 trace file and I noticed that
> > timestamps are written to the trace file. Sometimes they were
> > very regular (3 minutes apart give or take 30 seconds) while
> > other times they were hours apart. I have noticed that two
> > timestamps are never written without any intervening
> > activity. Anyone have any idea on the reasoning behind the
> > timestamps and the 'triggering event'?
> >
> > Daniel
> >
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gudmundur Bjarni Josepsson
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Timestamps in trace files