Thinking Out Loud

September 13, 2015

Scaling Export and Import Tables Residing in Different Schemas 10gR2

Filed under: 10g,DataPump,oracle — mdinh @ 2:40 pm

Our team was tasked to unpartion all partition tables.

Import: Release 11.2.0.4.0 has PARTITION_OPTIONS=DEPARTITION but we are on Release 10.2.0.4.0

The first step was to find all schemas with partition tables.

select owner, table_name, partitioning_type, subpartitioning_type, partition_count, status 
from dba_part_tables where owner not in ('SYS','SYSTEM') 
and (owner,table_name) not in (
 select owner,mview_name table_name 
 from dba_mviews 
 where owner not in ('SYS','SYSTEM') 
) 
order by 1,2
;

NOTE: SQL is not 100% fail proof as we ran into a scenario where the table and materialized view had the same name.

Export the metatadata for table from multiple schemas failed:

UDE-00012: table mode exports only allow objects from one schema

Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
Example:
File: expdp_tabs.par 
-------------------- 
DIRECTORY = my_dir  
DUMPFILE  = expdp_tabs.dmp  
LOGFILE   = expdp_tabs.log  
SCHEMAS   = scott,hr,oe   
INCLUDE   = TABLE:"IN ('EMP','EMPLOYEES','CATEGORIES_TAB')" 

Great solution but not scalable.

Dig to find – How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases
http://uralural.blogspot.com/2011/06/how-to-export-tables-from-multiple.html

I will let you read the post; however, the following was the key for me.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                            

Database has 22 partition tables.

*** List partition tables, exluding MVIEW ***
22 rows selected.

There are 11 schemas with partition tables.

*** List partition tables count by owner, exluding MVIEW ***
11 rows selected.

Partition table SYSTEM_QUEUE resides in 7 different schemas and ACCOUNT_OBJECT_TRANSACTIONS resides in 2 different schemas.


*** List same table name across owner, exluding MVIEW ***
TABLE_NAME			 COUNT(*)
------------------------------ ----------
SYSTEM_QUEUE				7
ACCOUNT_OBJECT_TRANSACTIONS		2

Create tables to use for export.

create table OWNER01.expdp_tables (table_name varchar2(30))
;
insert into OWNER01.expdp_tables
select DISTINCT table_name
from dba_part_tables
where owner not in ('SYS','SYSTEM')
and (owner,table_name) not in (
  select owner,mview_name table_name from dba_mviews where owner not in ('SYS','SYSTEM')
)
; 

Create export parameter.

$ cat expdp_schema_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
content=METADATA_ONLY
dumpfile=TEST.dmp
logfile=exp_TEST.log
schemas=OWNER01,OWNER02,OWNER03,OWNER04,OWNER05,OWNER06,OWNER07,OWNER08,OWNER09,OWNER10,OWNER11
INCLUDE=TABLE:"IN (SELECT table_name FROM OWNER01.expdp_tables)"

Perform export.

$ expdp parfile=expdp_schema_TEST.par 

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:04:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  parfile=expdp_schema_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 20 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 5 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 85 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 30 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 22 TABLE_STATISTICS objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u02/oracle/exp/TEST.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:46

Create import parameter.

$ cat impdp_sqlfile_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
exclude=STATISTICS
dumpfile=TEST.dmp
logfile=imp_TEST.log
sqlfile=create_TEST.sql

Perform import.

$ impdp parfile=impdp_sqlfile_TEST.par 

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:07:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  parfile=impdp_sqlfile_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 8 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:07:48

BVT

$ grep -c "CREATE TABLE" /u02/oracle/exp/create_TEST.sql|sort
22
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: