-- Setup
del ../log/*
del ../files/*

CONNECT SYSTEM@bing

DROP DIRECTORY expdir;
DROP DIRECTORY logdir;
REVOKE read, write ON DIRECTORY data_pump_dir FROM scott;
REVOKE read, write ON DIRECTORY data_pump_dir FROM public;

CONNECT SCOTT@bing
drop table external_emp purge;

CONNECT SCOTT@bong
@drop_all

-- -----------------------------------
-- Directory basics
-- -----------------------------------
CONNECT SYSTEM@bing

-- Create required directory objects and grant privileges
-- requires CREATE_ANY_DIRECTORY system privilege

CREATE DIRECTORY expdir AS 'c:\oracle\sqlplus\datapump\files';
GRANT read, write ON DIRECTORY expdir TO scott;

CREATE DIRECTORY logdir AS 'c:\oracle\sqlplus\datapump\log';
GRANT read, write ON DIRECTORY logdir TO scott;

-- Verify creation of directory object and privs
SELECT * FROM dba_directories;
SELECT * FROM dba_tab_privs WHERE table_name='LOGDIR'


-- -----------------------------------
-- Beware of the old way!
-- -----------------------------------
expdp scott/tiger@bing
expdp system/oracle@bing
expdp -help

-- -----------------------------------
-- Demonstrate simple export / import
-- -----------------------------------
CONNECT SCOTT

-- Simplest schema export
expdp scott/tiger@bing DIRECTORY=expdir DUMPFILE=expscott.dmp

-- Alternate syntax to put logfile in different directory
expdp scott/tiger@bing DUMPFILE=expdir:expscott.dmp LOGFILE=logdir:expscott.log
expdp scott/tiger@bing DIRECTORY=expdir DUMPFILE=expscott.dmp LOGFILE=logdir:expscott.log

-- Simplest import
impdp scott/tiger@bong DIRECTORY=expdir DUMPFILE=expscott.dmp


-- -----------------------------------
-- Demonstrate query / sample
-- -----------------------------------
CONNECT UW

-- Export part of a table based on data selection
expdp uw/uw@bing parfile=exp_airplanes_usaf.par

   DUMPFILE=expdir:expplanes_usaf.dmp
   LOGFILE =logdir:expplanes_usaf.log
   INCLUDE=table:"LIKE 'AIR%'"
   QUERY="airplanes:WHERE customer_id='USAF'"

-- Export a random sample of table data
expdp uw/uw@bing parfile=exp_airplanes_sample.par

   DUMPFILE=expdir:expplanes_sample.dmp
   LOGFILE =logdir:expplanes_sample.log
   INCLUDE=table:"IN ('AIRPLANES')"
   SAMPLE="AIRPLANES":25

-- Export the whole table so we can play with it on import
expdp uw/uw@bing parfile=exp_airplanes_all.par

   DUMPFILE=expdir:expplanes_all.dmp
   LOGFILE =logdir:expplanes_all.log


-- Import the USAF data into its own table
impdp uw/uw@bong parfile=imp_airplanes_usaf.par

   DUMPFILE=expdir:expplanes_usaf.dmp
   LOGFILE=logdir:impplanes_usaf.log
   TABLE_EXISTS_ACTION=replace

-- Import the sample data into its own schema
impdp fred/fred@bong parfile=imp_airplanes_fred.par

   DUMPFILE=expdir:expplanes_sample.dmp
   LOGFILE=logdir:impplanes_sample.log
   TABLE_EXISTS_ACTION=replace
   REMAP_SCHEMA=uw:fred

-- Import a subset of the whole table
impdp uw/uw@bong parfile=imp_airplanes_june.par

   DUMPFILE=expdir:expplanes_all.dmp
   LOGFILE=logdir:impplanes_may.log
   TABLES="AIRPLANES"
   QUERY=airplanes:"WHERE TRUNC(order_date) BETWEEN '01-MAY-08' AND '31-MAY-08'"
   TABLE_EXISTS_ACTION=replace


-- -----------------------------------
-- Demonstrate a BIG job
-- -----------------------------------

-- use STATUS parameter

expdp system/oracle@bing parfile=bigjob.par

   DUMPFILE=expdir:bigjob.dmp
   LOGFILE=logdir:bigjob.log
   FULL=y
   JOB_NAME=bigjob
   STATUS=2

detach from the job
^C

-- query the job status in the database
descr bigjob
select count(*) from bigjob;
select * from dba_datapump_jobs;

query the master table
re-attach to the job
re-start the job
continue_client
^C
stop_job
kill_job



-- -----------------------------------
-- Demonstrate PL/SQL interface
-- -----------------------------------
CONNECT SYSTEM

DECLARE
  h1 NUMBER;  -- Job handle

BEGIN
-- Create a (user-named) Data Pump job to do a schema export.

    h1 := DBMS_DATAPUMP.OPEN(
             operation   => 'EXPORT'
            ,job_mode    => 'SCHEMA'
            ,remote_link => NULL
            ,job_name    => 'PLSJOB'
            ,version     => 'LATEST');


-- Specify dump file and log file

    DBMS_DATAPUMP.ADD_FILE(
         handle    => h1
        ,filename  => 'PLS.DMP'
        ,directory => 'EXPDIR'
        ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE );
  
    DBMS_DATAPUMP.ADD_FILE(
         handle => h1
        ,filename => 'PLS.LOG'
        ,directory => 'LOGDIR'
        ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

-- Add metadata filter to specify which schema to export

    DBMS_DATAPUMP.METADATA_FILTER(
         handle      => h1
        ,NAME        => 'SCHEMA_EXPR'
        ,VALUE       => 'IN (''SCOTT'')' );

-- Start the job.

    DBMS_DATAPUMP.START_JOB(h1);

end;
/



-- -----------------------------------
-- Demonstrate SQL File
-- -----------------------------------

impdp scott/tiger@bong PARFILE=imp_sqlfile.par

   DUMPFILE=expdir:pls.dmp
   LOGFILE=logdir:sqlfile.log
   SQLFILE=logdir:sqlfile.txt




-- -----------------------------------
-- Demonstrate External Table
-- -----------------------------------
CONNECT SCOTT@bing

CREATE TABLE external_emp
   ORGANIZATION EXTERNAL 
      ( TYPE oracle_datapump
        DEFAULT DIRECTORY expdir
        LOCATION ('external_emp.dmp')
      )
   AS SELECT * FROM emp;


CONNECT SCOTT@bong

CREATE TABLE external_emp
   ( EMPNO     NUMBER(4),
     ENAME     VARCHAR2(10),
     JOB       VARCHAR2(9),
     MGR       NUMBER(4),
     HIREDATE  DATE,
     SAL       NUMBER(7,2),
     COMM      NUMBER(7,2),
     DEPTNO    NUMBER(2)
   )
   ORGANIZATION EXTERNAL
      ( TYPE oracle_datapump
        DEFAULT DIRECTORY expdir
        LOCATION ('external_emp.dmp')
      );



-- -----------------------------------
-- Demonstrate Parallelism
-- -----------------------------------

expdp system/oracle@bing parfile=parjob.par

   DUMPFILE=expdir:parjob%u.dmp
   LOGFILE=logdir:parjob.log
   FULL=y
   JOB_NAME=parjob
   STATUS=2
   PARALLEL=2