Team,
How about using the "In-Database archiving feature" introduced in 12.1.0.1 for this.
https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT237 Say, i got an EMP table with sample of four rows in it .
demo@ORA12C> create table emp as
2 select rownum as empno,
3 object_name as ename,
4 object_id as sal
5 from all_objects
6 where rownum <=4;
Table created.
demo@ORA12C> select * from emp;
EMPNO ENAME SAL
---------- -------------------- ----------
1 ORA$BASE 133
2 DUAL 142
3 DUAL 143
4 MAP_OBJECT 356
4 rows selected.
to enable in-database archival, we need an alter statement like this.
demo@ORA12C> alter table emp row archival;
Table altered.
demo@ORA12C> select emp.*,ora_archive_state from emp;
EMPNO ENAME SAL ORA_ARCHIV
---------- -------------------- ---------- ----------
1 ORA$BASE 133 0
2 DUAL 142 0
3 DUAL 143 0
4 MAP_OBJECT 356 0
4 rows selected.Say if we need to archive the rows with empno in (1,2) then do an update like this.
demo@ORA12C> update emp set
2 ora_archive_state = dbms_ilm.archiveStateName(1)
3 where empno in (1,2);
2 rows updated.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C>
demo@ORA12C> select * from emp;
EMPNO ENAME SAL
---------- -------------------- ----------
3 DUAL 143
4 MAP_OBJECT 356
2 rows selected.
to get all the rows back, we need to do an alter session like this.
demo@ORA12C> alter session set row archival visibility=all;
Session altered.
demo@ORA12C> select * from emp;
EMPNO ENAME SAL
---------- -------------------- ----------
1 ORA$BASE 133
2 DUAL 142
3 DUAL 143
4 MAP_OBJECT 356
4 rows selected.