/*
|| 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
;