{"id":3328,"date":"2025-05-18T07:21:27","date_gmt":"2025-05-18T07:21:27","guid":{"rendered":"https:\/\/w3buddy.com\/?p=3328"},"modified":"2026-01-15T13:10:23","modified_gmt":"2026-01-15T07:40:23","slug":"purge-unified-audit-trail-in-oracle-to-free-up-sysaux-space","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/purge-unified-audit-trail-in-oracle-to-free-up-sysaux-space\/","title":{"rendered":"Purge Unified Audit Trail in Oracle to Free Up SYSAUX Space"},"content":{"rendered":"\n

Oracle\u2019s unified audit trail<\/strong> can silently consume significant space in the SYSAUX tablespace<\/strong>, especially when audit logs aren\u2019t purged regularly. If you’re noticing SYSAUX growing unusually large, it may be time to clean up the audit data stored by the AUDSYS<\/strong> schema.<\/p>\n\n\n\n

Here\u2019s a practical guide to identify the issue and purge old unified audit records using Oracle’s DBMS_AUDIT_MGMT<\/code> package.<\/p>\n\n\n\n

Step 1: Check SYSAUX Usage by AUDSYS<\/h3>\n\n\n\n

Connect as SYSDBA and inspect how much space AUDSYS<\/code> is using:<\/p>\n\n\n\n

SQL> show user\nUSER is \"SYS\"\n\nSQL> column occupant_name format a30;\nSQL> column occupant_desc format a30;\nSQL> column size_gb format 999.99;\n\nSQL> select occupant_name, occupant_desc, space_usage_kbytes\/1024\/1024 size_gb\n     from v$sysaux_occupants\n     where occupant_name = 'AUDSYS';<\/code><\/pre>\n\n\n\n
Example output:<\/p>\n\n\n\n
OCCUPANT_NAME     OCCUPANT_DESC           SIZE_GB\n----------------- ------------------------ -------\nAUDSYS            AUDSYS schema objects     58.05<\/code><\/pre>\n\n\n\n
This indicates that audit data is heavily occupying SYSAUX, primarily in the AUD$UNIFIED<\/code> table.<\/p>\n\n\n\n
Step 2: Retain Only Recent Audit Logs<\/h3>\n\n\n\n
Before purging, you may want to keep recent logs (e.g., from the last 30 days). Use SET_LAST_ARCHIVE_TIMESTAMP<\/code> to define the cutoff:<\/p>\n\n\n\n
BEGIN\n  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(\n    AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,\n    LAST_ARCHIVE_TIME  => SYSTIMESTAMP - INTERVAL '30' DAY\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
This ensures only data older than 30 days is removed.<\/p>\n\n\n\n
Step 3: Purge Older Audit Records<\/h3>\n\n\n\n
With the timestamp set, run the following to clean up old entries:<\/p>\n\n\n\n
BEGIN\n  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(\n    AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,\n    USE_LAST_ARCH_TIMESTAMP => TRUE\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
This deletes only those records older than the defined timestamp.<\/p>\n\n\n\n
Optional: Remove All Audit Logs<\/h3>\n\n\n\n
To completely purge the audit trail (ignoring any retention settings), set the flag to FALSE:<\/p>\n\n\n\n
BEGIN\n  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(\n    AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,\n    USE_LAST_ARCH_TIMESTAMP => FALSE\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
Be cautious: this will delete all<\/strong> unified audit logs.<\/p>\n\n\n\n
Step 4: Clear the Archive Timestamp<\/h3>\n\n\n\n
Once done, it\u2019s good practice to reset the archive timestamp:<\/p>\n\n\n\n
BEGIN\n  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(\n    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
This avoids confusion during future purging operations.<\/p>\n\n\n\n
Step 5: Recheck SYSAUX Usage<\/h3>\n\n\n\n
Finally, verify the space savings:<\/p>\n\n\n\n
SQL> select occupant_name, occupant_desc, space_usage_kbytes\/1024\/1024 size_gb\n     from v$sysaux_occupants\n     where occupant_name = 'AUDSYS';<\/code><\/pre>\n\n\n\n
Expected result:<\/p>\n\n\n\n
OCCUPANT_NAME     OCCUPANT_DESC           SIZE_GB\n----------------- ------------------------ -------\nAUDSYS            AUDSYS schema objects     4.85<\/code><\/pre>\n\n\n\n
This confirms a successful cleanup.<\/p>\n\n\n\n
Summary<\/h3>\n\n\n\n
Regular maintenance of the unified audit trail can significantly reduce storage consumption in Oracle\u2019s SYSAUX tablespace. Using the DBMS_AUDIT_MGMT<\/code> package provides a clean and efficient way to manage audit data while preserving recent logs for compliance or analysis.<\/p>\n\n\n\n
Tip:<\/strong> Automate this process via a scheduled job to avoid future SYSAUX bloating.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle\u2019s unified audit trail can silently consume significant space in the SYSAUX tablespace, especially when audit logs aren\u2019t purged regularly. If you’re noticing SYSAUX growing unusually large, it may be time to clean up the audit data stored by the AUDSYS schema. Here\u2019s a practical guide to identify the issue and purge old unified audit […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3328","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3328","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3328"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3328\/revisions"}],"predecessor-version":[{"id":3332,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3328\/revisions\/3332"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}