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>