Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Shrink AUD$ TABLE in PDB fails

chileme88Nov 24 2020

Hi,
in my pluggable database my audit ( 30 days audited ) table size is 180G and it was moved in the big tablespace DBAAUDIT_BIGTBSDAT with DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.
Now I've a space problem and I store ony 1 day in the audit table:
SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type =>SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, last_archive_time => sysdate-1);
SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp => true) ;

It works: The min date in AUD$ is yesterday.
The aud$ size is 180G but I think the are a lot of empty segments.
Now I want shrink the table and I found 2 ways:

1st way:
CREATE BIGFILE TABLESPACE "DBAAUDIT_BIGTBSDAT_TMP" DATAFILE SIZE 50G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'DBAAUDIT_BIGTBSDAT_TMP');
END;
/
But I have:
ORA-46267: Insufficient space in 'DBAAUDIT_BIGTBSDAT_TMP' tablespace, cannot complete operation
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 3293
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 3197
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 789
ORA-06512: at line 3
46267. 00000 - "Insufficient space in '%s' tablespace, cannot complete operation"
*Cause: Could not complete the operation because the DBMS_AUDIT_MGMT API
involved movement of one or more audit tables and there was not
enough space in the destination tablespace.
*Action: Turn on tracing using DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL to find
the amount of space required. Then, allocate that space in the
destination tablespace, even if destination tablespace datafile has
AUTOEXTEND option turned on. And then, retry the operation.

In the trace:
*** 2020-11-24T15:50:06.247607+01:00 (PDBBI2(3))
KZAM_TRC:AUD$: Space occupied = 189465354240 per row, space required = 198938656768
KZAM_TRC:Space available = 53545730048
KZAM_TRC:tbs_space_check: return FALSE
KZAM_TRC:ORA-46267...1
*** 2020-11-24T15:50:48.301981+01:00 (PDBBI2(3))
KZAM_TRC: In set_audit_trail_location
KZAM_TRC: In set_audit_trail_location_11g
KZAM_TRC: In tbs_space_check
KZAM_TRC:Space check for AUD$
KZAM_TRC:Dropped SYS.DAM_TEMP_AUD$
KZAM_TRC:Tablespace 'DBAAUDIT_BIGTBSDAT_TMP' has '8192' blocksize
KZAM_TRC:Factor '5'
KZAM_TRC:AUD$: Space occupied = 189465354240 per row, space required = 198938656768
*** 2020-11-24T15:58:31.477706+01:00 (PDBBI2(3))
KZAM_TRC:Space available = 53545730048
KZAM_TRC:tbs_space_check: return FALSE
KZAM_TRC:ORA-46267...1

2nd way:
alter table sys.aud$ enable row movement;
ORA-65040: operation not allowed from within a pluggable database

How can I shrink the table?

Comments
Post Details
Added on Nov 24 2020
8 comments
1,202 views