Shulou(Shulou.com)05/31 Report--

This article shows you how to create external tables in oracle, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Oracle db allows external tables to be queried as read-only. External tables can be stored in any storage device that can be read by oracle db, and their contents are not saved in db. Db only saves external table's metadata,db can query (join, sort) external table, can create view, synonym, but cannot execute DML statements.

Create external table syntax create table .orginzition external, you can imagine the external table as a view, you can do select normally.

Analyze and virtual list analysis are not used with external tables

Create external table syntax

External table file:

Example1.txt

360pr Janej Janusjusjanus janus

361 Mark, Jasper.361, 361, Jasper.Rep, 145, 17, MAYMAY, 2001, 8000. 1, 80, mjasper.

362 pencils Brenda pr é cor Starr pr agem ADLI ASSTJI 200pr 17 Murray MAYPY 2001 pr 5500 meme 0je 10 meme bstarr

363 mae mai mai 2001 meme 9000 meme. 15pl 80 meme Aalda.

Example2.txt

401, Jesse.Cromwell, HR, REP, 203, 17, May, 2001, 7, 000, and 40, jcromwel.

402, Abby, Applegate.402, Abby, Applegate.It 's called Program, 103, May, May, 2001, May, 2001, 9000,. 2, 60, aapplega.

403 Carolin.Cousins.AdditionVP.100.17murmurly2001 27000dy.3jin90dycousins.

404 John RichardsonrecoveryACCOUNT205pr 17lylyly2001pr 5000jrichl 110jrichard

Create an external table oracle-aware path:

SQL > conn / as sysdbaSQL > create directory external_dir as'/ u01 grant read,write on directory external_dir to kevin;Grant succeeded.

The user kevin of the external table creates the external table

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), 9 commission_pct NUMBER (2), 10 department_id NUMBER (4) 11 email VARCHAR2 (25) 12) 13 ORGANIZATION EXTERNAL14 (15 TYPE ORACLE_LOADER16 DEFAULT DIRECTORY external_dir17 ACCESS PARAMETERS18 (19 records delimited by newline20 badfile external_dir:'empxt%a_%p.bad'21 logfile external_dir:'empxt%a_%) P.log'22 fields terminated by' '23 missing field values are null24 (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, email27) 28) 29 LOCATION (' example1.txt', 'example2.txt') 30) 31 PARALLEL32 REJECT LIMIT UNLIMITED Table created.

After the creation is completed, you can import the data into the database table through cats. If there is more 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 Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard10 rows selected. The above is how to create external tables in oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.