Environment:
Oracle 11.2.0.4 EE on Solaris 10
I have a Data Pump Import job that runs weekly but as it was running this last time the underlying tablespace ran out of room and by the time I added more space there were 26 indexes that didn't get imported.
So, all I wanted to do was import just those 26 indexes.
The underlying tables had already been imported.
I didn't want to import ALL the indexes again since there are over 2200 of them and many are large and I definitely didn't want to import the tables again as it takes many hours.
I set up a parameter file that looked like this:
USERID=<user>/<password>
DUMPFILE=<dumpfile name>
DIRECTORY=PECOS_DP_DIR
LOGFILE=impdp_pecos_indexes.log
REMAP_TABLESPACE=PECOS_U1M_DATA:PECOSAI_BASE_DATA,PECOS_U1M_INDX:PECOSAI_BASE_DATA
JOB_NAME=impdp_pecos_indexes
INCLUDE=INDEX:"='PEC_ADI_UNPRCSD_RPT_AK01'"
INCLUDE=INDEX:"='PEC_ADI_UNPRCSD_RPT_FK01'"
INCLUDE=INDEX:"='PEC_ADI_UNPRCSD_RPT_FK02'"
INCLUDE=INDEX:"='PEC_ADI_UNPRCSD_RPT_PK'"
INCLUDE=INDEX:"='PEC_APLCTN_TRANS_AUDT_LOG_PK'"
INCLUDE=INDEX:"='PEC_DME_ACRDTN_PROD_HS_BK_AK01'"
INCLUDE=INDEX:"='PEC_EMPLER_ASCTN_CNTR_BK721_I1'"
INCLUDE=INDEX:"='PEC_EMPLER_ASCTN_C_H_BK721_I1'"
INCLUDE=INDEX:"='PEC_ENRLMT_ASCTN_BK721_I1'"
INCLUDE=INDEX:"='PEC_ENRLMT_ASCTN_HSTRY_BK721_I'"
INCLUDE=INDEX:"='PEC_ENRLMT_PHYSN_SPC_BK722_I1'"
INCLUDE=INDEX:"='PEC_HIGLAS_AK01'"
INCLUDE=INDEX:"='PEC_HIGLAS_AK02'"
INCLUDE=INDEX:"='PEC_HIGLAS_AK03'"
INCLUDE=INDEX:"='PEC_HIGLAS_AK04'"
INCLUDE=INDEX:"='PEC_HIGLAS_AK05'"
INCLUDE=INDEX:"='PEC_HIGLAS_FIL_PK'"
INCLUDE=INDEX:"='PEC_HIGLAS_PK'"
INCLUDE=INDEX:"='PEC_INDVDL_NAME_CHG_LOG_PK'"
INCLUDE=INDEX:"='PEC_MED_RPT_MV_AK01'"
INCLUDE=INDEX:"='PEC_MSTR_REVLDTN_2_RPT_PK'"
INCLUDE=INDEX:"='PEC_ORG_BUSNS_NAME_CHG_LOG_PK'"
INCLUDE=INDEX:"='PEC_PRCTC_LCTN_BK720_I1'"
INCLUDE=INDEX:"='PEC_PRCTC_LCTN_HSTRY_BK720_I1'"
INCLUDE=INDEX:"='PEC_UAM_DATA_LOG_PK'"
INCLUDE=INDEX:"='T_DEA_NUM_AI_FINAL_I8'"
I got the message:
Import: Release 11.2.0.4.0 - Production on Fri Apr 14 13:03:02 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
ORA-31655: no data or metadata objects selected for job
Master table "PECOPAI"."IMPDP_PECOS_INDEXES" successfully loaded/unloaded
Starting "PECOPAI"."IMPDP_PECOS_INDEXES": PECOPAI/******** parfile=impdp_pecos_indexes.par
Job "PECOPAI"."IMPDP_PECOS_INDEXES" successfully completed at Fri Apr 14 13:03:39 2017 elapsed 0 00:00:36
On the one hand this makes sense because when I specify the 'INCLUDE:INDEX...' according to the documentation that will 'exclude' all other data types, including tables I assume. So, without the tables being in the mix the indexes were being skipped as well.
I tried using just 'INCLUDE=TABLE' and 'INCLUDE=INDEX', without specifying the 26 individual indexes, but because I'm using 'TABLE_EXISTS_ACTION=SKIP', when it skips the table it skips the indexes as well.
So, how do I import just the 26 indexes I want?
Thanks for all suggestions!!
-gary