RE: Auditing, how to capture sql statement issued

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 16 Jan 2001 17:37:56 -0000
Message-Id: <10743.126811@fatcity.com>

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-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Norrell, Brian
Sent: Tuesday, January 16, 2001 04:26
To: Multiple recipients of list ORACLE-L Subject: RE: Auditing, how to capture sql statement issued

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,

Sachin Puri
Oracle Development and Administration
Solutions NY
Tel : +91-11-6220102 x 22
Direct : +919810373243

-----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 Lists


--------------------------------------------------------------------

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 Lists


--------------------------------------------------------------------

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.com
--

Author: Norrell, Brian
INET: BNorrell_at_QuadraMed.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Tue Jan 16 2001 - 11:37:56 CST