Solved

Data Pump Script to Exclude a few tables out of thosands in a schema

Posted on 2009-05-18
7
617 Views
Last Modified: 2012-05-07
I request help in writing Data Pump commands to do the following:
1) Export a Schema, Schema_1 (This Schema has 2000 Tables)
2) Use the Export File Created in Step 1, to Import selected 10 tables and all associated objects into Schema _2
3) Use the Export File Created in Step 1, to Import the rest of the 1990 tables and all associated objects into Schema _3
0
Comment
Question by:KamalAgnihotri
7 Comments
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 24418963
Make use of exclude/include parameters in Oracle data pump (assuming Oracle version is 10g)

http://www.oraclefaq.net/2007/03/09/expdp-datapump-excludeinclude-parameters/
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24419292
Excluding indexes that start with EMP:
EXCLUDE=INDEX:LIKE EMP%

Excluding the SCOTT schema from a FULL export:
EXCLUDE=SCHEMA:=SCOTT

impdp DIRECTORY=tmp dumpfile=tetst2.dmp logfile=log2 REMAP_SCHEMA=surachart:chart
EXCLUDE=TABLE:\"= \'ADDRESS_BOOKS\'\"

Possibly in the last example you will be able to use SQL IN clause

EXCLUDE=TABLE:\"= \IN ('ADDRESS_BOOKS\')\"

0
 
LVL 7

Expert Comment

by:Piloute
ID: 24419321
Hi,

Check out the DataPump official doc here
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Go down into the docs for the actions like export and import and you have all the commands with examples.

Cheers,
P
0
 

Author Comment

by:KamalAgnihotri
ID: 24424291
The following worked for me:

impdp moment/*****@asc999 DIRECTORY=dpump_dir_009 DUMPFILE=MOM_ALL_033.dmp LOGFILE=impdp_MOM_minus_DC_009.log EXCLUDE=TABLE:"IN('DMS_CONTENT')"

However, my issue has not been resolved:

1) TabSpa_1 is the Tablespace for Schema_1 which 2000+ Tables. A few of them have LOB datatypes.
2) I want to seperate those 10 tables which have LOB DataTypes, into a new schema, Schema_2 having its own Tablespace TabSpa_2

I tried the DataPump and the Export/Import Utility and in both cases, the Import ends up going to TabSpa_1 Tablespace.

I think, it can be done. If not please let me know as well.

Kamal


0
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 24424734
You can also move objects from one tablespace to another by using the REMAP_TABLESPACE option.
Example:
$ impdp
SCHEMAS=iself
REMAP_TABLESPACE=iself_tablespace:urself_tablespace

Many times though we want to import into a different schema and this is accomplished by the REMAP_SCHEMA option. Here is an example where we imported the DEPT table into a different schema.

C:\>impdp system/tiger REMAP_SCHEMA=SCOTT:JKOOPMANN DIRECTORY=datapump DUMPFILE=dept.dmp LOGFILE=dept.log

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pfile and SPfile - Oracle 2 54
export Oracle diagram from Oracle DB including VIEWS 8 101
grouping on time windows 6 41
oracle 11g 23 47
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now