Hi,
I got ” ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM , ORA-02002: error while writing to audit trail , ORA-00604: error occurred at recursive SQL level 1 ” error during the connect database.
Details of error are as follows. When I try to drop database in mount mode, i got the following error.
ORA-00604: error occurred at recursive SQL level 1 ORA-01653: unable to extend table SYS.AUD$ by 8192 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 8192 in tablespace SYSTEM
This error is related with the Audit trail tablespace, namely System tablespace is full.
To solve this error, add datafile to system tablespace.
If you use file system for datafiles, then run the following command to add datafile.
ALTER TABLESPACE SYSTEM ADD DATAFILE '/oradata/msdba/datafile/system02.dbf' SIZE 10M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED;
If you use Oracle ASM for datafiles, then run the following command to add datafile.
ALTER TABLESPACE SYSTEM ADD DATAFILE '+DATA' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;
Or Truncate audit table as follows.
truncate table SYS.AUD$;
Or You can disable the auditing as follows. After setting audit_trail, you need to restart database.
alter system set audit_trail=none scope=spfile;
restart the database.
shutdown immediate; sqlplus / as sysdba startup;
Read the following post to learn more details about Audit.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/