(no subject)

marksc_at_wpmail.code3.com (Mark A. Scarton) wrote:
>I am working on a transformation agent based upon using an Oracle database
>recipient. It receives messages in various formats and needs to store the
>values in relational form.
>
>The essence of the tool is to create table definitions on the fly if a suitable
>table doesn't already exist, then populate tuples of the table with the contents
>of the newly received messages. These tables will be read-only to all but the
>transform agent, since the translation to relational form is solely for
>manipulation by SQL-based products (SAS, SPSS, Access, etc.).
>
>As a utility appendage to the transformer, I need to be able to clean up the
>tablespaces as tables become dormant. I need to be able to keep track of when a
>table was last accessed (much like the Unix file system tracks access time). If
>a table of type <foo> hasn't been accessed in the last <bar> days, I can then
>drop the table.
>
>Any suggestions for how to track access times? Please e-mail to
>'mascarton_at_wpmail.code3.com'. Thanks!!!
>
>Mark A. Scarton, ABD
>CompUtah!, Park City, Utah USA
>Home: 801.565.9835
>Office: 801.265-4612
>

Mark,

First make sure that the data dictionary views for auditing have been created. From SQL*Plus, connect as sys and run the cataudit.sql script (on unix hosts, this script can be found in $ORACLE_HOME/rdbms/admin). Then make sure that the parameter AUDIT_TRAIL=DB is set in the init.ora or config.ora file (this enables auditing at the next database startup).

To log successful and unsuccessful database connection attempts, type AUDIT SESSION; at the SQL> prompt (you should be connected as sys to do this). To log table access, you may want to enter something like this: AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, CREATE TABLE
BY ACCESS; (you get the idea...)

The database audit trail is stored in the SYS.AUD$ table, this table can grow rapidly, so keep a fairly close watch on it.

The chapter on Auditing Database Use in the Administrator's Guide provides some fairly good guidance on database auditing. Be as selective about auditing as possible as it does create some additional database overhead.

hope this helps

regards,
Chuck

-- 
********************************************************************
Chuck Wolfe                         Phone:  (703)325-3718
Senior Consultant                   E-mail: cwolfe_at_ix.netcom.com
TELOS Consulting Services
460 Herndon Parkway
Herndon, VA 22070

I don't want the world, I just want your half...

********************************************************************