Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle to create an external table     - Installed FFmpeg 2.6.3 on Ubuntu / Debian / Fedora system (Linux)

- Ubuntu resolve sudo: source: command not found error (Linux)

- Nginx supports user multi-threaded downloads and resume broken (Server)

- Unetbootin make use U disk loading Linux system (Linux)

- TeamCity continuous integration in the Linux installation (Linux)

- Linux, Oracle listen address modification (Database)

- C ++ why we chose to use the smart pointer (Programming)

- GitLab issued Merge Request return error 500 when the two solutions log (Linux)

- To configure parameter configuration and software installation and uninstallation under Linux (Linux)

- Related to optimize the use of Btrfs file system on SSD (Linux)

- Using monitoring tool dsniff (Linux)

- Spring next ActiveMQ combat (Programming)

- 11 you Linux Terminal Command (Linux)

- CentOS installation pycurl (Linux)

- To control based on IP address routing policy under Linux (Linux)

- Linux 6 install Oracle 11g (64bit) (Database)

- C ++ Supplements - References (Lvalue Reference, Rvalue Reference) (Linux)

- Ubuntu users how to install the latest Nvidia graphics drivers (Linux)

- Oracle 11g DataGuard service to start automatically (Database)

- How Linux Log Analysis (Linux)

 
         
  Oracle to create an external table
     
  Add Date : 2017-08-31      
         
       
         
  Oracle db allows read-only queries in the form of an external table. External table can be stored in any storage device that can be read in oracle db, its contents are not stored in db, db saves only the external table metadata, db can query (join, sort) external table, you can create a view, synonym, but not You can execute DML statements.

Create an external table syntax create table ...... orginzition external, can put a hypothetical external table view, you can do a normal select.
analyze and virtual column table analysis does not use external tables

Create an external table syntax
External table file:
example1.txt
360, Jane, Janus, ST_CLERK, 121,17-MAY-2001,3000,0,50, jjanus
361, Mark, Jasper, SA_REP, 145,17-MAY-2001,8000, .1,80, mjasper
362, Brenda, Starr, AD_ASST, 200,17-MAY-2001,5500,0,10, bstarr
363, Alex, Alda, AC_MGR, 145,17-MAY-2001,9000, .15,80, aalda
example2.txt
401, Jesse, Cromwell, HR_REP, 203,17-MAY-2001,7000,0,40, jcromwel
402, Abby, Applegate, IT_PROG, 103,17-MAY-2001,9000, .2,60, aapplega
403, Carol, Cousins, AD_VP, 100,17-MAY-2001,27000, .3,90, ccousins
404, John, Richardson, AC_ACCOUNT, 205,17-MAY-2001,5000,0,110, jrichard
Create an external table oracle path can be identified:

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 kevin;

Grant succeeded.
User external table kevin, create an 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,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 by ','
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.
You can import the data by cats when you are finished to a table in the database, if the data more, you can open the 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 jrichard


10 rows selected.
Created, external table can be used normally
     
         
       
         
  More:      
 
- Golang use Oracle database on Ubuntu 14.04 (Linux)
- Disk storage structure and file recovery experiment (FAT file system) (Linux)
- Linux environment to build next Cocos2dx-3.3.1 (Linux)
- Object Oriented Programming Java reflection (Programming)
- Ubuntu installation 2.10.x version of Scala (Linux)
- Several Methods of SSH Auto - login (Linux)
- To setup the Swift language learning environment under linux (Linux)
- RedHat yum configuration and the prompt (Linux)
- Linux installed Cisco Packet Tracer (Linux)
- Linux supports serial output method (Linux)
- Install Open vSwitch under CentOS 6.5 (Linux)
- DataGuard the MRP can not start to analyze and solve problems (Database)
- Comparison of sorting algorithms (Programming)
- Linux into single user mode to modify the administrator password (Linux)
- Xshell configure SSH free password (Server)
- Nodejs nano library to handle couchdb: need a timeout (Database)
- 64 Ubuntu 15.04 Linux kernel upgrade to Linux 4.1.0 (Linux)
- There is sort of a directed acyclic graph topology (Programming)
- Linux system security audit tools scan nessus installation tutorial (Linux)
- MySQL password on those things you should know (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.