Plsql_oracle the concept and use of external tables (case) (querying data files directly from external tables for section database tablespace)

2014-08-25 Baoxinjian

I. Summary

Oracle external tables are used to access text files other than databases (text file) or Oracle proprietary format files.

Therefore, creating an external table does not result in a storage structure such as segments, extents, and data blocks, except that the table-related definitions are placed in the data dictionary.

External tables, as the name implies, are stored in tables outside the database.

When accessing the data from the Oracle proprietary format file, the external table is for querying only and cannot modify the contents of the external table (INSERT, UPDATE, delete operation).

External tables cannot be indexed. Because creating an index means that there is a corresponding index record. The external table is not actually stored in the database.

Therefore, the external cannot be indexed, if the hard to establish, then the system will prompt "operation on the external organization table is not supported" error prompt.

1. External Table Features

• In the file system (be sure to be in the database server, not other network paths), in a certain format, such as @#$, text files or other types of files 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.
• You can query for operations and connections. You can also operate in parallel.
• The data is organized outside the database and is the operating system file.
• The flags of the operating system files in the database are mapped by a logical directory.

2. External Table Restrictions

• You can only select a table, and you cannot delete, UPDATE, insert these DML operations.
• Because external tables require a directory to be created on the Oracle database "Server", the OS files must be placed in these directories. That is, these files can only be placed on the database server side. If the data file is not located on the server, the external table cannot be used.
• Indexes cannot be created on external tables. However, you can create a view.
• The external table does not support LOB objects. If you want to use LOB types, you cannot use an external table.

3. Advantages of external tables

If you want to talk about the advantages of external tables, generally compared with SQLLDR, the external table a lot of grammar and sqlldr control files do have a lot of similar places, the following talk about their understanding and "Oracel 9i&10g programming Art" in some of the contrast

• Sqlldr need to load the data to query the relevant records, if only to query some records, the external table is indeed more advantageous than the SQLLDR, it is convenient and does not occupy the database storage space. Especially big data, previously do mobile comprehensive Analysis project processing SGSN (hundreds of g of data, if all loaded into storage, very waste of space and time) when there is this experience. External table Virtual Import process is extremely fast
• When the flat file changes, the data inside the external table changes. This avoids insertions, updates, deletions, and so on, which is quite advantageous for external tables of large records, and I just need to use shell commands to get things done at a very high cost to the database.
• External tables can selectively load data using complex where conditions. Although SQLLDR has a when clause to select the rows to load, you can only use an and expression and an expression that performs equality comparisons, you cannot use intervals (greater than, less than) in the When clause, no or expression, no is NULL, and so on.
• Ability to merge (merge) data. You can take an operating system file that fills the data and update the existing database records by it.
• Can perform efficient code lookups. You can join an external table to another database table as part of the loading process.
• Use Insert to perform multiple table insertions more easily. Starting with oracle9i, you can insert one or more tables with an INSERT statement by using the complex when condition. Although Sqlldr can also be loaded into multiple tables, the corresponding syntax is quite complex.

Second, case-create an external table, and do the increase and deletion check operation

Case: Create an external table and import the test data for testing

1. Create a Directory

CREATE  as ' /home/oracle/extertable ';

2. Create a test data file and test

3. Create an external table

CREATE TABLEexter_test_table (ID Number(5), NAMEVARCHAR( A), SEXVARCHAR(8), age Number(3), GRADE Number(5)) ORGANIZATION EXTERNAL (TYPE oracle_loaderDEFAULTDIRECTORY extertable ACCESS PARAMETERS (RECORDS delimited byNEWLINE Fields TERMINATED by ',') Location ('Student.data' )   );ALTER TABLEExter_test_table REJECT LIMIT UNLIMITED;

4. After creating the table, you can directly query the data in the database file

5. External tables can only be Select, cannot be delete/update, and DML operations are required only in the data file itself

6. When the operation occurs, a log file is generated in the data file directory to record the data import and export.

7. Querying external tables in the database Dba_external_locations/dba_external_tables

Iii. cases-Deleting external tables

Deleting an external table SQL syntax is the same as a normal table, but the difference is that it is possible to delete the corresponding directory object.

When the external table is not used, it is necessary to delete the external table or the corresponding directory object in time.

However, there are some limitations when deleting this content. These limitations are primarily management constraints, not technical constraints.

In other words, the Oracle database system does not impose restrictions on it.

However, some problems may occur if the database administrator does not follow these restrictions.

If you want to delete the external table first, then delete the directory object. Sometimes a directory object may contain multiple external tables.

You must confirm that all external tables are not used, and that they have been removed cleanly before you can delete the directory object.

When you create an external table, the operating system will determine whether the corresponding directory object has been created.

However, when the object is deleted, the system does not determine whether the external table associated with the directory object has been completely deleted.

If the directory object is deleted, but there are external tables present.

When querying this external table, the system will prompt the "object does not exist" error message. Therefore, when the directory object is deleted, the database system lacks a check, and only when the database administrator deletes the directory object, first manually confirm that the directory object exists other external tables

Abalone New ********************

Reference: Xiaoxiang hermit great god http://www.cnblogs.com/kerrycode/p/3894260.html

Plsql_oracle the concept and use of external tables (case) (querying data files directly from external tables for section database tablespace)