Je devais importer un dump d'un schéma (dump généré par un export classique) dans un schéma d'une autre base. Toutefois une énorme table ne m'intéressait pas dans ce dump. Avec Datapump, il est facile d'exclure une table de l'import en utilisant le paramètre EXCLUDE. Avec l'import classique c'est plus compliqué car la seule chose qu'on puisse faire dans les règles de l'art c'est lister les tables à importer. Or il est plus facile d'exclure une table sur 1000 en la nommant explicitement que de lister les 999 tables qu'on souhaite importer.
L'astuce que j'ai utilisée consiste à créer dans le schéma cible une table du même nom que celle qu'on veut exclure mais avec une structure différente. Ainsi, une erreur sera générée lors de l'import de cette table mais il ira au bout. Ensuite il ne nous reste plus qu'à dropper la "fausse" table.
Exemple:
Je souhaite importer le schéma ADMRTI contenu dans le dump export_admrti_s11.dump vers le schéma RTI$OWNER de la base OPARLISD12 tout en excluant la table TLS_AUDIT.
Pour ce faire, je me connecte d'abord à la base cible pour créer une table portant le même nom que celle que je veux exclure:
SQL> create table tls_audit as select * from user_objects where 1=2; Table created.
Ensuite je peux effectuer mon import:
C:\HOMEWARE>imp userid=rti$owner/xxxxx@oparlisd12 file=export_admrti_s11.dump log=import_rti$owner_LISD12.log Fromuser=ADMRTI Touser=RTI$OWNER ignore=y
Voici le log d'import obtenu:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via direct path
Warning: the objects were exported by ADMRTI, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table "ADM_IMPORT" 2 rows imported
. . importing table "CONTACT" 5 rows imported
. . importing table "CO_SG_BDR" 11569 rows imported
. . importing table "CO_SG_RTI" 62 rows imported
. . importing table "DATABASECHANGELOG" 28 rows imported
. . importing table "DATABASECHANGELOGLOCK" 1 rows imported
. . importing table "LNK_CONTACT_3PTYBR" 0 rows imported
. . importing table "LNK_CO_EXTCODE" 58 rows imported
. . importing table "LST_CONTACT_DEPARTMENT" 43 rows imported
. . importing table "LST_CONTACT_FUNCTION" 42 rows imported
. . importing table "LST_CONTACT_LEVEL" 4 rows imported
. . importing table "LST_INVESTOR_CATEGORY" 13 rows imported
. . importing table "LST_TITLE" 3 rows imported
. . importing table "RTI_3PTY_EXT_CODE" 5239 rows imported
. . importing table "RTI_BDR_3PTY" 92 rows imported
. . importing table "RTI_LNK_3PTY_BUSINESS_ROLE" 1007 rows imported
. . importing table "RTI_LYX_3PTY" 2474 rows imported
. . importing table "RTI_REF_A_CLI_TYPE" 5 rows imported
. . importing table "RTI_REF_BUSINESS_ROLE" 18 rows imported
. . importing table "RTI_REF_CATEGORY" 3 rows imported
. . importing table "RTI_REF_CLI_TYPE" 68 rows imported
. . importing table "RTI_REF_EXT_CODE" 11 rows imported
. . importing table "RTI_REF_PRIMARY_ROLE" 9 rows imported
. . importing table "RTI_REF_SENSIBILITY" 6 rows imported
. . importing table "RTI_SDD_3PTY" 333605 rows imported
. . importing table "RTI_TMP_SDD_3PTY_ADR" 17 rows imported
. . importing table "TLS_AUDIT"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "LATEST": invalid identifier
IMP-00017: following statement failed with ORACLE error 904:
"CREATE INDEX "IDX_TLS_AUDIT_CL_LA_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME" ,"
" "LATEST" , "PK" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5200"
"93696 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRT"
"I_L01" LOGGING"
IMP-00003: ORACLE error 904 encountered
ORA-00904: "PK": invalid identifier
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_PK"',NULL,NULL"
",NULL,5679533,59956,429434,1,2,956931,3,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_PK" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 4455
ORA-06512: at "SYS.DBMS_STATS", line 9464
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 904:
"CREATE INDEX "IDX_TLS_AUDIT_CL_LA_OT_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME"
"" , "LATEST" , "OTHER_PK" ) PCTFREE 10 INITRANS 2 MAXTRANS 155 STORAGE(INI"
"TIAL 562036736 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPAC"
"E "INDXRTI_L01" LOGGING"
IMP-00003: ORACLE error 904 encountered
ORA-00904: "OTHER_PK": invalid identifier
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_OT_PK"',NULL,N"
"ULL,NULL,5558841,61214,5481,3825,16419,262719,3,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_OT_PK" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 4455
ORA-06512: at "SYS.DBMS_STATS", line 9464
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 904:
"CREATE INDEX "IDX_TLS_AUDIT_ID" ON "TLS_AUDIT" ("AUDIT_ID" ) PCTFREE 10 IN"
"ITRANS 2 MAXTRANS 255 STORAGE(INITIAL 173015040 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRTI_L01" LOGGING"
IMP-00003: ORACLE error 904 encountered
ORA-00904: "AUDIT_ID": invalid identifier
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_ID"',NULL,NULL,NULL,"
"5505116,20231,5486471,1,1,394696,2,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_ID" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 4455
ORA-06512: at "SYS.DBMS_STATS", line 9464
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C31D374E'; SREC.MAX"
"VAL := 'C40A0C5D11'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
"MS_STATS.NUMARRAY(285477,9119216); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);"
" SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"AUDIT_ID"'"
", NULL ,NULL,NULL,5486471,.000000182266524328662,0,srec,6,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column AUDIT_ID: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '786C0B14131C0F'; SR"
"EC.MAXVAL := '786F0B0412301B'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NO"
"VALS := DBMS_STATS.NUMARRAY(2454791.76891204,2455870.74127315); SREC.BKVALS"
" := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NU"
"LL,'"TLS_AUDIT"','"DATETIME"', NULL ,NULL,NULL,40852,.0000244786056986194,0"
",srec,8,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column DATETIME: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '494E53455254'; SREC"
".MAXVAL := '555044415445'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS"
" := DBMS_STATS.NUMARRAY(380626296013610000000000000000000000,44297323347720"
"5000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC "
":= 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"TRIGGER_EVENT"', NUL"
"L ,NULL,NULL,2,.5,0,srec,7,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column TRIGGER_EVENT: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '424541312D303030363"
"436354641303143453531444138423828343938313229'; SREC.MAXVAL := '424541312D3"
"74646383230443939383342343336414133304328323138343430'; SREC.EAVS := 0; SRE"
"C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(3440962439759090000000"
"00000000000000,344096243975942000000000000000000000); SREC.BKVALS := DBMS_S"
"TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
"UDIT"','"TX_ID"', NULL ,NULL,NULL,355868,.00000281003068553509,8537,srec,36"
",6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column TX_ID: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3C616E6F6E796D6F757"
"33E'; SREC.MAXVAL := '6E617468616E61656C2E697469632D73696D6F6E69'; SREC.EAV"
"S := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(31351395482"
"7912000000000000000000000,573129270941465000000000000000000000); SREC.BKVAL"
"S := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(N"
"ULL,'"TLS_AUDIT"','"USER_ID"', NULL ,NULL,NULL,12,.0833333333333333,0,srec,"
"10,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column USER_ID: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '636F6D2E6C79786F722"
"E7274692E64616F2E656E746974792E436F5367426472'; SREC.MAXVAL := '636F6D2E6C7"
"9786F722E7274692E64616F2E656E746974792E52746953646433'; SREC.EAVS := 0; SRE"
"C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5162973866981150000000"
"00000000000000,516297386698115000000000000000000000); SREC.BKVALS := DBMS_S"
"TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
"UDIT"','"ENTITY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,0,srec,37,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column ENTITY_CLASS_NAME: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '80'; SREC.MAXVAL :="
" 'C102'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUM"
"ARRAY(0,1); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_ST"
"ATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"IS_LONG_PK"', NULL ,NULL,NULL,2,."
"5,0,srec,3,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column IS_LONG_PK: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C20431'; SREC.MAXVA"
"L := 'C422054140'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS"
"_STATS.NUMARRAY(348,33046463); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SRE"
"C.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"PK"', NULL ,NU"
"LL,NULL,274069,.00000364871619920531,6214,srec,6,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column PK: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '286C797849643A312C6"
"272436F64653A3129'; SREC.MAXVAL := '636F6D2E6C79786F722E7274692E64616F2E656"
"E746974792E4C6E6B436F4578'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVAL"
"S := DBMS_STATS.NUMARRAY(209891998413178000000000000000000000,5162973866981"
"15000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC"
" := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"OTHER_PK"', NULL ,N"
"ULL,NULL,5481,.000182448458310527,5490846,srec,2,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column OTHER_PK: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '626472416374697665'"
"; SREC.MAXVAL := '727469427573696E657373526F6C6573'; SREC.EAVS := 0; SREC.C"
"HVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5108823853440640000000000"
"00000000000,594282940912058000000000000000000000); SREC.BKVALS := DBMS_STAT"
"S.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDI"
"T"','"PROPERTY_NAME"', NULL ,NULL,NULL,27,.037037037037037,0,srec,17,6); EN"
"D;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column PROPERTY_NAME: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 8837
ORA-06512: at "SYS.DBMS_STATS", line 9103
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20000:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '6A6176612E6C616E672"
"E4C6F6E67'; SREC.MAXVAL := '6A6176612E7574696C2E536574'; SREC.EAVS := 0; SR"
"EC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(552360239735591000000"
"000000000000000,552360239735634000000000000000000000); SREC.BKVALS := DBMS_"
"STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_"
"AUDIT"','"PROPERTY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,12066,srec,17,6); EN"
"D;"
[...]
. . importing table "WLSTORE" 36 rows imported
About to enable constraints...
Import terminated successfully with warnings.
On constate que toutes les tables ont été importées sauf la table TLS_AUDIT et ses objets associés (index, contraintes etc.)
Aucun commentaire:
Enregistrer un commentaire