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

相关文章

vue使用docxtemplater导出word

vue使用docxtemplater导出word

《vue使用docxtemplater导出word》docxtemplater是一种邮件合并工具,以编程方式使用并处理条件、循环,并且可以扩展以插入任何内容,下面我们来看看如何使用docxtempl... 目录docxtemplatervue使用docxtemplater导出word安装常用语法 封装导出方
阅读更多...
Linux换行符的使用方法详解

Linux换行符的使用方法详解

《Linux换行符的使用方法详解》本文介绍了Linux中常用的换行符LF及其在文件中的表示,展示了如何使用sed命令替换换行符,并列举了与换行符处理相关的Linux命令,通过代码讲解的非常详细,需要的... 目录简介检测文件中的换行符使用 cat -A 查看换行符使用 od -c 检查字符换行符格式转换将
阅读更多...
使用Jackson进行JSON生成与解析的新手指南

使用Jackson进行JSON生成与解析的新手指南

《使用Jackson进行JSON生成与解析的新手指南》这篇文章主要为大家详细介绍了如何使用Jackson进行JSON生成与解析处理,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. 核心依赖2. 基础用法2.1 对象转 jsON（序列化）2.2 JSON 转对象（反序列化）3.
阅读更多...
使用Python实现快速搭建本地HTTP服务器

使用Python实现快速搭建本地HTTP服务器

《使用Python实现快速搭建本地HTTP服务器》：本文主要介绍如何使用Python快速搭建本地HTTP服务器,轻松实现一键HTTP文件共享,同时结合二维码技术,让访问更简单,感兴趣的小伙伴可以了... 目录1. 概述2. 快速搭建 HTTP 文件共享服务2.1 核心思路2.2 代码实现2.3 代码解读3.
阅读更多...
Elasticsearch 在 Java 中的使用教程

Elasticsearch 在 Java 中的使用教程

《Elasticsearch在Java中的使用教程》Elasticsearch是一个分布式搜索和分析引擎,基于ApacheLucene构建,能够实现实时数据的存储、搜索、和分析,它广泛应用于全文... 目录1. Elasticsearch 简介2. 环境准备2.1 安装 Elasticsearch2.2 J
阅读更多...
使用C#代码在PDF文档中添加、删除和替换图片

使用C#代码在PDF文档中添加、删除和替换图片

《使用C#代码在PDF文档中添加、删除和替换图片》在当今数字化文档处理场景中,动态操作PDF文档中的图像已成为企业级应用开发的核心需求之一,本文将介绍如何在.NET平台使用C#代码在PDF文档中添加、... 目录引言用C#添加图片到PDF文档用C#删除PDF文档中的图片用C#替换PDF文档中的图片引言在当
阅读更多...
Java中List的contains()方法的使用小结

Java中List的contains()方法的使用小结

《Java中List的contains()方法的使用小结》List的contains()方法用于检查列表中是否包含指定的元素,借助equals()方法进行判断,下面就来介绍Java中List的c... 目录详细展开1. 方法签名2. 工作原理3. 使用示例4. 注意事项总结结论：List 的 contain
阅读更多...
C#使用SQLite进行大数据量高效处理的代码示例

C#使用SQLite进行大数据量高效处理的代码示例

《C#使用SQLite进行大数据量高效处理的代码示例》在软件开发中,高效处理大数据量是一个常见且具有挑战性的任务,SQLite因其零配置、嵌入式、跨平台的特性,成为许多开发者的首选数据库,本文将深入探... 目录前言准备工作数据实体核心技术批量插入：从乌龟到猎豹的蜕变分页查询：加载百万数据异步处理：拒绝界面
阅读更多...
Android中Dialog的使用详解

Android中Dialog的使用详解

《Android中Dialog的使用详解》Dialog（对话框）是Android中常用的UI组件,用于临时显示重要信息或获取用户输入,本文给大家介绍Android中Dialog的使用,感兴趣的朋友一起... 目录android中Dialog的使用详解1. 基本Dialog类型1.1 AlertDialog（
阅读更多...
Python使用自带的base64库进行base64编码和解码

Python使用自带的base64库进行base64编码和解码

《Python使用自带的base64库进行base64编码和解码》在Python中,处理数据的编码和解码是数据传输和存储中非常普遍的需求,其中,Base64是一种常用的编码方案,本文我将详细介绍如何使... 目录引言使用python的base64库进行编码和解码编码函数解码函数Base64编码的应用场景注意
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2