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

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
Kamal AgnihotriAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
schwertnerConnect With a Mentor Commented:
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
 
ravindran_eeeCommented:
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
 
schwertnerCommented:
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
 
PilouteCommented:
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
 
Kamal AgnihotriAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.