Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle to create an external table     - Oracle RMAN repair logical bad blocks (Database)

- Spring AOP custom annotation way to achieve log management (Programming)

- Linux at command (Linux)

- Linux Desktop allows exceptionally different launch applications (Linux)

- Commentary Apache + Tomcat + JK implement Tomcat clustering and load (Server)

- Windows and Ubuntu dual system, repair of two ways UEFI boot (Linux)

- Oracle database physical file backup / restore (Database)

- Linux, Eclipse flash back and reinstall the JDK methods (Linux)

- Install and configure GO 1.2.1 under CentOS 6.5 (Linux)

- OpenGL Superb Learning Notes - New Patterns (Programming)

- How to Install Puppet in the Ubuntu 15.04 (Server)

- IBM Data Studio to use ---- window displays all rows (Database)

- Apache POI Excel Document Processing (Linux)

- Enterprise-class GitHub warehousing environment build (Server)

- PostgreSQL Source Customization: Online global read only (Database)

- Configuring DNS process under CentOS 6.5 (Server)

- C ++ constant definition (Programming)

- PSUADE installation under Linux (Linux)

- Linux 6 use UDEV binding shared storage (Linux)

- Installation Yarock 1.1.4 Music Player in Ubuntu (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:      
 
- Install the latest development version of Wine on RedHat and Debian-based systems (Linux)
- Linux FAQ - How to fix tar:Exitingwith failure status due to previous errors (Linux)
- Minimum period string problem (Programming)
- Connect to the Oracle Database Help class (Database)
- Oracle Client Easy Connection error ORA-12154, TNS-03505 (Database)
- To install Scribus 1.4.4 under ubuntu (Linux)
- How to configure chroot environment in Ubuntu 14.04 (Linux)
- Linux System Administrator common interview questions and answers 30 (Linux)
- Under CentOS using yum command to install the Task Scheduler crontab (Linux)
- PHP call a Python program (Programming)
- Increase Linux system security --chattr (Linux)
- OpenNMS separate database (Server)
- CentOS 6.5 makes the LAN http source (Linux)
- Commonly used Linux system camouflage method (Linux)
- MySQL 5.7 and 5.6 group by differences (Database)
- Java method to read and write files summary (Programming)
- IOS interview questions Summary (Programming)
- You must ask yourself four questions before deploying Docker (Server)
- MySQL optimization tabs (Database)
- Linux automatically install service components and optimize the kernel parameters (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.