Description:-
In this article we are going to see the Oracle 18c Datapump-SKIP_UNUSABLE_INDEXES
It specifies whether import skips loading tables that have indexes that were set to the Index Unusable state.
- If SKIP_UNUSABLE_INDEXES is set to YES, and a table or partition with an index in the Unusable state is encountered, then the load of that table or partition proceeds anyway, as if the unusable index did not exist.
- If SKIP_UNUSABLE_INDEXES is set to NO, and a table or partition with an index in the Unusable state is encountered, then that table or partition is not loaded. Other tables, with indexes not previously set Unusable, continue to be updated as rows are inserted.
- If the SKIP_UNUSABLE_INDEXES parameter is not specified, then the setting of the Oracle Database configuration parameter, SKIP_UNUSABLE_INDEXES (whose default value is y).
Syntax :
SKIP_UNUSABLE_INDEXES=[YES | NO]
SKIP_UNUSABLE_INDEXES=YES :
[oracle@test18c empdata]$ impdp directory=TEST_DIR dumpfile=empdata.dmp logfile=imp_empdata.log SKIP_UNUSABLE_INDEXES=y Import: Release 18.0.0.0.0 - Production on Fri Aug 24 21:26:48 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=TEST_DIR dumpfile=empdata.dmp logfile=imp_empdata.log SKIP_UNUSABLE_INDEXES=y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HARI"."EMP_DATA" 5.562 KB 10 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with successfully at Fri Aug 24 21:38:10 2018 elapsed 0 00:00:19
SKIP_UNUSABLE_INDEXES=NO :
Check index status :
SQL> select index_name , status from dba_indexes where table_name ='EMP_DATA'; INDEX_NAME STATUS ----------- ------ IDX_EMP VALID EMP_ID VALID
Make index status as UNUSABLE
SQL> alter index IDX_EMP unusable;
Index altered.
SQL> select index_name , status from dba_indexes where table_name ='EMP_DATA';
INDEX_NAME STATUS
----------- --------
IDX_EMP UNUSABLE
EMP_ID VALID
The table which is having UNUSABLE index got error ORA-26028: index EMP_DATA.IDX_EMP initially in unusable state
[oracle@test18c empdata]$ impdp directory=TEST_DIR dumpfile=empdata.dmp logfile=imp_empdata.log SKIP_UNUSABLE_INDEXES=n Import: Release 18.0.0.0.0 - Production on Fri Aug 24 21:26:48 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=TEST_DIR dumpfile=empdata.dmp logfile=imp_empdata.log SKIP_UNUSABLE_INDEXES=n Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object "EMP"."EMP_DATA" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-26028: index EMP_DATA.IDX_EMP initially in unusable state Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Fri Aug 24 21:27:10 2018 elapsed 0 00:00:16
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter Group:https://www.linkedin.com/groups/10387079 Twitter: https://twitter.com/hariprasathdba
