Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle to create an external table     - Ubuntu Froxlor Server Administration panel installation (Server)

- ApacheDS configuration of users and user groups to achieve SSO (Server)

- JavaScript common array manipulation functions and usage (Programming)

- Python 2 Chinese garbage problem solved (Linux)

- Android custom slideshow menu slidmenu (Programming)

- Ubuntu 12.04 commonly use shortcuts finishing Share (Linux)

- Embedded Linux to solve the problem in the time zone (Linux)

- An example of troubleshooting of embedded Linux OpenWRT (Linux)

- CentOS 7 virt-manager can not connect a local hypervisor (Linux)

- RedHat Linux 6.4 installation RTL8188CUS wireless network card driver (Linux)

- Iptables small summary (Linux)

- Terminal fun: 6 interesting Linux command-line tools (Linux)

- Linux system security norms (Linux)

- Processor in protected mode of protection (Linux)

- How to create an alternative Android / iOS connected wireless hotspot AP in Ubuntu 15.04 (Linux)

- Use cmake to compile and install MySQL 5.5 (Database)

- MySQL event table to achieve timing build a small note (Database)

- Configuration OpenOCD + FT2232 under Ubuntu (Linux)

- Talk about Java EE Learning (Programming)

- Automate deployment of Docker-based Rails applications (Server)

 
         
  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:      
 
- ORA-12545: Connection failed because the target host or object does not exist (Database)
- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)
- Varnish achieve page jump (Server)
- Four levels of intrusion on Linux server and counter-measures (Linux)
- The security configuration of Linux (Linux)
- To install Redis under Linux (Database)
- Spring + Log4j + ActiveMQ remote logging - Analysis of combat (Server)
- C ++ casts New Standard Comments (Programming)
- MySQL master-slave delay problem (Database)
- 10 tips daily Docker (Server)
- Java object serialization (Programming)
- Redis performance test (Database)
- Docker Build a Java compiler environment (Linux)
- Hibernate + JUnit test entity class generate database table (Programming)
- Getting Started with Linux: Learn how to install and access CentOS 7 Remote Desktop on a VPS (Server)
- How linux network security configuration (Linux)
- Hard disk encryption to protect data security (Linux)
- The correct way of logical backup mysqldump (Database)
- File easier to compare tools under Linux (Linux)
- Linux CPU Monitoring Index (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.