关于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。

相关文章

springboot整合 xxl-job及使用步骤

springboot整合 xxl-job及使用步骤

《springboot整合xxl-job及使用步骤》XXL-JOB是一个分布式任务调度平台,用于解决分布式系统中的任务调度和管理问题,文章详细介绍了XXL-JOB的架构,包括调度中心、执行器和Web... 目录一、xxl-job是什么二、使用步骤1. 下载并运行管理端代码2. 访问管理页面，确认是否启动成功
阅读更多...
使用Nginx来共享文件的详细教程

使用Nginx来共享文件的详细教程

《使用Nginx来共享文件的详细教程》有时我们想共享电脑上的某些文件,一个比较方便的做法是,开一个HTTP服务,指向文件所在的目录,这次我们用nginx来实现这个需求,本文将通过代码示例一步步教你使用... 在本教程中，我们将向您展示如何使用开源 Web 服务器 Nginx 设置文件共享服务器步骤 0 —
阅读更多...
Java中switch-case结构的使用方法举例详解

Java中switch-case结构的使用方法举例详解

《Java中switch-case结构的使用方法举例详解》：本文主要介绍Java中switch-case结构使用的相关资料,switch-case结构是Java中处理多个分支条件的一种有效方式,它... 目录前言一、switch-case结构的基本语法二、使用示例三、注意事项四、总结前言对于Java初学者
阅读更多...
Golang使用minio替代文件系统的实战教程

Golang使用minio替代文件系统的实战教程

《Golang使用minio替代文件系统的实战教程》本文讨论项目开发中直接文件系统的限制或不足,接着介绍Minio对象存储的优势,同时给出Golang的实际示例代码,包括初始化客户端、读取minio对... 目录文件系统 vs Minio文件系统不足：对象存储：miniogolang连接Minio配置Min
阅读更多...
使用Python绘制可爱的招财猫

使用Python绘制可爱的招财猫

《使用Python绘制可爱的招财猫》招财猫,也被称为“幸运猫”,是一种象征财富和好运的吉祥物,经常出现在亚洲文化的商店、餐厅和家庭中,今天,我将带你用Python和matplotlib库从零开始绘制一... 目录1. 为什么选择用 python 绘制？2. 绘图的基本概念3. 实现代码解析3.1 设置绘图画
阅读更多...
使用Python实现大文件切片上传及断点续传的方法

使用Python实现大文件切片上传及断点续传的方法

《使用Python实现大文件切片上传及断点续传的方法》本文介绍了使用Python实现大文件切片上传及断点续传的方法,包括功能模块划分（获取上传文件接口状态、临时文件夹状态信息、切片上传、切片合并）、整... 目录概要整体架构流程技术细节获取上传文件状态接口获取临时文件夹状态信息接口切片上传功能文件合并功能小
阅读更多...
Golang使用etcd构建分布式锁的示例分享

Golang使用etcd构建分布式锁的示例分享

《Golang使用etcd构建分布式锁的示例分享》在本教程中,我们将学习如何使用Go和etcd构建分布式锁系统,分布式锁系统对于管理对分布式系统中共享资源的并发访问至关重要,它有助于维护一致性,防止竞... 目录引言环境准备新建Go项目实现加锁和解锁功能测试分布式锁重构实现失败重试总结引言我们将使用Go作
阅读更多...
Linux使用nohup命令在后台运行脚本

Linux使用nohup命令在后台运行脚本

《Linux使用nohup命令在后台运行脚本》在Linux或类Unix系统中,后台运行脚本是一项非常实用的技能,尤其适用于需要长时间运行的任务或服务,本文我们来看看如何使用nohup命令在后台... 目录nohup 命令简介基本用法输出重定向& 符号的作用后台进程的特点注意事项实际应用场景长时间运行的任务服
阅读更多...
Python3中Sanic中间件的使用

Python3中Sanic中间件的使用

《Python3中Sanic中间件的使用》Sanic框架中的中间件是一种强大的工具,本文就来介绍Python3中Sanic中间件的使用,具有一定的参考价值,感兴趣的可以了解一下... 目录Sanic 中间件的工作流程中间件的使用1. 全局中间件2. 路由中间件3. 异常处理中间件4. 异步中间件5. 优先级
阅读更多...
SpringBoot使用注解集成Redis缓存的示例代码

SpringBoot使用注解集成Redis缓存的示例代码

《SpringBoot使用注解集成Redis缓存的示例代码》：本文主要介绍在SpringBoot中使用注解集成Redis缓存的步骤,包括添加依赖、创建相关配置类、需要缓存数据的类（Tes... 目录一、创建 Caching 配置类二、创建需要缓存数据的类三、测试方法Spring Boot 熟悉后，集成一个外
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2