Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Oracle 10g Datapump.

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Oracle 10g Datapump utility.

    In order to work with oracle data pump we will have to first unlock the scott user and create a directory object. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system.

    First startup the database and its associated services.
    Then from the root prompt, do the following.

    su - oracle
    export ORACLE_SID=orcl
    sqlplus
    / as sysdba

    ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;


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

    Note: Directory must be created physically before executing the above command.

    TABLE BASED IMPORT AND EXPORT

    export ORACLE_SID=orcl
    $ expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expEMP_DEPT.log

    export ORACLE_SID=orcl
    $ impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impEMP_DEPT.log
    CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

    # All loads all the metadata as well as data from the source dump file.
    # DATA_ONLY only loads row data into the tables no database objects are created.
    # METADATA_ONLY only creates database objects, no data is inserted.

    SCHEMA BASED IMPORT AND EXPORT

    export ORACLE_SID=orcl
    $ expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expSCOTT.log

    export ORACLE_SID=orcl
    $ impdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impSCOTT.log
    CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

    # All loads all the metadata as well as data from the source dump file.
    # DATA_ONLY only loads row data into the tables no database objects are created.
    # METADATA_ONLY only creates database objects, no data is inserted.

    FULL DATABASE IMPORTS AND EXPORTS

    For this to work you must login as the system user not as the sys user.

    export ORACLE_SID=orcl
    $ expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expDB10G.log

    export ORACLE_SID=orcl
    $ impdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impDB10G.log
    CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

    # All loads all the metadata as well as data from the source dump file.
    # DATA_ONLY only loads row data into the tables no database objects are created.
    # METADATA_ONLY only creates database objects, no data is inserted.

    In the above commands you can also add a additional clause that is job_name.

    Example syntax :

    $ expdp system/password full=y directory=TEST_DIR dumpfile=DB10G.dmp job_name=qq



    You can view the datapump jobs from the following view:

    Sql> select * from dba_datapump_jobs;


    Even if you exit the prompt or press ctrl+c at the command prompt
    Or exit from the client side the datapump jobs will continue to run at the server.


    To reattach with the running job enter the following command.

    $ expdp system/password attach=qq

    If the import or export job is to be stopped temporarily then type the following command.
    press CTRL+C
    Export> STOP_JOB=IMMEDIATE
    Are you sure you wish to stop this job ([y]/n): y


    In order to resume the job do the following.

    export ORACLE_SID=orcl
    $expdp hr/hr ATTACH=qq
    Export> CONTINUE_CLIENT


    Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t want to continue with the export job.

    IMPORT SCENARIOS:

    REMAP_SCHEMA: loads all the objects from the source schema into the target schema.
    $ expdp system/sys schemas=scott directory=test_dir dumpfile=scott.dmp
    $ impdp system/sys remap_schema=scott:hr directory=test_dir dumpfile=scott.dmp
    Last edited by ajaychandi; 03-25-2012 at 02:08 PM.

+ Reply to Thread

Similar Threads

  1. difference between normal exp/imp and datapump expdp/impdp ?
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 3
    Last Post: 08-11-2012, 01:28 PM
  2. Script to Monitor datapump sessions
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 07-15-2012, 07:53 PM
  3. Oracle 10g Datapump.
    By ajaychandi in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 2
    Last Post: 04-25-2012, 11:28 AM
  4. DATAPUMP EXCLUDE/INCLUDE parameters
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 11-04-2011, 02:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics