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>

The Recycle Bin does not Store Tables from the SYSTEM Tablespace

Oracle introduced the recyclebin in version 10. This post shows that tables in the SYSTEM tablespace do not go into the recyclebin when they are dropped. It was tested on Oracle 11.2.0.2.7. First I created a table in the USERS tablespace: 

SQL> conn / as sysdba

Connected.

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Table created.

SQL>

Then I dropped the table, checked that I could see it in the recyclebin and restored it:

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

--------------------------------

TAB1

 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback complete.

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                NUMBER

SQL>

Then I repeated the process but created the table in the SYSTEM tablespace instead:

SQL> create table tab2

  2  (col1 number)

  3  tablespace system

  4  /

 

Table created.

 

SQL> drop table tab2

  2  /

 

Table dropped.

SQL>

This time, when I looked in the recyclebin, it was not there:

 

SQL> select original_name from recyclebin

  2  /

 

no rows selected

SQL>

... and when I tried to restore the table, I was unable to do so: 

 

SQL> flashback table tab2 to before drop

  2  /

flashback table tab2 to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

 

SQL>

ORA-12901

Looking at a couple of Oracle 9 databases recently, I noticed that, if the SYSTEM tablespace is DICTIONARY managed, you can still use it as the default temporary tablespace (although I’m not suggesting you should do so):

 

SQL> l

  1  select extent_management from dba_tablespaces

  2* where tablespace_name = 'SYSTEM'

SQL> /

 

EXTENT_MANAGEMENT

-----------------

DICTIONARY

 

SQL> alter database

  2  default temporary tablespace system

  3  /

 

Database altered.

 

SQL>

 

However, if you try the same thing in a database where the SYSTEM tablespace has LOCAL management, you get an ORA-12901:

 

SQL> select extent_management from dba_tablespaces

  2  where tablespace_name = 'SYSTEM'

  3  /

 

EXTENT_MANAGEMENT

-----------------

LOCAL

 

SQL> alter database

  2  default temporary tablespace system

  3  /

alter database

*

ERROR at line 1:

ORA-12901: default temporary tablespace must be of

TEMPORARY type

 

SQL>