前回のおさらい
前回はすぐに使える！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を変更せずに論理削除済みの行を表示しない仕組みを確認します。
投稿者プロフィール
Oracle Cloud2024年4月18日OCI ブロック・ボリュームをクローニングしてみよう！
Dbvisit Standby2024年3月29日【Dbvisit Standby】Dbvisit Software Limited 様と対談致しました 第４弾 世界の面白ウェビナー、新しいお客様達へ- Dbvisit Standby2024年3月21日【Dbvisit Standby】Dbvisit Software Limited 様と対談致しました 第３弾 グローバルスタンダードなDbvisit StandbyMP(最大規模のデータベース)
- Dbvisit Standby2024年3月12日【Dbvisit Standby】Dbvisit Software Limited 様と対談致しました 第２弾 FSDRの魅力 と FSDRにDbvisit Standbyがエントリーされます
