前回のおさらい

前回はすぐに使える!DBデータの論理削除(1/4) そもそも「論理削除」って何?ということで、SQLやテーブルの構成を変更することなくデータの論理削除を実現できる「In-Database Archiving」の概要について紹介しました。

今回は、In-Database Archivingによる論理削除が、どのように実現されているかを紹介します。

前提として、今回も少しOracle Databaseの知識がある人向け(マニア向け)の内容になっているかと思いますが、どうぞよろしくお願いいたします。

どのように削除フラグを管理しているのか?

論理削除の運用において、データが「削除されている」かどうかを管理するためには、テーブルにデータが削除されているかの目印(削除フラグ)を格納する列を追加する必要があります。
しかし、前回、descコマンドでテーブルの構成を確認しても、そのような列は追加されていないように見えました。

では、In-Database Archivingではどのように削除フラグを管理しているのでしょうか?

今回は実際にコマンドとその結果を見ながら進めていきたいと思います。
もし実際に検証用にDBを使用できるのであれば、是非実際に試してみてください!

まずは今回検証で使用するDB環境についてご紹介します。

DBバージョン 12.2.0.1.0
Oracle DB所有者ユーザ oracle
ORACLE_BASE /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/12.2.0/dbhome_1
リスナー名 LISTENER
DBインスタンス名 orcl
Oracle SID orcl
PDB名(※1) orclpdb
PDB接続用接続記述子(※1) orclpdb

※1
12c以降、Oracle DatabaseではCDBアーキテクチャを使用した構成が推奨となっています。
そのため、今回はPDBを一つだけ作成したシングルテナント構成としています。
シングルテナント構成であれば、CDBアーキテクチャの使用に特別なライセンスは必要ありません。
(PDBを複数作成するとOracle Multitenantオプションが必要となります。)

CDBアーキテクチャについては、後日、本ブログでも取り上げる予定です!

こちらは一例で、DBの構成によっては、実行結果が異なる場合があります。


それでは、実際にコマンドを見ていきましょう!

まずは検証環境のDBを起動します。

# 今回の検証は、DB所有者ユーザでログインし、実行します。
# まず、環境変数を確認します。
[oracle$] env | grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_SID=orcl
# シングルテナント構成では、ORACLE_SIDはCDBのものになります。
# そのため、直接PDBに接続するにはPDB用の接続記述子の定義が必要です。

[oracle$] env | grep PATH
PATH=/u01/app/oracle/product/12.2.0/dbhome_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

# 次に、リスナーを起動します。
[oracle$] lsnrctl start LISTENER

[略]
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.2.0.1.0 - Production
開始日                    26-3月 -2018 20:01:35
稼働時間                  0 日 0 時間 0 分 1 秒
[略]

# 次に、DBを起動します。

[oracle$] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 月 3月 26 20:02:54 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

アイドル・インスタンスに接続しました。

SQL> startup

ORACLEインスタンスが起動しました。

Total System Global Area  624951296 bytes
Fixed Size		    8795856 bytes
Variable Size		  310380848 bytes
Database Buffers	  297795584 bytes
Redo Buffers		    7979008 bytes
データベースがマウントされました。
データベースがオープンされました。

SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
orcl		 OPEN

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO
# PDBのORCLPDBがDB起動と同時に起動しています。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
# 現在の接続先はCDBですね。

次に、PDBに検証用のユーザとテーブルを作成します。

# PDBに接続します。
SQL> alter session set container=orclpdb;

セッションが変更されました。

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB

# 検証用ユーザを作成します。
SQL> create user test identified by test;

ユーザーが作成されました。

# In-Database Archivingに権限が必要かを確認するため、DBA権限は付与しません。
# 接続とテーブル作成に必要な権限を付与します。
SQL> grant create session, create table, unlimited tablespace to test;

権限付与が成功しました。

# testユーザでPDBに接続します。
SQL> conn test/test@orclpdb
接続されました。

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB

SQL> create table test.testtable(col1 number not null, col2 number);

表が作成されました。

SQL> desc test.testtable
 名前                         NULL?    型
 ---------------------------- -------- ----------------------------
 COL1		              NOT NULL NUMBER
 COL2		                       NUMBER

削除フラグ用の列は特に作成していません。
このテーブルに、データを入れていきます。

今回は、Databaseにあらかじめ用意されているサンプルスキーマを使用します。
サンプルスキーマを作成するSQLは、ORACLE_HOME/rdbms/admin配下に格納されています。ただし、このSQLはPDBにサンプルスキーマを作成してくれないため、今回テスト用に使用するPDBにサンプルスキーマが作成されるよう、少しだけ改造したSQLを用意しましょう。

# サンプルスキーマ作成用のSQLを確認します。
SQL> quit

[oracle$ ] cat $ORACLE_HOME/rdbms/admin/utlsampl.sql

[略]
CONNECT SCOTT/tiger
[略]

# 36行目辺りに上記のような行があるかと思います。
# 接続記述を使用していないためCDBにつながってしまいます。
# PDBに接続できるよう、SQLを変更します。
[oracle$] cp -p $ORACLE_HOME/rdbms/admin/utlsampl.sql  $ORACLE_HOME/rdbms/admin/utlsampl_pdborcl.sql
[oracle$] vi $ORACLE_HOME/rdbms/admin/utlsampl_pdborcl.sql
# 上で確認した36行目辺りのコマンドを以下のように変更します。
# [変更前]
# CONNECT SCOTT/tiger
# [変更後]
# CONNECT SCOTT/tiger@orclpdb

# 変更したSQLを実行します。
[oracle$] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 金 3月 23 20:02:39 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

SQL> alter session set container = orclpdb;

セッションが変更されました。

SQL> @?/rdbms/admin/utlsampl_pdborcl.sql

[oracle$] sqlplus scott/tiger@orclpdb

SQL*Plus: Release 12.2.0.1.0 Production on 金 3月 23 20:06:16 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

最終正常ログイン時間: 金 3月  23 2018 20:06:06 +09:00


Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

SQL> desc emp
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 ENAME						    VARCHAR2(10)
 JOB						    VARCHAR2(9)
 MGR						    NUMBER(4)
 HIREDATE					    DATE
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 DEPTNO 					    NUMBER(2)

# この時点で、削除フラグ用の列は定義されていません。

SQL> set pages 9999 lin 9999
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 80-12-17	   800			  20
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30
      7698 BLAKE      MANAGER	      7839 81-05-01	  2850			  30
      7782 CLARK      MANAGER	      7839 81-06-09	  2450			  10
      7839 KING       PRESIDENT 	   81-11-17	  5000			  10
      7844 TURNER     SALESMAN	      7698 81-09-08	  1500		0	  30
      7900 JAMES      CLERK	      7698 81-12-03	   950			  30
      7902 FORD       ANALYST	      7566 81-12-03	  3000			  20
      7934 MILLER     CLERK	      7782 82-01-23	  1300			  10

11行が選択されました。

 

まだIn-Database Archiveは無効なため、全ての行が表示されています。

それでは、このempテーブルに対し、In-Database Archivingを有効化してみましょう。

# 以下のコマンドにより、In-Database Archivingを有効化します。
SQL> ALTER TABLE emp ROW ARCHIVAL;

表が変更されました。

# desc	コマンドでempテーブルの定義を確認してみましょう。
SQL> desc emp
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 ENAME						    VARCHAR2(10)
 JOB						    VARCHAR2(9)
 MGR						    NUMBER(4)
 HIREDATE					    DATE
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 DEPTNO 					    NUMBER(2)

# 特にテーブルの定義は変更されていませんね。
# ここから、削除フラグを立てて動作を確認してみましょう。

# まず、削除フラグ設定前の出力を確認します。
# 出力が多くなってしまうので、ここからは参照の対象を絞ってみます。
SQL> select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 80-12-17	   800			  20
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30
      7521 WARD       SALESMAN	      7698 81-02-22	  1250	      500	  30
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30

# 以下のコマンドにより、EMPNOが7369または7521の行の削除フラグを設定します。
SQL> update emp set ora_archive_state = '1' where empno in (7369, 7521);

2行が更新されました。

# 削除フラグの設定は通常のupdate文と同等なため、コミットを行います。
SQL> commit;

コミットが完了しました。

# 再度テーブルを参照します。
SQL> select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30

 

削除フラグを設定したEMPNOが7369の行と7521の行が表示されなくなりました。
入力しているSQLは、In-Database Archiving有効化前と同様です。また、テーブル参照時の列の見え方も変化ありません。
では、削除フラグはどのように管理されているのでしょうか。

ここで、削除フラグ設定時のアップデート文に注目します。
アップデートの対象列は「ora_archive_state」となっていますね。ということは、テーブルが何らかの形でこのora_archive_state列を持っていて、ここに削除フラグを格納していると考えられます。
descとは別の方法でempテーブルの構造を確認しましょう。

SQL> col table_name for a10
SQL> col column_name for a30
SQL> col data_type for a20

SQL> select table_name, column_name, data_type, hidden_column from user_tab_cols
2  where table_name = 'EMP';

TABLE_NAME COLUMN_NAME			  DATA_TYPE	       HID
---------- ------------------------------ -------------------- ---
EMP	   EMPNO			  NUMBER	       NO
EMP	   ENAME			  VARCHAR2	       NO
EMP	   JOB				  VARCHAR2	       NO
EMP	   MGR				  NUMBER	       NO
EMP	   HIREDATE			  DATE		       NO
EMP	   SAL				  NUMBER	       NO
EMP	   COMM 			  NUMBER	       NO
EMP	   DEPTNO			  NUMBER	       NO
EMP	   SYS_NC00009$ 		  RAW		       YES
EMP	   ORA_ARCHIVE_STATE		  VARCHAR2	       YES
10行が選択されました。

 

HIDDEN_COLUMN列が「YES」のORA_ARCHIVE_STATE列が確認できますね。
HIDDEN_COLUMN列が「YES」の列は非表示列と言い、ユーザの操作から特定の列を隠す12cの新機能です。
非表示列が有効化された列は、明示的に列名を追加しない限り、select文やdelete文などDMLの対象にならず、descコマンドでも表示されません。
非表示列のデータは、明示的に列名を指定することで参照が可能です。

SQL> col ORA_ARCHIVE_STATE for a5
SQL> select emp.*, emp.ora_archive_state from scott.emp
  2  where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO ORA_A
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- -----
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30 0
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20 0
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30 0

ora_archive_state列が表示できました。ただ、ora_archive_state列が0以外の行は論理削除されているため、表示されません。
ここで、論理削除のメリットの一つに、削除済みの行のデータも参照可能、という点があったかと思います。
In-Database Archivingでは、論理削除済みの行の表示は以下のように行います。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SQL> select emp.*, emp.ora_archive_state from scott.emp
  2  where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO ORA_A
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- -----
      7369 SMITH      CLERK	      7902 80-12-17	   800			  20 1
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30 0
      7521 WARD       SALESMAN	      7698 81-02-22	  1250	      500	  30 1
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20 0
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30 0

 

非表示となっていた行が表示され、ora_archive_state列が1になっていることが確認できました。
これはALTER SESSION文で変更しているため、他のセッションからは論理削除された行は見えないままです。
また、自分自身も一度セッションを切断すると論理削除済みの行が見えなくなります。
セッションを維持したまま論理削除済みの行を非表示にする場合は以下のようにします。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
SQL> select emp.*, emp.ora_archive_state from scott.emp
  2  where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO ORA_A
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- -----
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30 0
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20 0
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30 0

 

論理削除された行の物理削除は、ora_archive_state列を条件にdeleteすることで実現できます。
また、In-Database Archivingでは、ora_archive_state列が「0」以外であれば論理削除済みと扱うため、論理削除時は論理削除日をora_archive_state列に入れることで論理削除を行い、delete文でora_archive_state列の条件を作りこむことで、特定期間よりも前に論理削除した行を物理削除する、といった処理が実現可能です。

ちょっと長くなってきたので、このあたりで休憩しましょう。
今回、削除フラグを保持する仕組みが確認できたので、次回はテーブルの参照時に,入力するSQLを変更せずに論理削除済みの行を表示しない仕組みを確認します。

投稿者プロフィール

DBひとりでできるもん運営チーム
DBひとりでできるもん運営チーム
「DBひとりでできるもん」運営チームです。
「親しみやすさと技術力」をテーマに、技術情報・サービス・インフラ系資格取得に役立つ情報、社員等の情報をお届けします。
70名弱の事業部員で鋭意、執筆中です。
少しでも当社を知って頂けるよう、愛情込めて頑張ります!
※facebook、X(旧twitter)、インスタグラムでは「DBひとりでできるもん」の更新情報を発信しています。