oracle 外部表用法

--创建数据目录
 create or replace directory dat_dir as '/home/oracle/external_tb/data/';
 
 b.创建外部表
SQL>create table ex_tb1   --创建外部表
    (ename,job,sal,dname)      --表列描述，注意未指定数据类型
    organization external
    (
    type oracle_datapump      --使用datapump将查询结果填充到外部表,注,此处由select生成，故不支持
                                           --oracle_loader
    default directory dat_dir   --指定外部表的存放目录
    location('tb1.exp，tb2.exp'))
    parallel 2                       --按并行方式来填充，这里的并行度必须与生成的文件数量一致才能起作用，详细算法可
    as                                   --以参看http://czmmiao.iteye.com/blog/1268453 
   select ename,job,sal,dname --填充使用的原始数据
   from emp join dept
   ON emp.depno=dept.deptno 
   
SELECT * FROM emp
SELECT * FROM dept
SELECT * FROM ex_tb1


create table emp (
  empno number(4),
  ename char(10),
  job char(9),
  mgr number(4),
  hiredate date,
  sal number(9,2),
  comm number(9,2),
  deptno number(4));


insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, null, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);






SQL> 
create table dept (
deptno number(4),
dname char(14),
loc char(14));


insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');




SQL> 
create table salgrade (
grade number(6),
losal number(6),
hisal number(6));


insert into salgrade (grade, losal, hisal) values (1,700,1200);
insert into salgrade (grade, losal, hisal) values (2,1201,1400);
insert into salgrade (grade, losal, hisal) values (3,1401,2000);
insert into salgrade (grade, losal, hisal) values (4,2001,3000);


insert into salgrade (grade, losal, hisal) values (5,3001,5999);




SQL>grant create any directory to scott;
SQL>grant drop any directory to scott;
create table dept_new
 (deptno number,dname varchar2(20),loc varchar2(25)); 
 
 
 
  
 CREATE TABLE external_tab
    (
       id varchar2(4 char),
       name varchar2(12 char)
    )
    ORGANIZATION EXTERNAL
    (
       TYPE ORACLE_LOADER
      DEFAULT DIRECTORY dat_dir
      ACCESS PARAMETERS
   (
   RECORDS DELIMITED BY NEWLINE
   FIELDS TERMINATED BY ',' )
   LOCATION('cc.txt') );
  
  
  SELECT * FROM external_tab
  
  
  drop TABLE external_tab
  
  
  create table students(-- 外部表的创建语句和通常的create table 语句大部分相同。
 id varchar2(16),
 name varchar2(30),
 email varchar2(20),
 gender char(1),
 age number(2)
 )
 organization external--指定为外部表。
 ( type oracle_loader-- 指定外部表的加载类型。有oracle_loader,oracle_datapump。
 default directory dat_dir--指定外部表默认的读写位置，是通过目录对象指定的，而不是实际的目录。
 access parameters--指定根据什么规则把外部文件中的数据加载到外部表中。
 ( records delimited by newline
 fields terminated by ','--指定以什么分隔
 )
 location ('students_info.csv')--指定外部表加载的数据源的位置。
 ) 
   
   SELECT * FROM students
   
   
   SELECT * FROM t1 
   
   
   
   CREATE TABLE stenny_ext_product
 (product_id NUMBER(4),
 product_name VARCHAR2(20),
 location VARCHAR2(25)
 )
 ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY dat_dir
 ACCESS PARAMETERS
 (
 records delimited by newline
 badfile dat_dir:'bad_product.dat'
 logfile dat_dir:'product.log'
 fields terminated by ','
 missing field values are null
 ( product_id, product_name, location )
 )
 LOCATION ('product1.dat')
 )
 
 SELECT * FROM stenny_ext_product
 DROP TABLE stenny_ext_product
 SELECT * FROM user_objects WHERE object_type='TABLE'
 
 CREATE OR REPLACE PROCEDURE proc_txn_product AS
BEGIN
insert into stg_product select product_id,product_name,loc_id from stenny_ext_product,loc_std
where loc_std.loc_name=stenny_ext_product.location;
insert into stg_excep select * from stenny_ext_product where product_id not in (select product_ id from stg_product);
commit;
END proc_txn_product




SELECT * FROM user_tablespaces






create cluster my_clu (deptno number )  
    pctused 60  
    pctfree 10  
    size 1024  
    tablespace USER_DATA
    storage (  
    initial 128 k  
    next 128 k  
    minextents 2  
    maxextents 20  
    );  
    
  create table t1_dept(  
    deptno number ,  
    dname varchar2 ( 20 )  
    )  
    cluster my_clu(deptno);  
    create table t1_emp(  
    empno number ,  
    ename varchar2 ( 20 ),  
    birth_date date ,  
    deptno number  
    )  
    cluster my_clu(deptno);     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26495863/viewspace-1349801/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26495863/viewspace-1349801/