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
|
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
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 |
Hey exceptional website! Does running a blog such as this take a massive amount work? I’ve virtually no understanding of computer programming however I had been hoping to start my own blog soon. Anyways, if you have any recommendations or tips for new blog owners please share. I understand this is off topic however I simply wanted to ask. Cheers!