/*
|| Oracle 10g DataPump - Listing 1
||
|| Contains examples of new Oracle 10g DataPump Features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new DataPump and should be carefully proofread before executing it against || any existing Oracle database to insure that no potential damage can occur.
||
*/

----- 
-- Listing 1.1: Setting up a DIRECTORY object for DataPump use.
--              Note that the directory folder need not exist for this command
--              to succeed, but any subsequent attempt to utilize the DIRECTORY
--              object will fail until the folder is created on the server.
--              This should be run from SYSTEM for best results
-----
DROP DIRECTORY export_dir;
CREATE DIRECTORY export_dir as 'c:\oracle\export_dir';
GRANT READ, WRITE ON DIRECTORY export_dir TO hr, sh;

----- 
-- Listing 1.2: Determining what object types can be exported/imported
--              and filtering levels available
-----
COL object_path          FORMAT A25      HEADING 'Object Path Name'
COL comments             FORMAT A50      HEADING 'Object Description'
COL named                FORMAT A3       HEADING 'Nmd|Objs'

TTITLE 'Database-Level Exportable Objects'
SELECT 
     object_path
    ,named 
    ,comments
  FROM database_export_objects;
  
TTITLE 'Schema-Level Exportable Objects'
SELECT 
     object_path
    ,named 
    ,comments
  FROM schema_export_objects;

TTITLE 'Table-Level Exportable Objects'
SELECT 
     object_path
    ,named 
    ,comments
  FROM table_export_objects;

----- 
-- Listing 1.3: A simple DataPump Export operation. Note that if the export
--              dump file already exists when this is executed, Oracle will 
--              return an ORA-39000 error and terminate the operation
-----
EXPDP hr/hr DUMPFILE=export_dir:hr_schema.dmp LOGFILE=export_dir:hr_schema.explog

>> DataPump Export command issued:

SET ORACLE_SID=zdcdb
EXPDP system/******** PARFILE=c:\rmancmd\dpe_1.expctl

>> DataPump Export parameters file (dpe_1.expctl):

DIRECTORY=export_dir
SCHEMAS=HR,OE
JOB_NAME=hr_oe_schema
DUMPFILE=export_dir:hr_oe_schemas.dmp
LOGFILE=export_dir:hr_oe_schemas.explog

>> Results of Export Operation:

Export: Release 10.1.0.2.0 - Production on Thursday, 10 March, 2005 17:52
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."HR_OE_SCHEMA":  system/******** parfile=c:\rmancmd\dpe_1.expctl 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.562 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/SE_TBL_FBM_INDEX_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/SE_TBL_FBM_IND_STATS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/SE_POST_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
. . exported "HR"."LOBSEP"                               9.195 KB       1 rows
. . exported "HR"."BIGATFIRST"                           277.7 KB   10000 rows
. . exported "HR"."APPLICANTS"                           10.46 KB      30 rows
. . exported "HR"."APPLICANTS_1"                          11.5 KB      45 rows
. . exported "HR"."APPROLES"                             6.078 KB       7 rows
. . exported "HR"."APPS"                                 5.632 KB       3 rows
. . exported "HR"."COST_CENTERS"                         6.328 KB      29 rows
. . exported "HR"."COST_CENTER_ASSIGNMENTS"              6.312 KB      20 rows
. . exported "HR"."COUNTRIES"                            6.093 KB      25 rows
. . exported "HR"."DATEMATH"                             6.984 KB       1 rows
. . exported "HR"."DEPARTMENTS"                          7.101 KB      28 rows
. . exported "HR"."DIVISIONS"                            5.335 KB       3 rows
. . exported "HR"."EMPLOYEES"                            16.67 KB     118 rows
. . exported "HR"."EMPLOYEE_HIERARCHY"                   6.414 KB       5 rows
. . exported "HR"."JOBS"                                 7.296 KB      27 rows
. . exported "HR"."JOB_HISTORY"                          6.765 KB      15 rows
. . exported "HR"."LOCATIONS"                            7.710 KB      23 rows
. . exported "HR"."MY_USER_ROLES"                        6.453 KB      10 rows
. . exported "HR"."PAYROLL_CHECKS"                       7.609 KB       6 rows
. . exported "HR"."PAYROLL_HOURLY"                       6.039 KB       3 rows
. . exported "HR"."PAYROLL_SALARIED"                     5.687 KB       3 rows
. . exported "HR"."PAYROLL_TRANSACTIONS"                 7.195 KB       6 rows
. . exported "HR"."REGIONS"                              5.296 KB       4 rows
. . exported "HR"."TIMECLOCK_PUNCHES"                    5.718 KB       6 rows
. . exported "HR"."USERS"                                5.968 KB       3 rows
. . exported "HR"."USER_ROLES"                           6.453 KB      10 rows
. . exported "HR"."IOT_TAB"                                  0 KB       0 rows
. . exported "HR"."NO_UPDATES"                               0 KB       0 rows
. . exported "HR"."PLAN_TABLE"                               0 KB       0 rows
Master table "SYSTEM"."HR_OE_SCHEMA" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.HR_OE_SCHEMA is:
  C:\ORACLE\EXPORT_DIR\HR_OE_SCHEMAS.DMP
Job "SYSTEM"."HR_OE_SCHEMA" successfully completed at 17:53

----- 
-- Listing 1.4: A simple DataPump Import. Note that only database objects from
--              the HR schema will be used to populate a new schema (HR_OLTP),
--              and all objects other than tables and their dependent objects 
--              will be excluded from the import
-----

>> SQL to create new HR_OLTP schema:

DROP USER hr_oltp CASCADE;
CREATE USER hr_oltp
    IDENTIFIED BY misdev
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp02
    QUOTA 50M ON example
    PROFILE default;
GRANT CONNECT TO hr_oltp;
GRANT RESOURCE TO hr_oltp;

>> DataPump Import command issued:

SET ORACLE_SID=zdcdb
IMPDP system/****** PARFILE=export_dir:dpi_1.impctl

>> DataPump Import parameters file (dpi_1.impctl):

DIRECTORY=export_dir
JOB_NAME=hr_oltp_import
DUMPFILE=export_dir:hr_oe_schemas.dmp
LOGFILE=export_dir:hr_oltp_import.implog
REMAP_SCHEMA=hr:hr_oltp
STATUS=5

>> Results of Import operation:

Import: Release 10.1.0.2.0 - Production on Thursday, 10 March, 2005 18:02
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."HR_OLTP_IMPORT" successfully loaded/unloaded
Starting "SYSTEM"."HR_OLTP_IMPORT":  system/******** parfile=c:\rmancmd\dpi_1.impctl 
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR_OLTP"."LOBSEP"                          9.195 KB       1 rows
. . imported "HR_OLTP"."BIGATFIRST"                      277.7 KB   10000 rows
. . imported "HR_OLTP"."APPLICANTS"                      10.46 KB      30 rows
. . imported "HR_OLTP"."APPLICANTS_1"                     11.5 KB      45 rows
. . imported "HR_OLTP"."APPROLES"                        6.078 KB       7 rows
. . imported "HR_OLTP"."APPS"                            5.632 KB       3 rows
. . imported "HR_OLTP"."COST_CENTERS"                    6.328 KB      29 rows
. . imported "HR_OLTP"."COST_CENTER_ASSIGNMENTS"         6.312 KB      20 rows
. . imported "HR_OLTP"."COUNTRIES"                       6.093 KB      25 rows
. . imported "HR_OLTP"."DATEMATH"                        6.984 KB       1 rows
. . imported "HR_OLTP"."DEPARTMENTS"                     7.101 KB      28 rows
. . imported "HR_OLTP"."DIVISIONS"                       5.335 KB       3 rows
. . imported "HR_OLTP"."EMPLOYEES"                       16.67 KB     118 rows
. . imported "HR_OLTP"."EMPLOYEE_HIERARCHY"              6.414 KB       5 rows
. . imported "HR_OLTP"."JOBS"                            7.296 KB      27 rows
. . imported "HR_OLTP"."JOB_HISTORY"                     6.765 KB      15 rows
. . imported "HR_OLTP"."LOCATIONS"                       7.710 KB      23 rows
. . imported "HR_OLTP"."MY_USER_ROLES"                   6.453 KB      10 rows
. . imported "HR_OLTP"."PAYROLL_CHECKS"                  7.609 KB       6 rows
. . imported "HR_OLTP"."PAYROLL_HOURLY"                  6.039 KB       3 rows
. . imported "HR_OLTP"."PAYROLL_SALARIED"                5.687 KB       3 rows
. . imported "HR_OLTP"."PAYROLL_TRANSACTIONS"            7.195 KB       6 rows
. . imported "HR_OLTP"."REGIONS"                         5.296 KB       4 rows
. . imported "HR_OLTP"."TIMECLOCK_PUNCHES"               5.718 KB       6 rows
. . imported "HR_OLTP"."USERS"                           5.968 KB       3 rows
. . imported "HR_OLTP"."USER_ROLES"                      6.453 KB      10 rows
. . imported "HR_OLTP"."IOT_TAB"                             0 KB       0 rows
. . imported "HR_OLTP"."NO_UPDATES"                          0 KB       0 rows
. . imported "HR_OLTP"."PLAN_TABLE"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR_OLTP"."BIN$55fGDdubQL6YVYB0dGS/nw==$1" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."BIN$55fGDdubQL6YVYB0dGS/nw==$1" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."TR_BRIU_APPLICANTS" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."TR_BRIU_APPLICANTS" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR_OLTP"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/SE_TBL_FBM_INDEX_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/SE_TBL_FBM_IND_STATS/INDEX_STATISTICS
Job "SYSTEM"."HR_OLTP_IMPORT" completed with 8 error(s) at 18:02

----- 
-- Listing 1.5: Querying status of DataPump operations
-----
TTITLE 'Currently Active DataPump Operations'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'JobName'
COL operation           FORMAT A12      HEADING 'Operation'
COL job_mode            FORMAT A12      HEADING 'JobMode'
COL state               FORMAT A12      HEADING 'State'
COL degree              FORMAT 9999     HEADING 'Degr'
COL attached_sessions   FORMAT 9999     HEADING 'Sess'

SELECT 
     owner_name
    ,job_name
    ,operation
    ,job_mode
    ,state
    ,degree
    ,attached_sessions
  FROM dba_datapump_jobs
;

TTITLE 'Currently Active DataPump Sessions'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A06      HEADING 'Job'
COL osuser              FORMAT A12      HEADING 'UserID'

SELECT 
     DPS.owner_name
    ,DPS.job_name
    ,S.osuser
  FROM 
     dba_datapump_sessions DPS
    ,v$session S
 WHERE S.saddr = DPS.saddr
;