Re: ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM

Toay Morning ,I am getting following error while  connecting schema

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM

when i check tablespace there is no SYSTEM tablespace

10:30:08 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name order by 1;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts                                                 19210.375
SYSAUX                                           1877.9375
UNDOTBS1                                        554.875


 SYSTEM tablespace was missing

Solution
========

conn sqlplus '/as sysdba'

10:30:33 SQL> Create table Aud$_backup as select * from aud$ tablespace SYSAUX;


Table Created.

Elapsed: 00:00:00.01
10:30:54 SQL>
10:31:20 SQL>
10:31:20 SQL> truncate table aud$;

Table truncated.


10:32:10 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name order by 1;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts                                          19210.375
SYSAUX                                    1877.9375
SYSTEM                                     1.015625
UNDOTBS1                                    554.875

Finally ,I  added SYSTEM tablesapce (This is  optional )


alter tablespace SYSTEM  add datafile '/u01/test/system02.dbf' size 2G;

11:12:58 SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts                                            19171.375
SYSAUX                                       1877.875
SYSTEM                                        2049.02344
UNDOTBS1                                    549.875

Immediately take full backup  and bounce the database
(If the SYSTEM tablespace not added there is no need to take  backup)




Suggestions and Comments  are Most Welcome


Happy learning