Background: In the recent past, you received a number of questions about the HR schema. To analyze them without interfering in daily activities, you decide to use Data Pump export to export the HR schema to a file. When you perform the export, you are not sure into which database you will be importing this schema.
In the end, you learn that the only database for which management approves an import is the orcl database. So you perform the import with Data Pump import, remapping the HR schema to the DBA1 schema.
Then you receive two data load requests for which you decide to use SQL*Loader.
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.
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.
Use the Data Pump export utility to export the HR schema. Specify the DBA1 user to execute the export operation.
Now, import the EMPLOYEES table from the exported HR schema into the DBA1 schema.
sqlplus /as sysdba SELECT*from dba_directories WHERE directory_name ='DATA_PUMP_DIR'; alter session set container=emrep; grant read on directory data_pump_dir to dba1; grant write on directory data_pump_dir to dba1;
Import: Release 12.2.0.1.0 - Production on Tue Feb 4 22:23:55 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "DBA1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "DBA1"."SYS_IMPORT_TABLE_01": dba1/********@emrep 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.08 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"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"DBA1"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "DBA1"."SYS_IMPORT_TABLE_01" completed with 4 error(s) at Tue Feb 4 22:24:18 2020 elapsed 0 00:00:22
As the OE user, use SQLLoader to load the PRODUCT_DESCRIPTIONS table from the product_descriptions.dat data file in Express Mode.
Warning: DO NOT execute this SQL*Loader command a second time without first executing the cleanup script in step 3. Duplicate rows will be loaded and the Primary Key Index will become unusable.
As the OE user, load data into the **INVENTORIES table by using SQL*Loader command line. The lab_16_02_02.dat data file contains rows of data for the PRODUCT_ON_HAND table. The lab_16_02_02.ctl** file is the control file for this load.
Optionally, view the lab_16_02_02.dat and lab_16_02_02.ctl files to learn more about their structure before going further.
Execute the $LABS/P16/lab_16_cleanup.sh script to remove the rows and files generated by this practice.
SQL*Loader: Release 12.2.0.1.0 - Production on Wed Feb 5 00:52:12 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: PRODUCT_DESCRIPTIONS Path used: External Table, DEGREE_OF_PARALLELISM=AUTO SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file product_descriptions.dat ORA-01031: insufficient privileges SQL*Loader-579: switching to direct path for the load SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE Express Mode Load, Table: PRODUCT_DESCRIPTIONS Path used: Direct
Check the log file: product_descriptions.log for more information about the load.
SQL> !cat product_descriptions.log
SQL*Loader: Release 12.2.0.1.0 - Production on Wed Feb 5 00:52:12 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: PRODUCT_DESCRIPTIONS Data File: product_descriptions.dat Bad File: product_descriptions_%p.bad Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table
Table PRODUCT_DESCRIPTIONS, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- PRODUCT_ID FIRST * , CHARACTER LANGUAGE_ID NEXT * , CHARACTER TRANSLATED_NAME NEXT * , CHARACTER TRANSLATED_DESCRIPTION NEXT * , CHARACTER
Generated control file for possible reuse: OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM) LOAD DATA INFILE 'product_descriptions' APPEND INTO TABLE PRODUCT_DESCRIPTIONS FIELDS TERMINATED BY "," ( PRODUCT_ID, LANGUAGE_ID, TRANSLATED_NAME, TRANSLATED_DESCRIPTION ) End of generated control file for possible reuse.
------------------------------------------------------------------------ SQL*Loader-579: switching to direct path for the load SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE ------------------------------------------------------------------------
Express Mode Load, Table: PRODUCT_DESCRIPTIONS Data File: product_descriptions.dat Bad File: product_descriptions.bad Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: Direct
Table PRODUCT_DESCRIPTIONS, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- PRODUCT_ID FIRST * , CHARACTER LANGUAGE_ID NEXT * , CHARACTER TRANSLATED_NAME NEXT * , CHARACTER TRANSLATED_DESCRIPTION NEXT * , CHARACTER
Generated control file for possible reuse: OPTIONS(DIRECT=TRUE) LOAD DATA INFILE 'product_descriptions' APPEND INTO TABLE PRODUCT_DESCRIPTIONS FIELDS TERMINATED BY "," ( PRODUCT_ID, LANGUAGE_ID, TRANSLATED_NAME, TRANSLATED_DESCRIPTION ) End of generated control file for possible reuse.
Table PRODUCT_DESCRIPTIONS: 8 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.
Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576
Total logical records skipped: 0 Total logical records read: 8 Total logical records rejected: 0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 1 Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Wed Feb 05 00:52:12 2020 Run ended on Wed Feb 05 00:52:14 2020
Elapsed time was: 00:00:01.55 CPU time was: 00:00:00.06
SQL> SELECT count(*) FROM PRODUCT_DESCRIPTIONS WHERE product_id > 4000;
Check the log file: lab_16_02_02.log for more information about the load.
SQL> !cat lab_16_02_02.log
SQL*Loader: Release 12.2.0.1.0 - Production on Wed Feb 5 00:56:04 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Control File: lab_16_02_02.ctl Data File: lab_16_02_02.dat Bad File: lab_16_02_02.bad Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional
Table OE.INVENTORIES, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- WAREHOUSE_ID FIRST * , CHARACTER PRODUCT_ID NEXT * , CHARACTER QUANTITY_ON_HAND NEXT * , CHARACTER
Table OE.INVENTORIES: 83 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.