|
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
--------------------------------------------------------------------------------
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 !
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 -----
Sent: Wednesday, September 24, 2003
11:09 AM
Subject: Re: suggestion
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 -----
Sent: Wednesday, September 24, 2003
1:39 AM
Subject: suggestion
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
|
********************************************************************This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*********************************************************************2
