This practice covers
using direct load insert operations.
Instructions
1. As user HR, create a table employees2 as a select
of the employees table where 1 = 2 so that an empty table is created. Validate the result by performing a select
from the employees2 table
|
SQL> create table
employees2 2 as
select * from employees 3 where
1 = 2; Table created. SQL> select
* from employees2; no rows
selected |
2. Perform a direct-load insert into employees2 from employees
and query employees2 to verify the load.
|
SQL> INSERT /*+ append */ INTO employees2 2 NOLOGGING 3 SELECT * from employees; 101 rows created. SQL>
COMMIT; SQL> SELECT employee_id, first_name, last_name 2 FROM employees2; EMPLOYEE_ID FIRST_NAME LAST_NAME -----------
-------------------- ------------------------- 100 Steven King 101 Neena Kochhar ... 205 Shelley Higgins 206 William Gietz 101
rows selected. |
3. Truncate the employees2
table once again. Then perform a select
from the employees2 table to verify no data is in the table.
|
SQL> truncate table employees2; Table truncated. SQL>
select * from employees2; no rows selected |
4. Restore the data with a parallel direct-load insert from the
employees table. Specify a
degree of parallelism of two. Verify
the data.
|
SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL>
INSERT /*+ parallel(employees2,2) */ 2 INTO employees2 NOLOGGING 3 SELECT * FROM employees; 101
rows created. SQL> SELECT employee_id, first_name,
last_name 2
FROM
employees2; EMPLOYEE_ID
FIRST_NAME LAST_NAME -----------
-------------------- ------------------------- 100 Steven King 101 Neena Kochhar … 101 rows
selected. |
This
ends this practice.
This practice covers the creation of regular and temporary tables.
INSTRUCTIONS:
1. Create the department2 table using the following SQL statement.
|
SQL> CONNECT hr/hr Connected. SQL> create table departments2 2 as select * from departments 3 where 1 = 2; Table created. |
2. Run SQL*Loader using the Conventional Path method to load data into the DEPARTMENTS2 table using the control file shown below. Name the control file case1.ctl
|
-- case1.ctl LOAD DATA INFILE * INTO TABLE DEPARTMENTS2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) BEGINDATA 10,Administration,200,1700 20,Marketing,201,1800 30,Purchasing,14,1700 40,Human Resources,203,2400 50,Shipping,121,1500 60,IT,103,1400 70,Public Relations,204,2700 80,Sales,145,2500 90,Executive,100,1700 100,Finance,108,1700 110,Accounting,205,1700 120,Treasury,,1700 130,Corporate Tax,,1700 140,Control And Credit,,1700 150,Shareholder Services,,1700 160,Benefits,,1700 170,Manufacturing,,1700 180,Construction,,1700 190,Contracting,,1700 200,Operations,,1700 210,IT Support,,1700 $ sqlldr hr/hr control=case1.ctl log=$HOME/lcase1.log SQL*Loader: Release 9.0.1.1.1 - Production on Tue Mar 19 20:18:10 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Commit point reached - logical record count 27 |
3. Examine the log file using operating system command. .
|
$ more $HOME/lcase1.log SQL*Loader: Release 9.0.1.1.1 - Production on Tue Mar 19 20:18:10 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Control File: case1.ctl Data File: case1.ctl Bad File: case1.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 DEPARTMENTS2, loaded from every logical record. Insert option in effect for this table: INSERT … |
4. Query the DEPARTMENTS2 table to check the data.
|
SQL> CONNECT hr/hr Connected. SQL> select * from departments2; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- --------------------- ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 … 270 Payroll 1700 27 rows selected. SQL> |
5. Truncate the department table to remove all of the rows.
|
SQL> truncate table departments2; Table truncated. |
6. Run SQL*Loader in Direct-Path mode to load data into the DEPARTMENTS2 table using the case2.ctl control file as shown below and the case2.dat file as listed below..
|
-- case2.ctl LOAD DATA INFILE 'case2.dat' INTO TABLE DEPARTMENTS2 ( DEPARTMENT_ID POSITION(01:04) INTEGER EXTERNAL, DEPARTMENT_NAME POSITION(06:35) CHAR, MANAGER_ID POSITION(37:41) INTEGER EXTERNAL, LOCATION_ID POSITION(43:46) INTEGER EXTERNAL) |
Note: this is the case2.dat file
|
10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 |
|
$ sqlldr hr/hr control=case2.ctl direct=true log=$HOME/lcase2.log SQL*Loader: Release 9.0.1.1.1 - Production on Tue Mar 19 21:02:49 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Load completed - logical record count 27. |
7. Examine the log file using operating system command.
|
$ more $HOME/lcase2.log SQL*Loader: Release 9.0.1.1.1 - Production on Tue Mar 19 21:02:49 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Control File: case2.ctl Data File: case2.dat Bad File: case2.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 DEPARTMENTS2, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype --------------------- ---------- ----- ---- ---- --------- DEPARTMENT_ID 1:4 4 CHARACTER …
|
8. Query the DEPARTMENTS2 table to check the data.
|
SQL> CONNECT hr/hr Connected. SQL> select * from departments2; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- --------------------- ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 … 270 Payroll 1700 27 rows selected. SQL> |
