Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Archive aud$ to another table

735549Jun 22 2011 — edited Jun 23 2011
I'm trying to work out a mechanism to move the data contained in aud$ to another table (which I'll call older_audit_data) and then delete the data from the aud$ table. My eventual goal will be to maintain 7 days worth of data in aud$ and 7 (older) days worth of data in older_audit_data. Each week I want to run a procedure to truncate older_audit_data and to move aud$ into that tablespace and then remove the copied data. I don't want to impact availability while I move data and can't afford to accidently delete something not archived.

I've created a package, relocate_audit_data which uses a cursor to iterate through aud$ and insert values into older_audit_data and delete the entry from aud$.
It looks like:

cursor time_cursor is select ntimestamp# from sys.aud$ for update;

procedure myProc as
commit_cnt number;
BEGIN
EXECUTE IMMEDIATE 'truncate table older_audit_data';
commit_cnt := 0;
FOR times in time_cursor LOOP
-- procedure call to determine if a group of 5,000 transactions has occurred. If so, commit then continue.
insert into older_audit_data VALUES (
times."SESSIONID",
times."ENTRYID",
...
-- all the columns from aud$ are listed in the format <cursor identifier>."<column name>"
...
times."SQLTEXT");
delete from sys.aud$ where current of time_cursor;
commit_cnt := commit_cnt+1;
END LOOP;
END myProc;

I get two errors when I try to compile the package body. The first is PLS-00302 complaining that SQLTEXT must be declared. The second error is an ORA-00984 column not allowed here. Both errors are reported on the last line of the statement doing an insert off the cursor. I can't figure out where the flaw in my logic is. I'm using 10gR2 Enterprise Edition.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 21 2011
Added on Jun 22 2011
5 comments
771 views