advertisements
_____________________________________________________________________________________________________________________
Purpose of this parameter is to skip the import of a table or a table partition with index in UNUSABLE state. Which means the table should be there in the database with an index in unusable state. This will reduce the unnecessary time for index creation which is in unusable state during the import. By default value for this parameter is ‘Y’. It will come into action when its value is N.
Syntax: SKIP_UNUSABLE_INDEXES={y | n}
Example
I have table called EMP with one index and one primary key. I am going to make the index to UNUSABLE for this example.
SQL> select index_name , status from dba_indexes where table_name ='EMP';
INDEX_NAME STATUS
------------------------------ --------
EMP_IDX VALID
PK_EMP VALID
SQL> alter index EMP_IDX unusable;
Index altered.
SQL> select index_name , status from dba_indexes where table_name ='EMP';
INDEX_NAME STATUS
------------------------------ --------
EMP_IDX UNUSABLE
PK_EMP VALID
$ expdp directory=exp_dir dumpfile=exp.dmp logfile=exp.log tables=scott.emp,scott.dept
Export: Release 11.2.0.2.0 - Production on Wed May 30 04:08:43 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=exp_dir dumpfile=exp.dmp logfile=exp.log tables=scott.emp,scott.dept
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
. . exported "SCOTT"."EMP" 8.578 KB 14 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/shony/exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 04:08:52
I am going to import the dump into the database. I haven’t dropped the tables from schema. The table which is having UNUSABLE index got error ORA-26028: index SCOTT.EMP_IDX initially in unusable state
$impdp directory=exp_dir dumpfile=exp.dmp logfile=imp.log SKIP_UNUSABLE_INDEXES=n full=y table_exists_action=append
Import: Release 11.2.0.2.0 - Production on Wed May 30 05:47:13 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=exp.dmp logfile=imp.log SKIP_UNUSABLE_INDEXES=n full=y table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.937 KB 4 rows
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-26028: index SCOTT.EMP_IDX initially in unusable state
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 05:47:19
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment