There seems to be some confusion with the “EXCLUDE” clause in expdp.The confusion is,when a schema level expdp is taken with few table names(one or more) in the exclude parameter and imported, these excluded tables will be created (metadata only) but won’t have data.It means even if we exclude the tables while export, its metadata is present in the dumpfile, which is “WRONG”. Here’s a test to proof its WRONG !!!!!!
As per Oracle Document :-
EXCLUDE
Default: none
Purpose
Enables you to filter the metadata that is exported by specifying objects and object types that you want excluded from the export operation.
I have a schema “ANAND” with 4 tables.I will take a schema level export, excluding 1 table, drop the schema “ANAND” and import the dump.
Session 1 –
10:29:49 SYS @ BRAVE >@users USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TBS PROFILE ------------------------- -------------------------------- ------------------------------ --------------- -------- ANAND OPEN ANDY TEMP DEFAULT 10:29:52 SYS @ BRAVE >@schema_table_size Enter value for owner_name: anand OWNER SEGMENT_NAME SEGMENT_TYPE MB ------------------------------ ---------------------------------------- ------------------ ---------- ANAND LOCK_TEST TABLE .125 ANAND ORDERS TABLE .0625 ANAND PROCESS_STATE_BKP TABLE .0625 ANAND PROCESS_STATE TABLE .0625
Session 2 –
C:\Documents and Settings\user>expdp directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test.log schemas=anand exclude=TABLE:\"IN(\'PROCESS_STATE\')\" job_name=anand_expdp Export: Release 10.2.0.4.0 - Production on Friday, 02 April, 2010 10:42:38 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."ANAND_EXPDP": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test.log schemas=anand exclude=TABLE:"IN(\'PROCESS_STATE\')" job_name=anand_expdp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ANAND"."LOCK_TEST" 51.25 KB 500 rows . . exported "ANAND"."ORDERS" 5.601 KB 4 rows . . exported "ANAND"."PROCESS_STATE_BKP" 5.593 KB 4 rows Master table "SYS"."ANAND_EXPDP" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.ANAND_EXPDP is: D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORACLE\DPDUMP\ANAND_TEST.DMP Job "SYS"."ANAND_EXPDP" successfully completed at 10:43:44
Session 1 –
10:43:53 SYS @ BRAVE >drop user anand cascade; User dropped.
Session 2 –
C:\Documents and Settings\user>impdp directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test_imp.log full=y job_name=anand_impdp Import: Release 10.2.0.4.0 - Production on Friday, 02 April, 2010 10:45:17 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."ANAND_IMPDP" successfully loaded/unloaded Starting "SYS"."ANAND_IMPDP": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test_imp.log full=y job_name=anand_impdp Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "ANAND"."LOCK_TEST" 51.25 KB 500 rows . . imported "ANAND"."ORDERS" 5.601 KB 4 rows . . imported "ANAND"."PROCESS_STATE_BKP" 5.593 KB 4 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."ANAND_IMPDP" successfully completed at 10:45:35
Session 1 –
10:46:22 SYS @ BRAVE >@schema_table_size Enter value for owner_name: anand OWNER SEGMENT_NAME SEGMENT_TYPE MB ------------------------------ ---------------------------------------- ------------------ ---------- ANAND LOCK_TEST TABLE .125 ANAND PROCESS_STATE_BKP TABLE .0625 ANAND ORDERS TABLE .0625
The table “PROCESS_STATE” is not created after importing the dump.So, the exclude parameter filtered the “metadata” in the expdp.
The sqlfile output :-
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "ANAND"."LOCK_TEST"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
CREATE TABLE "ANAND"."ORDERS"
( "ORDER_ID" NUMBER,
"ORDER_DT" DATE,
"CUST_ID" NUMBER NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
CREATE TABLE "ANAND"."PROCESS_STATE_BKP"
( "JOB_ID" NUMBER,
"PROCESS_ID" NUMBER,
"PROCESS_CODE" VARCHAR2(3)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT ANAND
CREATE INDEX "ANAND"."LOCK_TEST_IDX" ON "ANAND"."LOCK_TEST" ("OBJECT_NAME")
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)
TABLESPACE "ANDY" PARALLEL 1 ;
ALTER INDEX "ANAND"."LOCK_TEST_IDX" NOPARALLEL;