关于impdp导入时候索引是否使用了并行了？

2023-12-14 18:20
文章标签 使用 是否 索引 导入 并行 impdp

本文主要是介绍关于impdp导入时候索引是否使用了并行了？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

关于impdp导入时候索引是否使用了并行的问题，不是看sqlfile，而是看实际worker

参看：Impdp Parallel Index Creation Always Creates Indexes with Degree 1 (Doc ID 1289032.1）

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GOAL
Indexes seem always created with parallel degree 1 during import as seen from a sqlfile.The sql file shows content like:CREATE INDEX "<SCHEMA_NAME>"."<INDEX_NAME>" ON
"<SCHEMA_NAME>"."<TABLE_NAME>" ("<COLUMN_NAME>")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "<TABLESPCE_NAME>"
PARALLEL 1 ;ALTER INDEX "<SCHEMA_NAME>"."<INDEX_NAME>" PARALLEL 24;The database is version 11.2.0.2 or above where Bug 8604502 has been fixed.SOLUTION
The issue is discussed in
Bug 10408313 - INDEXES ARE CREATED WITH PARALLEL DEGREE 1 DURING IMPORT
closed with status 'Not a Bug'.The import job with SQLFILE parameter option cannot use multiple execution streams.
It always executed with parallel 1. Hence its showing the PARALLEL 1 in generated sqlfile and this is an expected behavior.When you run the import with parallel value and without sqlfile option, then you can see the actual index creation statement with correct parallel value in DW(worker) traces.

测试：

[oracle@lncs dmp]$ sqlplus jyc/jycSQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 14 13:56:58 2023Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> desc t;Name                                      Null?    Type----------------------------------------- -------- ----------------------------ID                                        NOT NULL NUMBER(38)NAME                                               VARCHAR2(20)SQL> create index idx on t(name);Index created.SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lncs dmp]$ expdp system/jyc directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc  content=metadata_only PARALLEL=1 CLUSTER=N include=table,indexExport: Release 11.2.0.4.0 - Production on Thu Dec 14 13:58:08 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc content=metadata_only PARALLEL=1 CLUSTER=N include=table,index 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:/oracle/dmp/jyc1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 14 13:58:12 2023 elapsed 0 00:00:03[oracle@lncs dmp]$ impdp system/jyc directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc  content=metadata_only PARALLEL=7 CLUSTER=N include=table,index sqlfile=jyc1.sqlImport: Release 11.2.0.4.0 - Production on Thu Dec 14 13:58:37 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc content=metadata_only PARALLEL=7 CLUSTER=N include=table,index sqlfile=jyc1.sql 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Thu Dec 14 13:58:40 2023 elapsed 0 00:00:01[oracle@lncs dmp]$ more jyc1.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "JYC"."T" (    "ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
CREATE TABLE "JYC"."TAB1" (    "ID" NUMBER, "C1" VARCHAR2(16 BYTE), "C2" NVARCHAR2(16)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT JYC
CREATE UNIQUE INDEX "JYC"."PK_T" ON "JYC"."T" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;
CREATE INDEX "JYC"."IDX" ON "JYC"."T" ("NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYSTEM
ALTER TABLE "JYC"."T" ADD CONSTRAINT "PK_T" PRIMARY KEY ("ID")USING INDEX "JYC"."PK_T"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_T'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,4,0,10,NV,NV,TO_DATE('2023-12-08 14:51:05',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'IDX'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,6,0,10,NV,NV,TO_DATE('2023-12-14 13:57:23',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE c varchar2(60); nv varchar2(1); df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; s varchar2(60) := 'JYC'; t varchar2(60) := 'T'; p varchar2(1); sp varchar2(1); stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,10,5,7,10,0,NULL,NULL,NULL,TO_DATE('2023-12-08 14:51:05',df));c := 'ID'; EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,10,.1,10,10,0,1,10,3,0,nv,nv,TO_DATE('2023-12-08 14:51:05',df),'C102','C10B',nv,2,nv;c := 'NAME'; EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,10,.1,10,10,0,3.27114702087694E+35,1.32206830519483E+36,4,0,nv,nv,TO_DATE('2023-12-08 14:51:05',df),'3F','FE9F',nv,2,nv;DBMS_STATS.IMPORT_TABLE_STATS('"JYC"','"T"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/BEGINDBMS_STATS.LOCK_TABLE_STATS('"JYC"','"T"','ALL'); 
END; 
/[oracle@lncs dmp]$ more jyc1.sql|grep PARALLELTABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
[oracle@lncs dmp]$ impdp system/jyc directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc  content=metadata_only PARALLEL=12 CLUSTER=N  sqlfile=jyc2.sqlImport: Release 11.2.0.4.0 - Production on Thu Dec 14 13:59:44 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc content=metadata_only PARALLEL=12 CLUSTER=N sqlfile=jyc2.sql 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Thu Dec 14 13:59:47 2023 elapsed 0 00:00:01[oracle@lncs dmp]$ more jyc2.sql|grep PARALLELTABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
[oracle@lncs dmp]$ more jyc2.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "JYC"."T" (    "ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
CREATE TABLE "JYC"."TAB1" (    "ID" NUMBER, "C1" VARCHAR2(16 BYTE), "C2" NVARCHAR2(16)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT JYC
CREATE UNIQUE INDEX "JYC"."PK_T" ON "JYC"."T" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;
CREATE INDEX "JYC"."IDX" ON "JYC"."T" ("NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYSTEM
ALTER TABLE "JYC"."T" ADD CONSTRAINT "PK_T" PRIMARY KEY ("ID")USING INDEX "JYC"."PK_T"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_T'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,4,0,10,NV,NV,TO_DATE('2023-12-08 14:51:05',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'IDX'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,6,0,10,NV,NV,TO_DATE('2023-12-14 13:57:23',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE c varchar2(60); nv varchar2(1); df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; s varchar2(60) := 'JYC'; t varchar2(60) := 'T'; p varchar2(1); sp varchar2(1); stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,10,5,7,10,0,NULL,NULL,NULL,TO_DATE('2023-12-08 14:51:05',df));c := 'ID'; EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
[oracle@lncs dmp]$ expdp system/jyc directory=ORADMP dumpfile=jyc1.dmp logfile=d.log schemas=jyc  PARALLEL=3 CLUSTER=N include=table,indexExport: Release 11.2.0.4.0 - Production on Thu Dec 14 14:01:48 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/oracle/dmp/jyc1.dmp"
ORA-27038: created file already exists
Additional information: 1[oracle@lncs dmp]$ expdp system/jyc directory=ORADMP dumpfile=jyc3.dmp logfile=d.log schemas=jyc  PARALLEL=3 CLUSTER=N include=table,indexExport: Release 11.2.0.4.0 - Production on Thu Dec 14 14:01:57 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=ORADMP dumpfile=jyc3.dmp logfile=d.log schemas=jyc PARALLEL=3 CLUSTER=N include=table,index 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
. . exported "JYC"."T"                                   5.515 KB      10 rows
. . exported "JYC"."TAB1"                                5.812 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:/oracle/dmp/jyc3.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 14 14:02:05 2023 elapsed 0 00:00:07[oracle@lncs dmp]$ impdp system/jyc directory=ORADMP dumpfile=jyc3.dmp logfile=d.log schemas=jyc PARALLEL=12 CLUSTER=N  sqlfile=jyc3.sqlImport: Release 11.2.0.4.0 - Production on Thu Dec 14 14:02:32 2023Copyright (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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** directory=ORADMP dumpfile=jyc3.dmp logfile=d.log schemas=jyc PARALLEL=12 CLUSTER=N sqlfile=jyc3.sql 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Thu Dec 14 14:02:34 2023 elapsed 0 00:00:01[oracle@lncs dmp]$ more jyc3.sql|grep PARALLELTABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
[oracle@lncs dmp]$ more jyc3.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "JYC"."TAB1" (    "ID" NUMBER, "C1" VARCHAR2(16 BYTE), "C2" NVARCHAR2(16)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
CREATE TABLE "JYC"."T" (    "ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT JYC
CREATE UNIQUE INDEX "JYC"."PK_T" ON "JYC"."T" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."PK_T" NOPARALLEL;
CREATE INDEX "JYC"."IDX" ON "JYC"."T" ("NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" PARALLEL 1 ;ALTER INDEX "JYC"."IDX" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYSTEM
ALTER TABLE "JYC"."T" ADD CONSTRAINT "PK_T" PRIMARY KEY ("ID")USING INDEX "JYC"."PK_T"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_T'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,4,0,10,NV,NV,TO_DATE('2023-12-08 14:51:05',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:
11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'IDX'; i_o := 'JYC'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,10,1,10,1,1,6,0,10,NV,NV,TO_DATE('2023-12-14 13:57:23',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE c varchar2(60); nv varchar2(1); df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; s varchar2(60) := 'JYC'; t varchar2(60) := 'T'; p varchar2(1); sp varchar2(1); stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGINDELETE FROM "SYS"."IMPDP_STATS"; INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,10,5,7,10,0,NULL,NULL,NULL,TO_DATE('2023-12-08 14:51:05',df));c := 'ID'; EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,10,.1,10,10,0,1,10,3,0,nv,nv,TO_DATE('2023-12-08 14:51:05',df),'C102','C10B',nv,2,nv;c := 'NAME'; EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,10,.1,10,10,0,3.27114702087694E+35,1.32206830519483E+36,4,0,nv,nv,TO_DATE('2023-12-08 14:51:05',df),'3F','FE9F',nv,2,nv;DBMS_STATS.IMPORT_TABLE_STATS('"JYC"','"T"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/

如果一开始没有导入索引，约束，那么可以根据sqlfile脚本修改并行处理。

可参考：

impdp 导入约束或索引时，并行执行 - 墨天轮

这篇关于关于impdp导入时候索引是否使用了并行了？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/493548。

相关文章

使用Python和Pyecharts创建交互式地图

使用Python和Pyecharts创建交互式地图

《使用Python和Pyecharts创建交互式地图》在数据可视化领域,创建交互式地图是一种强大的方式,可以使受众能够以引人入胜且信息丰富的方式探索地理数据,下面我们看看如何使用Python和Pyec... 目录简介Pyecharts 简介创建上海地图代码说明运行结果总结简介在数据可视化领域，创建交互式地
阅读更多...
Java Stream流使用案例深入详解

Java Stream流使用案例深入详解

《JavaStream流使用案例深入详解》：本文主要介绍JavaStream流使用案例详解,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录前言1. Lambda1.1 语法1.2 没参数只有一条语句或者多条语句1.3 一个参数只有一条语句或者多
阅读更多...
Java Spring 中 @PostConstruct 注解使用原理及常见场景

Java Spring 中 @PostConstruct 注解使用原理及常见场景

《JavaSpring中@PostConstruct注解使用原理及常见场景》在JavaSpring中,@PostConstruct注解是一个非常实用的功能,它允许开发者在Spring容器完全初... 目录一、@PostConstruct 注解概述二、@PostConstruct 注解的基本使用2.1 基本代
阅读更多...
C#使用StackExchange.Redis实现分布式锁的两种方式介绍

C#使用StackExchange.Redis实现分布式锁的两种方式介绍

《C#使用StackExchange.Redis实现分布式锁的两种方式介绍》分布式锁在集群的架构中发挥着重要的作用,：本文主要介绍C#使用StackExchange.Redis实现分布式锁的... 目录自定义分布式锁获取锁释放锁自动续期StackExchange.Redis分布式锁获取锁释放锁自动续期分布式
阅读更多...
springboot使用Scheduling实现动态增删启停定时任务教程

springboot使用Scheduling实现动态增删启停定时任务教程

《springboot使用Scheduling实现动态增删启停定时任务教程》：本文主要介绍springboot使用Scheduling实现动态增删启停定时任务教程,具有很好的参考价值,希望对大家有... 目录1、配置定时任务需要的线程池2、创建ScheduledFuture的包装类3、注册定时任务，增加、删
阅读更多...
使用Python实现矢量路径的压缩、解压与可视化

使用Python实现矢量路径的压缩、解压与可视化

《使用Python实现矢量路径的压缩、解压与可视化》在图形设计和Web开发中,矢量路径数据的高效存储与传输至关重要,本文将通过一个Python示例,展示如何将复杂的矢量路径命令序列压缩为JSON格式,... 目录引言核心功能概述1. 路径命令解析2. 路径数据压缩3. 路径数据解压4. 可视化代码实现详解1
阅读更多...
Pandas透视表(Pivot Table)的具体使用

Pandas透视表(Pivot Table)的具体使用

《Pandas透视表(PivotTable)的具体使用》透视表用于在数据分析和处理过程中进行数据重塑和汇总,本文就来介绍一下Pandas透视表(PivotTable)的具体使用,感兴趣的可以了解一下... 目录前言什么是透视表？使用步骤1. 引入必要的库2. 读取数据3. 创建透视表4. 查看透视表总结前言
阅读更多...
Python 交互式可视化的利器Bokeh的使用

Python 交互式可视化的利器Bokeh的使用

《Python交互式可视化的利器Bokeh的使用》Bokeh是一个专注于Web端交互式数据可视化的Python库,本文主要介绍了Python交互式可视化的利器Bokeh的使用,具有一定的参考价值,感... 目录1. Bokeh 简介1.1 为什么选择 Bokeh1.2 安装与环境配置2. Bokeh 基础2
阅读更多...
Android使用ImageView.ScaleType实现图片的缩放与裁剪功能

Android使用ImageView.ScaleType实现图片的缩放与裁剪功能

《Android使用ImageView.ScaleType实现图片的缩放与裁剪功能》ImageView是最常用的控件之一,它用于展示各种类型的图片,为了能够根据需求调整图片的显示效果,Android提... 目录什么是 ImageView.ScaleType？FIT_XYFIT_STARTFIT_CENTE
阅读更多...
Java学习手册之Filter和Listener使用方法

Java学习手册之Filter和Listener使用方法

《Java学习手册之Filter和Listener使用方法》：本文主要介绍Java学习手册之Filter和Listener使用方法的相关资料,Filter是一种拦截器,可以在请求到达Servl... 目录一、Filter（过滤器）1. Filter 的工作原理2. Filter 的配置与使用二、Listen
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2