Oracle External Table Detailed

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. From the query result set, use Oracle_datapump to populate the data to generate the external table A. Create a system directory and Oracle data directory name to establish correspondence, and grant permissions

mkdir -p/home/oracle/external_tb/data

Create or Replace  as ' /home/oracle/external_tb/data/ ' ; Grant Read  on  to Scott;

B. Creating an external table

Create Table ex_tb1            (ename,job,sal,dname)            Organization External            default directory Data_dir location ('ex_tb1' ))            1as            Selectfromjoin on Emp.deptno=dept.deptno;

C. Validating external tables

Select *  fromex_tb1; ename JOB SAL dname------------------------- -------------------- ---- -------------------------CLARK MANAGER2450Accountingking President theAccountingmiller Clerk1300Accountingjones MANAGER2975Researchford ANALYST theresearchadams Clerk1100Researchsmith Clerk -Researchscott ANALYST theResearchward salesman1250Salesturner salesman theSalesallen salesman thesalesjames Clerk950Salesblake MANAGER2850salesmartin salesman1250SALES -Rows selected.

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.

D. Copy the external table file to a new file name for impersonation to another server

/Home/Oracle/EXTERNAL_TB/data//Home/Oracle/ EXTERNAL_TB/data/IN_TB1

E. Create a new table and import the data from the external table above into a new table

Create Table in_tb1             varchar2 (varchar2(9Number (7,2varchar(14  ))            organization external            default directory Data_dir location (' in_tb1 '));

F. Validating data for a new external table

Select *  fromin_tb1; ename JOB SAL dname------------------------- -------------------- ---- -------------------------CLARK MANAGER2450Accountingking President theAccountingmiller Clerk1300Accountingjones MANAGER2975Researchford ANALYST theresearchadams Clerk1100Researchsmith Clerk -Researchscott ANALYST theResearchward salesman1250Salesturner salesman theSalesallen salesman thesalesjames Clerk950Salesblake MANAGER2850salesmartin salesman1250SALES -Rows selected.

G. Creating a normal table, importing external table data, which is the use of additional part tables of the Oracle_datapump type for data migration

Create Table  as Select *  from IN_TB1;

3. Use Oracle_datapump to populate data to generate external tables using external file data

Oracle External Table Detailed