Create external table in Oracle

Create external table in Oracle

Oracle db allows you to query External tables in read-only mode. External tables can be stored on any storage device that can be read by oracle databases. The content of external tables is not saved in the database. db stores only the metadata of external tables, and db can query (join and sort) external table, which can create view and synonym, but cannot execute DML statements.

Create table... orginzition external: create table syntax. You can imagine an external table as a view and select the table normally.

Analyze and virtual column table analysis are not used with external tables

Create external table syntax

External table file:

Example1.txt

360, Jane, Janus, ST_CLERK, 2001-MAY-, 0, 50, jjanus
361, Mark, Jasper, SA_REP, 2001-MAY-8000,., mjasper
362, Brenda, Starr, AD_ASST, 2001-MAY-, 5500,0, 10, bstarr
363, Alex, Alda, AC_MGR, 2001-MAY-9000,., aalda
Example2.txt
401, Jesse, Cromwell, HR_REP, 203,17-MAY-2001, 0, 40, jcromwel
402, Abby, Applegate, IT_PROG, 2001-MAY-9000,., aapplega
403, Carol, Cousins, AD_VP, 2001-MAY-27000,., ccousins
404, John, Richard, AC_ACCOUNT, 2001-MAY-110, jrichard

Create an External table for oracle recognizable path:

SQL> conn/as sysdba
SQL> create directory external_dir as '/u01/app/oracle/oradata/external ';
 
Directory created.
 
SQL> grant read, write on directory external_dir to bkjia;
 
Grant succeeded.

Users who use external tables bkjia to create external tables

1 SQL> CREATE TABLE ex_employees
2 (employee_id NUMBER (4 ),
3 first_name VARCHAR2 (20 ),
4 last_name VARCHAR2 (25 ),
5 job_id VARCHAR2 (10 ),
6 manager_id NUMBER (4 ),
7 hire_date DATE,
8 salary NUMBER (8, 2 ),
9 commission_pct NUMBER (2, 2 ),
10 department_id NUMBER (4 ),
11 email VARCHAR2 (25)
12)
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 default directory external_dir
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile external_dir: 'empxt % a _ % p. bad'
21 logfile external_dir: 'empxt % a _ % p. Log'
22 fields terminated ','
23 missing field values are null
24 (employee_id, first_name, last_name, job_id, manager_id,
25 hire_date char date_format date mask "dd-mon-yyyy ",
26 salary, commission_pct, department_id, email
27)
28)
29 LOCATION ('example1.txt ', 'example2.txt ')
30)
31 PARALLEL
32 reject limit unlimited;
 
Table created.

After creation, you can import data to the database table through cats. If there is a large amount of data, you can enable session level parallel import.

Alter session enable parallel;
Create table employee as select * from ex_employee;
 
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
---------------------------------------------------------------------------------------------------------------------------------------------------
360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50 jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000. 1 80 mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0 10 bstarr
363 Alex Alda AC_MGR 145 17-MAY-01 9000. 15 80 aalda
 
401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40 jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000. 2 60 aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000. 3 90 ccousins
404 John Richard AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard
 
 
10 rows selected.

The External table is ready for use.