UDE-00012 - 墨天轮文档

UDE-00012

Oracle6

200

6页

0次

2022-10-12

10墨值下载

UDE-00012: table mode exports only allow objects

from one schema

Scaling Export and Import Tables Residing in Different Schemas on 10gR2.

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

of 6

10墨值下载

ude-00012

关注

评论