Skip to Main Content
  • Questions

  • oracle data pump for a list of tables from different schemas

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vish.

Asked: November 17, 2016 - 2:46 pm UTC

Last updated: November 02, 2022 - 4:59 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Please note that I have a requirement where I need to export and import data in oracle 11g. At the source there are 4 schemas and each schema contains different numbers of tables. I need to load the target schema with the exported data from the different schemas for a selected list of tables in each schema.

For Example

Source (where expdb would run)
--------------------------------
Schema1 (table 1..... table 20),
Schema2 (table1....table 100).

Target (where impdb would run)
--------------------------------
Schema1 (table 1..... table 20),
Schema2 (table1....table 100).

Please suggest an example which can help me as i am just a beginner in the world of DBA.

Thanks in Advance.
Vish


and Connor said...

In you parameter file, you can use an INCLUDE clause, eg

mypar.par
==========================================
schemas=schema1
include=TABLE:"IN ('T1','T2','T3','T4')"
directory=MYDIR
dumpfile=schema1.dmp
logfile=schem1.log

and then do

expdp user/pass parfile=mypar.par

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question

A reader, November 19, 2016 - 6:26 am UTC

This shows one single schema. What if I need many schema for some tables in each? Feasible in single parfile? That is the question.
Connor McDonald
November 21, 2016 - 2:55 am UTC

You can have something like:

exclude=SCHEMA:"IN ('MCDONAC','SYS','ASKTOM','SH','SOE')"
exclude=TABLE:"LIKE 'EMP%'"

but the second line applies to all schemas, so its depends on how your table names are spread across schemes.

Idea

A reader, November 21, 2016 - 6:05 am UTC

Reading all above I wish from oracle something that allows identifying the schema "type". Eg something similar to the namespace . Having this I would be able to chose my "User defined " schema. .

Export and Import across schema

Rajeshwaran, Jeyabal, November 21, 2016 - 8:41 am UTC

....
I wish from oracle something that allows identifying the schema "type". Eg something similar to the namespace . Having this I would be able to chose my "User defined " schema. .
....

you could make use of the INCLUDE parameter from DATAPUMP utility.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL853

using that - you could export/import multiple object types from either within a single schema or across different schema.

D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>expdp rajesh/oracle@ora11g parfile=d:\par.txt

Export: Release 11.2.0.4.0 - Production on Mon Nov 21 14:07:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RAJESH"."SYS_EXPORT_SCHEMA_01":  rajesh/********@ora11g parfile=d:\par.txt
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path SEQUENCE was not found.
ORA-39168: Object path PROCEDURE was not found.
ORA-31655: no data or metadata objects selected for job
Job "RAJESH"."SYS_EXPORT_SCHEMA_01" completed with 3 error(s) at Mon Nov 21 14:07:52 2016 elapsed 0 00:00:06


D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>type d:\par.txt
DIRECTORY=etl_data_dir
DUMPFILE=expdp2.dmp
LOGFILE=explog2.txt
include=SEQUENCE:"in ('DEMO','SCOTT')"
include=PROCEDURE:"in('RAJESH')"
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>


you could do export and import across the schema, ensure that the user perform export/import have the necessary privilege to access the objects across schema

demo@ORA11G> create table t1 as select * from all_objects;

Table created.

demo@ORA11G> conn rajesh/oracle@ora11g
Connected.
rajesh@ORA11G> create table t2 as select * from all_objects;

Table created.

rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\179818>d:

D:\>cd app\Vnameit\product\11.2.0\dbhome_1\bin

D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>expdp rajesh/oracle@ora11g DIRECTORY=etl_data_dir DUMPFILE=expdp.dmp LOGFILE=explog.txt TABLES=(rajesh.t2,demo.t1)

Export: Release 11.2.0.4.0 - Production on Mon Nov 21 13:54:24 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RAJESH"."SYS_EXPORT_TABLE_01":  rajesh/********@ora11g DIRECTORY=etl_data_dir DUMPFILE=expdp.dmp LOGFILE=explog.txt TABLES=(rajesh.t2,demo.t1)
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEMO"."T1"                                 8.229 MB   84874 rows
. . exported "RAJESH"."T2"                               8.229 MB   84876 rows
Master table "RAJESH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJESH.SYS_EXPORT_TABLE_01 is:
  D:\EXPDP.DMP
Job "RAJESH"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 21 13:54:47 2016 elapsed 0 00:00:16


D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>impdp rajesh/oracle@ora11g directory=ETL_DATA_DIR dumpfile=expdp.dmp remap_table=demo.t1:demo.t2,rajesh.t2:rajesh.t1 logfile=implog.txt

Import: Release 11.2.0.4.0 - Production on Mon Nov 21 13:57:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RAJESH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "RAJESH"."SYS_IMPORT_FULL_01":  rajesh/********@ora11g directory=ETL_DATA_DIR dumpfile=expdp.dmp remap_table=demo.t1:demo.t2,rajesh.t2:rajesh.t1 logfile=implog.txt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."DEMO.T2"                            8.229 MB   84874 rows
. . imported "RAJESH"."RAJESH.T1"                        8.229 MB   84876 rows
Job "RAJESH"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 21 13:58:08 2016 elapsed 0 00:00:06



D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>

Export one Table with the same name in different schemas

Flavio Melo, November 02, 2022 - 2:01 am UTC

Hello, I have a scenario where I need to export tables and views from different schemas (SCHEMA__A, SCHEMA_B and SCHEMA_C), until then the INCLUDE clause resolves. I happen to have tables and views with the same names in two of the three different schemas. I need it, but I only want one of them. How to select just one of them?
Connor McDonald
November 02, 2022 - 4:59 am UTC

Could you not just list the objects you to export ? eg

tables=(scott.emp,scott2.emp)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.