External Tables

External tables are read-only tables whose data resides outside the database in an external operating system resident flat file but whose definition resides in the database. You can query the external table just as if it were a regular table. You specify the external table by adding the ORGANIZATION EXTERNAL clause to your CREATE TABLE command.

You need to develop the specifications for the external file using formats similar to that used in SQL Loader control files.

First, you need to create the directory objects in Oracle that refer to the operating system directories that contain the data sources that are the destination for bad records and the destination for log files:

 CREATE OR REPLACE DIRECTORY datadir as 'd:\datadir'; CREATE OR REPLACE DIRECTORY logdir as 'd:\logdir'; CREATE OR REPLACE DIRECTORY baddir as 'd:\baddir'; Next, grant permissions on these directories. GRANT READ ON DIRECTORY datadir TO user1; GRANT WRITE ON DIRECTORY logdir TO user1; GRANT WRITE ON DIRECTORY baddir TO user1; 

Now, define the external table:

 CREATE TABLE ext_addresses( Fname varchar2(35), Lname varchar2(35), Birthday date, Email_address varchar2(100), Street_addr1 varchar2(100), Street_addr2 varchar2(100), Street_addr3 varchar2(100), City varchar2(50), State_abbr char(2), Zip_code number) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY datadir ACCESS PARAMETERS (badfile baddir:'external_addresses.bad' Logfile logdir:'external_addresses.log' Records delimited by newline Fields terminated by ',' optionally enclosed by '"' missing field values are null (Fname, Lname, Birthday, Email, Street_addr1,Street_addr2, Street_addr3, City, State_abbr, Zip_code) ) ) Location ('addresses.dat' ) REJECT LIMIT UNLIMITED; 

You can change the table definition on these the same as you can for any other table, using add, modify, or drop columns with the ALTER TABLE command. You can DROP the external table (although this removes only the definition, not the operating system file, even if you are using OFS).

The following lists some limitations on the use of external tables:

• You cannot generate indexes on external tables.

• You cannot perform DML on the data; the table is read-only.

• Oracle's security can be bypassed if a user has access to the underlying operating system files and the authority to change the contents of those files.

It is important to understand how storage structures are managed in Oracle even if you are not using dictionary managed tablespaces.