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

相关文章

如何使用Docker部署FTP和Nginx并通过HTTP访问FTP里的文件

如何使用Docker部署FTP和Nginx并通过HTTP访问FTP里的文件

《如何使用Docker部署FTP和Nginx并通过HTTP访问FTP里的文件》本文介绍了如何使用Docker部署FTP服务器和Nginx,并通过HTTP访问FTP中的文件,通过将FTP数据目录挂载到N... 目录docker部署FTP和Nginx并通过HTTP访问FTP里的文件1. 部署 FTP 服务器 (
阅读更多...
MySQL 日期时间格式化函数 DATE_FORMAT() 的使用示例详解

MySQL 日期时间格式化函数 DATE_FORMAT() 的使用示例详解

《MySQL日期时间格式化函数DATE_FORMAT()的使用示例详解》`DATE_FORMAT()`是MySQL中用于格式化日期时间的函数,本文详细介绍了其语法、格式化字符串的含义以及常见日期... 目录一、DATE_FORMAT()语法二、格式化字符串详解三、常见日期时间格式组合四、业务场景五、总结一、
阅读更多...
浅析Python中的绝对导入与相对导入

浅析Python中的绝对导入与相对导入

《浅析Python中的绝对导入与相对导入》这篇文章主要为大家详细介绍了Python中的绝对导入与相对导入的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1 Imports快速介绍2 import语句的语法2.1 基本使用2.2 导入声明的样式3 绝对import和相对i
阅读更多...
Python中配置文件的全面解析与使用

Python中配置文件的全面解析与使用

《Python中配置文件的全面解析与使用》在Python开发中,配置文件扮演着举足轻重的角色,它们允许开发者在不修改代码的情况下调整应用程序的行为,下面我们就来看看常见Python配置文件格式的使用吧... 目录一、INI配置文件二、YAML配置文件三、jsON配置文件四、TOML配置文件五、XML配置文件
阅读更多...
Go使用pprof进行CPU,内存和阻塞情况分析

Go使用pprof进行CPU,内存和阻塞情况分析

《Go使用pprof进行CPU,内存和阻塞情况分析》Go语言提供了强大的pprof工具,用于分析CPU、内存、Goroutine阻塞等性能问题,帮助开发者优化程序,提高运行效率,下面我们就来深入了解下... 目录1. pprof 介绍2. 快速上手：启用 pprof3. CPU Profiling：分析 C
阅读更多...
MySQL InnoDB引擎ibdata文件损坏/删除后使用frm和ibd文件恢复数据

MySQL InnoDB引擎ibdata文件损坏/删除后使用frm和ibd文件恢复数据

《MySQLInnoDB引擎ibdata文件损坏/删除后使用frm和ibd文件恢复数据》mysql的ibdata文件被误删、被恶意修改,没有从库和备份数据的情况下的数据恢复,不能保证数据库所有表数据... 参考：mysql Innodb表空间卸载、迁移、装载的使用方法注意！此方法只适用于innodb_fi
阅读更多...
Python中conda虚拟环境创建及使用小结

Python中conda虚拟环境创建及使用小结

《Python中conda虚拟环境创建及使用小结》本文主要介绍了Python中conda虚拟环境创建及使用小结,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们... 目录0.前言1.Miniconda安装2.conda本地基本操作3.创建conda虚拟环境4.激活c
阅读更多...
Spring中@Lazy注解的使用技巧与实例解析

Spring中@Lazy注解的使用技巧与实例解析

《Spring中@Lazy注解的使用技巧与实例解析》@Lazy注解在Spring框架中用于延迟Bean的初始化,优化应用启动性能,它不仅适用于@Bean和@Component,还可以用于注入点,通过将... 目录一、@Lazy注解的作用（一）延迟Bean的初始化（二）与@Autowired结合使用二、实例解
阅读更多...
SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)

SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)

《SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)》本文介绍了如何在SpringBoot项目中使用Jasypt对application.yml文件中的敏感信息（如数... 目录SpringBoot使用Jasypt对YML文件配置内容进行加密（例：数据库密码加密）前言一、J
阅读更多...
MySQL进阶之路索引失效的11种情况详析

MySQL进阶之路索引失效的11种情况详析

《MySQL进阶之路索引失效的11种情况详析》：本文主要介绍MySQL查询优化中的11种常见情况,包括索引的使用和优化策略,通过这些策略,开发者可以显著提升查询性能,需要的朋友可以参考下... 目录前言图示1. 使用不等式操作符（!=, <, >）2. 使用 OR 连接多个条件3. 对索引字段进行计算操作4
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2