Archive/Purge table data using plsql!

What do you want to do when you say "archive?"
Update a flag to mark inactive?
Copy to a different table?
Move to a different table?
Something else?

For purge,   simply delete the rows

delete your_table where create_daytime < sysdate - 365*8;

Create a dbms_scheduler job that runs once per day to execute the delete.

If your data is partitioned by create_daytime, you might need to drop old partitions once empty.
Depending on the criteria, you might even be able to use the drop itself to purge old data instead of delete.

HI sdstuber,

What do you want to do when you say "archive?"
-- Means i just want to took backup of the table ex: table_arc data if the create_daytime indicator more than 365*3.

Update a flag to mark inactive?  --Yes
Copy to a different table?  --Yes
Move to a different table?  --Yes

For purge its only delete from the archive table. ex.table_arc

Thanks,

-- Means i just want to took backup of the table ex: table_arc data if the create_daytime indicator more than 365*3.

Update a flag to mark inactive?  --Yes
Copy to a different table?  --Yes
Move to a different table?  --Yes

Those are 4 different options.  You can't say yes to all of them, which one do you want?

Also a "backup" isn't a sql or pl/sql operation, you take backups with RMAN.

Hi @sdstuber,

Move to a different table?
I just want to move data from prod table to archive as per following conditions.

i'm using following test indicator, in order to archive the prod records based on conditions.


Thanks,