"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
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.
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 ?
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.
February 02, 2007 - 10:13 am UTC
have the code recognize:
MISTAKE BEING MADE - THUS I SHOULD FAIL
that is how mistakes are typically handled? Be recognizing them and not doing it.
Follow-up
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
Tom,
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".
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.