External Tables
External tables uses the SQL*Loader functionality to access data in the operating system files without ever loading the data into a real oracle table. It is possible to read and write to a external table. Basically the data dictionary holds the table definition but the data remains in the o/s file but you can access the table just like any other table. External table offers some advantages over SQL*Loader
One small note is that you cannot index a external table, so high powered query work is impractical, if you must have indexes then use SQL*Loader and load the data into staging table or a temporary table. External tables are heavy used in data warehousing environments.
Creating | create table sales_ext ( Note: there are two types of access driver oracle_loader (load only) or oracle_datapump (load or unload) |
Creating (using datapump) | create table test ( product_id number(6), warehouse_id number(3), quantity_on_hand number (8) ) organization external ( type oracle_datapump default directory ext_data_dir location ('test.dmp') ); |
Writing (using datapump) | create table test organization external ( type oracle_datapump default directory ext_data_dir location ('test.dmp')) as select * from scott.dept; |
get SQL*Loader to generate the creation statement | sqlldr userid=vallep control=test.ctl external_table=generate_only log=log.txt Note: the creation statement will be located on the log file, obviously you have to create the SQL*Loader control file. |
See DataPump for more information