Solved

How to prevent system tablespace fragmentation when using Oracle Audit?

Posted on 2001-06-19
3
1,149 Views
Last Modified: 2008-03-03
Hi All,

I plan to use Oracle Audit function to monitor the tables being updated or deleted. (Set Audit_trail=DB in initialaztion file).

Since the Aud$ table is stored in system tablespace, frequently truncating  the Aud$ table will cause system tablespace fragmentation and performance degrade.

Can anyone tell me how to  prevent this problem?

Thanks.
0
Comment
Question by:DBFan
  • 2
3 Comments
 

Accepted Solution

by:
mattia earned 30 total points
ID: 6207918
Hi,

What version of Oracle are you using?
There are two ways you can do this:

1) You can either issue:

ALTER TABLE SYS.AUD$ MOVE TABLESPACE <tablespace name>;
That will move the audit table out of SYSTEM tablespace and into another tablespace. Or...

2) Create a new table xyz using SELECT * FROM AUD$;
Drop table AUD$, then issue a rename command to rename xyz to AUD$.

In both cases make sure that auding isn't switched on.

Regards,
--M.A
0
 

Author Comment

by:DBFan
ID: 6208303
Mattia,

I use Oracle 8.0.6.  Alter table sys.aud$ move tablespace <new tablespace name> is not a valid command.  Can you check it for me?

Thanks.

DBFan

0
 

Expert Comment

by:mattia
ID: 6208644
Since you are using 8.0.6, you have to use the second option I mentioned above.

Moving tables between table spaces is supported in Oracle 8i (8.1.5 and later) only.

Hope this helps,
--M.A
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now