source :
http://dbaclass.com/index.php/article/how-to-archive-rows-in-oracle-12c/
In oracle 12c a new feature called In-Database Archiving has been introduced. With this we can archive specific rows of a table as per our requirement. This is very helpful,when table contains lot of historical data and for full scan it is taking a lot of time.Using this we can archive the historical data.
We will see an example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--- DBATOOL has 8 rows
SQL> select * from dbatool;
EMP
----------
1
2
3
4
4
3
3
3
8 rows selected.
|
You can check the explain plan( Scanning all 8 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL> explain plan for select * from dbatool;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2022247455
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DBATOOL | 8 | 104 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic statistics used: dynamic sampling (level=2)
12 rows selected.
|
Enable archiving of that table.
|
|
SQL> alter table dbatool row archival;
Table altered.
|
This will create one invisible column ORA_ARCHIVE_STATE:(0/1)
ORA_ARCHIVE_STATE:0 – Means that row is not archived
ORA_ARCHIVE_STATE:1 – Means that row is archived
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> ;
1* select ora_archive_state,emp from dbatool
SQL> /
ORA_ EMP
---- ----------
0 1
0 2
0 3
0 4
0 4
0 3
0 3
0 3
8 rows selected.
|
Lets archive the rows whose EMP is 1 or 2 .
|
|
SQL>
SQL> update dbatool set ora_archive_state=dbms_ilm.archivestatename(1) where emp in (1,2);
2 rows updated.
SQL> commit;
Commit complete
|
Now we can see the table has 6 rows and rest were archived.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> select ora_archive_state,emp from dbatool;
ORA_ EMP
---- ----------
0 3
0 4
0 4
0 3
0 3
0 3
6 rows selected.
SQL> select count(*) from dbatool;
COUNT(*)
----------
6
|
If you check the explain ( It will scan only 6 rows, excluding the archival rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL> explain plan for select * from dbatool;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2022247455
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 12090 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DBATOOL | 6 | 12090 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBATOOL"."ORA_ARCHIVE_STATE"='0')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
17 rows selected.
|
If you want to see the rows including archived rows at session level , then use
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> alter session set ROW ARCHIVAL VISIBILITY = all;
Session altered.
SQL> select ora_archive_state,emp from dbatool;
ORA_ EMP
---- ----------
1 1
1 2
0 3
0 4
0 4
0 3
0 3
0 3
8 rows selected.
|
For disabling archiving:
|
|
alter table DBATOOL no row archival;
Table altered.
|
This will drop that invisible colum
