How to export with data pump

February 1st, 2010 Posted in Oracle How-To

I have always used Oracle good old export utility to backup the logical definition of all my databases. In addition to using RMAN backup I feel a logical dump of the database is a good idea. I have now started using Oracle’s data pump utility. I work quite well, but does operate a bit differently that the older export utility.

Now you create an Oracle directory and dump the data to this directory. Here is how you create the directory.

CREATE OR REPLACE DIRECTORY expdump
AS '/array/oracle/prod/o1dw/exports/temp';

Next give the proper permissions to the directory:

GRANT EXP_FULL_DATABASE to DWEXP;
GRANT READ, WRITE ON DIRECTORY expdump to DWEXP;

Now it’s time to export the database from the OS:

expdp uername/password schemas=o1dw
directory=EXPDUMP dumpfile=myexport.dmp logfile=myexport.log

That is all there is to it. Watch your case (i.e. directory=EXPDUMP). directory=expdump did not work for me.

Tags: , , ,
This entry was posted on Monday, February 1st, 2010 at 8:13 pm and is filed under Oracle How-To. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “How to export with data pump”

  1. John Jeracevich Says:

    February 4th, 2010 at 6:04 am

    create the physical directory on the file system.

    CONN sys/password@db10g AS SYSDBA
    ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
    GRANT CREATE ANY DIRECTORY TO scott;

    CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
    GRANT READ, WRITE ON DIRECTORY test_dir TO scott;



  2. Mark Daniel@Abs Training Says:

    February 4th, 2010 at 6:05 am

    The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

    expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

    impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

    For example output files see expdpSCOTT.log and impdpSCOTT.log.



Leave a Reply