Help - Search - Members - Calendar
Full Version: IMPDP EXCLUDE TABLES
Oracle DBA Forums > Oracle > Oracle Forum
ERSQL
Hi, i need some help trying to exclude 4 tables on my impdp sentence.

I'm using this syntax

impdp system/******* directory=bkp dumpfile=fullSM.dmp logfile=IMPSM.log schemas=myschema EXCLUDE=TABLE:'TABLE1','TABLE2', 'TABLE3', 'TABLE4'

I'm getting some errors:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator

What is the correct syntax to exclude more than one table?

Thanks in advance.

RobinT
Here is an example of exclude command with datapump:

expdp <other_parameters> SCHEMAS=scott EXCLUDE=TABLE:”IN (’EMP’,'DEPT’)”;

RobinT
syntax:

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

Examples of operator-usage:

EXCLUDE=SEQUENCE

or:EXCLUDE=TABLE:”IN (’EMP’,'DEPT’)”

or:EXCLUDE=INDEX:”= ‘MY_INDX’”

or:INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%’”

or:INCLUDE=TABLE:”> ‘E’”

The parameter can also be stored in a par (parameter file) as shown

Parameter file:exp.par

DIRECTORY = my_dir

DUMPFILE = exp_tab.dmp

LOGFILE = exp_tab.log

SCHEMAS = scott

INCLUDE = TABLE:”IN (’EMP’, ‘DEPT’)”

Carol
I can make this work, thanks to your guidance in the syntax.
Now.. I need to export all tables, exclude rows in some of them.
The import needs to include all but the data in some of the tables.

How would I do a schema export, and exclude only the rows in
10 of roughly 800 tables?

Thanks.

Carol
Asad
[quote name='Carol' date='Jul 7 2009, 03:30 PM' post='52108']
I can make this work, thanks to your guidance in the syntax.
Now.. I need to export all tables, exclude rows in some of them.
The import needs to include all but the data in some of the tables.

How would I do a schema export, and exclude only the rows in
10 of roughly 800 tables?

Thanks.

Carol


expdp help=y
Carol
QUOTE (Asad @ Jul 7 2009, 10:58 AM) *
expdp help=y



This would help if I wanted to exclude all data from all tables. Or wanted to exclude certain tables completely.

But I am not seeing a way to exclude only the data from a subset of tables. I need all the objects, with the
exception of no data in a subset of the tables.
Will I be doing a query=<schema>.<table>:'"where rownum<1"' ?

or query='"where table in ('tab1','tab2','tab3') and rownum <1"'

I guess I have to go test with this, was hoping I would't have to reinvent a wheel.

Thanks unsure.gif
dave
you are inventing the wheel again, there is a where clause

looking at the help would have shown you this
Carol
I read the help file and am working with the where clause and trying an AND:... as well.
I am hitting a syntax wall trying to get the entire schema with a mix
of tables where i want all records and tables with no records.

Thanks anyway.
burleson
Hi Carol,

>> Now.. I need to export all tables, exclude rows in some of them.

It looks like you have not been trained in Oracle utilities!

***************************************************************
>> I read the help file

Those suck!

I would highly recommend getting the Oracle Utilities book.

http://www.rampant-books.com/book_0801_oracle_utilities.htm

It will aid you greatly because it has working examples for almost every possibility . . .
Carol
Nope.. no formal training in the utilities.. and needed a fast answer..
I have got it working.. may not be the most efficient means, but I got what I needed.
Help files give me a research start point when digging for a quick answer on the web.

I have added the book you suggested to my acquire list. I have several other books in your
series and have found them extremely useful. One of my faves has been the tuning book.

Thanks.. Have a great week.

Carol-
burleson
Hi Carol,

>> One of my faves has been the tuning book.

Thanks! That mean a lot to me, as I worked very hard on that . . .

BTW, a 2nd edition will be available by August:

http://www.rampant-books.com/book_0902_awr...tive_tuning.htm
lakshmikanth
QUOTE (Carol @ Jul 7 2009, 12:14 PM) *
This would help if I wanted to exclude all data from all tables. Or wanted to exclude certain tables completely.

But I am not seeing a way to exclude only the data from a subset of tables. I need all the objects, with the
exception of no data in a subset of the tables.
Will I be doing a query=<schema>.<table>:'"where rownum<1"' ?

or query='"where table in ('tab1','tab2','tab3') and rownum <1"'

I guess I have to go test with this, was hoping I would't have to reinvent a wheel.

Thanks unsure.gif



hi i hope you got the answer for you r Question...if not just look at below code

Windows:

D:\> expdp username/password DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log EXCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”


D:\> impdp username/password DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=IMP_tab.log REMAP_SCHEMA=FROM:TO EXCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”


Unix:

% expdp username/password DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott EXCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

% impdp username/password DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=imp_tab.log REMAP_SCHEMA=FROM:TO EXCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.