Bo's Oracle Station

查看: 2841|回复: 4

课程第8次(2018-10-18星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-10-18 21:15:13 | 显示全部楼层 |阅读模式
用图形界面,做控制文件模板。

日期的格式和字符集往往要改:
  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. SQL*Loader: Release 11.2.0.3.0 - Production on Thu Oct 18 21:12:25 2018

  2. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

  3. Control File:   ccb.ctl
  4. Character Set ZHS16GBK specified for all input.

  5. Data File:      ccb.dat
  6.   Bad File:     ccb.bad
  7.   Discard File:  none specified

  8. (Allow all discards)

  9. Number to load: ALL
  10. Number to skip: 0
  11. Errors allowed: 50
  12. Continuation:    none specified
  13. Path used:      Direct

  14. Table HR.T_BANK_INVOICE, loaded from every logical record.
  15. Insert option in effect for this table: APPEND
  16. TRAILING NULLCOLS option in effect

  17.    Column Name                  Position   Len  Term Encl Datatype
  18. ------------------------------ ---------- ----- ---- ---- ---------------------
  19. ACCOUNT_NUMBER                      FIRST     1           CHARACTER            
  20.     SQL string for column : "4367421823250099999"
  21. SUB_NUMBER                           NEXT     1           CHARACTER            
  22.     SQL string for column : "00000"
  23. TRANSAC_DATE                          1:8     8   ,       DATE YYYYMMDD        
  24. TRANSAC_ADDRESS                        10     *   ,       CHARACTER            
  25. TRANSAC_COMMENT                      NEXT     *  WHT      CHARACTER            
  26. CURRENCY                             NEXT     *   ,       CHARACTER            
  27. IN_AMOUNT                            NEXT     *   ,       CHARACTER            
  28. OUT_AMOUNT                           NEXT     *   ,       CHARACTER            
  29. BALANCE                              NEXT     *   ,       CHARACTER            
  30. OPPOSITE_NUMBER                      NEXT     *   ,       CHARACTER            
  31. OPPOSITE_USERNAME                    NEXT     *   ,       CHARACTER            

  32. Record 1: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
  33. Multibyte character error.
  34. Record 2: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
  35. Multibyte character error.
  36. Record 3: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
  37. Multibyte character error.
  38. Record 4: Rejected - Error on table HR.T_BANK_INVOICE, column ACCOUNT_NUMBER.
  39. Multibyte character error.
  40. Record 9: Discarded - all columns null.

  41. Table HR.T_BANK_INVOICE:
  42.   4 Rows successfully loaded.
  43.   4 Rows not loaded due to data errors.
  44.   0 Rows not loaded because all WHEN clauses were failed.
  45.   1 Row not loaded because all fields were null.

  46.   Date cache:
  47.    Max Size:      1000
  48.    Entries :         4
  49.    Hits    :         0
  50.    Misses  :         0

  51. Bind array size not used in direct path.
  52. Column array  rows :    5000
  53. Stream buffer bytes:  256000
  54. Read   buffer bytes: 1048576

  55. Total logical records skipped:          0
  56. Total logical records read:             9
  57. Total logical records rejected:         4
  58. Total logical records discarded:        1
  59. Total stream buffers loaded by SQL*Loader main thread:        1
  60. Total stream buffers loaded by SQL*Loader load thread:        0

  61. Run began on Thu Oct 18 21:12:25 2018
  62. Run ended on Thu Oct 18 21:12:26 2018

  63. Elapsed time was:     00:00:00.52
  64. CPU time was:         00:00:00.01
复制代码
  1. [oracle@station90 dir1]$ sqlldr  

  2. SQL*Loader: Release 11.2.0.3.0 - Production on Thu Oct 18 21:42:45 2018

  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  4. Usage: SQLLDR keyword=value [,keyword=value,...]

  5. Valid Keywords:

  6.     userid -- ORACLE username/password           
  7.    control -- control file name                  
  8.        log -- log file name                     
  9.        bad -- bad file name                     
  10.       data -- data file name                     
  11.    discard -- discard file name                  
  12. discardmax -- number of discards to allow          (Default all)
  13.       skip -- number of logical records to skip    (Default 0)
  14.       load -- number of logical records to load    (Default all)
  15.     errors -- number of errors to allow            (Default 50)
  16.       rows -- number of rows in conventional path bind array or between direct path data saves
  17.                (Default: Conventional path 64, Direct path all)
  18.   bindsize -- size of conventional path bind array in bytes  (Default 256000)
  19.     silent -- suppress messages during run (header,feedback,errors,discards,partitions)
  20.     direct -- use direct path                      (Default FALSE)
  21.    parfile -- parameter file: name of file that contains parameter specifications
  22.   parallel -- do parallel load                     (Default FALSE)
  23.       file -- file to allocate extents from      
  24. skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
  25. skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
  26. commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  27.   readsize -- size of read buffer                  (Default 1048576)
  28. external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
  29. columnarrayrows -- number of rows for direct path column array  (Default 5000)
  30. streamsize -- size of direct path stream buffer in bytes  (Default 256000)
  31. multithreading -- use multithreading in direct path  
  32. resumable -- enable or disable resumable for current session  (Default FALSE)
  33. resumable_name -- text string to help identify resumable statement
  34. resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
  35. date_cache -- size (in entries) of date conversion cache  (Default 1000)
  36. no_index_errors -- abort load on any index errors  (Default FALSE)

  37. PLEASE NOTE: Command-line parameters may be specified either by
  38. position or by keywords.  An example of the former case is 'sqlldr
  39. scott/tiger foo'; an example of the latter is 'sqlldr control=foo
  40. userid=scott/tiger'.  One may specify parameters by position before
  41. but not after parameters specified by keywords.  For example,
  42. 'sqlldr scott/tiger control=foo logfile=log' is allowed, but
  43. 'sqlldr scott/tiger control=foo log' is not, even though the
  44. position of the parameter 'log' is correct.
  45. [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. 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;
复制代码







回复

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-10-20 23:48:38 | 显示全部楼层
老师,我创建外部表后,执行查询,提示文件未找到,我将path用/home/oracle/practice/less08与/home/oracle/practice/less08/,都提示一样的错误,这是什么情况?
ext_table_log.png

创建外部表的脚本:
  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;
复制代码


回复 支持 反对

使用道具 举报

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
 楼主| 发表于 2018-10-21 09:12:33 | 显示全部楼层
jarlean 发表于 2018-10-20 23:48
老师,我创建外部表后,执行查询,提示文件未找到,我将path用/home/oracle/practice/less08与/home/oracle ...

没有什么其他的,检查一下passwd.dat文件是否存在。
select  * from dba_external_table_locations;
回复 支持 反对

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-10-21 17:01:03 | 显示全部楼层
本帖最后由 jarlean 于 2018-10-22 09:37 编辑

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

location存在正确的信息.png

  1. OWNER        DIRECTORY_NAME          DIRECTORY_PATH
  2. 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
复制代码


通过tns查不了外部表数据.png

通过本地连接能查到外部表数据.png
回复 支持 反对

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-10-22 14:47:29 | 显示全部楼层
是权限问题,放到$ORACLE_HOME后面的目录就可以正常访问了。结帖。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 18:15 , Processed in 0.056689 second(s), 28 queries .

快速回复 返回顶部 返回列表