Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2009-05-18
Medium Priority
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
Question by:KamalAgnihotri
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Expert Comment

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

Expert Comment

ID: 24419292
Excluding indexes that start with EMP:

Excluding the SCOTT schema from a FULL export:

impdp DIRECTORY=tmp dumpfile=tetst2.dmp logfile=log2 REMAP_SCHEMA=surachart:chart

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



Expert Comment

ID: 24419321

Check out the DataPump official doc here

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


Author Comment

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.


LVL 48

Accepted Solution

schwertner earned 500 total points
ID: 24424734
You can also move objects from one tablespace to another by using the REMAP_TABLESPACE option.
$ impdp

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


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

721 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