- 3. Oracle_Loader External Tables
- 3.1 In the scripts directory, you will find prod_master.dat and prod_master.ctl. Using the information found in these files, create an external table names PROD_MASTER in the SH schema of the PROD database.
In the script directory, you will find the prod_master.dat script and the prod_master.ctl script. Use the information found in these files to create an external table named PROD_MASTER. In the SH solution of the PROD database
- 1. First find the two scripts and check the content;
[Oracle @ ocm1 ~] $ Cd/home/oracle/script/[oracle @ ocm1 script] $ lltotal 12-rw-r -- r -- 1 oracle oinstall 64 Mar 19 create_bishhr.sql-rw-r -- r -- 1 root 154 Mar 27 prod_master.ctl-rw-r -- r -- 1 root 56 Mar 27 :01 prod_master.dat [oracle @ ocm1 script] $
Cat prod_master.ctlLoad datainfile '/home/oracle/script/prod_master.dat' into table
Sh. exm1Fields terminated by whitespace -- insert Table sh. exm1, interval trailing nullcols (emp_no, dept_no, name, num)
[Oracle @ ocm1 script] $
Cat prod_master.dat1 1 tom 12 2 rose 23 1 jone 24 3 jack 35 2 jacky 4
[Oracle @ ocm1 script] $
Reference online document: Utilities ==> 13 The ORACLE_LOADER Access Driverhttp: // docs.oracle.com/cd/b19306_01/server.102/b14215/et_params.htm1_i1012274
- 2. Use sqlldr to generate statements for creating external tables.
[Oracle @ ocm1 script] $
Sqlldr
SQL * Loader: Release 10.2.0.1.0-Production on Thu Mar 27 11:23:24 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Usage: SQLLDR keyword = value [, keyword = value,...]
Valid Keywords:
Userid-- ORACLE username/password
Control-- Control file name log -- log file name bad -- bad file name data -- data file name discard -- discard file name discardmax -- number of discards to allow (Default all) skip -- number of logical records to skip (Default 0) load -- number of logical records to load (Default all) errors -- number of errors to allow (Default 50) rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- size of conventional path bind array in bytes (Default 256000) silent -- suppress messages during run (header, feedback, errors, discards, partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- file to allocate extents from catalog -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576)
External_table-- Use external table for load; NOT_USED,
GENERATE_ONLY, EXECUTE (Default NOT_USED) columnarrayrows -- number of rows for direct path column array (Default 5000) streamsize -- size of direct path stream buffer in bytes (Default 256000) multithreading -- use multithreading in direct path resumable -- enable or disable resumable for current session (Default FALSE) resumable_name -- text string to help identify resumable statementresumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200) date_cache -- size (in entries) of date conversion cache (Default 1000)
Please note: Command-line parameters may be specified either byposition or by keywords. an example of the former case is 'sqlldrscott/tiger foo'; an example of the latter is 'sqlldr control = foouserid = scott/tiger '. one may specify parameters by position beforebut not after parameters specified by keywords. for example, 'sqlldr scott/tiger control = foo logfile = log' is allowed, but 'sqlldr scott/tiger control = foo log' is not, even though theposition of the parameter 'log' is correct. [oracle @ ocm1 script] $
Sqlldr userid = sh/sh control = prod_master.ctl external_table = GENERATE_ONLY
- 3. Execute sqlldr to generate an External table. However, create the sh. exm1 table first. Otherwise, an error is reported.
The statement used to create an external table is placed in the log, and then the log is checked to view the statement used to create the table.
[Oracle @ ocm1 script] $ sqlldr userid = sh/sh control = prod_master.ctl external_table = GENERATE_ONLY
SQL * Loader: Release 10.2.0.1.0-Production on Thu Mar 27 12:04:00 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
SQL * Loader-941: Error during describe of table SH. EXM1
ORA-04043: object SH. EXM1 does not exist[Oracle @ ocm1 script] $
A. Create a table named sh. exm1 first.
SH @ PROD>
Create table sh. exm1 (emp_no int, dept_no int, name varchar2 (30), num int );
Table created.
B. Import again:
[Oracle @ ocm1 script] $
Sqlldr userid = sh/sh control = prod_master.ctl external_table = GENERATE_ONLY
SQL * Loader: Release 10.2.0.1.0-Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
[Oracle @ ocm1 script] $
C. View logs:
[Oracle @ ocm1 script] $
Cat prod_master.log
SQL * Loader: Release 10.2.0.1.0-Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Control File: prod_master.ctlData File:/home/oracle/script/prod_master.dat Bad File: prod_master.bad Discard File: none specified (Allow all discards)
Number to load: ALLNumber to skip: 0 Errors allowed: 50 Continuation: none specifiedPath used: External Table
Table SH. EXM1, loaded from every logical record. Insert option in effect for this table: inserttrailing nullcols option in effect
Column Name Position Len Term Encl Datatype =---------- ----- ---- --------------------- EMP_NO FIRST * wht character DEPT_NO NEXT * wht character name next * wht character num next * WHT CHARACTER
Create directory statements needed for files ------------------------------------------------------------------------ create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script'
Create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script /'
Create table statement for external table: Creating create table "SYS_SQLLDR_X_EXT_EXM1" ("EMP_NO" NUMBER (38), "DEPT_NO" NUMBER (38), "NAME" VARCHAR2 (30 ), "NUM" NUMBER (38) ORGANIZATION external (TYPE oracle_loader default directory named access parameters (records delimited by newline characterset US7ASCII BADFILE 'sys _ sqlldr_xt_tmpdir_00000': 'prod _ master. bad 'logfile' prod _ master. log_xt 'readsize 1048576 fields terminated by whitespace ldrtrim missing field values are null reject rows with all null fields ("EMP_NO" CHAR (255) terminated by whitespace, "DEPT_NO" CHAR (255) terminated by whitespace, "NAME" CHAR (255) terminated by whitespace, "NUM" CHAR (255) terminated by whitespace) location ('prod _ master. dat ') REJECT LIMIT UNLIMITED
------- The red part above is the statement used to create an external table.
INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT/* + append */into sh. EXM1 (EMP_NO, DEPT_NO, NAME, NUM) SELECT "EMP_NO", "DEPT_NO", "NAME", "NUM" FROM "SYS_SQLLDR_X_EXT_EXM1"
------ The preceding statements are used to load data.
Statements to cleanup objects created by previous statements: Dropping drop table "SYS_SQLLDR_X_EXT_EXM1" drop directory SYS_SQLLDR_XT_TMPDIR_00000DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
------ The preceding statements are used to delete a temporary table.
Run began on Thu Mar 27 12:04:44 2014Run ended on Thu Mar 27 12:04:45 2014
Elapsed time was: 00:00:00. 91CPU time was: 00:00:00. 23 [oracle @ ocm1 script] $
- 4. Create a directory under the SH user and create an external table
Create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script /'
A. Create a directory
SH @ PROD> create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'; create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' * ERROR at line 1: ORA-01031: insufficient privileges
SH @ PROD> conn/as sysdbaConnected. SYS @ PROD> create directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' 2;
Directory created.
SYS @ PROD> grant read, write on directory SYS_SQLLDR_XT_TMPDIR_00000 to sh;
Grant succeeded.
SYS @ PROD>
Or, directly authorize sys to create a directory to THE sh user, and then create SYS @ PROD> grant create any directory to sh; SH @ prodcreate directory SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script /'
B. Create an external TABLE statement
Create table"
PROD_MASTER"--- Change to the NAME (" EMP_NO "NUMBER (38)," DEPT_NO "NUMBER (38)," NAME "VARCHAR2 (30 ), "NUM" NUMBER (38) ORGANIZATION external (TYPE oracle_loader DEFAULT DIRECTORY
SYS_SQLLDR_XT_TMPDIR_00000Access parameters (records delimited by newline characterset US7ASCII BADFILE'
SYS_SQLLDR_XT_TMPDIR_00000': 'Prod _ master. bad 'logfile' prod _ master. log_xt 'readsize 1048576 fields terminated by whitespace ldrtrim missing field values are null reject rows with all null fields ("EMP_NO" CHAR (255) terminated by whitespace, "DEPT_NO" CHAR (255) terminated by whitespace, "NAME" CHAR (255) terminated by whitespace, "NUM" CHAR (255) terminated by whitespace) location ('prod _ master. dat ') REJECT LIMIT UNLIMITED
C. Run the SQL plus command using SH.
SH@ PROD> create table "PROD_MASTER" 2 (3 "EMP_NO" NUMBER (38), 4 "DEPT_NO" NUMBER (38), 5 "NAME" VARCHAR2 (30 ), 6 "NUM" NUMBER (38) 7) 8 ORGANIZATION external 9 (10 TYPE oracle_loader 11 default directory named 12 access parameters 13 (14 records delimited by newline characterset US7ASCII 15 BADFILE 'sys _ sqlldr_xt_tmpdir_00000': 'prod _ master. bad '16 logfile' prod _ master. log_xt '17 READSIZE 1048576 18 fields terminated by whitespace ldrtrim 19 missing field values are null 20 reject rows with all null fields 21 (22 "EMP_NO" CHAR (255) 23 terminated by whitespace, 24 "DEPT_NO" CHAR (255) 25 terminated by whitespace, 26 "NAME" CHAR (255) 27 terminated by whitespace, 28 "NUM" CHAR (255) 29 terminated by whitespace) 30 31) 32 location 33 (34 'prod _ master. dat '35) 36) reject limit unlimited 37;
Table created.
SH @ PROD> desc PROD_MASTER Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_NO NUMBER (38) DEPT_NO NUMBER (38) NAME VARCHAR2 (30) num number (38)
SH @ PROD> select * from PROD_MASTER;
EMP_NO DEPT_NO name num ---------- ------------------------------ ---------- 1 1 tom 1 2 2 rose 2 3 1 jone 2 4 3 jack 3 5 2 jacky 4
5 rows selected.
SH @ PROD>
