Re: Paralelize CONSTRAINT VALIDATION during impdp

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 7 Aug 2013 16:07:48 +0000 (UTC)
Message-ID: <pan.2013.08.07.16.07.48_at_gmail.com>

On Mon, 05 Aug 2013 22:56:10 +0200, noreply wrote:

> I do not have the exact numbers but what is sure is that the data
> loading is just the quickest step and takes less than 1h30. The IMPDP
> total time is more than 7 hours !
>
> Thank you for your suggestion.

You can export constraints only like this:

expdp userid=system schemas=SCOTT include=CONSTRAINT content=metadata_only dumpfile=constraints.dmp directory=TMP

Export: Release 11.2.0.3.0 - Production on Wed Aug 7 11:54:23 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  userid=system/******** schemas=SCOTT include=CONSTRAINT content=metadata_only dumpfile=constraints.dmp directory=TMP
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

---

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:   /tmp/constraints.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:54:37

Once you have the constraints exported, you can do something like this:

oracle_at_oradb tmp]$ impdp userid=system schemas=SCOTT dumpfile=constraints.dmp directory=TMP sqlfile=scott.sql

Import: Release 11.2.0.3.0 - Production on Wed Aug 7 11:59:07 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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": userid=system/******** schemas=SCOTT dumpfile=constraints.dmp directory=TMP sqlfile=scott.sql Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

The file itself looks like this:

• 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/CONSTRAINT/CONSTRAINT ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE;
• new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

When you have that, you can use the universal hammer to get rid of the validation step:

perl -i.bak -pe 's/ENABLE;/ENABLE NOVALIDATE;/;' scott.sql

and happily run the file thereafter. That's how it's done.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Wed Aug 07 2013 - 18:07:48 CEST