CUUG 外部表學習筆記

oracle 外部表
一、建立外部表以及產生
1.建立一個directory，需要有create any directory許可權
create directory ext as 'D:/';

2.賦予許可權
grant read,write on directory ext to scott;

3.建立外部表
create table ext_emp
organization external
( type oracle_datapump
default directory ext
location ('ext_emp.dmp')
)
as select * from emp;

4.查詢
select * from ext_emp;

5.修改檔案，再次查詢測試

SQL> /
select * from ext_emp
              *
第 1 行出現錯誤:
ORA-29913: 執行 ODCIEXTTABLEOPEN 調出時出錯
ORA-29400: 資料外掛錯誤KUP-11010: unable to open at least one dump file for
fetch

 

二、使用已經產生了pump檔案建立外部表

1，把產生的 dmp dmp檔案複製到其它主機上

2.建立一個directory，需要有create any directory許可權
create directory ext2 as 'C:\app\YanWei\';

3.賦予許可權
grant read,write on directory ext2 to scott;

4.建立外部表

create table emp_ext3
(
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_datapump
default directory ext2
location ('1.DMP')
)

如果基表發生變化，如何更新，刪除dmp和外部表，然後匯出建立一遍

三、建立外部表，有三個匯出檔案。

create table ext_emp1
organization external
( type oracle_datapump
default directory ext
location ('ext_emp1.dmp''ext_emp2.dmp'，'ext_emp3.dmp')
)
as select * from emp;

 

---------------》外部表引用的dmp和expdp匯出dmp內部格式還是不一樣的，因此不能公用

四、sqlldr引擎的外部表

使用SQLLDR提供外部表的定義並建立外部表
關於SQL*Loader的使用請參照：SQL*Loader使用方法
我們使用SQL*Loader和下面的這個控制檔案來生成外部表的定義
$ cat demo1.ctl
LOADDATA
INFILE*
INTOTABLEDEPT_NEW
FIELDS TERMINATEDBY','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia        
賦予相應的許可權和建立表
SQL>grant create any directory to scott;
SQL>grant drop any directory to scott;
SQL>create table dept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
執行sqlldr命令
$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE引數有以下三個值：
NOT_USED：預設值。
EXECUTE：這個值說明SQLLDR不會生成並執行一個SQLINSERT語句；而是會建立一個外部表，且使用一個批次SQL語句來載入。
GENERATE_ONLY：使SQLLDR並不具體載入任何資料，而只是會生成所執行的SQL DDL和DML語句，並放到它建立的日誌檔案中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會載入資料，而不會生成外部表。

檔案檔案
179,Charles,6835.5,04-JAN-00
180,Winston,3528,24-JAN-98
181,Jean,3417.75,23-FEB-98
182,Martha,2756.25,21-JUN-99
183,Girard,3087,03-FEB-00
184,Nandita,4630.5,27-JAN-96
185,Alexis,4520.25,20-FEB-97
186,Julia,3748.5,24-JUN-98
187,Anthony,3307.5,07-FEB-99
188,Kelly,4189.5,14-JUN-97
189,Jennifer,3969,13-AUG-97
189,ERROR,3969,13-AUGs-9722
189,ERROR,3969S,13-AUG-9722

CREATE TABLE hr.sqlldr
(
  "EMPLOYEE_ID" NUMBER(6),
  "FIRST_NAME" VARCHAR2(20),
  "SALARY" NUMBER(8,2),
  "HIRE_DATE" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY datamove
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    LOAD WHEN ("EMPLOYEE_ID" != "188")
    BADFILE 'cardata.bad'
    DISCARDFILE 'cardata.dsc'
    LOGFILE 'cardata.log'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "EMPLOYEE_ID" CHAR(255)
        TERMINATED BY ",",
      "FIRST_NAME" CHAR(255)
        TERMINATED BY ",",
      "SALARY" CHAR(255)
        TERMINATED BY ",",
      "HIRE_DATE" CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "DD-MON-YY"
    )
  )
  location
  (
    'cardata.txt'
  )
)REJECT LIMIT UNLIMITED

可以追加資料。