Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle to create an external table     - Redis-2.8.17 installation and configuration process some errors (Linux)

- Let's Encrypt with semiautomatic into Nginx configuration https (Server)

- Joseph Central Java implementation (Programming)

- How to upgrade to Ubuntu 14.04 Ubuntu 14.10 (Linux)

- Linux system performance monitoring with Nmon (Linux)

- Nodejs complete installation instructions for Express (Linux)

- How to choose the first programming language based on the life you want (Programming)

- 11.2.04 Oracle RAC directory crfclust.bdb file is too large, Bug 20186278 (Database)

- Linux System Getting Started Learning: Join cron job in Linux (Linux)

- Nagios (centreon) monitoring Linux Log (Server)

- Oracle online redefinition (Database)

- Sqoop data export import command (Database)

- Use regular expressions to check whether the input box to enter a URL (Programming)

- CentOS 6.5 installation configuration DRBD (Server)

- Python in yield (Programming)

- Linux iptables: combat scenes (Linux)

- Configure the ASM process on Red Hat Linux 6.5 (Database)

- Oracle Linux 5.5 (64bit) Install Oracle 11gR2 RAC detailed tutorial (Database)

- Linux LVM Logical Volume Management to resize partitions (Linux)

- Linux in order to make NMAP hide and seek with the firewall (Linux)

 
         
  Oracle to create an external table
     
  Add Date : 2016-05-14      
         
         
         
  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 linuxidc;
 
Grant succeeded.

User external table linuxidc, create an external table

  1 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.
     
         
         
         
  More:      
 
- Database Blob data type conversion String (Programming)
- CentOS installed JDK8 (Linux)
- To configure Samba to share files with Windows under CentOS (Linux)
- Six Ways to view slides can be implemented Android (Programming)
- Using Vagrant create cross-platform development environment (Linux)
- Ubuntu 15.04 installation MATE 1.10 (Linux)
- Ubuntu Thunderbird 24.4.0 (Linux)
- MySQL 5.7 perfectly distributed transaction support (Database)
- IOS interview questions Summary (Programming)
- In the case of using cgroups Ubuntu 14.04 and Docker (Linux)
- Nginx version of helloworld (Server)
- VMware installed Linux system and JDK deployment (Linux)
- Nginx high concurrency optimization ideas (Server)
- Oracle Database ORA-01555 snapshot too old (Database)
- Iptables in Ubuntu (Linux)
- Syncthing: Private Security Synchronization tool to synchronize files between computers / folder (Linux)
- ActiveMQ memory settings and flow control (Linux)
- How to install web crawler tool in Ubuntu 14.04 LTS: Scrapy (Linux)
- Spring Data JPA call a stored procedure examples (Programming)
- CentOS / Linux kernel upgrade (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.