DBA Notes: 2011/10/10

Cheng Li

How to process  ORA-1653

From Alert log, we got following error message:

ORA-1653: unable to extend table SYS.AUD$ by 7156 in tablespace SYSTEM

The data dictionary of every database has a table named SYS.AUD$, commonly referred to as the database audit trail. Either the database or operating system audit trail can store all audit records generated as the result of statement, privilege, or object auditing.

Using the database audit trail offers the following advantages:

• You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary.
• You can use Oracle tools (such as Oracle Reports) to generate audit reports.

Reducing the Size of the Audit Trail:

1- getting into the database

sqlplus /nolog
conn sys as sysdba

If you get into the database, then...

2- Solving the issue

Here you can do whatever you want like:
a) truncate sys.aud$ table... or
b) resize a system datafile to a larger size... or
c) just add a datafile to your system tablespace.

Steps:

Truncate audit trail table

SQL> truncate table sys.aud$;

Table truncated.

Create a CRON job to truncate this sys.aud$ table timely such as once a month.

#Added by DBA for truncate table sys.aud$ 00 9 1 * * sh /home/oracle/ctmdba/scripts/truncate_sys_aud_ctmcs1d.sh

01 9 1 * * sh /home/oracle/ctmdba/scripts/truncate_sys_aud_ctmadmd.sh

Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.

B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

Reference:

http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67772/audit.htm

http://adminschoice.com/crontab-quick-reference