Practice: Using Index Compression on Import

Overview

The practice shows how to use index compression on import operations.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment

  • Create the HR schema. Change the password string in the command to your password.

    
    $ sqlplus "sys@PDB21 AS SYSDBA" @/home/oracle/labs/M104780GC10/hr_main.sql password users temp /tmp
    
    ...
    Commit complete.
    
    PL/SQL procedure successfully completed.
    
    SQL> EXIT
    $
  • Verify that the HR.EMPLOYEES table is not using compression and owns indexes that are not using compression.

    
    $ sqlplus SYSTEM@PDB21
    Enter password:
    
    Connected to:
    
    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> 
  • Create a directory for Oracle Data Pump dumpfiles.

    
    SQL> CREATE OR REPLACE DIRECTORY dp_dir AS '/home/oracle/labs';
    
    Directory created.
    
    SQL> GRANT read, write ON DIRECTORY dp_dir TO hr;
    
    Grant succeeded.
    
    SQL> EXIT
    $ 

Step 2 : Export the table

  • Export the HR.EMPLOYEES table. Ignore any Database Vault warning.

    
    $ expdp hr@PDB21 DUMPFILE=PDB21.dmp DIRECTORY=dp_dir TABLES=EMPLOYEES REUSE_DUMPFILES=YES
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@PDB21 DUMPFILE=PDB21.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:
      /home/oracle/labs/PDB21.dmp
    Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 15 08:29:22 2020 elapsed 0 00:00:26
    $ 

Step 3 : Import the table using the compression parameters

  • Drop the table in PDB21.

    
    $ sqlplus SYSTEM@PDB21
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Apr 08 2020 16:24:56 +00:00
    
    Connected to:
    
    SQL> DROP TABLE hr.employees CASCADE CONSTRAINTS;
    
    Table dropped.
    
    SQL> EXIT
    $ 
  • Import the table using the index compression and the table compression parameters.

    
    $ impdp hr@PDB21 FULL=Y DUMPFILE=PDB21.dmp DIRECTORY=dp_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "HR"."SYS_IMPORT_FULL_01":  hr/********@PDB21 FULL=Y DUMPFILE=PDB21.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
    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" successfully completed at Tue Dec 15 08:30:27 2020 elapsed 0 00:00:32
    $ 
  • Verify that the imported table is using compression and that its indexes also use compression.

    
    $ sqlplus SYSTEM@PDB21
                                      
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Apr 08 2020 16:38:57 +00:00
    
    Connected to:
    
    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_EMP_ID_PK                  DISABLED
    EMP_NAME_IX                    ADVANCED LOW
    
    SQL> EXIT
    $ 
  • Why is the primary key index not compressed?

    
    $ sqlplus SYSTEM@PDB21
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Apr 08 2020 16:24:56 +00:00
    
    Connected to:
    
    SQL> ALTER INDEX hr.emp_emp_id_pk REBUILD COMPRESS ADVANCED LOW;
     ALTER INDEX hr.emp_emp_id_pk REBUILD COMPRESS ADVANCED LOW
    *
    ERROR at line 1:
    ORA-25193: cannot use COMPRESS option for a single column key
    
    
    SQL> EXIT
    $