1

I have a SYSTEM's table AUD$ in ORACLE's database that is constantly being populated (whenever a DB user logs in to DB - a records is written to AUD$).

The table AUD$ has like 100 million records (100GB in size) and I want to delete data to regain the usable storage (100GB). I don't want to drop the table because it is still needed.

DELETE = does not reallocate the storage. TRUNCATE = does reallocate the storage but it LOCKS the table, which I cannot afford.

I thought about DBMS_AUDIT_MGMT already but my intention is not only to delete old records and reallocate used storage but also to create another tablespace and partitioned table (seperated from system's SYSAUX) and compress it (columns to be exact, because SQLBIND and SQLTEXT are CLOB's that are very space consuming), which is something I cannot achieve with DBMS_AUDT_MGMT.

So how do I delete records to regain storage without locking the table ?

migrated from stackoverflow.com Sep 13 at 9:19

This question came from our site for professional and enthusiast programmers.

  • It's about system's AUD$ table meant for AUDIT purposes. Old AUDIT records are not needed anymore, but the new ones will be needed. So I guess you recommend me to create partitions, move the data in partitions and then alter table aud$ truncate partition partition_name drop storage; to regain the storage? – korodani Sep 9 at 9:12
  • I thought about DBMS_AUDIT_MGMT already but my intention is not only to delete old records and reallocate used storage but also to create another tablespace and partitioned table (seperated from system's SYSAUX) and compress it (columns to be exact, because SQLBIND and SQLTEXT are CLOB's that are very space consuming), which is something I cannot achieve with DBMS_AUDT_MGMT. – korodani Sep 9 at 9:26
  • 1
    You are right @APC , I edited the question, thanks for warning, you go the point here. – korodani Sep 9 at 11:01
  • 1
    Although TRUNCATE will lock the entire table, it will probably only do so for a second or two. It would be a relatively painless command to run during a maintenance window. – Jon Heller Sep 9 at 16:29
  • @Jon Heller you are correct, I just truncated a 100GB table and the operation finished in 1 second. – korodani Sep 23 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.