Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, umesh.

Asked: September 15, 2005 - 11:06 am UTC

Last updated: August 24, 2009 - 4:37 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Hi Tom,

I have a production database in 8i 1.5 on which Oracle auditing is enabled.There was a scenario where my number of records in SYS.AUD$ table had increased to such an extent that my SYSTEM datafile original of size 512 MB has gone pass 10 GB, now this sounds strange but i was not able to delete the records due to some reason. But when i truncated the table SYS.AYUD$ (TRUNCATE TABLE SYS.AUD$ DROP STORAGE;) and tried to resize the system datafile (SQL>ALTER DATABASE
SQL>DATAFILE 'E:\ORACLE\ORADATA\DPSECURE\SYSTEM01.DBF' RESIZE 512M) system didn't allow me stating that some data is in use.As a result i was forced to recreate the database .

But when same thing was tested on my test server it was success, but ofcourse the size of the data file on test server was 512 MB and i resized it to 300 MB.

1.I don't understand why this has happend ?
2.Is there any other method by which same can be done ?
3.Normally i delete the records of SYS.AUD$ (after backup) by using PL/SQL loop, which takes a hell long, is it safe to carry out truncate command on that sys.aud$ on my production DB ?
4. Is it safe to resize RBS data file?

Tons of thanks in advance.

and Tom said...

1) it is because there was some extent left at the "end" of the file, suppose the file looked like this:


where x = some space used by something
f = free space
a = your audit trail.

After the truncate you would have:


now, you could resize the file to remove the trailing free bits:


but that X would prevent any further reduction.

2) the same? what did you mean by that?

3) I'll refer you to support for information regarding how to deal with sys.aud$. I can see how truncate on a busy system could cause issues (locking, possible internal self deadlocks)

4) yes.


  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment


"Resizing of System.DBF file", version 8.1.5

Phil Holmes, September 16, 2005 - 4:43 am UTC

My understanding is that it's generally regarded as useful to move the SYS.AUD$ table to a different tablespace (perhaps one just for that table) if auditing (to the database) is being used.

Resizing of System.DBF file

Umesh, September 16, 2005 - 6:35 am UTC

Dear TOM,
Thanks for your quick reply.
1] Quote<>Is there any other method by which same can be done ? <> Quote. I meant that is there any other way of resizing the SYSTEM.DBF file or do i have to recreate the database every time this file grows out of proportion.
2]If there is this issue of locking,possible internal self deadlocks on busy system,Can i take my server off the network restart the DB and then run Truncate will it be safe?
3]Finally as Phil says " move the SYS.AUD$ table to a different tablespace (perhaps one just for that table) if auditing" how it can be done, since it is a system's table and is it safe.

Thanks a lot for answering

Tom Kyte
September 16, 2005 - 8:26 am UTC

1) do not let it grow out of proportion, you cannot reorg SYS objects like that.

2-3) you need to work with support when dealing with this table.

Unfortunately, you are on "old" software as well - that will be problematic from a support perspective.

Need to get support involved

Niall Litchfield, September 16, 2005 - 6:39 am UTC

It can indeed be useful to move AUD$ but you do need to get support involved to OK it. I'm not sure what (other than the fact that you are changing the datadictionary tables) the issue was - but definitely not one to do without a tar.

Alternative to truncate.

Niall Litchfield, September 16, 2005 - 7:01 am UTC

Umesh, couple of mnore questions for you

You earlier on stated that you were currently using a pl/sql loop to clear out old records from AUD$. What does that loop consist of? Could it be replaced by a simple sql statement along the lines of.

delete sys.aud$ where timestamp# < trunc(sysdate) - 4;

If you wanted to keep 4 days history. In fact if truncating is acceptable are you in fact auditing too much already.

The second thing is that although I referred you to support (and I'd expect them to give a valid answer for this particular issue because it isn't really version specific) I hadn't noticed that the version of Oracle you are running is unsupported. Apologies if this means you don't have a current support contract and can't follow my suggestion

Lots of stuff on Metalink

Dan, September 16, 2005 - 10:35 am UTC

This is well covered in several articles on metalink. I did a quick look and I found some with dates in the mid-90's (7.3 vintage) - so some of them should cover 8.1.5.

Proceed with caution - if something blows up you will have a hard time getting attention from support on that release. Test Test Test Test Test and then Test.

Also, consider upgrading to a new release soon - you may find yourself without a direct upgrade path in the not too distant future (if not already).

Niall Litchfield and Dan

Umesh, September 17, 2005 - 6:23 am UTC

Niall :- "delete sys.aud$ where timestamp# < trunc(sysdate) - 4;" Basically i am having 4-5 thousand records getting added every day in that table and if i use above cmd then there will be substantial increase in size of RBS which again defeats my purpose of not allowing SYSTEM.DBF to grow out of proportion(Space constraint). In my process i commit 1000 row deletion at a time in a loop. And to my headache WE have a policy of not deleting the records for atleast a month (i know that is ridiculous but have no choice-since have no one competent enough above me to take a policy change decision). Also you can be dead sure that WE don't have a Oracle Support contract.

Anyway thanks both of you.

Tom Kyte
September 17, 2005 - 8:05 am UTC

4 to 5 thousand records? Big deal, that is *tiny*

4 to 5 HUNDRED thousand records? I'd still say the same thing.

disk is cheap compared to the amount of time you've spent looking at this problem and deleting 4/5K records isn't very much at all.

Turning off DML audits inside of execute procedure

Inho Chang, February 01, 2007 - 1:36 pm UTC

As part of audit requirements, this id needs to be audited for all DML activities and execute procedures. When this id is executing pl/sql procedures which invokes sub PL/SQL procedures, it generates millions of audit records for insert, delete, updated activites done within the sub-procedures. Is there a way to audit all DML activities and just execute PL/sql procedures while excluding dml activities invoked within the PL/SQL procedures ?
Tom Kyte
February 01, 2007 - 3:25 pm UTC

that doesn't make sense - "we need to audit all dml" but we don't want to audit all dml - because millions of dmls will not be audited when run through the procedure.

Why not use another schema for the procedure (another "id" - i believe by "id" you meant "identity, user" right?)

Turning off DML audits inside of execute procedure

Inho Chang, February 01, 2007 - 4:23 pm UTC

We use the method you mentioned, ie. use other ids to run this type of producre. But, we want to cover the situation when this id that we are auditing launches the procedure that generates millions records by mistake. I understand that we need to audit the dmls inside of PL/SQL procedure but just want to minimize impact to the overall database when this happens. Appreciate your reply.
Tom Kyte
February 02, 2007 - 10:13 am UTC

have the code recognize:


that is how mistakes are typically handled? Be recognizing them and not doing it.


A reader, February 02, 2007 - 11:24 am UTC

Appreciate your apply !

Moving SYS.AUD$ to a different tablespace other than SYSTEM

Sarayu, August 18, 2009 - 1:21 am UTC


If we dont move SYS.AUD$ to different tablespace, it might be difficult to manage SYSTEM tablespace growth.

But as per these metalinks, i think oracle says "it is not a supported feature". Then, how we can handle this situation?
Please advice.

See Metalink notes 72460.1 "Moving AUD$ to Another Tablespace and Adding Triggers to AUD$" and 1019377.6: "Script to move SYS.AUD$ table out of SYSTEM tablespace".

Tom Kyte
August 24, 2009 - 4:37 pm UTC

open an SR, they'll talk you through the process

and be aware that in a very soon to be released version there will be a documented supported PL/SQL package to move audit trails to any tablespace you want.