Site icon DBA의 정석

Oracle 12c ILM 테스트

In-Database Row Archiving

이번 실습은 테이블 내 로우를 보이지 않게 처리함으로써 아카이빙을 하는 In-Database Row Archiving 을 실습해 보겠습니다. [PDB1]에서 진행합니다.

먼저 [PDB1]에 SYS 유저로 접속하여 실습 유저인 HR 계정의 암호와 상태를 변경한 후, CTAS 구문으로 테스트용 테이블 EMP_ARCH를 생성합니다.

[oracle@New-Features-12c ~]$ sqlplus sys/oracle_4U@pdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 30 08:47:29 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> alter user hr identified by hr account unlock;

User altered.

SQL> connect hr/hr@pdb1

Connected.

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

Table created.

EMP_ARCH 테이블의 Row Archiving 을 enable 시킵니다.

SQL> alter table emp_arch row archival;

Table altered.

이제 테이블 구조를 확인해 보겠습니다. Row Archiving을 enable 했을 때 나타나는 히든 칼럼인 ora_archive_state 칼럼은 Describe 명령에서는 나타나지 않고 쿼리에서 해당 칼럼을 명시했을때만 나타납니다.

ora_archive_state 값이 0인 것은 Row Archiving 대상이 아니라는 뜻이며, 쿼리 수행 시 보여지는 로우입니다.

SQL> set lines 70

SQL> col ora_archive_state for a20

SQL> desc emp_arch

Name Null? Type

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

EMPLOYEE_ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

SQL> select employee_id,first_name,ora_archive_state from emp_arch;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

102 Lex 0

103 Alexander 0

조회 시 ora_archive_state 을 명시하지 않으면 나타나지 않습니다.

SQL> select * from emp_arch;

EMPLOYEE_ID FIRST_NAME

———– ——————–

100 Steven

101 Neena

102 Lex

103 Alexander

dbms_ilm.archivestatename 프로시저를 이용해서 employee_id 가 102, 103 인 로우에 대해 ora_archive_state 상태를 1로 업데이트하고 EMP_ARCH 테이블을 조회해 보겠습니다.

employee_id 가 102, 103 인 로우는 보이지 않게 된 것을 알 수있습니다.

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

2 rows updated.

SQL> commit;

Commit complete.

SQL> select employee_id,first_name,ora_archive_state from emp_arch;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

아카이빙된 로우를 볼 수 있게 하려면 아래와 같이 visibility 속성을 all로 변경합니다.

SQL> alter session set row archival visibility = all;

Session altered.

SQL> select employee_id,first_name,ora_archive_state from emp_arch;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0


102 Lex 1


103 Alexander 1

Active 상태(아카이빙 되지 않은) 로우만 나타나게 하려면 visibility 속성을 active로 변경합니다.

SQL> alter session set row archival visibility = active;

Session altered.

SQL> select employee_id,first_name,ora_archive_state from emp_arch;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

Row Archiving 된 테이블을 CTAS 구문 등으로 복사했을 때 원 테이블의 아카이빙 상태 정보는 복사되지 않습니다. 이를 확인해 보겠습니다. EMP_ARCH 테이블의 복사본을 먼저 만듭니다.

SQL> create table emp_arch_copy as select employee_id, first_name from emp_arch;

Table created.

복사한 EMP_ARCH_COPY 테이블은 Row Archiving을 enable 시키지 않았기 때문에 히든 칼럼 ora_archive_state 를 가지고 있지 않습니다. 따라서 ora_archive_state 칼럼이 없다는 에러를 리턴하게 됩니다.

SQL> select employee_id,first_name,ora_archive_state from emp_arch_copy;

select employee_id,first_name,ora_archive_state from emp_arch_copy

*

ERROR at line 1:

ORA-00904: “ORA_ARCHIVE_STATE”: invalid identifier

EMP_ARCH_COPY 테이블을 히든 칼럼은 제외하고 조회하면 정상 출력됩니다.

원본 테이블에서 Row Archiving이 enable 되어 있었더라도 이를 복사하게 되면 전체 로우가 복사됩니다.

SQL> select employee_id,first_name from emp_arch_copy;

EMPLOYEE_ID FIRST_NAME

———– ——————–

100 Steven

101 Neena

102 Lex

103 Alexander

row archival visibility 속성을 ALL로 변경한 후, EMP_ARCH_COPY 테이블의 Row Archiving 을 enable 시키면 이제 ora_archive_state 칼럼도 조회할 수 있게 됩니다.

SQL> alter session set row archival visibility = all;

Session altered.

SQL> alter table emp_arch_copy row archival;

Table altered.

SQL> select employee_id, first_name, ora_archive_state from emp_arch_copy;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

102 Lex 0

103 Alexander 0

다시 EMP_ARCH_COPY 테이블에서 employee_id 가 102, 103 인 로우에 대해 ora_archive_state 상태를 1로 업데이트하고나서 데이터를 조회해 보겠습니다. 2개의 로우는 active (visible), 2개는 inactive 상태인 것을 알 수 있습니다. 마찬가지로 EMP_ARCH 테이블의 데이터도 다음 실습 단계를 위해 확인해 보면 EMP_ARCH_COPY 테이블과 마찬가지로 2개의 로우는 active (visible), 2개는 inactive 상태임을 알 수 있습니다.

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

2 rows updated.

SQL> commit;

Commit complete.

SQL> select employee_id,first_name,ora_archive_state from emp_arch_copy;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

102 Lex 1

103 Alexander 1

SQL> select employee_id,first_name,ora_archive_state from emp_arch;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

102 Lex 1

103 Alexander 1

active와 inactive 로우가 혼재되어 있는 EMP_ARCH 모든 데이터를 EMP_ARCH_ARCH로 복사했을 때 어떻게 되는 지 알아보겠습니다. 이때 기존 데이터와의 구분을 위해 “_New”를 붙여 insert 합니다.

앞서 CTAS 구문으로 테이블 복사 시, 원 테이블의 아카이빙 상태 정보는 복사되지 않았던 것처럼 insert … select 구문에서도 아카이빙 상태 정보는 복사되지 않고 모두 0, 즉 active 상태로 남아 있는 것을 알 수 있습니다.

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

4 rows created.

SQL> commit;

Commit complete.

SQL> select employee_id,first_name,ora_archive_state from emp_arch_copy;

EMPLOYEE_ID FIRST_NAME ORA_ARCHIVE_STATE

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

100 Steven 0

101 Neena 0

102 Lex 1

103 Alexander 1


100 Steven_New 0


101 Neena_New 0


102 Lex_New 0


103 Alexander_New 0

8 rows selected.

SQL> exit

Disconnected from Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Exit mobile version