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
Thinking Out Loud 