| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: suggestion
this is the format .... instancename_ora_ospid.trc
perhaps this might help ...
SELECT c.VALUE || '/' || LOWER(INSTANCE) || '_ora_' ||
LTRIM(TO_CHAR(a.spid,'fm9999999')) || '.trc' FROM v$process a, v$session b, v$parameter c, v$thread c
WHERE a.addr = b.paddr
AND b.audsid = USERENV('sessionid')
AND c.NAME = 'user_dump_dest'
Raj
-----Original Message-----
Sent: Wednesday, September 24, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L
How did you go about determining the tracefile name within the trigger? I don't think I've ever seen an example for this...
-----Original Message-----
Sent: Wednesday, September 24, 2003 8:05 AM
To: Multiple recipients of list ORACLE-L
hmmm... here is what I did ...
change user_dump_dest to a file system which has lots of space, 100G in our
case
modify a logon trigger and for a certain group of people (based on a role)
"execute dbms_support.start_trace";
Create a logoff trigger that raises a alert user_logoff and sends a trace
file string (actual name of the trace file).
I had a SQR report running on both sides of RAC waiting to respond to this
alert.
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
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
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.
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: Norris, Gregory T [ITS]
INET: gregory.t.norris_at_mail.sprint.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).
--
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).
 |
 |