演習: インポートでの索引圧縮の使用

この演習では、インポート操作で索引圧縮を使用する方法を示します。

新しい演習を開始する前に、演習の環境の推奨事項を参照してください。

HRスキーマを作成します。コマンドの文字列passwordをパスワードで変更します。

$ $ORACLE_HOME/bin/sqlplus "sys@PDB20 AS SYSDBA" @/u01/app/oracle/product/20.0.0/dbhome_1/demo/schema/human_resources/hr_main.sql password users temp /tmp

...
Commit complete.

PL/SQL procedure successfully completed.

SQL>

HR.EMPLOYEES表が圧縮を使用しておらず、圧縮を使用していない索引を所有していることを確認します。

 SQL> CONNECT SYSTEM@PDB20
Enter password: password
Connected.
SQL> SELECT compression, compress_for FROM DBA_TABLES WHERE table_name='EMPLOYEES';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

SQL> COL INDEX_NAME FORMAT A30
SQL> SELECT index_name, compression FROM dba_indexes WHERE table_name='EMPLOYEES';

INDEX_NAME                     COMPRESSION
------------------------------ -------------
EMP_NAME_IX                    DISABLED
EMP_EMAIL_UK                   DISABLED
EMP_EMP_ID_PK                  DISABLED
EMP_DEPARTMENT_IX              DISABLED
EMP_JOB_IX                     DISABLED
EMP_MANAGER_IX                 DISABLED

6 rows selected.

SQL>

Oracle Data Pumpダンプファイル用のディレクトリを作成します。

SQL> CREATE DIRECTORY dp_dir AS '/u01/app/oracle/admin';

Directory created.

SQL> GRANT read, write ON DIRECTORY dp_dir TO hr;

Grant succeeded.

SQL> EXIT
$

HR.EMPLOYEES表をエクスポートします。Database Vaultの警告は無視してください。

$ expdp hr@PDB20 DUMPFILE=PDB20.dmp DIRECTORY=dp_dir TABLES=EMPLOYEES REUSE_DUMPFILES=YES

Export: Release 20.0.0.0.0 - Production on Wed Apr 8 16:27:21 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
Password: password

Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@PDB20 DUMPFILE=PDB20.dmp DIRECTORY=dp_dir TABLES=EMPLOYEES REUSE_DUMPFILES=YES
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/PDB20.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 8 16:27:55 2020 elapsed 0 00:00:29

$

PDB20の表を削除します。

$ sqlplus SYSTEM@PDB20

SQL*Plus: Release 20.0.0.0.0 - Production on Wed Apr 8 16:28:45 2020
Version 20.2.0.0.0

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

Last Successful login time: Wed Apr 08 2020 16:24:56 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> DROP TABLE hr.employees CASCADE CONSTRAINTS;

Table dropped.

SQL> EXIT
$

索引圧縮および表圧縮パラメータを使用して表をインポートします。

$ impdp hr@PDB20 FULL=Y DUMPFILE=PDB20.dmp DIRECTORY=dp_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT
Import: Release 20.0.0.0.0 - Production on Wed Apr 8 16:39:13 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
Password: password

Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01":  hr/********@PDB20 FULL=Y DUMPFILE=PDB20.dmp DIRECTORY=dp_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:"COMPRESS BASIC" TRANSFORM=INDEX_COMPRESSION_CLAUSE:"COMPRESS ADVANCED LOW" EXCLUDE=CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"HR"."EMP_EMP_ID_PK" failed to create with error:
ORA-25193: cannot use COMPRESS option for a single column key

Failing sql is:
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPRESS ADVANCED LOW  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Apr 8 16:39:55 2020 elapsed 0 00:00:36

$

エラーは無視してください。

インポートされた表で圧縮が使用されており、その索引でも圧縮が使用されていることを確認します。

$ sqlplus SYSTEM@PDB20    
                                  
SQL*Plus: Release 20.0.0.0.0 - Production on Wed Apr 8 16:40:59 2020
Version 20.2.0.0.0

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

Last Successful login time: Wed Apr 08 2020 16:38:57 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> SELECT compression, compress_for FROM DBA_TABLES WHERE table_name='EMPLOYEES';

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

SQL> COL INDEX_NAME FORMAT A30
SQL> SELECT index_name, compression FROM dba_indexes WHERE table_name='EMPLOYEES';

INDEX_NAME                     COMPRESSION
------------------------------ -------------
EMP_DEPARTMENT_IX              ADVANCED LOW
EMP_JOB_IX                     ADVANCED LOW
EMP_MANAGER_IX                 ADVANCED LOW
EMP_NAME_IX                    ADVANCED LOW

SQL> EXIT
$

親トピック: Oracle Data Pumpによるオプションの索引圧縮の提供