课程第8次（2018-10-18星期四）

botang

用图形界面，做控制文件模板。

日期的格式和字符集往往要改：

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'ccb.dat'
   
4. APPEND
   
5. INTO TABLE HR.T_BANK_INVOICE
   
6. trailing nullcols
   
7. (
   
8.   ACCOUNT_NUMBER "4367421823250099999",
   
9.   SUB_NUMBER "00000",
   
10.   TRANSAC_DATE  position(1:8)   DATE(8)  "YYYYMMDD"  terminated by ','  ,
    
11.   TRANSAC_ADDRESS position(10)  CHAR    terminated by ',',
    
12.   TRANSAC_COMMENT CHAR  TERMINATED BY whitespace,
    
13.   CURRENCY CHAR terminated by ',',
    
14.   IN_AMOUNT decimal EXTERNAL  terminated by ',',
    
15.   OUT_AMOUNT decimal  EXTERNAL  terminated by ',',
    
16.   BALANCE decimal EXTERNAL  terminated by ',',
    
17.   OPPOSITE_NUMBER terminated by ',',
    
18.   OPPOSITE_USERNAME terminated by ','
    
19.   )
    

复制代码

1. [oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=ccb.ctl direct=true

复制代码

1. [oracle@station90 ~]$ ls -l ccb.*
   
2. -rw-r--r-- 1 oracle oinstall  135 10月 18 21:12 ccb.bad
   
3. -rw-r--r-- 1 oracle oinstall  611 10月 18 21:12 ccb.ctl
   
4. -rwxr-xr-x 1 root   root      324 10月 18 20:34 ccb.dat
   
5. -rw-r--r-- 1 oracle oinstall 3042 10月 18 21:12 ccb.log
   

复制代码

ccb.log :

1. 
   
2. SQL*Loader: Release 11.2.0.3.0 - Production on Thu Oct 18 21:12:25 2018
   
3. 
   
4. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
5. 
   
6. Control File:   ccb.ctl
   
7. Character Set ZHS16GBK specified for all input.
   
8. 
   
9. Data File:      ccb.dat
   
10.   Bad File:     ccb.bad
    
11.   Discard File:  none specified
    
12. 
    
13. (Allow all discards)
    
14. 
    
15. Number to load: ALL
    
16. Number to skip: 0
    
17. Errors allowed: 50
    
18. Continuation:    none specified
    
19. Path used:      Direct
    
20. 
    
21. Table HR.T_BANK_INVOICE, loaded from every logical record.
    
22. Insert option in effect for this table: APPEND
    
23. TRAILING NULLCOLS option in effect
    
24. 
    
25.    Column Name                  Position   Len  Term Encl Datatype
    
26. ------------------------------ ---------- ----- ---- ---- ---------------------
    
27. ACCOUNT_NUMBER                      FIRST     1           CHARACTER            
    
28.     SQL string for column : "4367421823250099999"
    
29. SUB_NUMBER                           NEXT     1           CHARACTER            
    
30.     SQL string for column : "00000"
    
31. TRANSAC_DATE                          1:8     8   ,       DATE YYYYMMDD        
    
32. TRANSAC_ADDRESS                        10     *   ,       CHARACTER            
    
33. TRANSAC_COMMENT                      NEXT     *  WHT      CHARACTER            
    
34. CURRENCY                             NEXT     *   ,       CHARACTER            
    
35. IN_AMOUNT                            NEXT     *   ,       CHARACTER            
    
36. OUT_AMOUNT                           NEXT     *   ,       CHARACTER            
    
37. BALANCE                              NEXT     *   ,       CHARACTER            
    
38. OPPOSITE_NUMBER                      NEXT     *   ,       CHARACTER            
    
39. OPPOSITE_USERNAME                    NEXT     *   ,       CHARACTER            
    
40. 
    
41. Record 1: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
42. Multibyte character error.
    
43. Record 2: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
44. Multibyte character error.
    
45. Record 3: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
46. Multibyte character error.
    
47. Record 4: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
48. Multibyte character error.
    
49. Record 9: Discarded - all columns null.
    
50. 
    
51. Table HR.T_BANK_INVOICE:
    
52.   4 Rows successfully loaded.
    
53.   4 Rows not loaded due to data errors.
    
54.   0 Rows not loaded because all WHEN clauses were failed.
    
55.   1 Row not loaded because all fields were null.
    
56. 
    
57.   Date cache:
    
58.    Max Size:      1000
    
59.    Entries :         4
    
60.    Hits    :         0
    
61.    Misses  :         0
    
62. 
    
63. Bind array size not used in direct path.
    
64. Column array  rows :    5000
    
65. Stream buffer bytes:  256000
    
66. Read   buffer bytes: 1048576
    
67. 
    
68. Total logical records skipped:          0
    
69. Total logical records read:             9
    
70. Total logical records rejected:         4
    
71. Total logical records discarded:        1
    
72. Total stream buffers loaded by SQL*Loader main thread:        1
    
73. Total stream buffers loaded by SQL*Loader load thread:        0
    
74. 
    
75. Run began on Thu Oct 18 21:12:25 2018
    
76. Run ended on Thu Oct 18 21:12:26 2018
    
77. 
    
78. Elapsed time was:     00:00:00.52
    
79. CPU time was:         00:00:00.01

复制代码

1. [oracle@station90 dir1]$ sqlldr  
   
2. 
   
3. SQL*Loader: Release 11.2.0.3.0 - Production on Thu Oct 18 21:42:45 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. 
   
8. Usage: SQLLDR keyword=value [,keyword=value,...]
   
9. 
   
10. Valid Keywords:
    
11. 
    
12.     userid -- ORACLE username/password           
    
13.    control -- control file name                  
    
14.        log -- log file name                     
    
15.        bad -- bad file name                     
    
16.       data -- data file name                     
    
17.    discard -- discard file name                  
    
18. discardmax -- number of discards to allow          (Default all)
    
19.       skip -- number of logical records to skip    (Default 0)
    
20.       load -- number of logical records to load    (Default all)
    
21.     errors -- number of errors to allow            (Default 50)
    
22.       rows -- number of rows in conventional path bind array or between direct path data saves
    
23.                (Default: Conventional path 64, Direct path all)
    
24.   bindsize -- size of conventional path bind array in bytes  (Default 256000)
    
25.     silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    
26.     direct -- use direct path                      (Default FALSE)
    
27.    parfile -- parameter file: name of file that contains parameter specifications
    
28.   parallel -- do parallel load                     (Default FALSE)
    
29.       file -- file to allocate extents from      
    
30. skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
    
31. skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
    
32. commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
    
33.   readsize -- size of read buffer                  (Default 1048576)
    
34. external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
    
35. columnarrayrows -- number of rows for direct path column array  (Default 5000)
    
36. streamsize -- size of direct path stream buffer in bytes  (Default 256000)
    
37. multithreading -- use multithreading in direct path  
    
38. resumable -- enable or disable resumable for current session  (Default FALSE)
    
39. resumable_name -- text string to help identify resumable statement
    
40. resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
    
41. date_cache -- size (in entries) of date conversion cache  (Default 1000)
    
42. no_index_errors -- abort load on any index errors  (Default FALSE)
    
43. 
    
44. PLEASE NOTE: Command-line parameters may be specified either by
    
45. position or by keywords.  An example of the former case is 'sqlldr
    
46. scott/tiger foo'; an example of the latter is 'sqlldr control=foo
    
47. userid=scott/tiger'.  One may specify parameters by position before
    
48. but not after parameters specified by keywords.  For example,
    
49. 'sqlldr scott/tiger control=foo logfile=log' is allowed, but
    
50. 'sqlldr scott/tiger control=foo log' is not, even though the
    
51. position of the parameter 'log' is correct.
    
52. [oracle@station90 dir1]$
    

复制代码

1. sqlldr  hr/oracle_4U control=tpasswd.ctl  log=tpasswd.sql  external_table=GENERATE_ONLY
   

复制代码

SYS：

1. create directory dir1 as '/home/oracle/dir1';
   
2. 
   
3. grant read,write on directory dir1 to hr;

复制代码

1. CREATE TABLE tpasswd
   
2. (
   
3.   "I_USERNAME" VARCHAR2(30),
   
4.   "I_PASSWORD" CHAR(1),
   
5.   "I_UID" NUMBER(6),
   
6.   "I_GID" NUMBER(6),
   
7.   "I_COMMENT" VARCHAR2(40),
   
8.   "I_HOME" VARCHAR2(50),
   
9.   "I_SHELL" VARCHAR2(20)
   
10. )
    
11. ORGANIZATION external
    
12. (
    
13.   TYPE oracle_loader
    
14.   DEFAULT DIRECTORY dir1
    
15.   ACCESS PARAMETERS
    
16.   (
    
17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    
18.     BADFILE 'passwd_%a_%p.bad'
    
19.     LOGFILE 'passwd_%a_%p.log'
    
20.     READSIZE 1048576
    
21.     FIELDS TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"' LDRTRIM
    
22.     MISSING FIELD VALUES ARE NULL
    
23.     REJECT ROWS WITH ALL NULL FIELDS
    
24.     (
    
25.       "I_USERNAME" CHAR(255)
    
26.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
27.       "I_PASSWORD" CHAR(255)
    
28.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
29.       "I_UID" CHAR(255)
    
30.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
31.       "I_GID" CHAR(255)
    
32.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
33.       "I_COMMENT" CHAR(255)
    
34.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
35.       "I_HOME" CHAR(255)
    
36.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
37.       "I_SHELL" CHAR(255)
    
38.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"'
    
39.     )
    
40.   )
    
41.   location
    
42.   (
    
43.     'passwd.dat'
    
44.   )
    
45. )parallel  REJECT LIMIT UNLIMITED;
    

复制代码

jarlean

老师，我创建外部表后，执行查询，提示文件未找到，我将path用/home/oracle/practice/less08与/home/oracle/practice/less08/，都提示一样的错误，这是什么情况？


创建外部表的脚本：

1. create directory T_EXT_CSDN_PATH as '/home/oracle/practice/less08/';
   
2. grant read,write on directory t_ext_csdn_path to test;

复制代码

1. CREATE TABLE "T_EXT_PASSWD"
   
2. (
   
3.   "I_USERNAME" VARCHAR2(30),
   
4.   "I_PASSWORD" VARCHAR2(1),
   
5.   "I_UID" NUMBER(6),
   
6.   "I_GID" NUMBER(6),
   
7.   "I_COMMENT" VARCHAR2(40),
   
8.   "I_HOME" VARCHAR2(50),
   
9.   "I_SHARE" VARCHAR2(20)
   
10. )
    
11. ORGANIZATION external
    
12. (
    
13.   TYPE oracle_loader
    
14.   DEFAULT DIRECTORY T_EXT_CSDN_PATH
    
15.   ACCESS PARAMETERS
    
16.   (
    
17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    
18.     LOGFILE 'passwd_ext.log_xt'
    
19.     READSIZE 1048576
    
20.     FIELDS LDRTRIM
    
21.     MISSING FIELD VALUES ARE NULL
    
22.     REJECT ROWS WITH ALL NULL FIELDS
    
23.     (
    
24.       "I_USERNAME" CHAR(255)
    
25.         TERMINATED BY ":",
    
26.       "I_PASSWORD" CHAR(255)
    
27.         TERMINATED BY ":",
    
28.       "I_UID" CHAR(255)
    
29.         TERMINATED BY ":",
    
30.       "I_GID" CHAR(255)
    
31.         TERMINATED BY ":",
    
32.       "I_COMMENT" CHAR(255)
    
33.         TERMINATED BY ":",
    
34.       "I_HOME" CHAR(255)
    
35.         TERMINATED BY ":",
    
36.       "I_SHARE" CHAR(255)
    
37.         TERMINATED BY WHITESPACE
    
38.     )
    
39.   )
    
40.   location
    
41.   (
    
42.     'passwd.dat'
    
43.   )
    
44. )REJECT LIMIT UNLIMITED;

复制代码

botang

jarlean 发表于 2018-10-20 23:48
老师，我创建外部表后，执行查询，提示文件未找到，我将path用/home/oracle/practice/less08与/home/oracle ...

没有什么其他的，检查一下passwd.dat文件是否存在。
select  * from dba_external_table_locations;

jarlean

我试了下，是通过tns连接串(sqlplus test@orcl)的方式，查询不到，通过本地连接(sqlplus /)的方式是正常的。查看directory定义，以及目录权限，都没发现问题。

1. 
   
2. OWNER        DIRECTORY_NAME          DIRECTORY_PATH
   
3. SYS                T_EXT_CSDN_PATH           /home/oracle/practice/less08/

复制代码

1. [oracle@oracle11gasm less08]$ ll /home/oracle/practice/less08/
   
2. -rw-r--r-- 1 oracle oinstall      1531 Oct 20 07:46 passwd.dat

复制代码

jarlean

是权限问题，放到$ORACLE_HOME后面的目录就可以正常访问了。结帖。