RE: measuring TPM
Charlie, What is the perceived relevance of gaining this information? You would be much better off correlating statistics such as overall non idle wait time and database workload (# Users, Ion's/CPU etc...) to actual business functions the database is performing (invoices, sales orders, etc...). I could easily go write a job that doubles the total number of transactions per minute but has almost no effect on the other items which actually correlate application performance to database performance. Thanks, Ethan -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 9:29 AM To: Multiple recipients of list ORACLE-L I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- 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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Statspack Will Not Install Oracle 9.2.0.4
The order by st1.hash_value,ss.text_subset; -- deadlock avoidance Statement in the following Statspack code causes the package not to compile. Oracle 9.2.0.4.0 64 bit. Compatible is set to 9.2.0 If I remove the two ORDER BY's in error the package compiles fine. Could not find anything on Google or Metalink about this. insert into stats$sqltext ( hash_value , text_subset , piece , sql_text , address , command_type , last_snap_id ) select st1.hash_value , ss.text_subset , st1.piece , st1.sql_text , st1.address , st1.command_type , ss.snap_id from v$sqltext st1 , stats$sql_summary ss where ss.snap_id = l_snap_id and ss.dbid= p_dbid and ss.instance_number = p_instance_number and st1.hash_value = ss.hash_value and st1.address= ss.address and not exists (select 1 from stats$sqltext st2 where st2.hash_value = ss.hash_value and st2.text_subset = ss.text_subset ) order by st1.hash_value,ss.text_subset; -- deadlock avoidance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Statspack Will Not Install Oracle 9.2.0.4
Thanks. Gee, the Oracle sales folks keep telling our Lead Architect that RAC requires zero code changes...guess this was not true for Statspack. :) -Original Message- Sent: Monday, January 26, 2004 12:14 PM To: Multiple recipients of list ORACLE-L Ethan: You can remove the order by if you are not using RAC. Basically it is to avoid two identical SQLs inserted at the SAME time in RAC setup. If you are using RAC just add another column in the order by condition. (st1.hash_value,ss.text_subset,st1.piece) I don't have the bug # handy. But I was told this is fixed in 10g ;) KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
HP-UX Can not authenticate via OS
Any one see this one? I am working on getting more info about the configuration of this database, I don't have access to it. On AIX and Solaris, it is working fine. (9.2.0.4.0) on HP-UX. While I am trying to connect to the database using OS Authentication, the following error is raised $ sqlplus / SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 9 14:07:19 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ERROR: ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], [] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-01401: inserted value too large for column
Seeing this on AIX 5.1 64 bit Oracle 9.2.0.1.0 with char datatypes. Anyone
aware of this issue?
CREATE TABLE CHAR_TEST
(
CHAR1 CHAR(1)
)
--
CREATE OR REPLACE PROCEDURE TEST_CHAR (
po_out OUT CHAR_TEST.CHAR1%TYPE -- this column is defined as CHAR(1)
)
IS
BEGIN
po_out := 'X';
-- The value assigned to po_out (in Oracle 9i instance) is an 'X' followed
by 199 spaces.
-- The value assigned to po_out (in Oracle 8i instance) is an 'X', no
spaces.
dbms_output.put_line('[BEGIN]--' || po_out || '--[END]');
-- In Oracle 9i, the following will cause the exception: ORA-01401:
inserted value too large for column
INSERT INTO CHAR_TEST (CHAR1)
VALUES (po_out);
EXCEPTION WHEN OTHERS
THEN
dbms_output.put_line('Error: ' || SQLERRM);
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Shell script broken, bdf adds new line for long filesystem name.
Note that the foofoofoo volume causes the disk info to show up on the following line. A script I have that loops through each line coming back from bdf is breaking because of this. Any simple way to bring the line back up to the Filesystem line using awk, sed etc...? bdf -l Filesystem kbytesused avail %used Mounted on /dev/vg02/foofoo 2048000 1973804 70415 97% /foofoo /dev/vg02/foofoofoo 532486624 43711 13% /foofoofoo /dev/vg02/foofoof 512000 259286 236944 52% /foofoof -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
more 64 bit vs. 32 bit
a 32-bit environment to a 64-bit environment in order to take advantage of the vast address space to efficiently manage more data in memory, the kernel also needs to move from the constrained 32-bit environment to a 64-bit environment to efficiently support and manage the ever-expanding resources and workload. Some specific examples include: Increasing the size of Virtual Memory Manager (VMM) data structures in order to support the larger memory configurations The increased number and size of data structures in the global kernel address space required to support the possibility of thousands of physical and logical devices and their device drivers The ability to scale kernel data types to more easily support greater than 32-bit addressability in areas of 64-bit user address space, large files, number of inodes, device numbering, thread IDs, and so on 12.1.2 64-bit kernel considerations There are some points for consideration for this new 64-bit kernel. Both 32-bit and 64-bit kernels are available. Only 64-bit CHRP-compliant PowerPC machines are supported for the 64-bit kernel. Only 64-bit kernel extensions are supported; this means that no existing 32-bit kernel extensions can be reused for the 64-bit kernel. Kernel extensions and device drivers must be compiled in 64-bit mode to be loaded into the 64-bit kernel. The 32-bit and 64-bit application environments are available on all 64-bit platforms. 12.1.3 External page table scaling for 64-bit kernel (5.2.0) Prior to AIX 5L Version 5.2, the number of processes an application creates using fork() is limited to the remaining space in a PTA segment. This was also a restriction to the segments ability to create more virtual pages in expanding itself. This limitation has been removed from the Version 5.2 64-bit kernel using a dynamic allocation and creation of PTA segments at a tailend as opposed to the frontend. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Openworld Papers
Been off the list for a few days, anyone know if the papers from Openworld have been posted anyplace? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Software Download
Well I found the Openworld papers on technet but now I can't find the software download page for Oracle 9 software, have they removed it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
unread message event
Got a session just started showing up with an unread message event. Nothing in the 8.1.7 reference on it (that I could find) and not much on Metalink. Not using any replication or queueing to my knowlege, appears to be related to a EMN0 process. Any ideas? Here is the SQL is executed (appear to be around instance start up). select location_name from loc$; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Listener Lockout Feature
Any idea if a listener lockout feature is going to be added to the listener utility, i.e. lock after x failed login attempts (this is on the listener password). Are failed login attempts logged in the log file? Being lazy on that last question. Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Listener Lockout Feature
Yeah -Original Message- Sent: Wednesday, September 03, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Whoops, disregard my last post - do you mean logging on to lsnrctl? -Original Message- Post, Ethan Sent: 03 September 2003 17:39 To: Multiple recipients of list ORACLE-L Any idea if a listener lockout feature is going to be added to the listener utility, i.e. lock after x failed login attempts (this is on the listener password). Are failed login attempts logged in the log file? Being lazy on that last question. Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer_max_permutations
This makes me think of a stat that would be nice to see...it would be nice if I could see how many permutations the database was going through for each sql statement that it parsed. I might have a database that rarely uses more than 1000 permutations and I might have one that typically uses 10,000. By determining how often the statement is parsed and how many average permutations are used I might be able to more effectively judge the impact of lowering or raising this value. -Original Message- Sent: Tuesday, August 26, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Yes, it is required in one of our COTS applications. Inserts into complex views with instead-f triggers. The views are quite complex, ridiculously so, one might say. Before setting optimizer_max_permutations=1000, it took a very long time to parse those views. Jared On Tue, 2003-08-26 at 05:44, Boivin, Patrice J wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Free Training
Title: Message Anyone been contacted recently by Oracle with the offer of "free" training through their http://oracledirect.oracle.com/iccdocs/seminarList.shtmlwebsite? Is it all sales meterial? Is this part of a bigger customer retention move? Your thoughts. Greatly appreciated, Ethan
RE: perl/shell script for alert log
http://www.oracledba.co.uk/ Check out the alert log script in the admin section. Doesn't repeatedly check the same segment of the alert log file. - Ethan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, August 27, 2003 12:14 PMTo: Multiple recipients of list ORACLE-LSubject: perl/shell script for alert log I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak
RE: perl/shell script for alert log
If you use Connor's script you can modify it to send you the entire chunk of
file it is currently checking in the body of the email. Ideally you are
checking at a frequent interval so the time of the alert is usually about
the same time you get the error message.
One enhancement I suggest to every script is to configure the a SERVERERROR
trigger to throw certain errors out to the alert log. ORA-1555 is one that
will show up at the session level but not at the database level. Out of
TEMP space is another that is frequent at the session level but not the
database level. This way you know who is causing some important errors.
Overtime I find more and more session errors that are really critical
database errors. Try DDL on a table with an unusable index. Pretty big
deal on most production databases but this is a session error and would not
typically show up in the alert log. The risk of course is some huge loop
throwing 1000's of lines into the alert log. I would suggest a governor of
some sort in your servererror trigger.
- Ethan
-Original Message-
Sent: Wednesday, August 27, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L
When I grep something from the alert log,
it never tell me the date and time of the error.
Is there a setting for appending a timestamp on each error?
-Original Message-
Sent: Wednesday, August 27, 2003 1:35 PM
To: Multiple recipients of list ORACLE-L
HTH
#!/bin/sh
# This Script search for Oracle error messages in last
100 lines in the alert log file ,
# keep log to a file.
# You should pass name of ORACLE_SID as a parameter.
#!/usr/bin/sh
#
# Comments: Script checks last 100 lines of
# the alert log for specific
# Oracle errors, e-mails depending on the error.
# Parameter: ORACLE_SID
# ---
#
#
DIR=/u01/app/oracle/admin
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_HOME
ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH
COLLECTOR=`tail -100
$ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-`
if [ $COLLECTOR ]
then
echo
echo Errors found in:
$ALERT_DEST/alert_${ORACLE_SID}.log
echo
echo $COLLECTOR
echo
fi;
--- AK [EMAIL PROTECTED] wrote:
I am sure you guys might have some nice perl/shell
script to analyze alert log for errors or potential
problem . Can you share it with me /list .
Ohh thanks in advance guys .
-ak
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jose Luis Delgado
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
For technical support please email [EMAIL PROTECTED] or you can
call (972)721-8257.
This email has been scanned for all viruses by the MessageLabs Email
Security System.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Roger Xu
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: perl/shell script for alert log
Hey, awesome tip! Been here, lurking. Does that generate a trace file with more than just session id and serial# in it? Nice thing about SERVERERROR is I can get machinename, username, osuser etc and stuff it in alert log so I see who caused the error when I get the email, but your way is clearly easier to implement. - Ethan -Original Message- Sent: Wednesday, August 27, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Welcome back Ethan! An alternative is using the following lines in init.ora: event=1555 trace name errorstack level 3 event=4031 trace name errorstack level 3 event=1652 trace name processstate level 10 This catches the dreaded 01555, out of TEMP, and shared pool allocation errors *along* with the SQL/Stack from the offending process (and thus time of occurrence). And keep *all* the event lines together ;-) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Alert log....
Be careful of grepping for ^ORA-, I occasionally get ORA- (spaces) when I use this. Anjo gave me a call to make to attempt to get the line to start at the beginning but I don't think it works. Hard to test since this situation is not recreatable. AIX 64 bit 4.3 Oracle 8.1.4. - E -Original Message- Sent: Wednesday, August 27, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Roger Xu [EMAIL PROTECTED] for the guy that asked for writing the the alert.log file: dbms_system.ksdwrt(2,'Test line...'); Will write the text to the alert log. Use 1 instead of 2 to write to the trace file Use 3 to write to both. HTH JL __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: perl/shell script for alert log
Yeah, I log those. Some of the db's I support run with very small redo logs and I can't change. They get those quite frequently at times so I pretty much ignore them. My script also reacts (log,email,page, or run some script) in the event of startups, shutdowns and alter database structure. It also sends everything through a filter of known patterns and anything that is not recognized falls out. I call this the paranoid switch -p. One best practice I recommend is to automate control file backups anytime a ALTER TABLESPACE, ALTER DATABASE etc...is in the alert log. -Original Message- Sent: Wednesday, August 27, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Hi! I would add a check for Checkpoint not complete in alert just in case as well. Tanel. One enhancement I suggest to every script is to configure the a SERVERERROR trigger to throw certain errors out to the alert log. ORA-1555 is one that will show up at the session level but not at the database level. Out of TEMP space is another that is frequent at the session level but not the database level. This way you know who is causing some important errors. Overtime I find more and more session errors that are really critical database errors. Try DDL on a table with an unusable index. Pretty big deal on most production databases but this is a session error and would not typically show up in the alert log. The risk of course is some huge loop throwing 1000's of lines into the alert log. I would suggest a governor of some sort in your servererror trigger. - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Legal Mumbo Jumbo
I just got this question put to me and I didn't know the answer. For the most part I have always supported applications running commercial software such as ERP systems. In these cases it is clear that a license is required for Production and Development servers. But let's say the following occurs. 1. I download a free version of Oracle for the purpose of developing my widget. 2. One year later I begin selling my widget based web service and purchase a production server to run Oracle on. I buy my CPU based license and support. 3. I continue running Oracle on another server which is exclusively used for development. 4. I put Oracle on a laptop for the purpose of making sales calls. Will a per seat charge work here? Do I need a license for my development server? The text below would seem to say no...or yes...depending of course on how you read it. I have always understood Oracle licensing to mean, you can do what you want until you begin to sell it, then you need to license everything. This has been my experience but as I stated I work with a lot of ERP systems in which it makes sense. If you don't need to license the dev server, are you allowed to patch the database using the support from production?I searched google and did not see these specific examples answered. - Ethan License Rights We grant you a nonexclusive, nontransferable limited license to use the programs only for purposes of developing and prototyping your applications, and not for any other purpose. If you use the applications you develop under this license for any internal data processing or for any commercial or production purposes, or you want to use the programs for any purpose other than as permitted under this agreement, you must contact us, or an Oracle reseller, to obtain the appropriate license. We may audit your use of the programs. Program documentation is either shipped with the programs, or documentation may accessed online at http://otn.oracle.com/docs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 10046 tracing in PRO C programs?
I have seen this when I have created a logon trigger which activates trace but the owner of the trigger did not have ALTER SESSION granted directly to it (instead it was through a role). Sessions logging on ended up creating a trace file but nothing was in it but TRACE DUMP CONTINUED FROM FILE - Ethan -Original Message- Sent: Thursday, May 22, 2003 5:18 PM To: Multiple recipients of list ORACLE-L Glenn, I think the TRACE DUMP CONTINUED FROM FILE message occurs because file is actually opened by the SET TRACEFILE_IDENTIFIER command, and then re-opened by the SET EVENTS command. I see this all the time, except that after the *** line, there's a whole trace file full of stuff. Please forgive me if the following question seems impertinent (you seem to well know what you're doing)... Are you sure that in your test situation, your code actually makes database calls that should show up in your trace data? Aside from that, you can try leaving out the SET TRACEFILE_IDENTIFER out and seeing what happens. This will at least get rid of the TRACE DUMP CONTINUED FROM FILE message. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
C++ Issues
I am trying to track this down for a buddy, any ideas, I know nothing about C++. Thanks, Ethan Our application currently has presentation programs written in Microsoft Visual C++ that read Oracle Version 7.3.4 databases. We have Oracle Professional/2000 installed on 1 machine. The Pro*C/C++ pre-compiler provided by Oracle is the method we use to pre-compile our program. This pre-compile converts the EXEC SQL commands into C++ calls to incorporate the Oracle Database functions into the programs. Other methods I have found in research is to use OCI or ODBC calls. However everything I look at indicates that we would need to rewrite our applications to utilize additional include libraries as well as modify our SQL calls to wrap them with the appropriate language elements. We wish to fully utilize the functionality of the Microsoft Visual C++ Professional Edition environment to allow each developer to use their own machine to compile and unit test these programs. However, I am unable to get the pre compile process to work. Pro C will not run on our machines if it is not installed. We get a Incorrect environment variable. Please reinstall Pro*C/C++ message version error if we try to run the copy that is on the compiler machine from our machine. However, I can not install it because Oracle 7.3.4 does not support Windows 2000 and the installation process abends whenever I try to run it. What we are looking for is a method to compile, debug, and unit test using C++ on our machine without getting rid of Pro*C/C++ and yet utilize each developer's machine more to remove the load from our compiler machine. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sun=/var/messages HP-UX=???
Thanks, I looked through both files (they are both here) and the information in syslog seems more meaningfull on the HP-UX than what I see in the messages file, it is just the opposite on the Sun box. - Ethan -Original Message- Sent: Friday, March 14, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Actually /var/adm/messages on hpux is the message file, which my solaris 8(2.8) machine is also using /var/adm/messages, maybe I changed this, I don't remember. /var/adm/syslog/ is the default syslog directory equivalent to /var/log/ on solaris. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Is async IO configured on HP-UX?
/dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sun=/var/messages HP-UX=???
I monitor /var/messages on my Sun boxes, does HP-UX have anytype of OS log files worth monitoring? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How Reliable is Explain Plan in 9.2
Just had a fellow tell me that explain plan in completely unreliable in 9.2 and getting accurate results requires direct SGA access on executing SQL (he is working in a RAC environment). They are running Precise, a good product, but this sounds like something a sales person told him. I can only recall that occasionally the plan executed is not the plan you see in explain plan. Anyone know the truth of this issue? - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: How Reliable is Explain Plan in 9.2
That is my thinking but I will take Wolfgang's comments into consideration. -Original Message- Sent: Wednesday, March 05, 2003 12:39 PM To: Multiple recipients of list ORACLE-L Ethan, Seeing as your friend is running Precise I have heard similar pronouncements from them about the accurracy of explain plan for several versions of Oracle I'm not suprised. True, they make a very nice product, but them again explain plan is something they do themselves. Yup, sounds like a sales droid got to him. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 3/5/2003 9:59 AM Ethan, I am *shocked* we are running RAC 9202 ... I haven't seen anomalies ... does your friend have any example? Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How Reliable is Explain Plan in 9.2
Title: Message A, that is what he is talking about, I wasn't aware of v$sql_plan. -Original Message-From: Toepke, Kevin M [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 05, 2003 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How Reliable is Explain Plan in 9.2 As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time. If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view. Kevin
HP OpenView Service Desk
Anyone running Oracle to support HP OpenView Service Desk, would like to know # of users supported, options and size of system? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oh Where Oh Where Is My Redo Coming From
Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oh Where Oh Where Is My Redo Coming From
So here are two takes at the problem, one takes a look at costly (in regards to amount of redo) tables and the other indexes. Note this is only a way to guestimate this information. select owner, table_name, round((ratio_to_report(ttl) over ()) * 100, 1) as percent_ratio from ( select (m.inserts+m.deletes+m.updates)*t.avg_row_len ttl, t.owner, t.table_name from dba_tables t, all_tab_modifications m where t.table_name=m.table_name and t.owner=m.table_owner ) order by 3 desc; select owner, table_name, round((ratio_to_report(ttl) over ()) * 100, 1) as percent_ratio from ( select count(*)*sum((m.inserts+m.deletes+m.updates)) ttl, i.owner, i.table_name from all_indexes i, all_ind_columns c, all_tab_modifications m where i.index_name=c.index_name and i.table_name=c.table_name and i.owner=m.table_owner and i.table_name=m.table_name group by i.owner, i.table_name ) order by 3 desc; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oh Where Oh Where Is My Redo Coming From
Yes, that is what I was saying, however large rows or tables with a lot of indexes would also be prone to generate more redo, that is why I suggest joining DBA_TAB_MODIFICATIONS to DBA_TABLES to get avg_row_len and DBA_IND_COLUMNS to get the total # of columns indexes on the table, the thought being the more columns the more likley updates, inserts and deletes will cause index generated redo. See my other post for the solution (SQL) I came up with. - Ethan -Original Message- Sent: Friday, February 21, 2003 10:44 AM To: Multiple recipients of list ORACLE-L It might work to turn on monitoring on the tables. alter table xyz monitoring; Then periodically check dba_tab_modifications. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oh Where Oh Where Is My Redo Coming From
Sure that is the way I would typically do it, but in this case I have an application that is running 8000 batch processes per day, redo is very consistent for most of the 24 hours. I asked myself what is the simplest way to figure out which objects likely generate all of this redo. Monitoring sesstat is not going to be the most efficient and accurate method in this case. As it turns out the results (based on the SQL I posted) show the F0911 (JDE Oneworld GL Ledger) table likely produces at least 70-80% of the redo. During the batch job tuning process I will focus on tuning jobs that effect this table. There are also a ridiculous # of indexes on this table, over 120 columns involved in all of the indexes, I am sure many are redundant. Since redo log contention is one of the primary issues with this database I should see some dramatic improvements once a few of the jobs are tuned. - Ethan -Original Message- Sent: Friday, February 21, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Hi, The simpler approach is to check the user level redo (or session level redo) using the v$sysstat,sesstat views and you can find the programmes associated with those huge (!) redo. Dumping the redologs and analyzing is just complex when you have a simple solution ;) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oh Where Oh Where Is My Redo Coming From
John that doesn't make sense to me. Deleting 10,000 rows from a table with 200 columns is certainly going to generate more redo than a table with 1 column (which I am sure you know, so there must be some confusion in my understanding). The avg_row_len is going to be much bigger in the table with 200 columns. I think grabbing redo info out of sesstat is a great way to do this but the drawback is that I have to sit here and poll v$sesstat every N minutes trying to capture the session and SQL generating the redo. Not very practical in all circumstances. What I wanted is a quick way to sit down at any database and get a rough guess as to the objects which likely are involved in creating the most redo. This can help me get pointing in the direction of tuning HR jobs, Finance jobs, Inventory jobs etc...pretty easy to go query V$SQLAREA for table and INSERT, UPDATE DELETE at that point. The query I posted shows that the bulk of redo is coming from single table in the JDE Oweworld schema. It is a lot more than I am use to seeing in other systems I manage from the same table so I am pretty sure they have some poor code someplace. I can get the # of jobs and how long they run from the job queue tables, F986110. This will help me identify the top 5 jobs that hit the system. From there I will focus on the one or two jobs that hit the F0911 table. This approach to tuning is more of a top down approach. I don't want to try focusing on a single SQL statement/session as a starting point, the batch jobs will be my starting point. This information combined with the top N jobs will put me right where I want to be to begin making the biggest impact with the least amount of effort. - Ethan -Original Message- Sent: Friday, February 21, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Ethan, monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. Note that the amount of redo does not depend on the average row size. It depends on the amount of _change_ (+ some overhead). This argument might skew the situation towards a table that has a large row size but that does not have that many updates... I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. What you _do_ need to do is to use this SQL to detect the SIDs performing redo: select sid, name, value from v$statname n, v$sesstat v where v.statistic# = n.statistic# and name like 'redo size' and value 10 order by value desc You can then look at V$OPEN_CURSORS for those SIDs... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oh Where Oh Where Is My Redo Coming From
So what about an app server process that has been handling requests for 2 weeks? In this case I still won't know anything about the process(es) causing the redo. I have monitoring scripts that can trigger execution of another script(s) to enact SQL trace on the top N sessions generating redo, commits, whatever...and send me the trace files. It is nice when I see a process that runs at some point in the night and I want trace files but I also want sleep. -Original Message- Sent: Friday, February 21, 2003 11:59 AM To: Multiple recipients of list ORACLE-L select sid, name, value from v$statname n, v$sesstat v where v.statistic# = n.statistic# and name like 'redo size' and value 10 order by value desc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Programming languages that make DBA's lives easier
Title: RE: Programming languages that make DBA's lives easier That is a great link :) -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Friday, February 21, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Programming languages that make DBA's lives easier For some reason http://mindprod.com/unmain.html comes to my mind. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !!
RE: Know 1 database, know them all?
If you are always wrong then you must be right about one thing (being always wrong), so there is reason to be cheerful, at least you got one thing right. -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 18, 2003 2:19 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Know 1 database, know them all?Not quite true, as far as I know There's wait stuff in there, although not enough to my taste. There's cpu in there, and the start and stop time, which makes it possible to at least make a crude R = S + W, where the difficult part is breaking down the W into meaningful stuff. A long way to go, but I think they're aware of it. The guys from SQL Server Development I spoke to about it were very interested in the method and liked the whole idea.But don't forget that I'm always wrong.Mogens
RE: [new info] Redhat Advanced Server Dev Edition - RAC
If you asked me last week I might not have formulated much of an opinion, but I have been tainted by Mogens presentation on RAC or Not To RAC. Here are some questions you need to ask... Why not go with a box capable of the CPU's you will eventually need. Why add machines when adding CPU's might be just fine. Will these apps really not run on 64 CPU's? The added complexity of RAC and administration needs to be a factor in calculating your target uptime? My experience has been that most database downtime is a result of the following items. 1. DBA/Unix admin errors. 2. Application errors (run away batch jobs) 3. User errors (truncate table) RAC doesn't fix any of these things. However, a stand-by running a few hours behind could provide feasible solutions to most of these items. Just recently I saw a HACMP cluster (not RAC) come down causing a 1 hour outage as a result of the instructions provided directly from an IBM support rep to the Unix admin. The complexity of HA was the issue, so point #1 only becomes more likely as you add the complexity of running RAC to your environment. If you could chart all this stuff I got to feel that at some point the likelihood of one of issues above surpasses the likelihood of an actual hardware failure causing an outage. I think another point made during the presentation is that some very unique and hard to pinpoint errors can arise from running RAC. Don't be surprised if the answer back from Oracle is very vague (i.e. perhaps parameter X is set to high when circumstance Y happens... My 2 cents... - Ethan -Original Message- Sent: Thursday, February 13, 2003 9:40 AM To: Multiple recipients of list ORACLE-L With all this discussion on Why RAC?, I thought I'd chime in with our reasoning, at least as it stands before any testing. We currently have a few major databases for our ERP/MRP system, Engineering drawings, and legacy (I loathe that word) data. These databases are spread across three larger systems: Solaris, HP/UX, and OpenVMS. They are set up as any three independant systems with their own disks, own CPUs, own memory, etc. These relatively expensive systems are under utilized, and finally, are beginning to show their age (up to six years old). By combining these systems under a single system, we will be saving money in hardware cost (future upgrades and repair) as well as in service contracts, not to mention the utimate savings -- computer room floorspace! What I don't want to do is have the consolidation negatively affect the DBs in performance or downtime (perceived or real). So, the idea right now is to use commodity (read: inexpensive) servers, dual Intel (AMD???) 1Us, with a SAN, and 9iRAC. The theory being that while we'll take an initial kick in the fiscal crotch with the Oracle licensing, since we currently refuse to let go of our Concurrent User, we'll come out ahead in the long run with the added performance and unlimited user (per CPU) licensing. Also, with the commodity servers, we can switch out the server for faster CPUs without incurring more licensing cost should the need arise (yes, Cary, I'm well aware of the CPU Upgrade Myth!). With our testing, I hope to see that we'll be able to provide better uptime and performance with RAC than the total sum of the current boxes (save for the uptime on the OpenVMS box, which has 10 minutes of total downtime in the past 770+ days). Any comments on this? In the interest of bandwidth and brevity, I've been way too brief here. This should really be discussed over Guinness. Thx! Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Send Mail in Unix
I usually ... uuencode filename filename | mailx -s foo [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 13, 2003 11:39 AM To: Multiple recipients of list ORACLE-L -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Listener Hanging 64 Bit Solaris 8 Oracle 9.2
Anyone had this issue? Oracle support has been helpless so far, even with mega trace files. - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: AT command ??
Do a search for AT on msdn.microsoft.com and also check out MRTG.org, I recall a really whacky example script of how to get something running every 5 minutes using AT. It is attrocious and will get you looking for another solution rather quickly. - Ethan -Original Message- Sent: Thursday, February 13, 2003 4:00 PM To: Multiple recipients of list ORACLE-L Hi all, Can anyone give me some examples of AT command in windows? Thank you! Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle License for Training
I guess if I was going to download and learn another database it would probably be PostgreSQL http://www.postgresql.org/ it seems to be a bit more in the enterprise level of databases in comparison to MySQL but I have not been following MySQL development for some time so perhaps things are coming along. - Ethan -Original Message- Sent: Tuesday, February 11, 2003 5:04 PM To: Multiple recipients of list ORACLE-L A question for the DBA Gods on this list: Is it worth the time/effort to download MySQL and learn it? Is there going to be a viable (meaning $$) market for the product in the future? Or should I leave all the egg$ in the Oracle basket? Musing for fun and profit. Rick Weiss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [new info] Redhat Advanced Server Dev Edition - RAC
FYI, I am headed to Mogens RAC or Not to RAC presentation at the hotsos symposium, let you know what I learn! -Original Message- Sent: Tuesday, February 11, 2003 10:30 AM To: Multiple recipients of list ORACLE-L This is all cool technology, and fun stuff to play with. It all begs the questions, How many of us work for a business that actually need this? Are they willing to pay $400/user $20k/CPU above the cost of Oracle 9i EE to use it? Are they willing to pay the extra overhead required to maintain it? I'm not sure the ROI is there for many of us. Though downtime at our business is somewhat expensive, I think that a failover system or even standby database will provide adequate coverage for us, which is indeed a hot topic here right now, after our Dell SAN put us out of business for 36 hours. RAC wouldn't have helped much there. Niether would a cluster for that matter. Standby DB would have been perfect. This whole push of RAC by Oracle reminds me very much of the mlife phone campaign by ATT. Do you really need to take pictures with your phone? And what is the point of sending text messages to someone elses phone when you could just call them? ATT needs you to buy this stuff, because they have it for sale. I see RAC in a similar light. Do you need RAC? Oracle needs you to 'need' it, because they need some reason for you to spend more money on their product. Jared On Saturday 08 February 2003 21:23, Richard Ji wrote: To those who are interested in running RAC on Linux. I know we have been talking about RAC on linux lately. This is great news Redhat has made a special developer's edition for their Advanced Server which only costs $60! So we don't have to shell out $699 for a copy of RHAS 2.1 to play with RAC. http://www.redhat.com/software/advancedserver/developer/ Have fun. Richard Ji -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: AIX 4.3.3 / 8.1.7 Timed Statistics Tuning Question
Title: AIX 4.3.3 / 8.1.7 & Timed Statistics & Tuning Question No problems here AIX 4.3.3 ML9 Oracle 8.1.7 64 bit. Lisa, Be aware that there are some issues with ML9 as I am finding out with swap. We are planning on going to ML10 which should fix the problem as well as using vmtune command to change the system to use less OS Cache for files, I forgot the exact syntax. Happily the problem on these boxes is rare and no one complains so I am not rushing to make the changes but if you are running app servers on the same host you could see this a lot possibly. Search the AIX group on google for "vmtune oracle" and also check out the new Database Tuning book at IBM Redbooks, it was released in the past couple of weeks so it should be easy to find. It talks about all the vmtune settings. Also we are seeing issues with some of the psoft processes (Solaris app server) using up all the memory on the box when one of the jobs is run (not sure which one), appears to be a memory leak of some sort, we are working on it (just a note, the peoplesoft environment is not the AIX environment I spoke about). - Ethan -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 06, 2003 8:24 AMTo: Multiple recipients of list ORACLE-LSubject: AIX 4.3.3 / 8.1.7 Timed Statistics Tuning Question Good morning everyone - Quick poll for those of you on 8.1.7 and AIX 4.3.3: Do you have TIMED_STATISTICS = true? Have you encountered any problems with it? The databases I inherited have this set false all over the place, hence my tuning efforts are really limited. However I don't want to change it without checking around first. And a tuning question: This environment (peoplesoft) is very very low on memory. When the app servers and databases are up there's less than 50MB of memory free. Adding hardware is not a choice here. The databases have 100MB set for the SGA. It really looks like not much thought went into some of the parm settings. What I've read about tuning says that you must have a goal in mind. Well, afaik nothing is "broken", nothing is suffering - then again, no one really paid much attention to Oracle. It was up, fine, move on. Am I on the wrong path if my goal for tuning is to figure out if I can reduce the size of the SGA and redo logs without adversely affecting performance? Any comments are appreciated. Thanks everyone Lisa Koivu Oracle Dingbat Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax: 954-935-3639 Cell: 954-683-4459
RE: Oracle 101 Performance Tuning comes to the rescue again!
Tom, would a monitor in place have notified the admins as to a locking issue? I usually run Steve Adam's enqueue.sql script to find locked objects. Also have monitors in place that generate an email when locked exceed a specific time. Pretty typical for me to call an app dev and ask him if he forgot to issue a commit, which is usually the case. By the way I second the book recommend. I went to once of Gaja's presentations and got the gist of the methodology but when I bought the book I was able to actually absorb it. -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 11:52 AMTo: Multiple recipients of list ORACLE-LSubject: Oracle 101 Performance Tuning comes to the rescue again! All, you *MUST* buy this book. I just got called over by the Warehouse people. Their database was hung. We could log-on ok, but certain queries would hang. Ran the four "wait-state" queries and saw that two queries were hung on library cache. the two queries were an analyze table and a MV refresh - using the same table. hung them both out to dry. killed the analyze and the MV started up again. great book. solves all problems. great job Gaja, Kirti and John. you guys do the work, and I look like a hero. thanks again. Tom Mercadante Oracle Certified Professional
RE: Debate on rc commands Solaris and Oracle
: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dictionary location or initSID.ora and PWDsid.ora
I think it would just be best to follow the OFA standard and backup the everything under $ORACLE_BASE occasionally. If your sysadm runs incrementals on the server any changes to the init file should always be backed up. Changes should also be documented in some other change log/request etc..If you are following OFA then your init file is usually in $ORACLE_BASE/$ORACLE_SID/pfile with a pointer/link from $ORACLE_HOME/dbs -Original Message- Sent: Friday, January 24, 2003 9:35 AM To: Multiple recipients of list ORACLE-L D'oh! I forgot about those. Getting them out of the registry is another problem. Do you know Perl? Use Win32::TieRegistry. Jared On Thursday 23 January 2003 21:43, Naveen Nahata wrote: There are avaialable in the registry with String values 'ORA_SID_PFILE' and 'ORA_SID_PWFILE' From this you can find out the directory of the Password file Regards Naveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unix time conversion function
Title: unix time conversion function
Kinda...you can change the year to 1970 if you want, this also converts
to minutes, not seconds. It is a really ugly function but it seems to
work. You could always use perl.
function f_minutes { # Funky function I use to calculate
the number of minutes since 2000 MIN_YEAR=$( date +"%Y"
) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 )
MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +"%j"
) MIN_DAYS=$( expr "${MIN_DAYS}" - 1 )
MIN_DAYS=$( expr "${MIN_DAYS}" \* 1440 ) MIN_HOURS=$( date +"%H"
) MIN_HOURS=$( expr "${MIN_HOURS}" \* 60 )
MIN_MINS=$( date +"%M" ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS}
+ ${MIN_HOURS} + ${MIN_MINS} )) print
${MIN_TOTAL}}
-Original Message-From: Adams, Matthew (GECP, MABG,
088130) [mailto:[EMAIL PROTECTED]]Sent: Friday, January 24,
2003 1:14 PMTo: Multiple recipients of list
ORACLE-LSubject: unix time conversion function
Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date?
It would save me a lot of time not having to re-invent the
wheel.
Matt
Matt Adams - GE Appliances -
[EMAIL PROTECTED] My computer beat me at chess,
but I won when it came to kick boxing.
RE: Producing .mdb output from sqlplus or SQL or PL/SQL
Title: RE: Producing .mdb output from sqlplus or SQL or PL/SQL .mdb files are Microsoft Access database files. Best best is import the data from the database into Access using ODBC connection or to create a flat file. Access can work directly with the flat file (hey Access has been doing this for years, Oracle just got this feature!) or you can import the flat file. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Friday, January 24, 2003 3:34 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Producing .mdb output from sqlplus or SQL or PL/SQL .xls is possible ... using owa_sylk package ... (check asktom.oracle.com for more info). no clue what .mdb is ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Subject: Producing .mdb output from sqlplus or SQL or PL/SQL I am wondering if it is possible to produce .mdb or .xls format files from inside Oracle. Can this be done? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Perl Breakdown
This will make Jared happy, I finally broke down and fumbled my way through
some perl.
function f_file_date {
{
print #!/usr/bin/perl
print print scalar(localtime((stat(\${1}\))[9]))
} tmp.pl
perl tmp.pl
rm tmp.pl
}
This little diddy can be placed right in my .ksh script to get file
modification times in a consistent format, I wanted to use ls -l but then
it occurred to me that once the year changes the ls -l returns a different
formatted date entry for files modified during the last year. Maybe someone
can suggest a prettier method of doing this within a .ksh script without
calling another script.
- Ethan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: unix time conversion function
Title: unix time conversion function
Yes I knew it was easier in perl, I prefer to
distribute a single script whenever possible and I write pretty much everything
in shell. However, I just figured out a trick to put the perl directlyin
my .ksh scripts (see the post I posted a few minutes ago) so I will likely
change the way I have been doing that. Thanks for the perl however, saved
me some time looking it up!
See ya at the Symposium!
Thanks,
Ethan
-Original Message-From: Cary Millsap
[mailto:[EMAIL PROTECTED]]Sent: Friday, January 24, 2003 4:29
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
unix time conversion function
At the risk of
stating the obvious, doing it in Perl looks like this:
#!/usr/bin/perl
use Date::Format
qw(time2str);
my $t = 1043447100; #
for example
print time2str("%T %A
%d %B %Y", $t), "\n";
Cary
MillsapHotsos
Enterprises, Ltd.http://www.hotsos.comUpcoming
events:- 2003 Hotsos
Symposium on Oracle® System Performance, Feb 9-12 Dallas-
RMOUG Training Days 2003, Mar 5-6
Denver- Hotsos
Clinic101, Mar 26-28
London
Clean Up Win2K Event Log
Saw a message somewhere (Usenet maybe) about someone having trouble with the Win2K event log filling with Oracle messages whenever SYS privs are accessed. Here is a little free tool that might be useful if you are having this problem. http://ntsecurity.nu/toolbox/winzapper/ - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: create tablespace script
Title: create tablespace script
Watch out because this also creates the DDL for the
system tablespace, if you are using real temporary tablespaces it will not
create the DDL for that.
declare
cursor c_ts is select * from dba_tablespaces; cursor c_df
(p_tablespace varchar2) is select * from dba_data_files where tablespace_name =
p_tablespace; s varchar2(2000); file_name
varchar2(1000); file_size number(10);begin
dbms_output.enable(10); for ts in c_ts
loop dbms_output.put_line('create tablespace '
|| ts.tablespace_name || ' datafile '); for df
in c_df(ts.tablespace_name)
loop file_size :=
ceil(df.bytes/1024/1024);
file_name := df.file_name; s
:= s || || file_name || || ' SIZE ' || file_size || 'M, ' ||
chr(10); end
loop; s := substr(s, 1,
length(s)-3);
dbms_output.put_line(s); s := 'default storage
(' || chr(10) || ' initial ' || ts.initial_extent || chr(10)
|| ' next ' ||
ts.next_extent || chr(10) || ' maxextents unlimited);' || chr(10) ||
chr(10);
dbms_output.put_line(s); s :=
''; end loop;exception when others
then
dbms_output.put_line(dbms_utility.format_error_stack);end;/
-Original Message-From: Daiminger, Helmut
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 21,
2003 8:49 AMTo: Multiple recipients of list
ORACLE-LSubject: create tablespace script
Hi!
I want to write a "create tablespace" script that creates all
"create tablespace" statements for a database.
I got this script working if each tablesspace has only one
datafile. But how would I handle it if a tablespace consists of 2 datafiles,
e.g. datafile 5 and 87 from dba_data_files...
Is there an id for the datafiles within the
tablespace???
Any ideas?
Thanks, Helmut
filemon
Title: Message This may be a useful utility for those of you working with Oracle and NT. Found it while trying to diagnose issues on an AIX box which also has a program called filemon. http://www.sysinternals.com/ntw2k/source/filemon.shtml
Precise Purchased by Veritas
Title: Message Just saw this, perhaps it's old news. http://www.veritas.com/news/press/FeatureArticleDetail.jhtml?NewsId=21558
RE: Moving tables from one tablespace to another tablespace
Sorry Arup, I misunderstood your comment. -Original Message- Sent: Friday, January 17, 2003 5:34 PM To: Multiple recipients of list ORACLE-L And, therefore...? In case of long, array inserts are NOT performed in import. A record is committed as soon as it's inserted. Imagine a multi-thousand row table that issues a commit after each row; your log buffers will be flushed so frequently that you will experience severe log buffer related waits. COPY lets you do in chunks by specifying COPYCOMMIT, not for each row. That was the point. In import specifying COMMIT=Y does a commit after each row (in case of LONGs), a frequency impossible to control. Your excerpt from the doc simply reiterated what I mentioned. So are you merely reinforcing the post with an extract from the docs or proving it wrong? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 17, 2003 12:44 PM It lets you control the commit frequency; something impossible in export/import. http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02 .htm#40480 COMMIT Default: N Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object. If a table has nested table columns or attributes, the contents of the nested tables are imported as separate tables. Therefore, the contents of the nested tables are always committed in a transaction distinct from the transaction used to commit the outer table. If COMMIT=N and a table is partitioned, each partition and subpartition in the Export file is imported in a separate transaction. Specifying COMMIT=Y prevents rollback segments from growing inordinately large and improves the performance of large imports. Specifying COMMIT=Y is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported are rejected with a nonfatal error. If a table does not have a uniqueness constraint, Import could produce duplicate rows when you reimport the data. For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, DATE, or type columns, array inserts are not done. If COMMIT=Y, Import commits these tables after each row. -Original Message- Sent: Friday, January 17, 2003 8:45 AM To: Multiple recipients of list ORACLE-L You could use COPY command. Create a new table exactly as the old table in the new tablespace from the DDL scripts. Then use the COPY command to insert rows. It lets you control the commit frequency; something impossible in export/import. HTH. Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HOTSOS Symposium
Title: RE: HOTSOS Symposium I will be there, looking forward to meeting you, won't be staying at the hotel since I live in Dallas. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 16, 2003 10:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: HOTSOS Symposium Larry, I'll be there ... Raj -Original Message- From: Larry Elkins [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 7:19 PM To: Multiple recipients of list ORACLE-L Subject: OT: HOTSOS Symposium Listers, Just curious if anyone from the list will be going to the HOTSOS Symposium in Dallas on 2/9 thru 2/12? It sounds like it should be pretty good, but don't know yet if I will be able to tear away from work to attend. I'm already in Dallas so travel isn't the difficulty -- just finding the time is the trick. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Minutes Since Midnight 2000
I have a very clunky way of calculating the # of minutes in shell since
2000. This allows me to write the value to a variable and then do the math
later in a script to figure out how long a job ran, an alert has been
trigger etc...For those of you working in shell you know date math is not so
easy (without GNU date). This is the simplest method I have found, I would
just like the function below to look a bit cleaner.
function Minutes {
# Funky function I use to calculate the number of minutes since 2000
MIN_YEAR=$( date +%Y )
MIN_YEAR=$( expr ${MIN_YEAR} - 2000 )
MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 )
MIN_DAYS=$( date +%j )
MIN_DAYS=$( expr ${MIN_DAYS} - 1 )
MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 )
MIN_HOURS=$( date +%H )
MIN_HOURS=$( expr ${MIN_HOURS} \* 60 )
MIN_MINS=$( date +%M )
MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} ))
print ${MIN_TOTAL}
}
I am sure there is a more elegant way of doing this, anyone care to share
thier ideas/improvments/solutions?
Thanks,
Ethan
Perl is not an option Jared ;)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Moving tables from one tablespace to another tablespace
It lets you control the commit frequency; something impossible in export/import. http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02 .htm#40480 COMMIT Default: N Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object. If a table has nested table columns or attributes, the contents of the nested tables are imported as separate tables. Therefore, the contents of the nested tables are always committed in a transaction distinct from the transaction used to commit the outer table. If COMMIT=N and a table is partitioned, each partition and subpartition in the Export file is imported in a separate transaction. Specifying COMMIT=Y prevents rollback segments from growing inordinately large and improves the performance of large imports. Specifying COMMIT=Y is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported are rejected with a nonfatal error. If a table does not have a uniqueness constraint, Import could produce duplicate rows when you reimport the data. For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, DATE, or type columns, array inserts are not done. If COMMIT=Y, Import commits these tables after each row. -Original Message- Sent: Friday, January 17, 2003 8:45 AM To: Multiple recipients of list ORACLE-L You could use COPY command. Create a new table exactly as the old table in the new tablespace from the DDL scripts. Then use the COPY command to insert rows. It lets you control the commit frequency; something impossible in export/import. HTH. Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Minutes Since Midnight 2000
Yeah that would work if I want to use the database but I try to write things
in such a way that I can use them in other places and share them with other
non-DBA types, thus the reason I am not using Perl or GNU date. Oracle-L
has some very good *nix scripters so that is why I posted it here, I get
faster better responses then many of the *nix lists. Thanks for suggestion.
- E
-Original Message-
Sent: Friday, January 17, 2003 1:43 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Just idea, how about:
SQL select 24*60*(sysdate - to_date('00-00-00-01-01-2000',
'SS-MI-HH24-DD-MM-') ) from dual;
24*60*(SYSDATE-TO_DATE('00-00-00-01-01-2000','SS-MI-HH24-DD-MM-'))
--
1602151.58
You can spool this result to a file and then use cat | awk to read the
number back to your script.
HTH.
Guang
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Minutes Since Midnight 2000
Hey what can I say, I am a ksh bigot :) -Original Message- Sent: Friday, January 17, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] any more, a system without perl is hardly a system at all. it's kind of like a system without a shell. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Who has package open
Check v$open_cursor, I tested a procedure and it showed up. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I realize that a package can't be recompiled while a session has it open. How do I identify which session has a package open? -- 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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Important - Oracle Pricing on Standby/DR/Failover databases
This is interesting... So in the event of a true DR where a DR center has servers co-located and are available for hundreds of potential customers do we need to pay for the license on the box we use at the DR center if it is used more than 10 days a year? Does one pay after they have gone over the 10 day limit? What if the DR center gives you a box much larger than the one you are currently licensed for, say you go from a 4 CPU's to 12? Is is possible for hundreds of customers to be paying for Oracle licenses on just a few boxes at the DR center, with the assumption that they might be utilized? -Original Message- Sent: Thursday, January 16, 2003 4:54 AM To: Multiple recipients of list ORACLE-L Tony, Good to see your fingerprints here! I had always gone on the theory that I would need at least two of the licenses, one for production and one for the standby server. I hadn't thought about one for the DR site, on the theory, that since DR was up and running ONLY when production was not, it was the same software. I had had that information from my Oracle sales reps as well. Now it seems I'll have to go back to my IT operations people and have them verify that we are in compliance with the licensing. Or that they are ready to fight it. We do have an overall company license (Sony is a fairly large user) so I don't know how that affects our licensing as well. I hadn't realized that as an Oracle DBA I also had to be a lawyer! Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database tracking
Title: RE: Database tracking I concur, I have used MRTG for this in the past, now I have an Access database that I connect to via ODBC and get my graphs. They are very handy for trending and analysis, If someone says I had a problem yesterday around 3 pm Igo look at the charts and I usually see something out of whack. I can also look at my data over longer periods just like MRTG and RRD, I basically copied the storage logic I saw in those tools so I get a lot of history without the overhead of a ton of storage space. I opted to keep my data in the database because I can literally install the whole system in about 2 minutes on the average database. With MRTG and RRD it takes a bit more to get things set up. I have another system which stores 25 stats from the from the tables mentioned below in a single row and takes a snapshot every hour. 365 days * 24 rows per day is not really that much storage. This allows me to quickly determine what is increasing (V$SYSSTAT) and what is the impact (V$SYSTEM_EVENT). This can also be deployed in a couple of minutes on the average database. Finally I usually determine some other type of metric to gather data on, for example, J.D. Edwards OneWorld performance will be most impacted by batch jobs which are listed in the F986110 table. I have an report which gets the # of jobs, total run time and average run time for grouped by job. I can quickly see if particular jobs need to be tuned, are running more often or just taking longer for some reason. Most systems have some key components which impact performance the most. I use ofmix of the options above depending on the requirements at hand. As far as alerting goes I pretty much send everything to the Oracle alert log and I have a very nice script which allows me to respond to various patterns in the file including running commands, sending email/pages or just logging the event somewhere else. -Original Message-From: Orr, Steve [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 16, 2003 12:55 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database tracking I concur with the recommendation to use STATSPACK but you might want to augment it. I take STATSPACK snapshots every 15 minutes and if there's a performance problem caused by a few bad queries I can usually isolate the offenders. But constant fined-grained STATSPACK snapshots can be a lot of overhead so you may want something more lightweight. I've developed a DBA web app which queries V$SYSSTAT and V$SYSTEM_EVENT every minute. I assume regular queries on these tables do not impact system performance enough to worry about. I record the result sets from these queries outside of Oracle in a very light weight RRDTool "round robin database." (RRDTool is free, http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/.) From this I can produce 55 graphs on demand for 5 different time spans: daily; weekly; monthly; quarterly; and yearly. Of course damagement loves graphs/pictures. The storage needed for one plus year's worth of minute to minute V$SYSSTAT/V$SYSTEM_EVENT query data only comes to 3.2MB for each database instance being monitored. A cool thing to do is produce a graph with a visually obvious spike in some V$SYSTEM_EVENT wait statistic at say 3:15PM yesterday then correlate that graphic spike to a specific problem query as recorded in STATSPACK. It provides nice "smoking gun" incriminating evidence to be used for putting duhvelopers on trial. Steve Orr Bozeman, MT
RE: Unix Max Extent Script
Run as stored procedure scheduled using DBMS_JOB and if an alert occurs send it to the alert log using the procedure in DBMS_SYSTEM. I presume you already have a system which monitors you alert logs so it should be pretty easy to generate your email/pages without any further modification without creating a whole bunch of new scripts for people to manage. If you still insist on going the UNIX scripting route please DO NOT write a specific script to check extents. Instead write a script which runs any .sql file and checks for rows returned, then takes some action based upon the fact that rows are returned or not. Now you can use this single script to run any number of checks against your database. - Ethan -Original Message- Sent: Thursday, January 16, 2003 12:36 PM To: Multiple recipients of list ORACLE-L I have found many great SQL scripts to identify segments whose next extents will not fit into their tablespace and segments whose number of extents are approaching the max number of extents. But, what I am looking for is a Unix shell script that will run one of these scripts and mail alerts based on the results. Has anyone written a shell script that will do this that they would like to share? There are many other SQL scripts that I would like to run from cron to evaluate SQL script results. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Unix Max Extent Script
Or perhaps this... Write a program that will manually allocate extents to each object until you run out of space, then capture error and number of extents and write to a flat file using UTL_FILE package. Then export object, truncate table and import to recover the space you allocated during each test. Then use SQL loader to bring the data from the flat file into the database and write queries against the X$ constructs to see if you are approaching too many extents. Use a minimum of 5 .sh scripts and 8 control files to accomplish this task. From what I have seen this will qualify you for numerous jobs as a Senior Oracle DBA. DISLAIMER: THE ABOVE WAS A JOKE BUT DOES REFLECT THE TYPES OF THINGS I HAVE SEEN IN THE PAST. :) -Original Message- Sent: Thursday, January 16, 2003 12:36 PM To: Multiple recipients of list ORACLE-L I have found many great SQL scripts to identify segments whose next extents will not fit into their tablespace and segments whose number of extents are approaching the max number of extents. But, what I am looking for is a Unix shell script that will run one of these scripts and mail alerts based on the results. Has anyone written a shell script that will do this that they would like to share? There are many other SQL scripts that I would like to run from cron to evaluate SQL script results. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Get Owner of Trigger or Table in Trigger
Anyone know how to get the owner of a trigger inside a trigger without using the stack dump shown here by Tom Kyte which would still need some work to get just the owner name. http://groups.google.com/groups?q=get+table+owner+in+trigger+oraclehl=enlr =ie=UTF-8oe=UTF-8selm=337efeab.1901213%40newshostrnum=1 I have the same trigger in same database in different environments (DEV,TEST,QA) and the trigger needs to send info using UTL_FILE to different directories based on which environment the trigger is in. Refreshes from production are automated but the production trigger get put in these other environments and I want the same trigger to run everywhere without modification. Current plan is to figure out the owner and set the path for UTL_FILE based on that. Any ideas. Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Unix Max Extent Script
My favorite is when people go nutty checking every possible variable in their scripts before getting to the thing the script was actually written for i.e... Check to see if database sid is valid, check to see if user name is valid, check to see if time zone on server is set, what is ambient temperature of server room, log every bit of meaninless info to a log file yada yada yada -Original Message- Sent: Thursday, January 16, 2003 3:40 PM To: Multiple recipients of list ORACLE-L Post, Ethan wrote: Or perhaps this... Write a program that will manually allocate extents to each object until you run out of space, then capture error and number of extents and write to a flat file using UTL_FILE package. Then export object, truncate table and import to recover the space you allocated during each test. Then use SQL loader to bring the data from the flat file into the database and write queries against the X$ constructs to see if you are approaching too many extents. Use a minimum of 5 .sh scripts and 8 control files to accomplish this task. From what I have seen this will qualify you for numerous jobs as a Senior Oracle DBA. DISLAIMER: THE ABOVE WAS A JOKE BUT DOES REFLECT THE TYPES OF THINGS I HAVE SEEN IN THE PAST. :) Reminds me of something which I have seen which basically was a spool to a file of a SELECT * on the various views in the dictionary, wrapped into a shell script and followed by a number of greps ... Why use SELECT when you can grep with regular expressions ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Get Owner of Trigger or Table in Trigger
Hey it works! Thanks.
-Original Message-
Sent: Thursday, January 16, 2003 5:15 PM
To: Multiple recipients of list ORACLE-L
create table test (C NUMBER);
create trigger test_trig
before insert on test
is
my_ownervarchar2(30);
begin
select a.owner
into my_owner
from v$access a,
v$session s
where s.audsid = sys_context('USERENV', 'SESSIONID')
and s.sid = a.sid
and a.object = 'TEST_TRIG'
create or replace trigger test_trig
before insert on test
declare
my_ownervarchar2(30);
begin
select a.owner
into my_owner
from v$access a,
v$session s
where s.audsid = sys_context('USERENV', 'SESSIONID')
and s.sid = a.sid
and a.object = 'TEST_TRIG'
and a.type = 'TRIGGER';
dbms_output.put_line(my_owner);
end;
/
Trigger created.
SQL set serveroutput on
SQL insert into test values(1);
ORIOLE
1 row created.
SQL
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Database tracking
-- worlds_smallest_perfmon
--
-- Monitors wait time and logs information to database alert logs.
--
-- p_interval = # of minutes to wait between checks
-- p_alert = # of seconds per minute spent in wait that triggers alert
--
-- This code is completely untested, use at your own risk. Run this for
-- a few hours to establish a baseline for performance and set you your
-- alert log monitor to capture the alert string. Consider adding or
-- removing events from those used to calculate the total wait time.
--
--
create or replace procedure worlds_smallest_perfmon (p_interval number,
p_alert number) is
l_new_total number(8,0) := 0;
l_old_total number(8,0) := 0;
l_diff number(8,0) := 0;
l_log varchar2(50);
begin
while true loop
select sum(time_waited/100) into l_new_total
from v$system_event
where event in
('db file scattered read',
'db file sequential read',
'log file sync',
'latch free',
'buffer busy waits',
'enqueue',
'log buffer space'
);
l_diff := (l_new_total - l_old_total)/p_interval;
if l_diff = 0 and l_old_total 0 then
if l_diff = p_alert then
l_log := '*** PERFORMANCE ALERT WAITING ' || l_diff || '
s/min';
else
l_log := '*** WAITING ' || l_diff || ' s/min';
end if;
sys.dbms_system.ksdwrt(2,l_log);
end if;
l_old_total := l_new_total;
dbms_lock.sleep(p_interval*60);
end loop;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
-Original Message-
Sent: Wednesday, January 15, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L
A more comprehensive solution would be statspack.
A simpler solution would be to get the sum of wait time (not counting the
idle ones) . it could provide you with some measure of database
performance... You need to arrive at a baseline wait time as being normal
for your database and any deviation from that could mean some change in
performance...
Babu
Terrian, Tom
(Contractor) To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
(DAASC) cc:
[EMAIL PROTECTED]Subject: Database tracking
a.mil
Sent by:
[EMAIL PROTECTED]
01/15/2003 12:53
PM
Please respond to
ORACLE-L
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Database tracking
ha ha, done. -Original Message- Sent: Wednesday, January 15, 2003 3:46 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High The worlds smallest perfmon could be 11 bytes smaller if you changed 'while true loop' to 'loop' Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
rdist Examples
rdist is a good way to keep files (sql, tools, tnsnames) synchronized
between servers, in my case I need to replicate
/db01/foo/* to /u01/foo/*
Going from /db01/foo to /db01/foo on a different server is easy but I can
not find any examples of going to another directory structure. The docs are
not very clear to me, sorry my IQ really isn't very high (never take an IQ
test with your wife, once she finds out she is smarter things are never the
same), anyway I ramble...
http://docs.sun.com/db/doc/805-3172/6j31br5lf?a=view
My rdist config file looks like this...
HOSTS=( bunnyfoofoo )
FILES=( /db01/foo/* )
(${FILES}) - (${HOSTS})
install -R;
Here is what I run...
rdist -f rdist.txt -y -R
Works great, now how do I go from db01 to u01?
Thanks ahead of time.
- Ethan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SLA Trigger/Procedure
For reasons too long to go in to, go buy a tool is not a good option for my purposes. Besides, why buy what I can build? These are exactly the sort of challenges that make my job worth coming to. Thanks, Ethan -Original Message- Sent: Tuesday, December 03, 2002 4:14 AM To: Multiple recipients of list ORACLE-L Yup, got that covered :) If I may add another point. When dealing with SLA's, you not only have to show that the database has been servicing users that are connected (showing database uptime), but also that users can also *connect* to the database as well (the listener is servicing requests). If you were to go about this with a trigger/procedure that inserts in to a table, then this doesn't show that the database was available to everybody. Typically what we do with monitoring tools is a connect on ping, so when we are checking availability of a database we do a full connect, then select 'PING' from dual;. If there are any errors along the way we search for the error code, and deal with the appropriate alerts (TNS = Listener problem, ORA = Database problem). Of course, the problem with doing it this way, is that you are going to have to write platform dependant scripts (batches for NT, shell type scripts for Unix), and not have a one for all with a database based solution. Go buy a tool. Let it write to a table/file, alert you AND make the coffee for when you get there! ;) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SLA Trigger/Procedure
I agree what is needed is something very generic that could monitor the uptime of pretty much any process. After giving this more thought and always liking to keep things simple here is the direction I might eventually head. The logic below would result in a log file that shows all unscheduled outage periods. You would have to ensure it runs at regular intervals. (NOT VERY PRETTY, BUT SIMPLE ENOUGH) Files Required: * Control file that has crontab like entries which define periods that are either outages or times when database is expected to be running. Script Workings: * Run command/script which returns 0 or 1 (0=thingy not running, 1=thingy running) use separate command/script to log into database, grep for process etc... If (( 0 )) check control file to see if thingy should be running if suppose to be running store time trigger flag (cat time to flag file) if not suppose to be running if flag unscheduled outage was triggered but has rolled into an scheduled outage period log start and end time for outage reset flag fi fi else if flag thingy is back up log start and end time for outage reset flag fi fi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SLA Trigger/Procedure
Just a thought here for a script I think would be handy but I haven't had time to write. It is would be a simple procedure you could call to get the service level for a particular database. I suppose you would have to have some sort of way of defining normal outage windows. Basically a startup trigger would log the times in a table. You should also check the startup time against the last startup time periodically to ensure the trigger always fires. Somehow a procedure/function should be able to use this information to report the service level for the database within the last (week/month/quarter/year). I suppose I will get around to it eventually but if anyone else wants to get started on it I won't mind! Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Security Focus Link - SQL Injection White Paper
SQL Injection and Oracle - By Pete Finnigan This is the first article in a two-part series that will examine SQL injection attacks against Oracle databases. The bjective of this series is to introduce Oracle users to some of the dangers of SQL injection and to suggest some simple ways of protecting against these types of attack. http://online.securityfocus.com/infocus/1644 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Slightly OT - Who would you take with you...
Robert Freeman Anjo Kolk Cary Millsap Connor McDonald Kirti Deshpande Jared Still Jeramiah Wilton John Kanagaraj Waleed Khedr Ian MacGregor Rachel Carmichael Tim Gorman Well that is more than 10, there are plenty of others that should be on this list. I have these posters highlighted then sort by subject or name. I can be pretty sure if I see a lot of red names that I am getting into something juicy and the other posters that are not mentioned will be right in there also. That way I can collect messages for a couple weeks and still get all the good stuff in an hour or so. Sorry if I got some lazy dba's mixed by in here. - Ethan -Original Message- Sent: Monday, November 25, 2002 4:05 PM To: Multiple recipients of list ORACLE-L Board - If you had to choose the 10 top posters here at Oracle-L who provided the biggest input and knowledge, who would they be? Who are the top 10 Oracle Guru's on this news group? I'm asking because my mailbox is FULL, and I have to trash a large amount of the stored stuff thats here. I'm going to archive some of it to CD, but I have limited space, so I want to archive those people who are constantly offering the most insightful advice (I know it's available online, but I'm not always online and it's nice to be able to search these emails for targeted content). Anyone want to take a crack at a list? You are welcome to email me private if you are afraid you would hurt someone's feelings. I'd be happy to compile the lists and report back to the group the overall answers, but all email to me will be treated as strictly confidential. Robert Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
1M STRIPE SIZE BEST?
A number of papers recommend a stripe size of 1 M (even for EMC) for volumes containing data files. I also have the following email from Eyal Aronoff of Quest dated Nov 2000. A number of the white papers are more recent. The reasons for a larger stripe size on a non-RAID 5 device are: 1) Sequential reads are faster if you can take advantege of the read ahead built into the disk caching 2) If a 64K read does not start on the first block of the stripe, two spindled are locked for the duration of the read However, lately we have been testing some EMC gear and it looks like EMC have optimized both of those for smaller strip size too. The bottom line - I no longer have an opinion one way or another. The undelying technology just changes too rapidly. Eyal Your opinions/comments as far as a best practice in setting stripe sizes would be greatly appreciated. Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Invalid Objects but no errors
I had some issues on an import lately with replication packages. I have recently gone from 8.1.7.3 to .4 and some of the packages would not compile. The solution was to run repcatr.sql, repcat.sql and utlrp.sql. -Original Message- Sent: Friday, November 15, 2002 12:10 PM To: Multiple recipients of list ORACLE-L try SHOW ERRORS PACKAGE BODY DBMS_REPCAT_RGT show errors may be showing there are no errors in the package spec! Kevin -Original Message- Sent: Friday, November 15, 2002 12:40 PM To: Multiple recipients of list ORACLE-L I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: RE: CONSISTANT GETS
Hold the press. NOT IN better than NOT EXISTS? Is this theory or fact? If so is there any supporting evidence out there? This is the first I have heard of this. Thanks! -Original Message- Sent: Friday, November 15, 2002 11:35 AM To: Multiple recipients of list ORACLE-L Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Man that is ridiculous. You would think Oracle would have it's act together on DBMS_STATS package by now. Since is it supposedly so superior to analyze table you would think it might actually work. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Monday, October 07, 2002 1:34 PM To: Multiple recipients of list ORACLE-L inde It is still quirky in 9.2.0.1. Now it does not like an FBI on a table :( Check out bug# 2606697 on Metalink... - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM indexes?
Title: RE: ora-904 invalid column name Looks like this is the case, does it know something I don't know? Are indexes OK to analyze in the SYS and SYSTEM schemas? Looks like is correctly does not do tables. - Ethan
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Arr... So anyway, I am thinking, hey it's high time I start using DBMS_STATS instead of my own procedure so I kick of the following (Oracle 8.1.7.4). After the first run I have SYS and SYSTEM stats on indexes and on other schemas with NO STATS it just ignored those tables even though you can see I have GATHER EMPTY below. So I kick it off again and guess what, it starts analyzing the tables it missed the first time, including SYS and SYSTEM. Guess I am going to use DBMS_STATS.GATHER_TABLE_STATS and be a bit more specific about what I get. define estimate_percent=5 declare begin -- Can easily change to gather_schema_stats (make sure you add schema name) dbms_stats.gather_database_stats( estimate_percent,FALSE,'FOR ALL COLUMNS SIZE 1', NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER EMPTY'); dbms_stats.gather_database_stats( estimate_percent,FALSE,'FOR ALL COLUMNS SIZE 1', NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE'); exception when others then dbms_output.put_line(dbms_utility.format_error_stack); end; Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Friday, October 04, 2002 3:56 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] indexes? Importance: High I seem to recall this is a bug. You may want to check MetaLink. In any case, you don't want to analyze SYS on any version of Oracle. ( yet ) Don't see what harm in having stats on SYSTEM tables though. It's just a DBA account. Jared Post, Ethan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/04/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM indexes? Looks like this is the case, does it know something I don't know? Are indexes OK to analyze in the SYS and SYSTEM schemas? Looks like is correctly does not do tables. - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cache OCI Calls to Improve Oracle Performance on Solaris[tm] Syst
Anyone tried this one? http://soldc.sun.com/articles/oci_cache.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Perplexed
Here is one I can't figure out. I just refreshed to schema in a test database with data from production. Different exports where used about 24 hours apart. I used the exact same type of tablespaces for each 128K LMT's. After the imports I compare # of bytes and extents from dba_segments for each schema and they are just about right on, however schema A is using 7.9GB and schema B required 9.5GB. Where am I losing almost 2 GB? I did allow the import to create the objects in schema A and I used the indexfile in schema B. Indexes are contained in same tablespace as data and all tables have primary keys. Do I have duplicate storage for primary key indexes in one of the schemas? I can't figure it out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perplexed
Ignore, I found the errors of my ways. Ethan Post perotdba (AIM), epost1 (Yahoo) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Staspack Grapher/Viewer ?
I have a 1.5 MB zip file containing my old web site for called gnumetrics which uses a PL/SQL package and MRTG to chart performance data. If someone has a webserver I can load this to I will. It is old but it works. At the very least you will get some ideas on how to do this. I store all my data internally now using the same aggregation policy as MRTG to get up to one year of data in very small size. I chart it using ODBC an Access with Microsoft Graph. Works very well and I get very useful reports. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, September 19, 2002 12:10 PM To: Multiple recipients of list ORACLE-L This product seems to use the now infamous CHR which has been banned from this list :) Has anyone figured out how to use MRTG (or Cricket) to get this done? [Why buy when you can use Open Source!] John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Stress Testing Peoplesoft App
Anybody have experience with stress testing software for Peoplesoft? Links, thoughts, comments etc... Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Migration Assistant Speeds
Can anyone share experience with running database migration assistant against a 150 GB database (7.3.4) and how long it takes? Don't have the box specs yet but I think it is a 3 CPU HPUX with 2 GB Ram and a couple of unmirrored disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Function-Based Index not working
There has been some good stuff on the Usenet list lately about the debating the usefulness of CACHE as opposed to KEEP buffer pool. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, September 05, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Given the fact that the table is so small and frequently accessed, it will get cached 'automagically'. No need to do anything. Anjo. On Thursday 05 September 2002 23:43, you wrote: Rachel, With a table that small I would consider caching the table to eliminate the io. I do not know if you can cache an IOT but then it should be even faster. Ron ROR -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Clip Text Between Lines in Korn Shell
Anyone got an easy method to clip the lines between # if foo.txt contains the following? # # NAME - foo.txt # # SYNTAX - foo -dgs # # # YOU GET THE IDEA BY NOW... # # # code code code -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ADV OT: Oracle-dba.com domain for sale
I would but I got to put some money down on www.phatdba.com Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, August 29, 2002 6:48 PM To: Multiple recipients of list ORACLE-L If you're interested, email me for the asking price. Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Auditing logons
Use a logon trigger to capture everything from v$session and you can look at program name etc...it will be pretty easy to figure out who and when. Something like this in the trigger... select distinct sid into l_sid from v$mystat; insert into session_log (select * from v$session where sid = l_sid; Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Friday, August 09, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Folks, Before I go off re-inventing the wheel once again I'll ask the group is anyone has tried this before. What I have is a request from damanagement to tell them when someone connects to our PeopleSoft database using the schema username, but outside of PeopleTools. The reason is that there have been some unexplained changes to data that have occurred over the last month that is causing a pile of concern. It is believed that someone who has the schema password is using SQL*Plus or Toad to update the data when they should not be doing so. Now auditing connects for the schema account is not a problem, but determining which are suspicious and which are due to the damned PeopleSoft panel processor I can't see a way around easily from sys.aud$. Anyone else been there, done that?? Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: missed Anjo's web cast..
I manage one db that has an obscene amount of physical IO but most of it has to be coming from EMC disk cache (2 GB). I am not so concerned with whether they are real reads or not ( I don't think you can know from within Oracle can you?), my real concern is establishing a baseline then getting notified when I have reached a point in the system that is above the baseline far enough that I know there might be problems. Wait time is of course a bigee but if I have 100 sessions and 90% of the wait time is from 2 sessions I really don't care. What I care about is when all wait times are starting to go above my baseline. So one thing I want to monitor is something like this... tell me when more than 20% of the sessions are contributing to more than %80 of the wait time and the total wait time is currently more than %150 of normal for this time of weekday/weekend or tell me when total wait time is more than %300 of normal I started hacking out a very simple PL/SQL package with one or two tables (hopefully) that will do all some of this. It will write out essentially the Oracle Server load out to the alert log, will work much like the UNIX load. When I get it done I will share it with the list. Personally I have learned to stop caring about actual numbers unless I am trouble shooting and just get my baseline. Find out what the system looks like when performance is bad and then tell it to tell me just before it is bad so I can dig in and make a phone call to the creep performing multiple full table scans against a 5 GB table. By the way, Anjo, we use Precise and it is a super product but I personally don't really use it that much. I find myself staring at multiple inefficient SQL statements that no one has the time to fix and besides nobody is complaining (usually). I think they may be getting accustomed to the slow response times however :) Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, August 08, 2002 2:26 PM To: Multiple recipients of list ORACLE-L I haven't checked this lately but 'physical reads' in v$sysstat is the number of blocks read. To get the real physical I/O operations do check the waits column in v$system_event for 'db file sequentail read' and 'db file scattered read'. And keep an eye on the TIME_WAITED column. Anjo. Post, Ethan wrote: What's wrong with V$SYSSTAT, db block gets + consistent gets = logical io (there may even be a logical IO stat, can't recall and too lazy to look) and physical reads. Don't worry about the cache rate but track the rate of logical IO and physical IO. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, August 08, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Having missed the web cast ... is there as simple way to identify LIOs and PIOs in the database 'as of now'? I know Craig@orapub does it by setting trace and then calculating values off the trace file. But is there a simple way to calculate without generating traces? Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, August 08, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Moi wrong ;-) Jeeh, human after all To summarize the webcast: db-block-buffers do mattter. Too many LIO do matter. Too many PIO do matter. But Buffer Cache Hit ratio doesn't matter ... End user satisfaction does matter. I am always willing to clarify any points that I made, you just have to ask me l Anjo. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: missed Anjo's webcast..
Title: RE: missed Anjo's webcast.. I think I will side with Anjo on this one, if my users are happy with 1 second response times and 99.9% of the data is coming completly through PIO that is fine with me. Of course that is an exageration but you get the point. Keep the customers happy and work on other things. I also think the point is to be a bit reactive about the Oracle tuning manual and training. I do recall at some point in my formative Oracle years as being a bit obsessed with my BCHR and really missing the big picture. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 08, 2002 3:31 PMTo: Multiple recipients of list ORACLE-LSubject: RE: missed Anjo's webcast.. Well, I guess that I disagree. Buffer hit radio does matter as one of the performance indicators, but certainly not the only one. Your and Mr. Milsap thesis is that LIO also is very expensive andits cost is far from being negligible, so having gazillion of LIOs instead of 100 times smaller number of PIOs will not make our system run faster. BHR alone cannot be used to judge to overall health of the system, but thebn again, there is no such thing as the "overall health of the system". It's the users of the system who will say whether the performance is satisfactory or not, and I'm usually tuning an application, not an imaginary "overall system". Low cache hit ratio usually tells me that I do have a hog who is using lots of PIOs. By my experience, it usually is a very good indicator that something is wrong, at least onan OLTP system. So, after all, I do find BHR a useful indicator, but by no means the only one or the most important one. Event 10046, SQL_TRACE (level 1 of 10046), explain plan and v$session_event still are the tools I need most, but I still do need BHR as an indicator. Mladen Gogala Oracle DBA Phone: (203) 459-6855 Email: [EMAIL PROTECTED] -Original Message-From: Anjo Kolk [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 08, 2002 1:05 PMTo: Multiple recipients of list ORACLE-LSubject: Re: missed Anjo's webcast.. Moi wrong ;-) Jeeh, human after all To summarize the webcast: db-block-buffers do mattter. Too many LIO do matter. Too many PIO do matter. But Buffer Cache Hit ratio doesn't matter ... End user satisfaction does matter. I am always willing to clarify any points that I made, you just have to ask me l Anjo. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Thursday, August 08, 2002 5:43 PM Subject: RE: missed Anjo's webcast.. Guys, I had this dream that I missed the webcast - which I did. However, someone said it wasn't very interesting but the conversation of the people (gurus) left over was very interesting as there was good solid evidence that he was incorrect and db_block_buffers do matter. Kind of inline with the discussion about redos yesterday and my indexing/partition issues - hmmm. -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 08, 2002 1:38 AM To: Multiple recipients of list ORACLE-L Subject: Re: missed Anjo's webcast.. Www.precise.com, go to Events-webcasts... On 2002.08.08 00:53 Madhusudhanan Sampath wrote: Are transcript documents available anywhere? Regards Madhusudhanan S _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public
RE: 20 Instances 1 Machine
Thanks for the comments, all were good and James makes some good points (your right up the road from me by the way). I personally like the blade systems. I have only seen Egenera's Linux based system but I guess HP and others have some systems out. How does the cost on these systems look? Anyone seen anything they think is something worth looking at? Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, August 01, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Your biggest problem is not going to be physical RAM or disk space (either of those could simply be purchased large enough). However, you *will* encounter a problem with Shared Memory. 32-bit (and even 64-bit) operating systems have a finite amount of shared memory addressable for use by 32-bit applications (namely the RDBMS shipped with the Oracle Applications). This number is 1.7GBytes on HP/UX and, I think, 2GBytes on Solaris. This Shared Memory limitation is systemwide. The Oracle RDBMS uses shared memory heavily for major components of the SGA. As a result, if you're running a 32-bit version of Oracle, this number represents the sum of all SGA's running on that machine at the time. (So, at 500M/instance, you'll run out somewhere between 3 and 4 instances). Possible solutions would be: 1) Use a 64-bit version of the Oracle RDBMS as certified for your platform. A 64-bit version of Oracle would address shared memory from a much larger total pool (most likely an absurdly large number), thus avoiding this 32-bit Shared Memory problem. 2) Consider using something like Sun's System Domains to partition a big box into multiple virtual machines. Each of these Domains would have it's own shared memory pool. 3) Consider using seperate machines. Personally, I'd vote for seperate machines. I tend to prefer only one production system exist on any given host as it tends to eliminate much of the performance-oriented fingerpointing that is bound to come up. Additionally, running a large number of production instances on a single host can be alot like putting all of your eggs into one basket. It may be cheaper, but if something happens to that basket, everything's hosed. As far as hardware: Lots of disk, plenty of I/O channels, and plenty of CPUs. Without actually knowing the nature of your applications, I'd say you're probably looking in the SunFire 6800 or SunFire 15k range (if you're looking at Sun equipment). Post, Ethan wrote: I got a request to spec out a machine that could handle 20 separate Oracle instances on a single UNIX server. SGA should total about 500 MB per instance. We have some hosts here with 6-8 instances but never tried 20 before. Wondering what types of things I should be worried about, obviously having enough memory but are there any other limitations I can expect? Anyone had to do this? Thanks, Ethan -- James James J. Morrow E-Mail: [EMAIL PROTECTED] Senior Principal Consultant Tenure Systems, Inc. McKinney, TX, USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 9 Standard/Enterprise Diffs
Anyone got a link to the guide with the features matrix for SE and EE? I can find it for 8i in the Getting to Know Oracle 8i but I can not find a Getting to Know Oracle 9i http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/nav/docind ex.htm Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Monitoring occurence of snaphot too old
Use a SERVERERROR trigger to capture the ORA-01555 event and log it somewhere, I find the alert log is easiest. I have a script that picks up custom events written to the alert log and notifies me about them. Other than that follow the advice in other posts and try to avoid them all together. If you are below 9i then get rid of OPTIMAL on all your rollback segments so they never shrink and monitor them. Shrink them manually when system use is very low. create or replace trigger tk$servererror after servererror on database declare mysession v$session%rowtype; begin -- Some errors are not logged... --1 - Key violation. -- 1004 - Default user feature not supported. -- 1013 - Use canceled operation. -- 1017 - Invalid User Name/Password -- 1400 - Can not insert null. -- 1418 - Index does not exist. -- 1722 - Invalid number. -- 1747 - Invalid column name. -- 1839 - Date not valid. -- 4043 - Object does not exist. -- 6550 - PL/SQL Complilation Failure -- if ora_server_error(1) not between 900 and 999 then if ora_server_error(1) not in (1,1004,1013,1017,1400,1418,1722,1747,1839,4043,6550) then select * into mysession from v$session where sid = (select distinct sid from v$mystat); sys.dbms_system.ksdind(0); sys.dbms_system.ksdwrt(2,'SERVERERROR[' || ora_server_error(1) || '] SESSION ' || mysession.sid || ',' || mysession.serial# || ' USER ' || mysession.username || ',' || mysession.osuser || ' PROGRAM ' || mysession.program || ',' || mysession.machine || ' CLIENT INFO ' || mysession.client_info); end if; end if; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); end; Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, August 02, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Dear List members, I was wondering if there is a way to monitor the database and tell if there is a possiblility of snapshot too old error occurence. Any input is highly appreciated.. Thanks Mohammed Ahsanuddin Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Locked Rows in the DB ... the search continues ...
Have you tried Steve's script enqueue_locks.sql (I think). It is on his site and does a really good job. You will will need to take the Object Id and look up the object but I use it all the time as my primary means of locating blocks. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, August 01, 2002 10:54 AM To: Multiple recipients of list ORACLE-L Hi all, I am trying to find who is locking rows on a (specific) table so far I have come up with following ... SELECT a.sid ,a.serial# ,a.osuser ,a.username ,a.ROW_WAIT_OBJ# object_id ,b.object_name ,dbms_rowid.rowid_create(1, a.row_wait_obj#, a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) wait_on_rowid FROM v$session a, db$objects b WHERE a.ROW_WAIT_OBJ# -1 AND a.ROW_WAIT_OBJ# = b.object_id ORDER BY 6, 7 which tells me that the displayed sessions are *waiting* for the specified rowid. The table db$objects is a copy of dba_objects. Joining with dba_objects is slow, so I have created a materialized view called db$objects (gets refreshed overnight). Now reading Steve Adams book, he mentions on pp46 that (in summary) ' ... The reason for waiting is that tx has modified a datablock., and the waiting session needs to modify some part of that data block. In such cases ROW_WAIT column of v$session can be useful in identifying the db object, file, block numbers, and even the row number in case of row lock. The view v$locked_object can then be used to obtain session information for the sessions holding DML locks on the crucial database objects. Now I can also select from v$locked_object to see who is holding locks on a specific table. Now my question is, how do I put 2 and 2 together to display a nice output something like ... User A is waiting for row R in object O, which is being blocked by user B in session 'bsid. Am I on track ... or I should have taken the previous exit?? Also does information in v$session pertains to 'waiting for rowid' or 'I have this rowid locked' or both? Thanks for your help in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
20 Instances 1 Machine
I got a request to spec out a machine that could handle 20 separate Oracle instances on a single UNIX server. SGA should total about 500 MB per instance. We have some hosts here with 6-8 instances but never tried 20 before. Wondering what types of things I should be worried about, obviously having enough memory but are there any other limitations I can expect? Anyone had to do this? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
