ORACLE External table Summary, ORACLE table Summary

ORACLE External table Summary, ORACLE table Summary

External table Introduction

ORACLE External tables are used to access Text files (Text files) other than databases or ORACLE exclusive format files. Therefore, when an External table is created, storage structures such as segments, partitions, and data blocks are not generated. Only table-related definitions are placed in the data dictionary. External tables, as the name suggests, are stored outside the database. Data can be obtained from ORACLE exclusive format files only during access. External tables are only for query and cannot be modified (INSERT, UPDATE, and DELETE operations ). You cannot create an index on an external table. Because creating an index means that a corresponding index record exists. However, external tables are not stored in the database. Therefore, indexes cannot be created externally. If you want to create a table, the system will prompt the error message "the operation is not supported on the external organization table.

Notice: External table is introduced after ORACLE 9i.

External table features

(1) located in the file system (must be in the database server, rather than other network paths), separated by a certain format,

For example, @ # $. A text file or another type of table can be used as an external table.

(2) access to external tables can be completed through SQL statements without loading data in external tables into the database.

(3) external data tables are read-only. Therefore, you cannot perform DML operations on External tables or create indexes.

(4) The ANALYZE statement does not support collecting statistics from external tables. You should use the DMBS_STATS package to collect statistics from external tables.

(5) query operations and connections. You can also perform operations in parallel.

(6) The data is in an external organization of the database and is an operating system file.

(7) The identifier of the operating system file in the database is mapped by a logical directory.

External table example:

1: create a directory object and authorize

From 9i, if an ORACLE database needs to access the file system, it must use directory objects to access files in a relative path to enhance database security. Create directory objects and grant permissions.

SQL> CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab ';

Grant the user the operation permission for the specified directory

SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO ETL;

2: Create an External table

I created a flat file as a test case, as shown below. There are 5 Records in total, one of which is an error record.

[Oracle @ DB-Server exterltab] $ more student. data

10001 @ # $ kerry @ # $ male @ #$28 @ #$1

10002 @ # $ jimmy @ # $ male @ #$22 @ #$1

10003 @ # $ ken @ # $ male @ #$21 @ #$1

10004 @ # $ merry @ # $ femal @ #$20 @ #$1

This is a bad file

CREATE TABLE EXTER_TEST

(

     ID              NUMBER(5)      ,

     NAME            VARCHAR(12)    ,

     SEX             VARCHAR(8)     ,

     AGE             NUMBER(3)      ,

     GRADE           NUMBER(1)

) ORGANIZATION EXTERNAL

(

            type        oracle_loader

            default directory dump_dir

            access parameters

            (

                    records delimited by newline

                    fields terminated by '@#$'

            )

            location ('student.data')

);

 

The syntax of the External table is quite complex, and there are many Parameter options. I will not explain them too much here. If you are interested, you can read the official documents.

 

SQL> select * from exter_test;

select * from exter_test

*

ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-30653: reject limit reached

ORA-06512: at "SYS.ORACLE_LOADER", line 52

The following error occurs because student. there are non-conforming records in the data file. You can delete the record "this is a bad file", but this is to test the following situation, so you can execute alter table exter_test reject limit unlimited; skip some restrictions.

SQL> alter table exter_test reject limit unlimited;

Table altered.

SQL> select * from exter_test;

        ID NAME         SEX             AGE      GRADE

---------- ------------ -------- ---------- ----------

     10001 kerry        male             28          1

     10002 jimmy        male             22          1

     10003 ken          male             21          1

     10004 merry        femal            20          1

SQL> 

View/oradata/exterltab. You will find that the EXTER_TEST_8907.bad and EXTER_TEST_8907.log files are generated automatically. The log records the access to external tables and the bad file records the error records. As follows:

[Oracle @ DB-Server exterltab] $ ls

EXTER_TEST_8907.bad EXTER_TEST_8907.log student. data

[Oracle @ DB-Server exterltab] $

3: view the External table directory

Xxx_external_locations can know all the current directory objects and related external tables, and query the names of the operating system files corresponding to these external tables.

Select * from all_external_locations;

Select * from user_external_locations;

Select * from dba_external_locations;

SQL> show user

USER is "SYS"

SQL> col owner for a20

SQL> col table_name for a30

SQL> col location for a30

SQL> col directory_owner for a3;

SQL> col directory_name for a30;

SQL> select * from dba_external_locations;

OWNER          TABLE_NAME               LOCATION           DIR DIRECTORY_NAME

--------- -------------------------- --------------- ---------------------------

SH           SALES_TRANSACTIONS_EXT    sale1v3.dat       SYS DATA_FILE_DIR

ETL          EXTER_TEST                student.data      SYS DUMP_DIR

4: view the details of an External table

select * from user_external_tables;

select * from all_external_tables;

select * from dba_external_tables;

SQL> desc dba_external_tables;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 TYPE_OWNER                                         CHAR(3)

 TYPE_NAME                                 NOT NULL VARCHAR2(30)

 DEFAULT_DIRECTORY_OWNER                            CHAR(3)

 DEFAULT_DIRECTORY_NAME                    NOT NULL VARCHAR2(30)

 REJECT_LIMIT                                       VARCHAR2(40)

 ACCESS_TYPE                                        VARCHAR2(7)

 ACCESS_PARAMETERS                                  VARCHAR2(4000)

 PROPERTY                                           VARCHAR2(10)

5. delete an External table

The SQL syntax for deleting External tables is the same as that for normal tables, but the difference is that the corresponding directory object may also be deleted. When the External table is not used, you need to delete the External table or its corresponding directory object in time. However, there are some restrictions when deleting the content. These restrictions mainly apply to management rather than technical restrictions. That is to say, the Oracle database system does not impose any restrictions on it. However, if the database administrator does not comply with these restrictions, some problems may occur. To delete an External table, delete the directory object. Sometimes a directory object may contain multiple external tables. In this case, you must confirm that all external tables are no longer in use and have deleted them before you can delete the directory objects. When creating an external table, the operating system checks whether the corresponding directory object has been created. However, when deleting an object, the system does not determine whether all external tables associated with this directory object have been deleted. If the directory object is deleted, but an External table exists. When querying this external table, the system will prompt the error message "the object does not exist. Therefore, when deleting a directory object, the database system lacks a check. Only the database administrator needs to manually check whether the directory object has other external tables when deleting the directory object.

External table restrictions:

1. You can only SELECT tables, but cannot perform DML operations such as DELETE, UPDATE, and INSERT.

2. Because external tables need to create directories on the ORACLE Database "server", OS files must be placed in these directories. That is, these files can only be stored on the database server. If the data file is not on the server, the External table cannot be used.

3. indexes cannot be created on External tables. However, you can create a view.

4. External tables do not support LOB objects. If you want to use the LOB type, you cannot use an external table.

Eg: Delete external table records

SQL> delete from exter_test where id=10001;

delete from exter_test where id=10001

            *

ERROR at line 1:

ORA-30657: operation not supported on external organized table

Eg: Create a view on an external table

SQL> create or replace view vv

  2  as 

  3  select * from etl.exter_test;

View created.

SQL> select * from vv;

        ID NAME         SEX             AGE      GRADE

---------- ------------ -------- ---------- ----------

     10001 kerry        male             28          1

     10002 jimmy        male             22          1

     10003 ken          male             21          1

     10004 merry        femal            20          1

SQL> 

Advantages of External tables:

If we want to talk about the advantages of External tables, we generally compare them with SQLLDR. Many syntaxes of External tables are similar to those of SQLLDR control files, next, let's talk about my understanding and some comparisons in "El 9i & 10g programming art ".

1. SQLLDR needs to load data into the database before querying related records. If it is only for querying some records, external tables are indeed more advantageous than SQLLDR, which is convenient and does not occupy the database storage space. Especially for large data volumes, the SGSN statement was previously processed by a mobile integrated analysis project (hundreds of GB of data, if all data is loaded into the database, it is a waste of space and time) this is the case. The External table virtual import process is extremely fast.

2: when the flat file changes, the data in the External table changes. This avoids insert, update, delete, and other operations. It is quite advantageous for external tables with large records. I only need to use Shell commands to do things that will take a high cost for the database.

3: External tables can use complex WHERE conditions to selectively load data. Although SQLLDR has a WHEN clause to select the row to be loaded, you can only use the AND expression AND the expression for Equality comparison. In the WHEN clause, the interval (greater than or less than) cannot be used ), no or is null.

4: ability to MERGE (MERGE) data. You can take an operating system file that fills up data and update existing database records.

5: efficient code search. An External table can be joined to another database table as part of the loading process.

6: Using INSERT makes it easier to execute multi-table INSERT. From Oracle9i, you can use an INSERT statement to INSERT one or more tables by using complex WHEN conditions. Although SQLLDR can be loaded into multiple tables, the corresponding syntax is quite complex.

References:

Http://blog.itpub.net/22578826/viewspace-703470

Http://www.cnblogs.com/lanzi/archive/2010/12/28/1918755.html

Http://blog.csdn.net/leshami/article/details/6078481
What is an external table in Oracle? You

External Table, which has field and data type constraints and can be queried like a common database Table, but the data in the Table is not stored in the database, it is in a common external file associated with the database. When you query the External Table, Oracle will parse the file and return Qualified Data, just as the data is stored in the database Table.

Oracle External table Problems

The External table has irregular data. Check the. bad file in the directory.