Oracle External table

Sqlldr loads data into the database. The data in the External table is based on the operating system file, and the real data is not saved to the database, which is in the operating system file. Therefore, the data in the External table can only be selected. External tables can basically replace sqlldr. In the following three cases, choose sqlldr instead of External table:

1. Data must be loaded through the network, that is, the input file is not on the database server.

2. Multiple users must concurrently use the same external table to process different input files.

3. the LOB type must be used. External tables do not support logs.

Use sqlldr to generate external table scripts

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ cat demo1.ctl
4. LOADDATA
5. INFILE *
6. INTO TABLEDEPT
7. FIELDS TERMINATEDBY ','
8. (DEPTNO, DNAME, LOC)
9. BEGINDATA
10. 10, Sales, Virginia
11. 20, Accounting, Virginia
12. 30, Consulting, Virginia
13. 40, Finance, Virginia
14. ABC, XYZ, Hello
15. [Oracle @ linux sqlldr] $ sqlldr ing/ing demo1.ctl external_table = generate_only
17. SQL * Loader: Release 10.2.0.4.0-ProductionOnTuesday October 4 21:59:06 2011
19. Copyright (c) 1982,200 7, Oracle.AllRights reserved.

View the generated log file

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ cat demo1.log
5. SQL * Loader: Release 10.2.0.4.0-ProductionOnTuesday October 4 21:59:06 2011
7. Copyright (c) 1982,200 7, Oracle.AllRights reserved.
9. Control File: demo1.ctl
10. Data File: demo1.ctl
11. Error file: demo1.bad
12. Obsolete file: Not specified
14. (All records can be discarded)
16. Number of objects to be loaded:ALL
17. Number of to be skipped: 0
18. Allowed error: 50
19. Continue: Unspecified
20. Path used: External table
22. Table DEPT, loaded from each logical record
23. Insert option for this tableINSERTEffective
25. Column name location length abort packaging Data Type
26. --------------------------------------------------------------------------
27. DEPTNOFIRST*,CHARACTER
28. DNAMENEXT*,CHARACTER
29. LOCNEXT*,CHARACTER
33. File needsCREATEDIRECTORY statements
34. ------------------------------------------------------------------------
35. CREATEDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000AS '/U01/sqlldr'
36. -- Connect sqlldr to the database and query the data dictionary. If no appropriate directory is found, create a SYS_SQLLDR_XT_TMPDIR_00000 directory.
38. Used for external tablesCREATE TABLEStatement:
39. ------------------------------------------------------------------------
40. CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"-- Create a table SYS_SQLLDR_X_EXT_DEPT
41. (
42. "DEPTNO"NUMBER (10 ),
43. "DNAME"VARCHAR2 (20 ),
44. "LOC"VARCHAR2 (20)
45. )
46. ORGANIZATION external-- Indicates that this is not a common table, but an External table.
47. (
48. TYPE oracle_loader-- Load the data type. In addition, there is a 10 Gb ORACLE_DATAPUMP that can be used to load or unload data.
49. DEFAULTDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000-- Use the previously created directory SYS_SQLLDR_XT_TMPDIR_00000
50. ACCESS PARAMETERS
51. (
52. RECORDS DELIMITEDBYNewline characterset ZHS16GBK-- The Record ends with a line break by default.
53. BADFILE'Sys _ SQLLDR_XT_TMPDIR_00000':'Demo1. bad'-- Record a bad file in the newly created directory. Records that cannot be loaded will be recorded in this file.
54. LOGFILE'Demo1. log_xt'-- Log file.
55. READSIZE 1048576-- Size of the cache for loading data. 1024*1024 = 1048576
56. SKIP 6-- Skip the first six rows without loading, because the first six rows are information of the sqlldr control file.
57. FIELDS TERMINATEDBY ","LDRTRIM-- Data is separated by commas (,). LDRTRIM indicates removing the leading and trailing spaces.
58. REJECTROWS WITH ALL NULLFIELDS-- External tables record empty rows in bad files without loading these rows.
59. (
60. "DEPTNO" CHAR(255)
61. TERMINATEDBY ",",
62. "DNAME" CHAR(255)
63. TERMINATEDBY ",",
64. "LOC" CHAR(255)
65. TERMINATEDBY ","
66. )
67. )
68. Location
69. (
70. 'Demo1. ctl'-- Tells the name of the file loaded by oracle.
71. )
72. ) REJECT LIMIT UNLIMITED
75. Used to load internal tablesINSERTStatement:
76. ------------------------------------------------------------------------
77. INSERT/* + Append */INTODEPT-- Load data directly from the External table.
78. (
79. DEPTNO,
80. DNAME,
81. LOC
82. )
83. SELECT
84. "DEPTNO",
85. "DNAME",
86. "LOC"
87. FROM "SYS_SQLLDR_X_EXT_DEPT"
90. The statement used to clear the object created by the previous statement:
91. ------------------------------------------------------------------------
92. DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"-- Delete an External table.
93. DROPDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000-- Delete a directory.
97. Starting from 21:59:06, January 1, 2011, Tuesday
98. The operation ended at 21:59:06, January 1, October 04, 2011.
100. Elapsed time: 00: 00: 00.14
101. CPU time: 00: 00: 00.04

1 2 Next Page