Search code examples
sqloracle-databaseoracle11gexportdatapump

expdp on a different SID and use of EXCLUDE, INCLUDE and CONTENT switch

I am trying to import a db into my local xe instance. I have a problem in that it is greater than 11GB and things fall over. My db has audit tables (courtesy of envers). I do not need this data.

Two questions here:

  • can I expdp table structures and not data for the aud_ tables that are my audit tables
  • can I expdp on a different SID. My SID is not orcl but orcllo (for historical reason)

For the second question I have done the following but 

sqlplus / as sysdba@orcllo
alter user MY_DB identified by MY_PASS ACCOUNT UNLOCK;
CREATE OR REPLACE DIRECTORY db_dumps AS '/tmp/db_dumps';
GRANT READ, WRITE ON DIRECTORY db_dumps TO MY_DB;

but when I run expdp I get an error about db_dumps not being found.

Thanks

Solution

  • Q.1:

    You can use 'EXCLUDE' or 'INCLUDE' switch during oracle impdp (data pump) to escape/include few tables.

    The following code shows how they can be used as command line parameters.

    impdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

    Same can be done during export as well:

    expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR

    CONTENT=METADATA_ONLY switch should be used if you indeed want to create structure on target schema/db.

    Q.2:

    You can not use expdp / impdp on a different SID as is but via DB Link route (using NETWORK_LINK switch).

    However, i think, Ans-1 should address your real problem due to Oracle XE limitations. Moreover, there may be performance issues due to cross network/DB data flow.

    Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

    impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

    HTH

    Check the following for more options with data pump:

    http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php