Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle to create an external table     - Linux System Getting Started Learning: After starting in Ubuntu or Debian, enter the command line (Linux)

- Encrypted with GnuPG signature to verify the authenticity and integrity of downloaded file (Linux)

- Awk include binding capacity larger than the specified size of all files directory (Linux)

- SecureCRT connect virtual machine Linux system (Ubuntu) (Linux)

- Oracle Database High-Risk Vulnerability Warning (Database)

- Ubuntu Eclipse configuration Gtk + 2.0 libraries (Linux)

- How to download apk file from the Google Play store on Linux (Linux)

- AngularJS achieve picture upload feature (Programming)

- Syncthing: Private Security Synchronization tool to synchronize files between computers / folder (Linux)

- IP Security Policy is to learn how to prevent Ping and closed ports (Linux)

- The best tools and techniques to find data on a Unix system (Linux)

- MySQL configuration file my.cnf increase the log file parameter error (Database)

- Compile and install Memcached can not find GCC (Programming)

- hexdump Linux command (Linux)

- Linux garbled file delete method (Linux)

- Linux installation and configuration curl command tool (Linux)

- Linux tmux tcpdump summary (Linux)

- Linux how to prohibit the use of Ping command (Linux)

- Several SQL Server data migration / Export Import Practice (Database)

- CentOS / Linux NIC set IP address configuration (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:      
 
- The user how to install Notepadqq 0.41.0 under ubuntu and debian (Linux)
- Android components series Activity (Programming)
- Keepalived achieve high availability Nginx Reverse Proxy (Server)
- Oracle inline view updates problems encountered (Database)
- Ubuntu 14.04 install Nmap 6.46.1 (Linux)
- Linux support exFAT and NTFS (Linux)
- Android annotation support (Support Annotations) (Programming)
- Ubuntu deployed under regular tasks with crontab (Linux)
- What Java 8 fastest garbage collector (Programming)
- Installation Experience open source car Automotive Grade Linux system (Linux)
- Ubuntu will be written in a command file, executable file, source command (Linux)
- Intrusion prevention network server security maintenance tips (Linux)
- Relationship between Linux permissions with the command (Linux)
- Android Sets the system screen brightness (Programming)
- Taught you how to install Ubuntu Linux (Linux)
- Install the Red Hat Container Development Kit on OSX (Server)
- Installation Mate Desktop in FreeBSD 10.1 (Linux)
- Android custom slideshow menu slidmenu (Programming)
- Ubuntu 14.10 / Linux Mint 17.1 Win10 compiler install rdesktop 1.8.3 Remote Desktop connection (Linux)
- Linux / UNIX: Use the dd command to create a 1GB size binary (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.