外部表

外部表的使用

外部表不可以做DML操作，也不可以被truncate,因为外部表的数据是存储在操作系统上的，而不是被数据库系统所管理；但是外部表可以被drop，但是实际的数据并没有被删除，人保留在操作系统上。

1. 创建文件目录

• 创建操作系统目录

    [oracle@oracle ~]$ mkdir /home/oracle/ext
  

• 在数据库中创建外部表目录

  SQL> create directory ext_dir as '/home/oracle/ext';
  
  Directory created.
  

• 赋予用户对外部表目录的读写权限

  SQL> grant read,write on directory ext_dir to hr;
  
  Grant succeeded.
  
  SQL>
  

2. 创建测试数据

• 创建测试数据

  [oracle@oracle ~]$ cd /home/oracle/ext
  [oracle@oracle ext]$ vi emp.dat
  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
  "7369","SMITH","CLERK","7902","1980/12/17","800.00","","20"
  "7499","ALLEN","SALESMAN","7698","1981/2/20","1600.00","300.00","30"
  "7521","WARD","SALESMAN","7698","1981/2/22","1250.00","500.00","30"
  "7566","JONES","MANAGER","7839","1981/4/2","2975.00","","20"
  "7654","MARTIN","SALESMAN","7698","1981/9/28","1250.00","1400.00","30"
  "7698","BLAKE","MANAGER","7839","1981/5/1","2850.00","","30"
  "7782","CLARK","MANAGER","7839","1981/6/9","2450.00","","10"
  "7788","SCOTT","ANALYST","7566","1987/4/19","3000.00","","20"
  "7839","KING","PRESIDENT","","1981/11/17","5000.00","","10"
  "7844","TURNER","SALESMAN","7698","1981/9/8","1500.00","0.00","30"
  "7876","ADAMS","CLERK","7788","1987/5/23","1100.00","","20"
  "7900","JAMES","CLERK","7698","1981/12/3","950.00","","30"
  "7902","FORD","ANALYST","7566","1981/12/3","3000.00","","20"
  "7934","MILLER","CLERK","7782","1982/1/23","1300.00","","10"
  

3. 创建外部表

• 执行以下语句创建外部表

  SQL> create table emp
  (
  empno number(4),
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2)
  )
  organization external
  (
  type oracle_loader
  default directory ext_dir
  access parameters
  (
  records delimited by newline
  badfile ext_dir:'emp.bad'
  discardfile ext_dir:'emp.dsc'
  logfile ext_dir:'emp.log'
  field names all files ignore
  fields terminated by ',' optionally enclosed by '"' ldrtrim
  reject rows with all null fields
  (empno,ename,job,mgr,hiredate char(10) date_format date mask 'yyyy-mm-dd',sal,comm,deptno))
  location('emp.dat')
   27  )reject limit unlimited;
  
  Table created.
  
  SQL>
  

• 查看外部表的数据

  SQL> select * from emp;
  
       EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
        7369 SMITH                CLERK                    7902 1980-12-17 00:00:00        800                    20
        7499 ALLEN                SALESMAN                 7698 1981-02-20 00:00:00       1600        300         30
        7521 WARD                 SALESMAN                 7698 1981-02-22 00:00:00       1250        500         30
        7566 JONES                MANAGER                  7839 1981-04-02 00:00:00       2975                    20
        7654 MARTIN               SALESMAN                 7698 1981-09-28 00:00:00       1250       1400         30
        7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30
        7782 CLARK                MANAGER                  7839 1981-06-09 00:00:00       2450                    10
        7788 SCOTT                ANALYST                  7566 1987-04-19 00:00:00       3000                    20
        7839 KING                 PRESIDENT                     1981-11-17 00:00:00       5000                    10
        7844 TURNER               SALESMAN                 7698 1981-09-08 00:00:00       1500          0         30
        7876 ADAMS                CLERK                    7788 1987-05-23 00:00:00       1100                    20
        7900 JAMES                CLERK                    7698 1981-12-03 00:00:00        950                    30
        7902 FORD                 ANALYST                  7566 1981-12-03 00:00:00       3000                    20
        7934 MILLER               CLERK                    7782 1982-01-23 00:00:00       1300                    10
  
  14 rows selected.
  
  SQL>
  

4. 总结

数据文件无论是在外部表之前创建，还是之后创建，均可以在数据库中查询到数据。