DUMPFILE=dp:full%u.dmp LOGFILE=dp:full.log FULL=Y
PARALLEL=2
COMPRESSION=ALL EXCLUDE=SCHEMA:"IN
('OUTLN','SYSTEM','SYSMAN','FLOWS_FILES','APEX_030200','APEX_PUBLIC_USER','ANONYMOUS')"
EX – 10 EXCLUDE SPECIFIC SCHEMAS – WHEN IMPORTING
Source database : crms Target database : hrms
EXPORT SCHMEAS - HR, MAYA, ROSE, SCOTT, SHAM from source database.
EXCLUDE SCHEMAS – MAYA, SHAM in target database.
1) Find schema associated tablespaces in source database.
2) If those tablespaces are not existing in target database, recreate it.
4) Check user profiles and roles in source database.
5) Recreate those profiles and roles as per source database.
6) Start your import process.
GATHER ALL SCHEMA INFO IN SOURCE DATABSE
SQL> select * from dba_ts_quotas where username='USER_NAME';
SQL> select * from dba_ts_quotas where username='SCOTT';
SQL> select username, profile from dba_users where username='USER_NAME';
SQL> select username, profile from dba_users where username='SCOTT';
SQL> select * from dba_profiles where profile='PROFILE_NAME';
SQL> select * from dba_profiles where profile='P1';
SQL> select * from dba_role_privs where grantee='USER_NAME'; # SEE GRANTED_ROLE COLUMN SQL> select * from dba_role_privs where grantee='SCOTT';
SYS>select * from role_sys_privs where role='ROLE_NAME';
SYS>select * from role_sys_privs where role='R1';
After export, create sqlfile from the dumpfile, you can verify all SQL DDL statements.
Start export then import the dumpfile with EXCLUDE option.
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ scp schema.dmp [email protected]:/u03/datapump [email protected]'s password:******
$ impdp system/<passeord> dumpfile=dp:schema.dmp ... EXCLUDE=SCHEMA:"IN\('JHIL'\)"
$ impdp system/<password> dumpfile=dp:schema.dmp ... EXCLUDE=SCHEMA:"IN\('MAYA'\,'SHAM'\)"
I am using dumpfile which was taken originaly taken from CRMS database.
For some examples, EXCLUDE with IMPDP utility is tested in HRMS database.
EX – 11 : EXCLUDE SCHEMA OBJECTS – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony
EXCLUDE=INDEX,TRIGGER,FUNCTION EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
EX – 12 : EXCLUDE TABLE – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=TABLE EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
...
ORA-39082: Object type ALTER_PACKAGE_SPEC:"SONY"."PKGSAL" created with compilation warnings ORA-39082: Object type PACKAGE_BODY:"SONY"."PKGSAL" created with compilation warnings
...
When you load PL/SQL coding without the dependent tables, you will encounter compilation errors.
EX – 13 : EXCLUDE INDEX , GRANT – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=INDEX,GRANT EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
We cannot explicitly exclude Primary key and unique key associated indexes. By default, Oracle attempts to create a UNIQUE INDEX to police a PK/UK constraint. I.e. Oracle always creates UNIQUE INDEX when we create PRIMARY KEY on the table.
EX – 14 : EXCLUDE REF_CONSTRAINT – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=REF_CONSTRAINT EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
After import, you can verify using following SQL statement.
SQL> select constraint_name, constraint_type from user_constraints where constraint_type='R';
EXCLUDE=CONSTRAINT
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=CONSTRAINT EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
SQL> select constraint_name, constraint_type from user_constraints where constraint_type='C';
Not null constraint explicitly can’t be excluded. You can verify using above SQL command.
EX – 14 EXCLUDE TRIGGER(S) – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=TRIGGER:"IN\('TRI_EMP'\,'USRLOG'\)"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
$ impdp parfile=param12.ini
$ vi param12.ini
# EXCLUDE TRIGGER(S) USING LIKE & IN OPERATORS USERID=SYSTEM/MANAGER
DUMPFILE=dp:schema.dmp LOGFILE=dp:schema.log REMAP_SCHEMA=SHAM:SONY
EXCLUDE=TRIGGER:"LIKE 'TRI%'" # Using LIKE Operator EXCLUDE=TRIGGER:"IN\('USRLOG'\)" # Using IN Operator EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
$ vi param13.ini
# EXCLUDE TRIGGER(S) USERID=SYSTEM/MANAGER DUMPFILE=dp:schema.dmp LOGFILE=dp:schema.log REMAP_SCHEMA=SHAM:SONY
EXCLUDE=TRIGGER:"IN\('TRI_EMP','USRLOG','TRI_SAL')"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
EX – 14 EXCLUDE INDEX(S) USING LIKE OPERATOR – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=INDEX:\"LIKE\'INDX\%\'\"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
EX – 14 EXCLUDE SPECIFIC TABLE – WHEN IMPORTING SCHEMA
$ expdp system/<password> dumpfile=dpdir:schema.dmp ... schemas=sham,rose,maya,scott,hr
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=TABLE:"IN('TAB1')"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=TABLE:\"=\'TAB1\'\"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
EX – 15 EXCLUDE MORE THAN ONE TABLE – WHEN IMPORTING SCHEMA
$ impdp system/<password> dumpfile=dp:schema.dmp ... remap_schema=sham:sony EXCLUDE=TABLE:"IN('TAB1'\,'TAB2'\)"
EXCLUDE=SCHEMA:"IN\('MAYA'\,'SCOTT'\,'HR'\,'ROSE'\)"
EX – 15 : EXCLUDE SCOTT OBJECTS – WHEN IMPORTING SCHEMA
$ expdp scott/password dumpfile=scott.dmp directory=data_pump_dir logfile=scott_exp.log
$ impdp scott/password dumpfile=scott.dmp directory=data_pump_dir logfile=scott_imp.log EXCLUDE=TABLE:\"IN('BONUS'\,'CUSTOMERS'\)\"
EX- 16 : EXCLUDE STATISTICS – WHEN EXPORT AND IMPORT
$ expdp hr/hr dumpfile=hr.dmp directory=data_pump_dir logfile=hr.log EXCLUDE=STATISTICS
$ impdp hr/hr dumpfile=hr.dmp directory=data_pump_dir remap_schema=hr:hr1 EXCLUDE=STATISTICS ..
...
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39168: Object path STATISTICS was not found.
Already done EXLUDE=STATISTICS with EXPDP. Again i set EXCLUDE=STATISTICS with IMPDP clause then, Oracle throws error ORA-39168: Object path STATISTICS was not found.
EX- 16 : WHY IMPDP DOES NOT IGNORE TO COLLECT INDEX STATISTICS
Does IMPDP ignore statistics collection for EXCLUDE=STATISTICS?
We think Data Pump IMPDP to exclude both table and index statistics collection;
When you do import EXCLUDE=STATISTICS option, IMPDP can ignore statistics collection for the tables and not for indexes. Whys so? Let’s us check.
SOURCE SCHEMA USR1 TARGET SCHEMA USR2 USR1> select * from cat;
TABLE_NAME TABLE_TYPE --- --- EMP TABLE
USR1> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS --- --- --- EMP TABLE VALID IN2_EMP_LEVL INDEX VALID IN1_EMP_DID INDEX VALID EMP_EMPID_C1_PK INDEX VALID SOURCE SCHEMA OBJECT STATISTICS
USR1> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL STA --- --- --- --- EMP 10000 21-AUG-15 NO
USR1> select INDEX_NAME,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED,STALE_STATS from user_ind_statistics;
INDEX_NAME DISTINCT_KEYS NUM_ROWS LAST_ANAL STA --- --- --- --- --- IN2_EMP_LEVL 4 9999 21-AUG-15 NO IN1_EMP_DID 12 10000 21-AUG-15 NO EMP_EMPID_C1_PK 10000 10000 21-AUG-15 NO
START EXPORT OF USR1 SCHEMA
$ expdp system/manager dumpfile=dp:schema.dmp logfile=schema.log schemas=usr1
Export: Release 11.2.0.1.0 - Production on Fri Aug 21 20:26:14 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=dp:schema.dmp logfile=dp:schema.log schemas=usr1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 640 KB
Processing object type SCHEMA_EXPORT/USER
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/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR1"."EMP" 496.9 KB 10000 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/datapump/schema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:26:35 IMPORT THE DUMP WITH EXCLUDE=STATISTICS
$ impdp system/manager dumpfile=dp:schema.dmp ... REMAP_SCHEMA=USR1:USR2 exclude=statistics
Import: Release 11.2.0.1.0 - Production on Fri Aug 21 20:30:43 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** dumpfile=dp:schema.dmp logfile=dp:schema.log schemas=usr1 remap_schema=usr1:usr2 exclude=statistics
Processing object type SCHEMA_EXPORT/USER
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 "USR2"."EMP" 496.9 KB 10000 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 20:30:46
It’s good I don’t see any lines related to statistics collection of the objects. IMPDP client shows table statistics or index statistics were not processed. So I hope no statistics collected for the schema objects. Can check at database level? Let’s us check it out.
CHECK OBJECT DETAILS IN TARGET SCHEMA
USR2> select * from cat;
TABLE_NAME TABLE_TYPE --- --- EMP TABLE
USR2> select object_name, object_type, status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS --- --- --- IN2_EMP_LEVL INDEX VALID IN1_EMP_DID INDEX VALID EMP_EMPID_C1_PK INDEX VALID EMP TABLE VALID