Using In-Database Row Archiving


Connect to pdb1 as the HR user.

. oraenv
[Enter cdb1 at the prompt.]

sqlplus hr/hr@localhost:1521/pdb1

Create a copy of the HR.employees table. Call the copy HR.emp_arch, and only copy over 4 rows.

create table emp_arch
as select employee_id, first_name from employees where rownum <= 4;

Enable row archival on HR.emp_arch.

alter table emp_arch row archival;

There is a hidden column in row-archival-enabled tables called ora_archive_state which indicates whether a row is archived or not. The hidden column is only displayed if specified in a query. First, describe the table structure ofHR.emp_arch. Notice that the ora_archive_state column is not listed.

desc emp_arch

Now, query the HR.emp_arch table. Display the ora_archive_state column in the query result.

select employee_id, first_name, ora_archive_state from emp_arch;

A value of 0 in the ora_archive_state column means the row is not archived; in other words, the row is active and visible via a standard query.

Confirm that all rows are active by issuing a standard query. You should see all 4 rows.

select * from emp_arch;

Setting the Archive State of Some Rows

Use the dbms_ilm.archivestatename procedure to update the ora_archive_state value for employee_id 102 and 103.

update emp_arch
set ora_archive_state=dbms_ilm.archivestatename(1)
where employee_id in (102, 103);

commit;

Confirm that now only 2 rows are visible via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 rows should be 0.

select employee_id, first_name, ora_archive_state from emp_arch;

Enabling a Session to View Archived Rows

Set the archival visibility to ALL for the session. This will allow this session to view all rows, archived or not.

alter session set row archival visibility = all;

Issue the same query as before, but now you should see all 4 rows.

select employee_id, first_name, ora_archive_state from emp_arch;

Set the archival visibility to ACTIVE for the session. This will allow this session to view only active (unarchived) rows.

alter session set row archival visibility = active;

Issue the same query as before, but now you should see only the 2 active rows.

select employee_id, first_name, ora_archive_state from emp_arch;

Verifying that Copying a Row-Archival Table Will Not Propagate the Source Table’s Archival State to the Target Table

Use CTAS to make a copy of the HR.emp_arch table.

create table emp_arch_copy as select employee_id, first_name from emp_arch;

Verify that HR.emp_arch_copy does not have row archival enabled, and therefore does not contain the ora_archive_state hidden column. You should see an “invalid identifier” error for this query.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

Query the table again, but this time omit the hidden column. Notice that all 4 rows are copied even though you set the row archival visibility for this session to active rows only.

select employee_id, first_name from emp_arch_copy;

Set the archival visibility to ALL for the session. From this point forward, this session should now see all rows, whether archived or not.

alter session set row archival visibility = all;

Enable row archival for HR.emp_arch_copy.

alter table emp_arch_copy row archival;

Verify that HR.emp_arch_copy now has row archival enabled, and therefore contains the ora_archive_state hidden column.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

Use the dbms_ilm.archivestatename procedure to update the ora_archive_state value for employee_id 102 and 103.

update emp_arch_copy
set ora_archive_state=dbms_ilm.archivestatename(1)
where employee_id in (102, 103);

commit;

Confirm that now 2 rows are active, and 2 rows are inactive, via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

Recall that the HR.emp_arch table you created earlier contains 2 active and 2 inactive rows. Confirm this via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.

select employee_id, first_name, ora_archive_state from emp_arch;

Copy all rows from HR.emp_arch to HR.emp_arch_copy. To distinguish between rows that were previously copied to HR.emp_arch_copy via CTAS, and the rows being copied again now, append ‘_New’ to the values in thefirst_name column values.

insert into emp_arch_copy select employee_id, first_name || ‘_New’ from emp_arch;

commit;

What row archival state do you think the newly inserted rows have? Remember that the archival state is not copied along with the rows.

Query HR.emp_arch_copy to determine the ora_archive_state value for each row.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

You should see that the ora_archive_state values from HR.emp_arch are not propagated to HR.emp_arch_copy. All of the _New rows in HR.emp_arch_copy have ora_archive_state set to the default value of 0 (active). The 2 inactive rows resulted from the update you performed in the HR.emp_arch_copy table in step 7.

Resetting Your Environment

Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

Drop the tables created in this tutorial.

drop table emp_arch;

drop table emp_arch_copy;

 —————————————————————————————–

So what’s the use of doing all of this. suppose I have a table that has financial records of my company of last 10 YR. that’s terabytes of data. I don’t want to store such large amount of data in my expensive and finite memory, One thing we can do is move the past 9 yr of data (compress it) at low cost storage(Like TAP) and when we require this data i can get it again thus improving the efficiency of data retrieval.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s