Moving Data by Using Data Pump - Practices for Lesson 16: Overview

Practices for Lesson 16: Overview

Practice 16-1: Moving Data by Using Data Pump

In this practice, you first grant the DBA1 user the privileges necessary to provide access to the DATA_PUMP_DIR directory. You then export the HR schema so that you can then import the tables that you want into the DBA1 schema. In the practice, you import only the EMPLOYEES table at this time.

1. First, you need to grant to the DBA1 user the appropriate privileges on the

DATA_PUMP_DIR directory. Be sure you know the OS directory where the Data Pump import file will be placed.

$ . oraenv

ORACLE_SID = [oracle] ? orcl The Oracle base for

ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle

$ sqlplus / as sysdba

SQL> SELECT * from dba_directories

2 WHERE directory_name = 'DATA_PUMP_DIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH

--- --- --- SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/ ORIGIN_CON_ID

--- 0

SQL> grant read on directory data_pump_dir to dba1;

Grant succeeded.

SQL> grant write on directory data_pump_dir to dba1;

Grant succeeded.

SQL> exit

2. Use the Data Pump export utility to export the HR schema. Specify the DBA1 user to execute the export operation.

$ rm /u01/app/oracle/admin/orcl/dpdump/HREXP01.dmp rm: cannot remove

`/u01/app/oracle/admin/orcl/dpdump/HREXP01.dmp': No such file or directory

$ expdp dba1/oracle_4U dumpfile=HREXP%U.dmp

directory=DATA_PUMP_DIR logfile=hrexp.log SCHEMAS=HR

Export: Release 12.1.0.2.0 - Production on Thu Oct 16 10:39:28 2014

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Starting "DBA1"."SYS_EXPORT_SCHEMA_01": dba1/********

dumpfile=HREXP%U.dmp directory=DATA_PUMP_DIR logfile=hrexp.log SCHEMAS=HR

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 448 KB

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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type

SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 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/VIEW/VIEW

Processing object type

SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type

SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

. . exported "HR"."COUNTRIES" 6.437 KB 25 rows . . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows . . exported "HR"."EMPLOYEES" 17.06 KB 107 rows . . exported "HR"."JOBS" 7.085 KB 19 rows . . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows . . exported "HR"."LOCATIONS" 8.414 KB 23 rows . . exported "HR"."REGIONS" 5.523 KB 4 rows

Master table "DBA1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

**************************************************************** Dump file set for DBA1.SYS_EXPORT_SCHEMA_01 is:

/u01/app/oracle/admin/orcl/dpdump/HREXP01.dmp

Job "DBA1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Oct 16 10:41:12 2014 elapsed 0 00:01:38

3. Now, import the EMPLOYEES table from the exported HR schema into the DBA1 schema. a. Enter the following entire command string. Do not press Enter before reaching the end

of the command:

$ impdp dba1/oracle_4U DIRECTORY=data_pump_dir

DUMPFILE=HREXP01.dmp REMAP_SCHEMA=hr:dba1 TABLES=hr.employees LOGFILE=empimport.log

Import: Release 12.1.0.2.0 - Production on Thu Oct 16 10:46:14 2014

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Master table "DBA1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "DBA1"."SYS_IMPORT_TABLE_01": dba1/******** DIRECTORY=data_pump_dir DUMPFILE=HREXP01.dmp

REMAP_SCHEMA=hr:dba1 TABLES=hr.employees LOGFILE=empimport.log Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "DBA1"."EMPLOYEES" 17.06 KB 107 rows

Processing object type

SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT 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/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT:"DBA1"."EMP_DEPT_FK" failed to create with error:

ORA-00942: table or view does not exist Failing sql is:

ALTER TABLE "DBA1"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DBA1"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE

ORA-39083: Object type REF_CONSTRAINT:"DBA1"."EMP_JOB_FK" failed to create with error:

ORA-00942: table or view does not exist Failing sql is:

ALTER TABLE "DBA1"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DBA1"."JOBS" ("JOB_ID") ENABLE

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type

SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER ORA-39082: Object type TRIGGER:"DBA1"."UPDATE_JOB_HISTORY" created with compilation warnings

ORA-39082: Object type TRIGGER:"DBA1"."SECURE_EMPLOYEES" created with compilation warnings

Job "DBA1"."SYS_IMPORT_TABLE_01" completed with 4 error(s) at Thu Oct 16 10:46:49 2014 elapsed 0 00:00:34

Note: You may see errors on constraints and triggers not being created because only the EMPLOYEES table is imported and not the other objects in the schema. These errors are expected.

b. You can also verify that the import succeeded by connecting as DBA1 and selecting data from the EMPLOYEES table.

$ sqlplus dba1/oracle_4U …

SQL> SELECT count(*) FROM employees;

COUNT(*) --- 107

SQL> exit $

In document D78846GC20_ag (Page 163-168)