Saturday, May 17, 2014

TRUNCATE TABLE SYS.AUD$ Gives ORA-00942

I saw that the SYSTEM tablespace in an Oracle 11.2 database was getting quite big so I checked the size of SYS.AUD$:
 
C:\Users\AJ0294094>sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 16 13:20:54 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(*) from sys.aud$;
 
  COUNT(*)
----------
  35483888
 
SQL>
 
… but when I tried to TRUNCATE it, Oracle returned an ORA-00942:
 
SQL> truncate table sys.aud$
  2  /
truncate table sys.aud$
                   *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL>
 
I took a closer look and saw that the AUD$ table had been moved to the SYSTEM schema and SYS.AUD$ was just a synonym pointing to it. This was a while later, by which time, several more rows had been added to it:
 
SQL> select owner, object_type
  2  from dba_objects where object_name = 'AUD$';
 
OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            SYNONYM
SYSTEM                         TABLE
 
SQL> select table_owner, table_name
  2  from dba_synonyms
  3  where owner = 'SYS'
  4  and synonym_name = 'AUD$'
  5  /
 
TABLE_OWNER          TABLE_NAME
-------------------- --------------------
SYSTEM               AUD$
 
SQL> select count(*) from system.aud$
  2  /
 
  COUNT(*)
----------
  35484319
 
SQL>
 
… so I did the TRUNCATE on SYSTEM.AUD$ instead:
 
SQL> truncate table system.aud$
  2  /
 
Table truncated.
 
SQL>
 
… then SYS.AUD$ appeared empty afterwards:
 
SQL> select count(*) from sys.aud$
  2  /
 
  COUNT(*)
----------
         0
 
SQL>
 
… and the SYSTEM tablespace had plenty of free space:
 
SQL> select sum(bytes) from dba_free_space
  2  where tablespace_name = 'SYSTEM'
  3  /
 
SUM(BYTES)
----------
8996651008
 
SQL>
 
This happened because you cannot TRUNCATE a table through a synonym. This applies, incidentally, even if you own both the table and the synonym, as you can see in the example below: 

SQL> create table tab1 (col1 number);
 
Table created.
 
SQL> truncate table tab1;
 
Table truncated.
 
SQL> create synonym tab2 for tab1;
 
Synonym created.
 
SQL> desc tab2
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                NUMBER
 
SQL> truncate table tab2;
truncate table tab2
               *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL>

No comments: