Oracle External Table details (reprint)

(external table creation is primarily concerned with the creation of directory access permissions issues, directory path format without spaces, and other irrelevant characters, that is, the current table must be accessible to users, and about the limit of the number of rows in the table, if not added reject limit unlimited The data format in the table should be synchronized with the definition in Access parameters when the table was created, using skip=1 as appropriate) External Table Overview

External tables can only be used after Oracle 9i. Simply put, an external table refers to a table that does not exist in the database. By providing Oracle with metadata that describes the external table, we can treat an operating system file as a read-only database table, as if the data were stored in a common database table. An external table is an extension to a database table.

Features of external tables
In the file system, split in a certain format, such as text files or other types of tables that can be used as external tables.
Access to external tables can be done through SQL statements without having to load the data from the external tables into the database first.
External data tables are read-only, so DML operations cannot be performed on external tables and indexes cannot be created.
Analyze statements do not support the collection of statistics for external tables, the Dmbs_stats package should be used to collect statistics from external tables.
Considerations for creating external tables
1. The directory object needs to be established first. When creating objects, be careful that the Oracle database system does not confirm that the directory is really there. If you accidentally write the path incorrectly when you enter this directory object, it is possible that the external table can still be set up normally, but the data cannot be queried. Because of the lack of this self-checking mechanism when creating directory objects, special attention is required to assign paths to this directory object. It is also important to note the case of the path. In the Windows operating system, the path is case insensitive. On the Linux operating system, this path needs to be case sensitive. Therefore, in different operating systems, the creation of directory objects need to pay attention to this difference in capitalization.
2. Requirements for operating system files
When you create an external table, you must specify the separator symbol used by the operating system file. And the delimiter has only one. When you create an external table, you cannot include a caption column. If the header information is inconsistent with the field type of the external table (for example, if the field content is a number data type and the header information is character data, an error occurs at the time of the query). If the data type coincides, the Oracle database of the header information is treated as a normal record.

When the Oracle database system accesses this operating system file, it automatically creates a log file in the same directory as the file. This log file is set up as expected, regardless of whether the final access is successful. View this log file to see how often the database accesses external tables, whether they are accessed successfully, and so on. By default, the log is generated under the same directory as the external table.
3. Restrictions on the creation of temporary tables
In cases where the name of a field in a table has special characters, the table column name must be concatenated using the double quotation marks under the English state. If you use "salseid#".
When a special symbol in a column name is not enclosed in double quotation marks, the data cannot be queried correctly.
It is not recommended to use special column header characters
When you create an external table, you do not create a table in the database, nor do you allocate any storage space for the external table.
Creating an external table simply creates metadata for the external table in the data dictionary to access the data in the external table instead of storing the data for the external table in the database.
Simply put, the database stores only one correspondence to the external file, such as the correspondence between fields and fields. Instead of storing the actual data.
Because the actual data is stored, an index cannot be created for an external table, and operations such as inserting, updating, deleting the external table are not supported when the data uses DML.
4. Delete external tables or directory objects
In general, delete the external table before deleting the directory object, and if you have more than one table in the directory object, delete all the tables and then delete the directory objects.
If you forcibly delete the directory without deleting the external table, you will receive an "object does not exist" error message when you query to the deleted external table.
Query dba_external_locations to get all current directory objects and related external tables, as well as give the names of the operating system files for those external tables. If you delete an external table only at the database level, the external table file on the operating system is not automatically deleted.
5. Limitations on operating system platforms
Different operating systems have different interpretations and display methods for external tables
Files created in the Linux operating system are semicolon-delimited and one record per line, but this is not the case when the file is opened on the Windows operating system.
Recommended to avoid the effects of different operating systems and different character sets
Create an external table
Use the Organization exteneral clause of the CREATE TABLE statement to create the external table. The external table does not allocate any extents, because only metadata is created in the data dictionary.
1. Creation syntax for external tables
Createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
Organization Exteneral
(.....)
Detailed syntax can be found in the author's other two articles

Oracle External Table Oracle_datapump type creation syntax Detailed: http://czmmiao.iteye.com/blog/1268453

Oracle External Table Oracle_loader type creation syntax Detailed: http://czmmiao.iteye.com/blog/1268157
2. By query result set, use Oracle_datapump to populate data to generate external table
A. Create a system directory and Oracle data directory name to establish correspondence and grant permissions
[Email protected] ~]$ Mkdir-p/home/oracle/external_tb/data

sql> Create or replace directory Dat_dir as '/home/oracle/external_tb/data/';
sql> alter user Scott account unlock identified by Scott;
B. Creating an external table

Sql>create Table EX_TB1--Creating an external table
2 (Ename,job,sal,dname)--table column description, note data type not specified
3 Organization External
4 (
5 Type Oracle_datapump--use DataPump to populate the query results to an external table, note that this is generated by select and is not supported

Oracle_loader
6 default directory Dat_dir--Specify the storage directory for external tables
7 location (' Tb1.exp,tb2.exp '))
8 Parallel 2--populated in parallel, where the degree of parallelism must be consistent with the number of files generated to function, the detailed algorithm can be

9 as to see http://czmmiao.iteye.com/blog/1268453
Ten Select Ename,job,sal,dname--fill in the raw data used
One from EMP Join dept
On Emp.deptno=dept.deptno
C. Validating external tables
Sql> select * from EX_TB1;
ename JOB SAL dname
---------- --------- ---------- --------------
SMITH Clerk
ALLEN salesman, SALES
..................................
MILLER Clerk 1300 ACCOUNTING
For external tables created using this method, you can copy them to a different path as the original data for the external table to generate a new external table for transferring data.
3. Use SQLLDR to provide definitions for external tables and create external tables
For the use of Sql*loader please refer to: Sql*loader How to use
We use Sql*loader and the following control file to generate the definition of the external table
$ cat Demo1.ctl
LOAD DATA
INFILE *
Into TABLE dept_new
Fields TERMINATED by ', '
(Deptno,dname,loc)
Begindata
10,sales,virginia
20,accounting,virginia
30,consulting,virginia
40,finance,virginia

Give the appropriate permissions and create tables

Sql>grant create any directory to Scott;
Sql>grant drop any directory to Scott;
Sql>create Table Dept_new
2 (Deptno number,dname varchar2 (), loc varchar2 (25));
Execute SQLLDR command
$ sqlldr Scott/tiger control=demo1.ctl external_table=generate_only
The external_table parameter has the following three values:
Not_used: Default value.
Execute: This value indicates that SQLLDR does not generate and execute a sqlinsert statement, but instead creates an external table and loads it using a bulk SQL statement.
Generate_only: So that SQLLDR does not load any data specifically, it simply generates the SQL DDL and DML statements that are executed and puts them in the log file that it creates.
Note: direct=true covers extenal_table=generate_only. If Direct=true is specified, the data is loaded and no external tables are generated.
$ cat Demo1.log--view Sqlldr generated log files
Sql*loader:release 10.2.0.1.0-production on Sun Nov 20 17:45:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File:demo1.ctl
Data File:demo1.ctl
Bad File:demo1.bad
Discard File:none specified

(Allow all discards)

Number to Load:all
Number to skip:0
Errors allowed:50
Continuation:none specified
Path used:external Table

Table dept_new, loaded from every logical record.
Insert option in effect for this Table:insert

Column Name Position Len term encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO First *, CHARACTER
Dname NEXT *, CHARACTER
LOC NEXT *, CHARACTER

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
Create directory sys_sqlldr_xt_tmpdir_00000 as '/home/oracle '--the statement that creates the Catalog object

CREATE Table statement for external table:
------------------------------------------------------------------------
CREATE TABLE "Sys_sqlldr_x_ext_dept_new"
(
"DEPTNO" number (2),
"Dname" VARCHAR2 (14),
"LOC" VARCHAR2 (13)
)
ORGANIZATION External
(
TYPE Oracle_loader--Specifies how external tables are accessed, 9i does not support Oracle_datapump
DEFAULT DIRECTORY sys_sqlldr_xt_tmpdir_00000
ACCESS PARAMETERS--Configure external table parameters
(
RECORDS delimited by NEWLINE CHARACTERSET Us7ascii--record for end of change
Badfile ' sys_sqlldr_xt_tmpdir_00000 ': ' Demo1.bad '--Storing the record file description for processing failure
LOGFILE ' demo1.log_xt '--log file
ReadSize 1048576--oracle reads the default buffer used by the input data file, which is MB, as private mode is allocated from the PGA, such as shared mode
It is allocated from the SGA

Skip 6-The number of skipped records because we used the control file, so the preceding control information needs to be skipped
Fields TERMINATED by "," Ldrtrim--The Terminator of the Description field
REJECT rows with the null fields-all null-valued rows are skipped and logged to bad file.
(--The following is the definition of each column that describes the external file
"DEPTNO" CHAR (255)
TERMINATED by ",",
"Dname" CHAR (255)
TERMINATED by ",",
"LOC" CHAR (255)
TERMINATED by ","
)
)
Location
(
' Demo1.ctl '--describes the filename of the external file
)
) REJECT Limit UNLIMITED--describes the number of allowed errors, which are unrestricted


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT/*+ Append */Into Dept_new
(
DEPTNO,
Dname,
LOC
)
SELECT
"DEPTNO",
"Dname",
"LOC"
From "Sys_sqlldr_x_ext_dept_new"

Statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
drop table "Sys_sqlldr_x_ext_dept_new"--defining information for deleting directories and external tables
DROP DIRECTORY sys_sqlldr_xt_tmpdir_00000

Run began on Sun Nov 20 17:45:36 2011
Run ended on Sun Nov 20 17:45:37 2011

Elapsed Time was:00:00:00.25
CPU Time was:00:00:00.05
4. Using flat files to define and generate external tables
A. Flat file data
1.dat:
7369,smith,clerk,7902,17-dec-80,100,0,20
7499,allen,salesman,7698,20-feb-81,250,0,30
7521,ward,salesman,7698,22-feb-81,450,0,30
7566,jones,manager,7839,02-apr-81,1150,0,20
2.dat:
7654,martin,salesman,7698,28-sep-81,1250,0,30
7698,blake,manager,7839,01-may-81,1550,0,30
7934,miller,clerk,7782,23-jan-82,3500,0,10

$ pwd
/home/oracle/external_tb/data
$ ls
1.dat 2.dat dat_dir:tb_test.exp emp_new_3198.log emp_new_3413.log ex_tb1_3021.log

Create an external table

CREATE TABLE Emp_new
(
emp_id Number (4),
ename VARCHAR2 (15),
Job Varchar2 (12),
MGR_ID Number (4),
HireDate Date,
Salary Number (8),
Comm Number (8),
dept_id Number (2)
)
Organization External
(
Type Oracle_loader
Default directory Dat_dir
Access parameters
(
Records delimited by newline
Fields terminated by ', '
)
Location
(' 1.dat ', ' 2.dat ')
);
Validating external Tables

Sql> select * from Emp_new;
    emp_id ename           job               mgr_id hiredate      salary       comm    dept_id
----------------------------------- ---------------------------------------------------
      7654 martin           salesman           7698 28-sep-81       1250          0         
..... ..... .....   ....... ..... ......
The external table cannot perform DML

, and ..... \ n * * * * * * * * * * * * * * * * * * * * *

Sql> Delete from emp_new;
Delete from Emp_new
*
ERROR at line 1:
Ora-30657:operation not supported on external organized table
View External Table information

Sql>select owner,table_name,type_name,default_directory_name,access_parameters
2 from Dba_external_tables;

To obtain the location of the flat file, use the following query:

Sql>select * FROM Dba_external_locations ORDER BY TABLE_NAME;
several highlights of the external table definition
A.organization external keyword, must have. To indicate that the table defined is an external table.
B. Types of external tables with important parameters
Oracle_loader: The default way to define external tables is to load text data only in a read-only manner.
Oracle_datapump: Support loading and unloading of data, data file must be binary dump file. You can extract data from an external table to an internal table, or you can unload data from an internal table as a binary file into an external table.
C.default directory: The default directory indicates the path to which the external file resides
D.location: Defines the location of the external table
F.access PARAMETERS: Describes how to access external tables
Records keyword defines how data rows are identified
Delimited by ' XXX '--line breaks, common newline define line breaks, and indicate character sets. For special characters you need to define separately, such as special symbols, you can use the Ox ' 16-bit value ', for example, tab (/T) 16 bits is 9, then delimitedby0x ' 09 ';
The 16 bits of CR (/R) are D, then delimitedby0x ' 0D '.
Skip x--Skips X rows of data, some of which are column names and need to skip the first row, use skip 1.
The fields keyword defines how the field is identified and is commonly used as follows:
fields:terminated by ' x '--field separator.
Enclosed by ' x '--field reference, the data contained within this symbol is treated as a field.
For example, a row of data formats such as: "ABC", "A" "B," "C,". Using the parameter terminated by ', ' enclosed by ' ", the system will read two fields, the first field value is ABC, the second field value is a" B, "C,.
lrtrim--Delete the trailing and ending whitespace characters.
MISSING field values is null--some field vacancy values are set to NULL.
For field lengths and delimiters that are indeterminate and ready to be used as external table files, you can use UltraEdit, EditPlus, and so on for analysis testing, and if the files are large, consider splitting the files into small files and extracting the data from them for testing.
external table handling of errors
REJECT LIMIT UNLIMITED
When you create an external table, the limit clause is added last to indicate the number of errors that can be allowed to occur. The default value is zero. Set to unlimited the error is not limited
Badfile and Nobadfile clauses
Used to specify which file to store the captured conversion errors to. If Nobadfile is specified, errors during conversion are ignored
If this parameter is not specified, the system automatically generates the same name as the external table under the source directory. Bad file Badfile records the results of this operation and will be overwritten with the logfile and nologfile clauses next time
Also add the logfile ' log_file.log ' clause in Access parameters, all Oracle error messages are placed in ' Log_file.log '
The Nologfile clause, however, indicates that the error message is not logged, such as ignoring the clause, and the system automatically generates the same name as the external table under the source directory. Log file
Note the following several common issues
1. External tables often encounter buffer shortages, so increase the readsize as much as possible
2. Problems with newline characters not being created. Line breaks are represented differently in different operating systems, and when you encounter an error log prompt for a newline character problem, you can use the
UltraEdit Open, straight look hex
3. When an error occurs on a particular line, check the log file with "Bad", which holds the error data, open it with Notepad to see if there is an error in the external table definition conflict

Limitations of external tables
1.SQLLDR can specify how many times to submit, that is, rows=?, the external table is not, this for the import of large amounts of data in some cases.
2.sqlldr errors represents the number of rows allowed for errors, and the external table uses reject LIMIT UNLIMITED, which is basically the same function.
3. The columns of the external table cannot be specified as not nullable, so it is difficult to reject a record that has a column null value.
4. External tables cannot use Continueif, which is more difficult to record if there is a newline.

Reference to: http://space.itpub.net/22578826/viewspace-703470

http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_dp_driver.htm#g1017944

http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_params.htm#g1031955

Http://www.examda.com/oracle/zhonghe/20090817/091840581.html

Http://news.newhua.com/news1/program_database/2010/71/1071152247EKHJED8IA04B6DIA4HA3GGJ4EJ3FEE7896H215DJ8B1HI.html

This article original, reproduced please indicate the source, the author

Oracle External Table details (reprint)