Folks. Can anyone please tell me if there is a way to delete data from the archive tables? I can run the monitor archive services to archive or delete data from the audit tables. But that will cause my archive tables to keep filling up. How do I clean the archive?

IS Version 7.1.2

Did you that running purge services on archive schema ?
It should work because archive schema may contains a full copy of audit one.

Bye the way, I suggest looking very closely on tables content after purging because I spent several weeks (w/ help of an SAG consultant) in order to have tables really purged.

Good luck

Laurent

By writing purge services you can delete data from tables, however when you delete data, this space / segments are not released and HWM is NOT reset to the deleted level.

As the data is added to table, table size grows and the segments are allocated more. The highest occupied level is known as HWM (High Water Mark).

Say at one stage, you may have 100 segments. You delete 90% the data. But still oracle maintains 100 segments. When you query this table, oracle will search all the 100 extents (upto the HWM), though most of them contains no data.

Ideally you should check with your DBAs to find out how you can free up these segments.

Alternatively you can amend the high water mark to gain the table space. you can do this by using one of the variations of the ALTER TABLE … SHRINK SPACE command:

This kind of shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled

Following commands might be helpful in achieving this shrinking.

– Execute below query to see the table space before and after shrinking the table
Select segment_name,bytes/1024/1024 “Space in MB” from user_segments where segment_type=‘TABLE’ order by 2 desc

– Enable row movement. you must enable row movement if you want to modify HWM.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

– Any one/all of the queries can be used to update HWN and recover unused space.
– Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

– Recover space, but don’t amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

– Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Let me know if it is helpful to you.