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 ?