Home » RDBMS Server » Server Utilities » Exclude Table in Schema Level (11.2.0.4)
Exclude Table in Schema Level [message #636284] Mon, 20 April 2015 16:38 Go to next message
azeem87
Messages: 100
Registered: September 2005
Location: dallas
Senior Member
Hi,
i have the below parameter .par file to export few schemas,and from this schema list
One table of the schema being exported i want to exclude, i still see that this table is exported.
what's missing in parameter Please suggest.

userid="/ as sysdba"
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=test_exlude_archiveTable_%U.dmp
CONTENT=ALL
SCHEMAS=(
'ARCHIVE',
'REPORTING',
'ACTIVITY',
'ENTL_PYMTS')
exclude=table:"IN ('ARCHIVE.ARCHIVE_PROPERTIES')"
LOGFILE=test_exp_excltable.log
parallel=4



Thanks for your time.

[Updated on: Mon, 20 April 2015 16:40]

Report message to a moderator

Re: Exclude Table in Schema Level [message #636287 is a reply to message #636284] Mon, 20 April 2015 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 26595
Registered: January 2009
Location: SoCal
Senior Member
>exclude=table:"IN ('ARCHIVE.ARCHIVE_PROPERTIES')"
instead of above what results when you use below?
exclude=table:ARCHIVE_PROPERTIES
Re: Exclude Table in Schema Level [message #636296 is a reply to message #636287] Mon, 20 April 2015 22:50 Go to previous messageGo to next message
azeem87
Messages: 100
Registered: September 2005
Location: dallas
Senior Member
Thanks for your time, I tried your above suggestion
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator
Re: Exclude Table in Schema Level [message #636300 is a reply to message #636284] Tue, 21 April 2015 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do NOT use SYSDBA unless something in the database constrain you to do so.
Read SYS is special.

Re: Exclude Table in Schema Level [message #636301 is a reply to message #636284] Tue, 21 April 2015 00:36 Go to previous message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
azeem87 wrote on Tue, 21 April 2015 03:08

exclude=table:"IN ('ARCHIVE.ARCHIVE_PROPERTIES')"


You can't mention the OWNER name, it wouldn't work. Just mention the table_name. If multiple OWNERs have the tables with same name, then all the tables will be excluded.

For multiple tables, in your parameter file, mention it as:

EXCLUDE=TABLE:"IN ('TABLE1', 'TABLE2' , 'TABLE3' , 'TABLE4')"


To exclude a single table, you could SPOOL the parameter file. For example,

SET heading OFF
SET feedback OFF

SPOOL exp.par

SELECT 'tables=(' FROM dual
UNION ALL
SELECT table_name
  ||','
FROM DBA_TABLES
WHERE OWNER       = 'ARCHIVE'
AND TABLE_name != 'ARCHIVE_PROPERTIES'
UNION ALL
SELECT 'DUAL)' from dual;

SPOOL OFF;


Format the way you want the SQL*Plus output, add other parameters that you need.

Another thing, why do you want to login as "/ as sysdba"?

Edit : Aah, Michel already pointed the sysdba thing.


Regards,
Lalit

[Updated on: Tue, 21 April 2015 00:39]

Report message to a moderator

Previous Topic: import dump
Next Topic: Can we perform export/import from source & target db having different db_block_size ?
Goto Forum:
  


Current Time: Tue Sep 17 05:37:54 CDT 2019