目的
従来型の LOB (BasicFiles LOB) から、Oracle Database 11g からの新しい LOB アーキテクチャである SecureFiles を使用した LOB へ移行する。
環境
- OS: Oracle Enterprise Linux 5.8
- DB: Oracle Database 11g Release 2 (11.2.0.3)
マニュアル
SecureFilesおよびラージ・オブジェクト開発者ガイド
–> 4 Oracle SecureFiles LOBの使用
–> BasicFiles LOBからSecureFiles LOBへの列の移行
SecureFiles LOBの初期化パラメータdb_securefile
SecureFiles LOBを含んだCREATE TABLEの使用
SecureFiles LOBを含んだALTER TABLEの使用管理者ガイド
–> 20 表の管理
–> 表のオンライン再定義
BasicFiles LOB から SecureFiles LOB への移行
Oracle Database 11g からの新しい LOB アーキテクチャである SecureFiles を使用した LOB を使用すると、以下のようなことが可能になる。
- 圧縮(LOB データの圧縮)
- 重複除外(列内で重複する LOB データのコピーを1つのみ格納)
- 暗号化(透過的データ暗号化により LOB データを暗号化)
従来型の LOB は、BasicFiles と呼ばれる。BasicFiles を使用した既存の LOB 列を SecureFiles に移行する。ALTER TABLE での BasicFiles から SecureFiles への変更はできないため、変更するためには表の再作成を行うか、オンライン再定義を行う必要がある。今回は、マニュアルに例があるように、オンライン再定義により行う。
- 検証用に BasicFiles LOB 列を持つ表、データを準備
BasicFiles LOB 列を持つ cust 表を作成してデータを insert。今回この表を SecureFiles LOB に移行してみる。
SQL> connect scott/tiger
SQL> CREATE TABLE cust
(
c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);
Table created.
SQL> INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
1 row created.
SQL> COMMIT;
Commit complete.
- オンライン再定義に必要な権限の付与
オンライン再定義に必要な権限を与える
SQL> connect /as sysdba
SQL> -- Grant privileges required for online redefinition.
SQL> GRANT EXECUTE ON DBMS_REDEFINITION TO scott;
SQL> GRANT ALTER ANY TABLE TO scott;
SQL> GRANT DROP ANY TABLE TO scott;
SQL> GRANT LOCK ANY TABLE TO scott;
SQL> GRANT CREATE ANY TABLE TO scott;
SQL> GRANT SELECT ANY TABLE TO scott;
SQL> -- Privileges required to perform cloning of dependent objects.
SQL> GRANT CREATE ANY TRIGGER TO scott;
SQL> GRANT CREATE ANY INDEX TO scott;
- 初期化パラメータ
db_securefileパラメータの変更
SecureFiles LOB の初期化パラメータ db_securefile パラメータを変更する。
SecureFiles LOB を使用するためには PERMITTED (デフォルト) または ALWAYS である必要がある。
例えば、NEVER に設定されている場合に SecureFiles LOB の列を作成しようとすると、ORA-43856 が発生する。
SQL> CREATE TABLE test_lob (c_lob CLOB) LOB(c_lob) STORE AS SECUREFILE (compress high);
CREATE TABLE test_lob (c_lob CLOB) LOB(c_lob) STORE AS SECUREFILE (compress high)
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
db_securefile を ALTER SYSTEM で変更する。
SQL> conn /as sysdba
Connected.
SQL> ALTER SYSTEM SET db_securefile = PERMITTED SCOPE=both;
System altered.
- 仮表を作成
オンライン再定義のための仮表を SecureFiles LOB 列を持つようにして作成。元表からコピーされるので主キーなどの制約の指定は不要。
SQL> connect scott/tiger
Connected.
SQL> CREATE TABLE cust_int
(
c_id NUMBER,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
)
LOB(c_lob) STORE AS SECUREFILE;
Table created.
- 表のオンライン再定義
表のオンライン再定義を実行する。
SQL> DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'c_id c_id , '||
'c_zip c_zip , '||
'c_name c_name, '||
'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'cust', 'cust_int', col_mapping);
END;
/
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott', 'cust', 'cust_int',
1, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
errors := 0
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'cust', 'cust_int');
PL/SQL procedure successfully completed.
SQL> DROP TABLE cust_int;
Table dropped.
- SecureFiles LOB に移行されたことを確認
USER_LOBS.SECUREFILE 列が YES であれば SecureFiles になっている。
SQL> col table_name for a30
SQL> col column_name for a30
SQL> SELECT table_name, column_name, securefile FROM user_lobs;
TABLE_NAME COLUMN_NAME SECUREFIL
------------------------------ ------------------------------ ---------
CUST C_LOB YES
SecureFiles LOB の変更
ALTER TABLE 文により既存の SecureFiles LOB 列を変更して、圧縮や重複除外の設定を行う。
- 現在の設定の確認
USER_LOBS にて確認できる。
SQL> col table_name for a30
col column_name for a30
col encrypt for a10
col compression for a15
col deduplication for a15
set lines 200
SQL> SELECT table_name, column_name, encrypt, compression, deduplication FROM user_lobs;
TABLE_NAME COLUMN_NAME ENCRYPT COMPRESSION DEDUPLICATION
------------------------------ ------------------------------ ---------- --------------- ---------------
CUST C_LOB NO NO NO
- 設定の変更
ALTER TABLE により圧縮、重複除外の設定を行う。
SQL> ALTER TABLE cust MODIFY LOB(c_lob) (COMPRESS HIGH);
Table altered.
SQL> ALTER TABLE cust MODIFY LOB(c_lob) (DEDUPLICATE);
Table altered.
- 設定変更の確認
USER_LOBS にて確認。
SQL> col table_name for a30
col column_name for a30
col encrypt for a10
col compression for a15
col deduplication for a15
set lines 200
SQL> SELECT table_name, column_name, encrypt, compression, deduplication FROM user_lobs;
TABLE_NAME COLUMN_NAME ENCRYPT COMPRESSION DEDUPLICATION
------------------------------ ------------------------------ ---------- --------------- ---------------
CUST C_LOB NO HIGH LOB