| 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: Auditing, how to capture sql statement issued
I've just finished Jonanthan Lewis's book (Practical Oracle8i) and if I
understood him correctly V$SQLAREA is not a very efficient V$ table to use
so I'm not sure looping on this table is a very good idea.
Just something to consider....
-----Original Message-----
From: Mark Leith [mailto:mark_at_cool-tools.co.uk]
Sent: 16 January 2001 17:39
To: Multiple recipients of list ORACLE-L
Subject: RE: Auditing, how to capture sql statement issued
You could infact look at writing a query to select from v$SQLAREA and loop infinitely (via whatever:), to collect SQL statements with certain thresholds etc, then insert these statements in full in to a "Repository" table.
SQL optimistation tools do this all the time..
-----Original Message-----
Brian
Sent: Tuesday, January 16, 2001 04:26
To: Multiple recipients of list ORACLE-L
Based on his subject, I think Mr. Cale was wanting to monitor other users' queries. For instance, looking for queries without where clauses that pull credit card numbers to find out if someone is doing something they shouldn't.
There is no effective way to monitor queries from inside the DB. There are no "on select" triggers, system auditing only tracks that a query was issued against the table without any info on the rows returned or query text, and no log is generated on queries, so logminer is no help.
Brian Norrell
Senior Software Engineer
QuadraMed
972-831-6600
-----Original Message-----
Sent: Tuesday, January 16, 2001 8:31 AM
To: Multiple recipients of list ORACLE-L
Cale
I think you mean to reuse the sql statement sometime again. if this is the case then u can save the query in the sql*plus editor in some file location
or u can just use SAVE <A> after your query in the sql*plus .
The next time u want to use the query u can just use ' @ A ' .
e.g select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ---------
AB TABLE CD TABLE DISTINCT_TEST TABLE EMP TABLE LXAPPLICATION TABLE LXAUCTION TABLE LXAUCTIONHISTORY TABLE LXAUCTIONSTATUS TABLE LXBID TABLE
SQL> save a replace
Wrote file a
SQL> @ a
TNAME TABTYPE CLUSTERID
------------------------------ ------- ---------
AB TABLE CD TABLE DISTINCT_TEST TABLE EMP TABLE LXAPPLICATION TABLE LXAUCTION TABLE LXAUCTIONHISTORY TABLE LXAUCTIONSTATUS TABLE LXBID TABLE
you can also use the sql*plus editor by just typing 'ed' as it will load
the last executed sql query in
the editor where you save your query.
Rgds,
-----Original Message-----
Sent: Tuesday, January 16, 2001 6:31 PM
To: Multiple recipients of list ORACLE-L
Hi DBAs,
Is there a way to capture the actual sql statement issued while in sql*plus,worksheet,etc?
Thanks,
Rick Cale, Science Applications International Corp.
Phone:865-481-2198, fax:865-481-8555
e-mail: caler_at_saic.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: RICHARD.T.CALE_at_saic.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sachin Puri
INET: sachin_at_siworldwide.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: mark_at_cool-tools.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Wed Jan 17 2001 - 02:37:19 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |