CODEWITHSUNDEEP
sqloracle-databasetruncate
korodani
korodani

Reputation: 178

Can I INSERT into table while table is being TRUNCATEd?

I have a a SYSTEM table AUD$ where every user's action is written to (AUDIT select, insert, update, ...). This table has 100 million records. I decided to truncate the table (which will take few minutes i guess) because we no longer need this data.

But every time a user logs in to the Database, a record in this table AUD$ gets created.

So my question is : does INSERT (if user logs in to database) works during the TRUNCATE table ?

Upvotes: 0

Views: 1313

Answers (1)

Adam vonNieda
Adam vonNieda

Reputation: 1745

Oracle has a package called DBMS_AUDIT_MGMT that can (and should) be used to manage your audit table, which is the proper way to reduce the size if AUD$. It can be used to manage OS level auditing as well, if you're writing those files out. Here's an example of moving the AUD$ table to its own tablespace, and setting up auditing management.

Create a dedicated audit tablespace

CREATE TABLESPACE AUDAUX DATAFILE '/u01/oracle/oradata/<DB_NAME>/audaux01.dbf'
SIZE 25M AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED;

Move the sys.aud$ table to the new tablespace

SET SERVEROUTPUT ON
BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  AUDIT_TRAIL_LOCATION_VALUE  => 'AUDAUX');
END;
/

Set the required parameters, bounce the database if needed

audit_trail='DB,EXTENDED'
audit_sys_operations=true

Initialize the cleanup operation

SET SERVEROUTPUT ON
BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    => 12 );
END;
/

Verify that cleanup is initialized

SET SERVEROUTPUT ON
BEGIN
 IF 
   DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
   DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for clean-up');
 ELSE
   DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for clean-up.');
 END IF;
END;
/

Create a purge job

SET SERVEROUTPUT ON
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_Purge',
   USE_LAST_ARCH_TIMESTAMP     => FALSE );
END;
/

Drop purge job example

SET SERVEROUTPUT ON
BEGIN
  DBMS_AUDIT_MGMT.DROP_PURGE_JOB (
   AUDIT_TRAIL_PURGE_NAME      => 'DB_AUDIT_TRAIL_PURGE');
END;
/

Upvotes: 2

Related Questions