My Oracle Support Banner

ORA-959 Caused By DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL After DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION and Drop Tablespace (Doc ID 2473084.1)

Last updated on FEBRUARY 18, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 to 18.9.0.0.0 [Release 12.2 to 18]
Information in this document applies to any platform.

Symptoms

SQL> -- 1.In order to generate unified audit log, create the following unified audit policy.

SQL> create audit policy audit_all actions all;

Audit policy created.

SQL> audit policy audit_all;

Audit succeeded.

SQL>
SQL> -- 2.Confirm the tablespace before executing DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.
SQL> col PARAMETER_NAME format a30
SQL> col PARAMETER_VALUE format a20
SQL> col AUDIT_TRAIL format a20
SQL> select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL' and PARAMETER_NAME='DB AUDIT TABLESPACE';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            SYSAUX               UNIFIED AUDIT TRAIL

SQL> col owner for a10;
SQL> col table_name format a30;
SQL> col tablespace_name format a20;
SQL> select owner ,  table_name from dba_tables where owner = 'AUDSYS';

OWNER      TABLE_NAME
---------- ------------------------------
AUDSYS     AUD$UNIFIED

SQL> -- 3.Create tablespace AUDIT_TS01.

SQL> create tablespace AUDIT_TS01 datafile 'AUDIT_TS01.dbf' size 50M autoextend on;

Tablespace created.

SQL> -- 4.Move unified audit to tablespace AUDIT_TS01 by executing SET_AUDIT_TRAIL_LOCATION.

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value => 'AUDIT_TS01') ;

PL/SQL procedure successfully completed.

SQL>  select owner ,  table_name from dba_tables where owner = 'AUDSYS';

OWNER      TABLE_NAME
---------- ------------------------------
AUDSYS     AUD$UNIFIED
AUDSYS     CLI_SWP$fa52015f$1$1

SQL> -- 5.Move unified audit back to tablespace SYSAUX by executing SET_AUDIT_TRAIL_LOCATION.

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value => 'SYSAUX') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- 6.Purge unified audit log before drop tablespace.
SQL> BEGIN
 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   container  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT,
    use_last_arch_timestamp  =>  FALSE);
 END;
 /  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> -- 7.Drop tablespace AUDIT_TS01.
SQL> DROP TABLESPACE AUDIT_TS01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> -- 8.Purge unified audit log after drop tablespace, and ORA-00959 error happened.
SQL> BEGIN
 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   container  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT,
    use_last_arch_timestamp  =>  FALSE);
 END;
 /  2    3    4    5    6    7
BEGIN
*
ERROR at line 1:
ORA-00959: tablespace 'AUDIT_TS01' does not exist
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5355
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 264
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5340
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1358
ORA-06512: at line 2

SQL> -- 9.Confirm the tablespace after moved back to SYSAUX.
SQL> set pages 1000
SQL> set long 10000
SQL> select DBMS_METADATA.GET_DDL('TABLE','CLI_SWP$fa52015f$1$1','AUDSYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','CLI_SWP$FA52015F$1$1','AUDSYS')
--------------------------------------------------------------------------------

  CREATE TABLE "AUDSYS"."CLI_SWP$fa52015f$1$1"
   (    "INST#" NUMBER,
        "BUCKET#" NUMBER,
        "INST_LOB#" NUMBER,
        "MAX_SEQ#" NUMBER,
        "FLUSH_SCN" NUMBER,
        "FLUSH_TIME" DATE,
        "MIN_SCN" NUMBER,
        "MAX_SCN" NUMBER,
        "MIN_TIME" DATE,
        "MAX_TIME" DATE,
        "SID#" NUMBER,
        "SERIAL#" NUMBER,
        "STATUS" NUMBER,
        "LOG_PIECE" BLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "AUDIT_TS01"          *<<<<<<<<<---------------------------Remaining at TABLESPACE "AUDIT_TS01"
 LOB ("LOG_PIECE") STORE AS SECUREFILE (
  TABLESPACE "AUDIT_TS01" ENABLE STORAGE IN ROW CHUNK 8192 *<<<<<<<<<---Remaining at TABLESPACE "AUDIT_TS01"
  CACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
  PARTITION BY RANGE ("FLUSH_SCN")
 (PARTITION "HIGH_PART"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"
 LOB ("LOG_PIECE") STORE AS SECUREFILE (
  TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) )

SQL> --10.Confirm that all segments have been in SYSAUX and no segments remained in AUDIT_TS01.
SQL> SET LINES 200
SQL> COL tablespace_name FOR A15
SQL> COL segment_name FOR A30
SQL> select tablespace_name, owner, segment_name, segment_type, bytes/1024/1024 mb
from dba_segments where owner = 'AUDSYS' order by 1,2,3;SQL> SQL> SQL>   2

TABLESPACE_NAME OWNER      SEGMENT_NAME                   SEGMENT_TYPE               MB
--------------- ---------- ------------------------------ ------------------ ----------
SYSAUX          AUDSYS     AUD$UNIFIED                    TABLE PARTITION          .125
SYSAUX          AUDSYS     CLI_LOB$fa52015f$1$1           INDEX PARTITION         .0625
SYSAUX          AUDSYS     CLI_SCN$fa52015f$1$1           INDEX PARTITION         .0625
SYSAUX          AUDSYS     CLI_SWP$fa52015f$1$1           TABLE PARTITION         .0625
SYSAUX          AUDSYS     CLI_TIME$fa52015f$1$1          INDEX PARTITION         .0625
SYSAUX          AUDSYS     SYS_IL0000017945C00030$$       INDEX PARTITION         .0625
SYSAUX          AUDSYS     SYS_IL0000017945C00031$$       INDEX PARTITION         .0625
SYSAUX          AUDSYS     SYS_IL0000017945C00097$$       INDEX PARTITION         .0625
SYSAUX          AUDSYS     SYS_IL0000075954C00014$$       INDEX PARTITION         .0625
SYSAUX          AUDSYS     SYS_LOB0000017945C00030$$      LOB PARTITION            .125
SYSAUX          AUDSYS     SYS_LOB0000017945C00031$$      LOB PARTITION            .125
SYSAUX          AUDSYS     SYS_LOB0000017945C00097$$      LOB PARTITION            .125
SYSAUX          AUDSYS     SYS_LOB0000075954C00014$$      LOB PARTITION            .125

13 rows selected.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.