Re: Reboot Solaris and Oracle 9i install
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).
Oracle Table API documentation
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: Oracle Table API documentation
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).
RE: SUPPRESS SQL STATEMENTS
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: Data Modelling Tools for a DBAs Job
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
Oracle Designer data model?
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: how is it possible
It probably contains an unprintable control character or an extra space.
Try doing
$ ls -li# to get the inode
$ find . -inode inode -exec mv {} newname \;
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).
Re: how is it possible
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: Clone db 9.2 on AIX 5L
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: dual
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 dv03set echo on
10:42:05 dv03
10:42:05 dv03create table jkstill.dual as select * from sys.dual;
Table created.
10:42:05 dv03
10:42:05 dv03select * from jkstill.dual;
D
-
X
1 row selected.
10:42:05 dv03
10:42:05 dv03drop table jkstill.dual;
Table dropped.
10:42:05 dv03
10:42:05 dv03insert into sys.dual values('Y');
1 row created.
10:42:05 dv03insert into sys.dual values('Z');
1 row created.
10:42:05 dv03
10:42:05 dv03commit;
Commit complete.
10:42:05 dv03
10:42:05 dv03select * from sys.dual;
D
-
X
1 row selected.
10:42:05 dv03
10:42:05 dv03create table jkstill.dual as select * from sys.dual;
Table created.
10:42:05 dv03
10:42:05 dv03select * from jkstill.dual;
D
-
X
Y
Z
3 rows selected.
10:42:05 dv03
10:42:05 dv03drop table jkstill.dual;
Table dropped.
10:42:05 dv03
10:42:05 dv03delete from sys.dual;
1 row deleted.
10:42:05 dv03delete from sys.dual;
1 row deleted.
10:42:05 dv03delete from sys.dual;
1 row deleted.
10:42:05 dv03
10:42:05 dv03insert into sys.dual values('X');
1 row created.
10:42:05 dv03commit;
Commit complete.
10:42:05 dv03
10:42:05 dv03
10:42:05 dv03create table jkstill.dual as select * from sys.dual;
Table created.
10:42:05 dv03
10:42:05 dv03select * from jkstill.dual;
D
-
X
1 row selected.
10:42:05 dv03
10:42:05 dv03drop 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 to be removed from). You may
also send the HELP
Re: Finding SID of current session
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: Database management techniques and frameworks
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, include a line containing: UNSUB ORACLE-L (or the
RE: Database management techniques and frameworks
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 individual DBA.
RE: Database management techniques and frameworks
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 periodic execution.
Oracle Java stored procedure question
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: RE: 10g new features question for beta testers
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 yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
sqldlr LOBFILE specification
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: 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).
Re: anyone use pipelined functions?
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?
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?
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 (like
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 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.94-- CACHE 100 [EMAIL
Re: anyone use pipelined functions?
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 successfully completed. Elapsed: 00:00:01.46 [EMAIL
starting/stopping trace in session
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: Quest....
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).
