Instructions for use in Oracle9i in the external table: This post describes the primary use of the Oracle9i External Table (External Table). What is an external table? External Table and regular tables are very similar. Specific definitions can be found in the Oracle Concept Handbook, and the following points need to note:> Created syntax is similar to: "CREATE TABLE ... Organization External"> Database external organization It is an operating system file. > Operating system files in the database are mapped by a logical directory. > The data is read-only. (External table is equivalent to a read-only virtual table)> You cannot run any DML operation above, you cannot create an index. > You can query the operation and connection. Can be operated in parallel. Example: Assuming two data files with the following: 1 Plane file data Description assumes the following two flat files 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, 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 (to have read and write permissions for this directory in the operating system.) 2 Create a logical directory and appropriate authorization: SQL> Create Directory TestDir as 'd: / temp /'; directory has create. SQL> Grant Read on Directory TestDir to Demo; Authorized Success. SQL> Grant Write on Directory TestDir to Demo; Authorized Success. Note: After creating a logical directory, copy the flat file to this directory, and pay attention to the file name should not be mistaken.
3 Creating an external table SQL> ED written file Afiedt.buf 1 Create Table Demo.ext 2 (Emp_id Number (4), 3 ename varcha2 (12), 4 Job Varchar2 (12), 5 mgr_id number (4), 6 hiredate date, 7 salary number (8), 8 comm number (8), 9 dept_id number (2)) 10 ORGANIZATION EXTERNAL 11 (TYPE ORACLE_LOADER 12 DEFAULT DIRECTORY TESTDIR 13 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE 14 FIELDS TERMINATED BY ',' 15 * location ('1.dat', '2.dat') SQL> / table has been created. 4 Take a SELECT selection operation to see if it is correct: SQL> Select * from demo.ext; Emp_id ename job mgr_id hiRedate Salry Comm Dept_ID ---------------------- ---------------------- ---------- ------- 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 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 If you want to get information about external tables: SQL> DBA_EXTERNAL_TABLES;
name------------------------------------------------- ---------------- OWNER TABLE_NAME TYPE_OWNER TYPE_NAME DEFAULT_DIRECTORY_NAME DEFAULT_DIRECTORY_NAME REJECT_LIMIT Access_Type ACCESS_PARAMETERS SQL> SELECT OWNER, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_PARAMETERS 2 FROM 3 DBA_EXTERNAL_TABLES; OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_PARAMETERS ------------------ ------------ -------------------------------------------- Demo EXT TestDir Records Delimited by Newline Fields Terminated By ',' If DBA wants to know the location of the planar file, use the following query: SQL> DESC DBA_EXTERNAL_LOCATIONS;
