Re: Quest....

[AdamDonahue]

We run Spotlight here, although I go right to the various v$ tables.  I 
did notice some quirkiness in how Spotlight's 'Top 10 SQL' lists rows. 
It's like it returns # of rows for a static query (e.g. select 'Y' from 
some_table where some_predicate = :b1) as '1' although it's executed with 
separate predicates a large number of times, which of course also skews 
its overall statistics.   A bit confusing.

Adam




"Jonathan Lewis" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
01/08/2004 08:44 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: Quest







The number of sites I go to that use spotlight 
to keep a check on the top 10 SQL statements -
it's weird, but it always seems to be this query
against v$sqlarea.

Regards

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

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, January 08, 2004 4:29 PM


> I'm TOADing and Spotlighting right now...  :)
> 
> 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: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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


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

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

starting/stopping trace in session

[AdamDonahue]

Folks,

I've noticed -- at least on our 9.2 instances, that it does not seem 
possible to generate two trace files from the same session.  Meaning, if I 
start a trace in a session, then stop it, use tkprof to run some analysis, 
and then erase that trace file, a second start_trace does /not/ generate a 
new tracefile.  Is this expected behavior?

Adam
-- 
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: anyone use pipelined functions?

[AdamDonahue]

"My responses below are below" -- sigh, it's been a long day. lol




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
12/31/2003 01:59 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?






My responses below are below




"Ryan" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 11:54 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?






great response. questions inline.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, December 31, 2003 2:14 PM


> At the time, I did: I used simple sql_tracing for much of the analysis,
> and definitely analyzed in stages.  Unfortunately, most of the trace 
data
> was lost.  I have a couple of the files, from which I started with 
10,000
> row inserts (with commit batches of 2000) vs. 10,000 directly appended
> rows.
>
> For 10,000 single row inserts (non-pipelined function), the average was
> about 370 rows/second.
> For 10,000 appended rows (pipelined), the average was about 2100
> rows/second; this scaled mostly linearly to 100 rows (in further
> testing), and to the total number of rows in the table.  Clearly this 
was
> a big improvement to the original function, although the query/index was
> probably the best performance improvement overall.

did you test this with an 'insert select'? or was it not possible given 
your
requirements?


Not sure what you mean by 'this,' but the pre-pipeline version was simply 
a bunch of single-row inserts within the body of the procedure.  The whole 

point of the needing to stuff this into a routine is that data 
manipulation requirements made it impossible to simple use an insert into 
.. select func1(col1), func2(col2), ... func3(col3) ... The pipelined 
version leveraged insert /*+ append */ ... as select, but I never tested 
omitting the append hint (which would have simply run slower).  I'm sure, 
though, that is would have at least been faster than single row inserts. 
So, pipelining has the advantage of allowing insert into with or without 
an append hint (for example, if you need to keep constraints and or 
triggers enabled), something you couldn't do based on looped inserts.  (I 
believe you can before similar functions with bulk inserts within the 
PL/SQL code.)

>
> Another not insignificant contributor to the overall time of the 
original
> (and tuned) procedure was the target table sequence.  Adding even a 
small
> cache (10) to the sequence dropped its overall contribution to the 
runtime
> significantly, but it was still the second-largest contributor to the
> tuned function, following the insert.  I also removed redundant calls to
> USER with a single call and variable (an obvious programming flaw).

how do you know how much time was spent on sequences?


~1400 sequence values / second with nocache.
~12000 sequence values / second with cache 100.

You only seem to get marginal benefits by increasing the cache by factors 
of 10.  A cache of 100 was, I think, only a bit better performance wise 
than a cache of 10.  We probably could have generate even better 
performance by getting the initial sequence value, using a variable within 

the routine to generate it, and then recreating the sequence following the 

load.  Almost a third of the ~30 minute runtime was spent generating 
sequence values.

It's pretty easy to test the use of a cache in a 'naive' way, too.  Let's 
compare against manually generated values (using PL/SQL variable):

[EMAIL PROTECTED]/rhsac3> create sequence cache0_seq nocache;

Sequence created.

[EMAIL PROTECTED]/rhsac3> create sequence cache10_seq cache 10;

Sequence created.

[EMAIL PROTECTED]/rhsac3> create sequence cache100_seq cache 100;

Sequence created.

[EMAIL PROTECTED]/rhsac3> set timing on 
[EMAIL PROTECTED]/rhsac3> set autotrace traceonly

[EMAIL PROTECTED]/rhsac3> begin
  2  for i in 0..1 loop
  3 

[EMAIL PROTECTED]/rhsac3> declare
  2  v_n number;
  3  begin
  4 for i in 1..1 loop
  5 select cache0_seq.nextval into v_n from dual;
  6 end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.06<-- NOCACHE
[EMAIL PROTECTED]/rhsac3> / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.58
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.91


[EMAIL PROTECTED]/rhsac3> declare
  2  v_n number;
  3  begin
  4 for i in 1..1 loop 
  5 select cache10_seq.nextval into v_n from dual;
  6 end loop;
  7  end; 
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.46<-- CACHE 10
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.45
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfu

Re: anyone use pipelined functions?

[AdamDonahue]

My responses below are below




"Ryan" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 11:54 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?






great response. questions inline.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, December 31, 2003 2:14 PM


> At the time, I did: I used simple sql_tracing for much of the analysis,
> and definitely analyzed in stages.  Unfortunately, most of the trace 
data
> was lost.  I have a couple of the files, from which I started with 
10,000
> row inserts (with commit batches of 2000) vs. 10,000 directly appended
> rows.
>
> For 10,000 single row inserts (non-pipelined function), the average was
> about 370 rows/second.
> For 10,000 appended rows (pipelined), the average was about 2100
> rows/second; this scaled mostly linearly to 100 rows (in further
> testing), and to the total number of rows in the table.  Clearly this 
was
> a big improvement to the original function, although the query/index was
> probably the best performance improvement overall.

did you test this with an 'insert select'? or was it not possible given 
your
requirements?


Not sure what you mean by 'this,' but the pre-pipeline version was simply 
a bunch of single-row inserts within the body of the procedure.  The whole 
point of the needing to stuff this into a routine is that data 
manipulation requirements made it impossible to simple use an insert into 
... select func1(col1), func2(col2), ... func3(col3) ... The pipelined 
version leveraged insert /*+ append */ ... as select, but I never tested 
omitting the append hint (which would have simply run slower).  I'm sure, 
though, that is would have at least been faster than single row inserts. 
So, pipelining has the advantage of allowing insert into with or without 
an append hint (for example, if you need to keep constraints and or 
triggers enabled), something you couldn't do based on looped inserts.  (I 
believe you can before similar functions with bulk inserts within the 
PL/SQL code.)

>
> Another not insignificant contributor to the overall time of the 
original
> (and tuned) procedure was the target table sequence.  Adding even a 
small
> cache (10) to the sequence dropped its overall contribution to the 
runtime
> significantly, but it was still the second-largest contributor to the
> tuned function, following the insert.  I also removed redundant calls to
> USER with a single call and variable (an obvious programming flaw).

how do you know how much time was spent on sequences?


~1400 sequence values / second with nocache.
~12000 sequence values / second with cache 100.

You only seem to get marginal benefits by increasing the cache by factors 
of 10.  A cache of 100 was, I think, only a bit better performance wise 
than a cache of 10.  We probably could have generate even better 
performance by getting the initial sequence value, using a variable within 
the routine to generate it, and then recreating the sequence following the 
load.  Almost a third of the ~30 minute runtime was spent generating 
sequence values.

It's pretty easy to test the use of a cache in a 'naive' way, too.  Let's 
compare against manually generated values (using PL/SQL variable):

[EMAIL PROTECTED]/rhsac3> create sequence cache0_seq nocache;

Sequence created.

[EMAIL PROTECTED]/rhsac3> create sequence cache10_seq cache 10;

Sequence created.

[EMAIL PROTECTED]/rhsac3> create sequence cache100_seq cache 100;

Sequence created.

[EMAIL PROTECTED]/rhsac3> set timing on 
[EMAIL PROTECTED]/rhsac3> set autotrace traceonly

[EMAIL PROTECTED]/rhsac3> begin
  2  for i in 0..1 loop
  3 

[EMAIL PROTECTED]/rhsac3> declare
  2  v_n number;
  3  begin
  4 for i in 1..1 loop
  5 select cache0_seq.nextval into v_n from dual;
  6 end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.06<-- NOCACHE
[EMAIL PROTECTED]/rhsac3> / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.58
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.91


[EMAIL PROTECTED]/rhsac3> declare
  2  v_n number;
  3  begin
  4 for i in 1..1 loop 
  5 select cache10_seq.nextval into v_n from dual;
  6 end loop;
  7  end; 
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.46<-- CACHE 10
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.45
[EMAIL PROTECTED]/rhsac3> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.46
[EMAIL PROTECTED]/rhsac3> declare
  2  v_n number;
  3  begin
  4 for i in 1..1 loop
  5 select cache100_seq.nextval into v_n from dual;
  6 end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.

Re: anyone use pipelined functions?

[AdamDonahue]

Directly in the SQL.  We use Designer TAPI autosequence generation for 
day-to-day operations, but triggers slow down inserts and of course can't 
be enabled for direct path inserts.

Adam




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
12/31/2003 11:29 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?







That would be cool if you have time for it. 

Re the sequence:  is it assigned in a trigger, or directly in the SQL? 




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 
 12/31/2003 11:19 AM 
 Please respond to ORACLE-L 

To:Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
cc: 
Subject:Re: anyone use pipelined functions?



In the interests of documentation, and if I have time, I could engineer a 
similar 'dumb' procedure, perform trace as each modification is made, and 
post the results here.  It's pretty easy to come up with an artificial 
routine, though, to do this kind of analysis oneself.  Use Tom Kyte's 
BIG_TABLE approach, and then create a procedure to populate a separate 
table using single-row inserts, and subsequently, an insert append. 

Adam




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
12/31/2003 10:14 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?







Fantastic results Adam. 

You didn't perhaps do interim testing did you, so that you 
know how much of the benefit was due to the pipelined functions? 

You made quite a few changes, and a breakdown of the 
the benefits of each would be interesting to see. 

Jared 






[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 
12/31/2003 09:04 AM 
Please respond to ORACLE-L 
 
   To:Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
   cc: 
   Subject:Re: anyone use pipelined functions?



I recently rewrote a poor-performing data load procedure (with single row 
inserts, commit batches of 2000) to a pipelined table function, which 
enabled insert /*+ append */ into the target table, which greatly enhanced 


performance.  The original routine contained an embedded select, a second 
select using a top-level select key, and then a large loop with data 
operations culminating with an insert of each row (and sequence value 
generation).  The routine took about four hours to run. 

I joined the queries into a single inner join select, parallelized; added 
a cache to the sequence (which had been set to zero); added a second index 


to the source table to enable FFS; engineered the function to leverage 
pipelining (moving the to an insert /*+ append */ into ... select * from 
table( function ); made the requisite modifications to the target table, 
and reran the load.  The time came down to about 25 minutes.  The total 
work spent in engineering the procedure was about 2 hours, so the work put 


into tuning it + its improved runtime came in at less than the total 
original runtime!  (Of course, this does not include reenabling 
constraints, triggers, etc., but these things bring the total runtime up 
to about ~1 hour in this case, still an improvement.)

The benefit-cost ratio here was quite high!

Adam




<[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 06:24 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
anyone use pipelined functions?






I read the little blurb in the 9i new features on it. The example there 
doesnt seem very useful. What have people used it for?

any good articles with good examples on this? 

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

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

Re: anyone use pipelined functions?

[AdamDonahue]

In the interests of documentation, and if I have time, I could engineer a 
similar 'dumb' procedure, perform trace as each modification is made, and 
post the results here.  It's pretty easy to come up with an artificial 
routine, though, to do this kind of analysis oneself.  Use Tom Kyte's 
BIG_TABLE approach, and then create a procedure to populate a separate 
table using single-row inserts, and subsequently, an insert append. 

Adam




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
12/31/2003 10:14 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?







Fantastic results Adam. 

You didn't perhaps do interim testing did you, so that you 
know how much of the benefit was due to the pipelined functions? 

You made quite a few changes, and a breakdown of the 
the benefits of each would be interesting to see. 

Jared 






[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 
 12/31/2003 09:04 AM 
 Please respond to ORACLE-L 

To:Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
cc: 
Subject:Re: anyone use pipelined functions?



I recently rewrote a poor-performing data load procedure (with single row 
inserts, commit batches of 2000) to a pipelined table function, which 
enabled insert /*+ append */ into the target table, which greatly enhanced 

performance.  The original routine contained an embedded select, a second 
select using a top-level select key, and then a large loop with data 
operations culminating with an insert of each row (and sequence value 
generation).  The routine took about four hours to run. 

I joined the queries into a single inner join select, parallelized; added 
a cache to the sequence (which had been set to zero); added a second index 

to the source table to enable FFS; engineered the function to leverage 
pipelining (moving the to an insert /*+ append */ into ... select * from 
table( function ); made the requisite modifications to the target table, 
and reran the load.  The time came down to about 25 minutes.  The total 
work spent in engineering the procedure was about 2 hours, so the work put 

into tuning it + its improved runtime came in at less than the total 
original runtime!  (Of course, this does not include reenabling 
constraints, triggers, etc., but these things bring the total runtime up 
to about ~1 hour in this case, still an improvement.)

The benefit-cost ratio here was quite high!

Adam




<[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 06:24 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
anyone use pipelined functions?






I read the little blurb in the 9i new features on it. The example there 
doesnt seem very useful. What have people used it for?

any good articles with good examples on this? 

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

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



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

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

Re: anyone use pipelined functions?

[AdamDonahue]

At the time, I did: I used simple sql_tracing for much of the analysis, 
and definitely analyzed in stages.  Unfortunately, most of the trace data 
was lost.  I have a couple of the files, from which I started with 10,000 
row inserts (with commit batches of 2000) vs. 10,000 directly appended 
rows.

For 10,000 single row inserts (non-pipelined function), the average was 
about 370 rows/second.
For 10,000 appended rows (pipelined), the average was about 2100 
rows/second; this scaled mostly linearly to 100 rows (in further 
testing), and to the total number of rows in the table.  Clearly this was 
a big improvement to the original function, although the query/index was 
probably the best performance improvement overall. 

Another not insignificant contributor to the overall time of the original 
(and tuned) procedure was the target table sequence.  Adding even a small 
cache (10) to the sequence dropped its overall contribution to the runtime 
significantly, but it was still the second-largest contributor to the 
tuned function, following the insert.  I also removed redundant calls to 
USER with a single call and variable (an obvious programming flaw).

It's interesting that such a 'little' procedure can be tuned in so many 
ways, and so quickly.  There must be thousands of these problems out 
there.  And this procedure was written by an Oracle consultant!

Finally (ignorance disclaimer) I'd consider this a quick-and-dirty 
analysis, as I was mainly looking for obvious quick wins. (The nature of 
the problem and the time constraints warranted this.)  I'm sure the 
readers here would have had much more to say.

Adam





[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
12/31/2003 10:14 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: anyone use pipelined functions?







Fantastic results Adam. 

You didn't perhaps do interim testing did you, so that you 
know how much of the benefit was due to the pipelined functions? 

You made quite a few changes, and a breakdown of the 
the benefits of each would be interesting to see. 

Jared 






[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 
 12/31/2003 09:04 AM 
 Please respond to ORACLE-L 

To:Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
cc: 
Subject:Re: anyone use pipelined functions?



I recently rewrote a poor-performing data load procedure (with single row 
inserts, commit batches of 2000) to a pipelined table function, which 
enabled insert /*+ append */ into the target table, which greatly enhanced 

performance.  The original routine contained an embedded select, a second 
select using a top-level select key, and then a large loop with data 
operations culminating with an insert of each row (and sequence value 
generation).  The routine took about four hours to run. 

I joined the queries into a single inner join select, parallelized; added 
a cache to the sequence (which had been set to zero); added a second index 

to the source table to enable FFS; engineered the function to leverage 
pipelining (moving the to an insert /*+ append */ into ... select * from 
table( function ); made the requisite modifications to the target table, 
and reran the load.  The time came down to about 25 minutes.  The total 
work spent in engineering the procedure was about 2 hours, so the work put 

into tuning it + its improved runtime came in at less than the total 
original runtime!  (Of course, this does not include reenabling 
constraints, triggers, etc., but these things bring the total runtime up 
to about ~1 hour in this case, still an improvement.)

The benefit-cost ratio here was quite high!

Adam




<[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 06:24 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
anyone use pipelined functions?






I read the little blurb in the 9i new features on it. The example there 
doesnt seem very useful. What have people used it for?

any good articles with good examples on this? 

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

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

Re: anyone use pipelined functions?

[AdamDonahue]

I recently rewrote a poor-performing data load procedure (with single row 
inserts, commit batches of 2000) to a pipelined table function, which 
enabled insert /*+ append */ into the target table, which greatly enhanced 
performance.  The original routine contained an embedded select, a second 
select using a top-level select key, and then a large loop with data 
operations culminating with an insert of each row (and sequence value 
generation).  The routine took about four hours to run. 

I joined the queries into a single inner join select, parallelized; added 
a cache to the sequence (which had been set to zero); added a second index 
to the source table to enable FFS; engineered the function to leverage 
pipelining (moving the to an insert /*+ append */ into ... select * from 
table( function ); made the requisite modifications to the target table, 
and reran the load.  The time came down to about 25 minutes.  The total 
work spent in engineering the procedure was about 2 hours, so the work put 
into tuning it + its improved runtime came in at less than the total 
original runtime!  (Of course, this does not include reenabling 
constraints, triggers, etc., but these things bring the total runtime up 
to about ~1 hour in this case, still an improvement.)

The benefit-cost ratio here was quite high!

Adam




<[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/31/2003 06:24 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
anyone use pipelined functions?






I read the little blurb in the 9i new features on it. The example there 
doesnt seem very useful. What have people used it for?

any good articles with good examples on this? 

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

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

sqldlr LOBFILE specification

[AdamDonahue]

Folks,

Is there a straightforward way to dynamically create a LOBFILE 
specification via an Oracle expression?

For example, assume the following table (note there is /not/ a field for 
the file name itself):

create table clob_data (
id number
,   data clob
);

And the following import file, clob_data.dat:

1,1.pdf
2,2.pdf

and so forth.

A typical control file might read:

LOAD DATA
INFILE clob_data.dat
TRUNCATE
INTO TABLE clob_data
...
(
id
,   file_name filler
,   data lobfile( file_name ) terminated by eof
)

Let's assume, though, that we cannot execute the script from the blob file 
directory, and, also, that the filenames in the import file cannot be 
changed.

Thus, what we need is to dynamically form the full path to the file, and 
then use that as the input to the lobfile call in the control file.

Assuming a path, '/data/clobs' we'd want something like

(
id
,   file_name filler
,   data lobfile( [[psuedo] '/data/clobs/' || file_name )
)

Unfortunately, sqlldr seems to support only imported filter columns (no 
constants or expressions), and lobfile only constants, not expressions. So 
there's no seeming way to do something like,

(
id
,   file_name
,   data lobfile( expression "'/data/clobs/' || :file_name"  )
)

which of course doesn't work.

Any workarounds for this, involving /only/ modification of the control 
file, and not rewriting of the import datafile?

Thanks,

Adam
-- 
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: RE: 10g new features question for beta testers

[AdamDonahue]

This could simplify life, particularly with wait event-based tuning.  If 
Oracle properly instruments these additional layers for timing, it makes 
it easy to diagnose performance problems, not harder.  Interested in 
Cary's thoughts on this.

Adam




"Goulet, Dick" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/19/2003 07:49 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: RE: 10g new features question for beta testers






And the more that vendor, namely the database in this case, controls more 
and more of the stack the more any performance problem must be a database 
problem.  No thank you.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Friday, December 19, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


As with anything I suppose, if a single vendor can be
in control of more of the stack between application
and physical server structure then there is a greater
opportunity for benefits.  For example, ASM offers the
ability to add disks to a stripe without needing to
redistribute(reload) the entire stripeset.

A (bug-free) ASM product looks very very impressive to
me.  Time will tell how close Oracle are to achieving
it.

hth
connor

 --- [EMAIL PROTECTED] wrote: > no ASMs are
considerably different. Its supposed to
> manage everything. You dont give it a file, you give
> it entire disks and oracle does everything. Sets up
> files, manages, I/O, everything.
> 
> you only look at the tablespace level. you dont even
> install any software on it. If your on SAN, you dont
> install SAN software on it. 
> > 
> > From: "Goulet, Dick" <[EMAIL PROTECTED]>
> > Date: 2003/12/19 Fri AM 09:14:27 EST
> > To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> > Subject: RE: 10g new features question for beta
> testers
> > 
> > That is not exactly a new feature.  Oracle 9i has
> Oracle Managed Files where you give it a directory
> and then just build tablespaces.  The database picks
> the filenames for you.  Now mind you it does work,
> but I'll be damned if I use it in anything other
> than a development environment.  For some reason
> Oracle has never gotten over that DUMB SAME (Stripe
> And Mirror Everything) idea.  The concept is great
> in theory, but in practice it's absolutely abysmal
> at best.
> > 
> > Dick Goulet
> > Senior Oracle DBA
> > Oracle Certified 8i DBA
> > 
> > -Original Message-
> > Sent: Friday, December 19, 2003 8:24 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I saw a presentation from Oracle on 10g new
> features last night in Reston,VA. I know atleast one
> other person from the list was there. Since Oracle
> is releasing details and its going to be released(in
> theory) in the next 2 weeks, I was wondering if you
> guys could talk about it.
> > 
> > 1. does ASMs work as well as Oracle claims? I
> always wonder about first generation features...
> takes most software vendors a couple of generations
> to get it right(takes any project Im on just as
> long). This is a radical departure.
> > 
> > for those of you who dont know. Oracle claims that
> they will manage your disks for you. All you do is
> give Oracle some Raw Disks and Oracle will set up,
> and handle all your datafiles. All you do is look at
> logical tablespaces. It will also handle I/O
> balancing. 
> > 
> > How well does this work? Anyone test it with a
> SAN? 
> > 
> > 
> > 2. RAC Load Balancing. Oracle claims that you only
> need Oracle software from now on. They also claim
> that you can load balance multiple applications.
> Lets say you have One application that runs batch
> loads over night and a transactional application
> during the day oracle will automatically steal
> resources from the other when its not busy...
> > 
> > anyone test this? 
> > 
> > 
> > 3. Flashback database. Kyte was the presenter and
> he said that you can keep massive undo areas, so
> that if you have a failure or delete data you
> shouldnt have you can have oracle automatically
> write the DML necessary to bring it back to any
> point in time. Kyte said that regular EIDE hard
> drives that you put in home PCs are plenty fast
> enough for most systems. He recommends getting 4 300
> GB drives(1.2 TBs) for about $1400 to do this and to
> make tape backups off of this since they are really
> slow.
> > 
> > Can any beta testers comment? 
> > 
> > Im pleased with the rename tablespace feature...
> that way I dont have to update TS$ anymore... I
> wonder if it was our complaining that got them to
> add it :)
> > 
> > -- 
> > 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 

Oracle Java stored procedure question

[AdamDonahue]

Folks,

Anyone know if it's possible to pass a package record type as a parameter 
to a Java stored procedure?

e.g.,

create or replace package el_zip_pkg
is
type file_rec_type is record (
namevarchar2(255)
,   datablob
);

type file_rec_list_type is table of file_rec_type;

procedure create_zip_file( zip_file_name varchar2(255), files 
file_rec_list_type );
end el_zip_pkg;

create or replace package body el_zip_pkg
is
procedure create_zip_file( zip_file_name varchar2(255), files 
file_rec_list_type )
is
language java
name 'com.maximus.hf.utils.Zipper.createZipFile( java.lang.String, 
oracle.sql.ARRAY )';
end el_zip_pkg;

This fails, complaining that a non-schema record type cannot be passed to 
a Java routine.

So, I'm using object types, but I can't embed these within the package -- 
I preferred to keep this structure packaged within its own namespace, 
rather than relying on additional types within the calling user's schema.

Any workarounds?  Am I missing something obvious?

Adam

-- 
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: Database management techniques and frameworks

[AdamDonahue]

Perhaps it should have said Occam's razorian ;)




"Bellow, Bambi" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/05/2003 01:59 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: Database management techniques and frameworks






Adam --

Generally, my approach is X *and* Y *and* Z, and I have found that maximum
flexibility with a decent level of functionality will be of at least
moderate complexity.

And I have never seen Occam's name turned into an adjective like that.  Is
that standard?

Bambi.

-Original Message-
Sent: Friday, December 05, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L


I guess the impetus here is my Occamian approach to technology problems. I 

abstract to the point of maximum flexibility with minimal complexity, 
which often also requires maximum time and effort.  Reality of course 
dictates that a solution that ends up in common ground.

So it's not that I'm asking for 'answers' so much as I'm attempting to 
identify patterns that have worked.  From your post, it's clear your 
method isn't X -- it's X, Y, or Z depending on the situation.  Perhaps we 
can extrapolate from these variables a more generic way ... a common 
thread throughout, that is understandable, deterministic, and 
implementable.

It's Friday, ignore my ramblings.

Adam




"Bellow, Bambi" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/05/2003 12:34 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: Database management techniques and frameworks






Adam --

I've done this more times than I can count.  The answer is "it depends on
your environment, your desired results, and, more often than not, your
corporate structure".  Here's some examples:

1)  Monitoring script pages DBA group if X happens, Unix group if Y 
happens,
Network group if Z happens.  Simultaneously, XTerm windows are popped up 
in
both Operations and HelpDesk with the name and pager number of the person
paged (via uucp)

2)  Monitoring script sends messages to centralized Error Management 
System.
Error Management System handles it

3)  Monitoring script finds problem and corrects problem.  If problem
continues, email is generated

4)  Error Management System has external handles (not APIs) which can be
used to call Monitoring Scripts, which need to be modified to ustilize
System's internal structures (sometimes written in French -- *that* was
fun!)

5)  Monitoring script simply sends emails

6)  Monitoring script keeps track of the errors in log files which are
compared to log files from X time ago and only the differences are 
reported

7)  Monitoring script has redundancy built in such that the first X times 
a
particular problem is encountered, the Monitoring System ignores it, then
generates a page

8)  Monitoring script has redundnacy built in such that after the first 
time
the problem is encountered, a page is sent, and if there is still a 
problem
15 minutes later, someone else is paged and so on up the company ladder

It goes on and on.  This is largely what I've been doing for the past 8
years.  Note that the words "Monitoring script" as used above is generally
an inherently complicated conglomeration of several different scripts,
generally with a governor and/or one or more driver(s), infrequently on
different operating systems, sometimes in multiple languages and/or
utilizing, or integrating with, or extending the capabilities of, one or
more COTS products, which use different mechanisms to trigger and
synchronize them.  Generally, there is some kind of "IGNORE" functionality
which allows for specified downtime for maintenance, or "ALTERNATE"
functionality for unusual yet definable situations, and hierarchy of tests
(if the database is down, that implies that a subsequent error that a user
cannot connect to it has already been dealt with) and, occasionally has
sniffers on other boxes to determine whether remote scripts need to be run
either dependent upon remote conditions or independent of them. Sometimes,
there is a process which kicks off other jobs and manages the security.  I
particularly enjoy those where there is fault tolerance built in such that
if Monitoring script X on Machine Y craps out, Machine Z takes over and 
runs
the scripts until Y is back, then copies the logs back, kicks off Y, make
sure it runs ok, then shuts itself down.  (note to the Oracle-L historians
who might be curious, this change in my utilization is largely why my 
posts
from 10 years ago were a lot more DBMS/internals heavy and my posts 
nowadays
are more OS/script heavy.)

Regardless, I hope this answers your question and shows some of the
complexity of what you're asking for...

Bambi.

-Original Message-
Sent: Friday, December 05, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L


So your approach is to write a series of custom scripts, add them to (I 
assume) oracle's crontab for pe

RE: Database management techniques and frameworks

[AdamDonahue]

I guess the impetus here is my Occamian approach to technology problems. I 
abstract to the point of maximum flexibility with minimal complexity, 
which often also requires maximum time and effort.  Reality of course 
dictates that a solution that ends up in common ground.

So it's not that I'm asking for 'answers' so much as I'm attempting to 
identify patterns that have worked.  From your post, it's clear your 
method isn't X -- it's X, Y, or Z depending on the situation.  Perhaps we 
can extrapolate from these variables a more generic way ... a common 
thread throughout, that is understandable, deterministic, and 
implementable.

It's Friday, ignore my ramblings.

Adam




"Bellow, Bambi" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/05/2003 12:34 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: Database management techniques and frameworks






Adam --

I've done this more times than I can count.  The answer is "it depends on
your environment, your desired results, and, more often than not, your
corporate structure".  Here's some examples:

1)  Monitoring script pages DBA group if X happens, Unix group if Y 
happens,
Network group if Z happens.  Simultaneously, XTerm windows are popped up 
in
both Operations and HelpDesk with the name and pager number of the person
paged (via uucp)

2)  Monitoring script sends messages to centralized Error Management 
System.
Error Management System handles it

3)  Monitoring script finds problem and corrects problem.  If problem
continues, email is generated

4)  Error Management System has external handles (not APIs) which can be
used to call Monitoring Scripts, which need to be modified to ustilize
System's internal structures (sometimes written in French -- *that* was
fun!)

5)  Monitoring script simply sends emails

6)  Monitoring script keeps track of the errors in log files which are
compared to log files from X time ago and only the differences are 
reported

7)  Monitoring script has redundancy built in such that the first X times 
a
particular problem is encountered, the Monitoring System ignores it, then
generates a page

8)  Monitoring script has redundnacy built in such that after the first 
time
the problem is encountered, a page is sent, and if there is still a 
problem
15 minutes later, someone else is paged and so on up the company ladder

It goes on and on.  This is largely what I've been doing for the past 8
years.  Note that the words "Monitoring script" as used above is generally
an inherently complicated conglomeration of several different scripts,
generally with a governor and/or one or more driver(s), infrequently on
different operating systems, sometimes in multiple languages and/or
utilizing, or integrating with, or extending the capabilities of, one or
more COTS products, which use different mechanisms to trigger and
synchronize them.  Generally, there is some kind of "IGNORE" functionality
which allows for specified downtime for maintenance, or "ALTERNATE"
functionality for unusual yet definable situations, and hierarchy of tests
(if the database is down, that implies that a subsequent error that a user
cannot connect to it has already been dealt with) and, occasionally has
sniffers on other boxes to determine whether remote scripts need to be run
either dependent upon remote conditions or independent of them. Sometimes,
there is a process which kicks off other jobs and manages the security.  I
particularly enjoy those where there is fault tolerance built in such that
if Monitoring script X on Machine Y craps out, Machine Z takes over and 
runs
the scripts until Y is back, then copies the logs back, kicks off Y, make
sure it runs ok, then shuts itself down.  (note to the Oracle-L historians
who might be curious, this change in my utilization is largely why my 
posts
from 10 years ago were a lot more DBMS/internals heavy and my posts 
nowadays
are more OS/script heavy.)

Regardless, I hope this answers your question and shows some of the
complexity of what you're asking for...

Bambi.

-Original Message-
Sent: Friday, December 05, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L


So your approach is to write a series of custom scripts, add them to (I 
assume) oracle's crontab for periodic execution.  Do you have one single 
machine (or pair of machines) that monitor remote databases?  Or do you 
install these scripts on each database server?  Do you leverage dbms_jobs? 

 And relying on email seems kind of iffy -- what happens if you're not 
around to check your email?  Page system?  Escalation matrix in place?

Not trying to ruffle any feathers here, and certainly, I appreciate the 
time requirements in fully answering a question as broad as the one I 
submitted, but I would like to probe further into various strategies.  The 

whole "run scripts to check, install statspack, etc." approach seems both 
highly unscalable and leaves much to the whim of the i

Re: Database management techniques and frameworks

[AdamDonahue]

So your approach is to write a series of custom scripts, add them to (I 
assume) oracle's crontab for periodic execution.  Do you have one single 
machine (or pair of machines) that monitor remote databases?  Or do you 
install these scripts on each database server?  Do you leverage dbms_jobs? 
 And relying on email seems kind of iffy -- what happens if you're not 
around to check your email?  Page system?  Escalation matrix in place?

Not trying to ruffle any feathers here, and certainly, I appreciate the 
time requirements in fully answering a question as broad as the one I 
submitted, but I would like to probe further into various strategies.  The 
whole "run scripts to check, install statspack, etc." approach seems both 
highly unscalable and leaves much to the whim of the individual DBA.  So 
what, you've installed statspack?  Do you use it regularly?  Is this a 
manual review, or is some system in place to monitor changes?  How easy is 
it to deploy this framework?

(Does anyone here use Oracle's SNMP agents for monitoring?  I've leveraged 
these -- along with a home-grown SNMP NMS (in Perl) -- to some degree at a 
multiple database site to good effect.)

Are there any 'design patterns for databases' around?  Should we come up 
with some?

(I'll post my own notes on the topic of management in a future post -- 
still compiling.)

Adam




<[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/05/2003 11:09 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: Database management techniques and frameworks






We have about 20-25 instances here. Nearly all on SUN. I dont touch the 
ones on windows. I also have development responsibilities, so I dont have 
time for a checklist. 

you need to automate tasks. You cant spend your time reading the alert 
log. you should poll it and get an email when something pops up. Same with 
chained rows, tablespace sizes, etc... Write scripts for this and send 
your self emails. 

Have statspack snapshots run daily. 

> 
> From: [EMAIL PROTECTED]
> Date: 2003/12/05 Fri PM 01:49:30 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Database management techniques and frameworks
> 
> Folks,
> 
> I thought it'd be interesting to take a survey on what techniques and 
> frameworks DBA's on this list use to manage their Oracle databases.  I 
> imagine that some of us manage only a single database and instance, but 
in 
> those configurations where there are many instances, multiple databases, 

> different platforms/versions, etc., what are some of the strategies for 
> management in place?  What daily tasks do you perform, and how do you 
> organize them?  How do you manage user requests (individually or as part 

> of a larger environment)?  How do you handle jobs?  Organization 
> techniques?  Naming standards?  User/application deployment framework, 
> etc., etc.?
> 
> (Obviously we could write a book about this -- there's an idea! -- but 
> summaries and pointers would be interesting.  Perhaps we can come up 
with 
> a best practices document and associated framework for Oracle database 
> management.)
> 
> Thanks,
> 
> Adam
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

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

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

Database management techniques and frameworks

[AdamDonahue]

Folks,

I thought it'd be interesting to take a survey on what techniques and 
frameworks DBA's on this list use to manage their Oracle databases.  I 
imagine that some of us manage only a single database and instance, but in 
those configurations where there are many instances, multiple databases, 
different platforms/versions, etc., what are some of the strategies for 
management in place?  What daily tasks do you perform, and how do you 
organize them?  How do you manage user requests (individually or as part 
of a larger environment)?  How do you handle jobs?  Organization 
techniques?  Naming standards?  User/application deployment framework, 
etc., etc.?

(Obviously we could write a book about this -- there's an idea! -- but 
summaries and pointers would be interesting.  Perhaps we can come up with 
a best practices document and associated framework for Oracle database 
management.)

Thanks,

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

Programmatically redefining authid setting on packages

[AdamDonahue]

Folks,

Is there a way to change the authid setting of a package via a simple 
procedure, e.g.,

alter package x authid current_user;

I believe this is a compile-time setting, part of the source of the 
package (user_source, dbms_metadata output), which leads me to believe 
there's no simple way to redefine this short of recreating the package 
completely.  But let's say we have several hundred packages to perform 
this on -- any easier way to accomplish this?

Obviously I could use various 
dbms_metadata/substr/instr/replace/concatenate combinations, or Perl, or 
any other s//-like mechanism to generate the updated package text, but I'm 
open to other ideas, or perhaps a command/tool I'm missing.

Adam
-- 
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: Finding SID of current session

[AdamDonahue]

I believe

select sid from v$session where audsid = USERENV( 'SESSIONID' );

is a universal way to determine one's current internal SID based on the 
sessionid returned by userenv.

Adam




"George Leonard" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
12/01/2003 11:44 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
Finding SID of current session






Hi there all.

How can I find the sid of the current session,

I got a pl/sql procedure that needs to do a select from v$session_event
but only want to do it for it's own session so I need to know this for
the where clause ?

George 
 
__
George Leonard
Oracle Database Administrator
Professional Services (Oracle Business Unit)
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 e-mail is sent on the Terms and Conditions that can be accessed by 
Clicking on this link http://www.vodacom.net/legal/email.asp "
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: George Leonard
  INET: [EMAIL PROTECTED]

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


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

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

RE: dual

[AdamDonahue]

Refer to 

http://asktom.oracle.com/pls/ask/f?p=4950:8:5481819534388360937::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1562813956388,

for the answers to some of your questions below.  I think it's safe to say 
that DUAL is a rather 'magic' table.  The normal rules for DML don't work 
on it; I'd not even posit that 'rownum < 1' is in there anywhere.  More 
likely it's intercepted by the optimizer and treated specially, without 
any actual data access, in certain contexts.

Adam




[EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
10/30/2003 02:49 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: dual







I encountered the same problem once many years ago, for the same reason. 

Quite a pickle for a newbie - OWW bailed me out on that one.  :) 

Jraed 





"M Rafiq" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 10/30/2003 12:29 PM 
 Please respond to ORACLE-L 

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



I have observed  2 rows in dual till version 7.3.4. All application using 
dual in their logic having more than 2 rows were giving wrong results. 
Quick 
fix was to track it and delete more than one row(s).  Duplicate import of 
sys/system stuff were known to be culprit.

Regards
Rafiq




Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 30 Oct 2003 11:24:24 -0800

I have seen many databases crash, yes crash, when dual had more than one 
row
or less than one?

Why you say?  As someone pointed out, this was an internal table to the
kernel so Oracle used it as they felt.  It was and still is considered a
heartbeat mechanism within the kernel.  In other words, don't mess with 
it.

Thank You

Stephen P. Karniotis
Technical Alliance Manager
Compuware Corporation
Direct:  (313) 227-4350
Mobile: (248) 408-2918
Email: [EMAIL PROTECTED]
Web:  www.compuware.com

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

Do you think it will work if it has no rows ?

Waleed
-Original Message-
Sent: Thursday, October 30, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L

You may find this interesting.

Looks like a 'where rownum = 1' is always imposed on dual.

Same results on 8.1.7.4 and 9.2.0.4

Don't try this on anything other than a trashable test database.

Jared

===

10:42:04 dv03>@dt
10:42:05 dv03>
10:42:05 dv03>set echo on
10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-
X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>
10:42:05 dv03>insert into sys.dual values('Y');

1 row created.

10:42:05 dv03>insert into sys.dual values('Z');

1 row created.

10:42:05 dv03>
10:42:05 dv03>commit;

Commit complete.

10:42:05 dv03>
10:42:05 dv03>select * from sys.dual;

D
-
X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-
X
Y
Z

3 rows selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>
10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>
10:42:05 dv03>insert into sys.dual values('X');

1 row created.

10:42:05 dv03>commit;

Commit complete.

10:42:05 dv03>
10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-
X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>






<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 10/30/2003 08:54 AM
 Please respond to ORACLE-L

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



List, here is a rtfm question which I was scared to ask, but its
bothering me too much so I just can't stay quite :

"why do multiple inserts into sys.dual complete sucessfully when connected
as
sysdba, but a subsequent select * from dual show only 1 row ?"


--
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 t

Re: Clone db 9.2 on AIX 5L

[adamdonahue]

How about some more details?  Are you cloning to a similar platform?  Are 
you using a cold backup with controlfile recreation?  RMAN backup or 
restore?  RMAN duplicate?  ...

Adam




"John Blake" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/27/2003 02:24 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
Clone db 9.2 on AIX 5L






Just checking to see if anyone has been able to clone a 9.2 DB from one 
machine to another.  I have never had a problem doing this prior to 9.2, 
and am just wondering if I have overlooked something peculiar to 9i. 
Thanks in adavance
John

-- 
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: how is it possible

[adamdonahue]

Are you using vi?  Sometimes if you try to :wq! to a specific name a 
little too quickly, you might accidentally punch in a non-printable 
control character in the filename.

Adam




"AK" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/24/2003 10:24 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: how is it possible






Thanks Ron ,
This worked . But do you know why it happens . As I said its happening 
third
time with me . My unix screen behaves weired sometimes it doesn't print 
any
character I type and some time it prints characters which I never type ( 
in
this particular case ).

Thanks,
-ak

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


>
> This generally happens when there is a CTL-H embedded in the filename.
Try
>
> mv *m* anotherfilename
>
> You should be able to get at it after that.
>
> Ron Thomas
> Hypercom, Inc
> [EMAIL PROTECTED]
> Each new user of a new system uncovers a new class of bugs. -- Kernighan
>
>
>
>   [EMAIL PROTECTED]
>   com  To:
[EMAIL PROTECTED]
>   Sent by: cc:
>   [EMAIL PROTECTED]Subject:  how is it
possible
>   .com
>
>
>   10/23/2003 04:49
>   PM
>   Please respond to
>   ORACLE-L
>
>
>
>
>
>
> This happening with me 3 rd time on this hp box . When I do ls -alt I 
can
see a file in current
> directory but when I try to open it (vi/cat ) it says no such file or
directory .  I am same user
> who created the file .. ( no permission problem )
>
> Any idea ??
>
> -ak
>
>
>
>
>
> /home/ak/myscripts/shell_scr $ ls -alt
> total 4
> drwxrwxrwx   2 ak   dba 96 Oct 23 14:40 .
> -rwxrwxrwx   1 ak   dba412 Oct 23 14:40 mon_scr
> drwxr-x---  10 ak   dba   1024 Oct 13 16:07 ..
>
>  /home/ak/myscripts/shell_scr $ cat mon_scr
> cat: Cannot open mon_scr: No such file or directory
>
>  /home/ak/myscripts/shell_scr $ cat ./mon_scr
> cat: Cannot open ./mon_scr: No such file or directory
>
> /home/ak/myscripts/shell_scr $
>
> /home/ak/shell_scr $ whoami
> ak
>
>
>
>
>
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Ron Thomas
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: AK
  INET: [EMAIL PROTECTED]

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


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

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

Re: how is it possible

[adamdonahue]

It probably contains an unprintable control character or an extra space. 
Try doing 

$ ls -li# to get the inode
$ find . -inode   -exec mv {}  \;

or something similar.

Adam




"AK" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/23/2003 04:49 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
how is it possible






This happening with me 3 rd time on this hp box . When I do ls -alt I can 
see a file in current directory but when I try to open it (vi/cat ) it 
says no such file or directory .  I am same user who created the file . ( 
no permission problem )
 
Any idea ??
 
-ak
 
 
 
 
 
/home/ak/myscripts/shell_scr $ ls -alt
total 4
drwxrwxrwx   2 ak   dba 96 Oct 23 14:40 .
-rwxrwxrwx   1 ak   dba412 Oct 23 14:40 mon_scr
drwxr-x---  10 ak   dba   1024 Oct 13 16:07 ..
 /home/ak/myscripts/shell_scr $ cat mon_scr
cat: Cannot open mon_scr: No such file or directory
 /home/ak/myscripts/shell_scr $ cat ./mon_scr
cat: Cannot open ./mon_scr: No such file or directory
/home/ak/myscripts/shell_scr $
/home/ak/shell_scr $ whoami
ak

 

-- 
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 Designer data model?

[adamdonahue]

Is there any documentation (particularly a schema diagram) of the Oracle 
Designer data model, with comments?  I'm doing a lot of work with the 
repository, particularly in cross-workspace reconciliation, and knowing 
the internals would help immensely.

Thanks,

Adam
-- 
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: Data Modelling Tools for a DBAs Job

[adamdonahue]

I'm using it as well -- a bit quirky, and I don't particularly like its 
PL/SQL development interface, but for schema design-to-generation, it's 
decent.  I still prefer ERwin.

Adam




"Mercadante, Thomas F" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/14/2003 10:44 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: Data Modelling Tools for a DBAs Job






I use Oracle Designer.  Works great - I'm very happy with it.  It does 
have
w to much functionality for Database Design.  But I ignore those
parts of the product and use just what I need.  Works for me.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 14, 2003 12:24 PM
To: Multiple recipients of list ORACLE-L


There is a nice tool for Linux...DBDesigner4...


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


Hi

I have used S-Designer and ERWin in the past - both in development DBA
roles.

Don't recall exact features of S-Designer (was about 5 to 6 years ago) but
remember it to be very useful. 
Have more recent experiences of ERWin and found it very useful. It is
expensive but well worth the investment.

I used ERWin in a product development environment where, due to the
development cycle, we had numerous versions of our product's data model 
e.g.
development, test, production etc. and multiple releases of our product.
In this environment ERWin was used to maintain each data model and promote
changes between physical databases. E.g. Development environment may have
new tables, new columns, different data types etc. to the test 
environment.
ERWin can maintain these differences by connecting to the databases and
comparing the two physical databases and reporting on and generating 
scripts
to synchronise the databases.


We also used ERWin to maintain our storage parameters (tablespaces, index
tablespaces) as well as the indexes themselves.

Both ERWin and S-Designer will generate the scripts required to create 
your
database objects - prerequisite is that database, schemas and optionally
tablespaces already exist.

Clear case, MS Visual Source Safe , RCS, SCCS etc. can be used to maintain
versioning of each ERWin/S-Designer model for each build/release to label
each data model version with same label as source code of rest of product.

Hope this helps.

Regards,
Mark. 




-Original Message-
Sent: 14 October 2003 11:10
To: Multiple recipients of list ORACLE-L

How essential the following tools to a DBAs job?
(viz) Rational Rose, Clear case for Versioning etc. What are Data 
Modelling tools avbl. in the market and which are widely used
(other than Oracle Designer)?.


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

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

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

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


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

RE: SUPPRESS SQL STATEMENTS

[adamdonahue]

Not recommended to include the password on the command-line -- you're then 
exposing it to other users (via ps, for example), particularly on UNIX 
systems.

Adam




"Khedr, Waleed" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/14/2003 10:09 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
RE: SUPPRESS SQL STATEMENTS






Don't run the script while you're inside sqlplus. Run it from outside:
sqlplus un/pw @your-script

-Original Message-
Sent: Monday, October 13, 2003 12:49 PM
To: Multiple recipients of list ORACLE-L


A script dumps out table info. (sqlplus on aix 4.3.3 and oracle 8.1.7).

I cannot suppress the PROMPT>@path/scriptname and 
PROMPT> spool off statements from the report output. 

The script contains both  set heading off and set feedback off as part of
the formatting.

Posssible solutions?

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

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

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


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

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

Re: Oracle Table API documentation

[adamdonahue]

Sorry -- my question was probably unclear.

Oracle Designer includes some deployable functionality known as a "Table 
API" -- it's basically a package of procedures on the table to automate 
checking and autopopulation of fields, along with triggers on various 
events on the table.  The key is the code is automatically generated from 
within Designer  by filling out various options, setting certain fields to 
be 'autogened', and so forth.

There is very little Oracle documentation on the Web that explains the 
full workings of the Table API (at least that I could find).

I did find a book, however, that seems to serve as quasi-official 
documentation of the Table API and the Designer tool, in general.  It's 
Oracle Designer Generation by Atkins, et. al.  A bit outdated, but 
relevant nonetheless.

Thanks!

Adam 




Mladen Gogala <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/13/2003 01:19 PM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: Oracle Table API documentation






You're looking for deplorable components? There are plenty on
the market.

On Mon, 2003-10-13 at 14:44, [EMAIL PROTECTED] wrote:
> Folks,
> 
> Any good online documentation on Oracle's Table API/triggers, a 
deployable 
> component available via Oracle Designer?  The documentation for this 
suite 
> of applications is poor -- in contrast to the database and iAS docs. 
> 
> Thanks,
> 
> Adam
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Mladen Gogala
Oracle DBA




Note:
This message is for the named person's use only.  It may contain 
confidential, proprietary or legally privileged information.  No 
confidentiality or privilege is waived or lost by any mistransmission.  If 
you receive this message in error, please immediately delete it and all 
copies of it from your system, destroy any hard copies of it and notify 
the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the 
intended recipient. Wang Trading LLC and any of its subsidiaries each 
reserve the right to monitor all e-mail communications through its 
networks.
Any views expressed in this message are those of the individual sender, 
except where the message states otherwise and the sender is authorized to 
state them to be the views of any such entity.

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

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

Oracle Table API documentation

[adamdonahue]

Folks,

Any good online documentation on Oracle's Table API/triggers, a deployable 
component available via Oracle Designer?  The documentation for this suite 
of applications is poor -- in contrast to the database and iAS docs. 

Thanks,

Adam
-- 
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: Reboot Solaris and Oracle 9i install

[AdamDonahue]

Solaris, I believe, uses the System V shutdown command.  Although you 
could use init to switch to the appropriate reboot runlevel, the typical 
way to reboot a Solaris system is:

# /etc/shutdown -i 6 -g 0 -y

I'll assume you haven't mucked with your inittab.

(This provides no grace period for users to log off. Adjust -g 
appropriately.)

Adam




"Robert Jenkins" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
10/13/2003 09:04 AM
Please respond to
[EMAIL PROTECTED]


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

Subject
Re: Reboot Solaris and Oracle 9i install






Use 'shutdown now'
Then 'reboot -r'

>>> [EMAIL PROTECTED] 10/13/03 7:3 4:24 AM >>>
List,
Does anyone know the correct Solaris commands to reboot a UNIX Solaris 64 
bit os system.  Is it init, shutdown or reboot?
I'm attempting to install Oracle 9i Release 2 on a Solaris machine.  I 
received a memory error when attempting to create an Oracle database, so I 
n eed the system rebooted to load the kernel with the correct shared 
memory and semaphore settings.  The system admin is only part time, and 
currently not available.  The system is in single user mode at this time. 
With all the knowledge on this list, I thought this may be a good place to 
ask the question or maybe to point me to a document that would have the 
reboot information.
Thanks,
M.
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

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