EXPDP – exclude parameter

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s