External Tables (again)

External Tables

External tables are operating system flat files (stored outside of Oracle), but whose definition is maintained in Oracle’s data dictionary. External tables appear as read-only tables to Oracle applications. They’re useful for reference to flat files that you don’t want to load into Oracle tables (perhaps the flat files are big and rarely accessed, for example). Here are the steps to set up an external table:

1. Create a directory object to define the external table. Example:

CREATE OR REPLACE DIRECTORY test_dir AS ‘c:\exttabs\data’;

2. GRANT privileges on that directory object
3. Create the external table definition with the CREATE TABLE command. The key clause here is ORGANIZATION EXTERNAL. Use keyword PARALLEL if you want to enable parallel query. By default the access driver will be ORACLE LOADER.

You can not place an index on an external table. Remember that since external tables are actually operating system files, one could bypass Oracle’s security to access the table.