RE: suggestion

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 24 Sep 2003 05:04:40 -0800
Message-ID: <F001.005D0ECB.20030924050440@fatcity.com>

hmmm... here is what I did ...  

1. change user_dump_dest to a file system which has lots of space, 100G in our case
2. modify a logon trigger and for a certain group of people (based on a role) "execute dbms_support.start_trace";
3. Create a logoff trigger that raises a alert user_logoff and sends a trace file string (actual name of the trace file).
4. I had a SQR report running on both sides of RAC waiting to respond to this alert.
5. As soon as this alert came, it waited 1 second and then went to system and verified that it owned the trace file (based on the instance) and then gzipped it and based on the date moved to a directory.

When we got tired of collecting trace files, we stopped the process. All of this is fairly easy to do, it took me about an hour to put this all together.  

If you just want sql text, you may want to use "dbms_support.start_trace(false,true)" .. it will avoid wait events listing but will give you bind variables.  

Raj

---

---

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

-----Original Message-----
Sent: Tuesday, September 23, 2003 9:10 PM To: Multiple recipients of list ORACLE-L

Actually, you can capture SQLs relatively inexpensively by using FGA - simply add the "audit_condition => '1=1'" when adding a policy to each table.  

For OLTP, this wouldn't make much sense. For ad-hoc (DSS), what you are going to do with all those captured SQLs is another story.

• Original Message ----- To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L_at_fatcity.com> Sent: Wednesday, September 24, 2003 11:09 AM

For statistics, logon & logoff triggers + v$mystat + autonomous transactions.
If you want to capture all sql, it will be hard & very resource hungry, you either enable trace for given session (which slows stuff down enormously) or poll v$sql or v$open_cursor frequently. This isn't a good idea either. You might want to look at fine grained auditing if you want to track which data is viewed by anybody.  

Tanel.  

• Original Message ----- To: Multiple recipients of list <mailto:ORACLE-L_at_fatcity.com> ORACLE-L Sent: Wednesday, September 24, 2003 1:39 AM

hi  

there is a requirement for capturing sqls and cpu consumed by any session logging into the database . this info should be stored in the database.  

can you please give me suggestions as to how i do this?  

thanks
sai  

--

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

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB 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). Received on Wed Sep 24 2003 - 08:04:40 CDT