Giving back sqlldr (was: Giving back Perl)
Great idea, Lisa! I may not have much to give back to the List for all that It's done for me, but this little ditty I just threw together might help someone: I've been struggling with grep, awk, and sed on the listener.log file to help me get an idea of how we're hitting our production DBs. Without much luck (not very good w/awk), a dim 10-watt bulb switched on when I thought of dumping the pertinent parts of the log into a DB so we can SQL some reports off of them. Since I rotate our listener.log daily to prevent them from growing too large (~10K attaches/day), a simple table and accompanying SQL*Load script ought to do the job. First, the simple table, indexes, and a trigger: CREATE TABLE LISTENER_LOG ( TIMESTAMP DATE, SIDNAMEVARCHAR2(10), PROGRAMVARCHAR2(80), HOSTNAME VARCHAR2(32), OSUSER VARCHAR2(32), IPADDR VARCHAR2(15) ); CREATE INDEX LISTENER_LOG_HOSTNAME ON LISTENER_LOG (HOSTNAME); CREATE INDEX LISTENER_LOG_OSUSER ON LISTENER_LOG (OSUSER); CREATE INDEX LISTENER_LOG_PROGRAM ON LISTENER_LOG (PROGRAM); REM Trigger used to remove Windohs directory specs from program name. CREATE OR REPLACE TRIGGER LISTENER_LOG_I BEFORE INSERT ON LISTENER_LOG FOR EACH ROW DECLARE BEGIN :NEW.program := LOWER(SUBSTR(:NEW.program,INSTR(:NEW.program,'\',-1)+1)); END listener_log_i; / SHOW ERRORS; Obviously, the above can be modified for size, tablespace, etc. Next, we need a SQL*Loader control file: load data infile '$ORACLE_HOME/network/log/listener.log' badfile 'listener.err' append into table listener_log when (43) = 'P' trailing nullcols (timestamp position(1:20) date "DD-MON- HH24:MI:SS", skip1 FILLER char terminated by '=', sidname enclosed by "(SID=" and ")", skip2 FILLER char terminated by "=", program enclosed by "(PROGRAM=" and ")", hostname enclosed by "(HOST=" and ")", osuser enclosed by "(USER=" and ")))", skip3 FILLER char terminated by '=', skip4 FILLER char terminated by '=', skip5 FILLER char terminated by ')', ipaddr enclosed by "(HOST=" and ")" ) I used the "WHEN" clause to only pickup those rows in the listener.log file that actually log a connection to our production DB. For this example, our production DB might be "PROD". The placement of the "(SID=PROD)" section will vary depending on how the service is defined in Oracle Networking (e.g. TNSNAMES.ORA, ONAMES, LDAP). Ours happens to start at column 43. Modify the "WHEN" clause according to your own listener.log. Additionally, with 8i (I think) there could be a "(SRVR=DEDICATED)" or "(SERVER=DEDICATED)" block -- unless you're using MTS (we're not). For this, I needed to change "skip2" to read: skip2 FILLER char enclosed by "(SRVR=" and "=", Finally, we need to call SQL*Loader (for me this is on 8.1.7 on HP/UX): sqlldr MYSCHEMANAME control=listener.sqlload log=listener_sqlload.log direct=false rows=2000 bindsize=1000 readsize=1000 ...where MYSCHEMANAME is the same user that owns the LISTENER_LOG table. Expect many many "Discarded - failed all WHEN clauses" in the listener_sqlload.log file. That's it! Afterwards, you can have all sorts of Holiday fun whilst querying your newly poplulated table. When we get to 9i, I'm thinking that an external table and a CTAS or other load script might do the trick, too. Ho-Ho-Hope this can help someone! Merry Christmas! Happy New Year! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, December 19, 2002 11:30 AM To: Multiple recipients of list ORACLE-L Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was "why don't you give back to the community". So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail m
RE: Giving back Perl
Title: Giving back Perl The scripts are posted at www.optimaldba.com/library.html. Thanks to Lisa for sharing this with all of us! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 10:30 AMTo: Multiple recipients of list ORACLE-LSubject: Giving back Perl Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was "why don't you give back to the community". So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: Giving back Perl
Title: Giving back Perl Pl. send me a copy of the code. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 12:30 PMTo: Multiple recipients of list ORACLE-LSubject: Giving back Perl Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was "why don't you give back to the community". So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: Giving back Perl
I'm interested, send at your convenience. And . "Oracle Diaper Administrator" ... Diaper ??? As in what my 3 year old has stopped needing at night? ~brian -Original Message- Sent: Thursday, December 19, 2002 11:30 AM To: Multiple recipients of list ORACLE-L Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was "why don't you give back to the community". So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Dunbar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Giving back Perl
Title: Giving back Perl Hello everyone - I've been trying to write more Perl lately and after showing my Perl mentor some of the stuff I had written over the last couple of weeks the first thing he said was "why don't you give back to the community". So I figure if I offer up my latest creation I could 1. give back and maybe help a person or two and 2. receive comments on my coding. My coding style is rather simplistic and I would appreciate any comments about what I'm doing wrong/how something could be done differently (TMTOWTDI), etc. My environment is W2K sp2/8.1.7.2. I slapped together a script that fires backup controlfile to trace (including the stored proc) and ftp's it off to another server. If anyone is interested in seeing it please email me directly. Lisa Koivu Oracle Diaper Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
