---empxt1.dat------
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
⇒これを素直に取り込む
---empxt2.dat------
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
⇒これを一度gzに固めて、preprocessor経由で取り込む。そのために解凍スクリプトを準備。
#解凍スクリプト
[oracle@single data]$ cat zcat
/bin/gunzip -c $1
その３として、datapumpと絡んだ外部表で遊びます。
ではでは、ここから外部表を作成します。
#ちょっと前準備。サンプルの日付パターンが英語なので。
SH@orcl > alter session set nls_language='AMERICAN';
#ディレクトリオブジェクトへ権限付与
SYS@orcl > grant all on directory data_dir to sh;
#外部表その１
CREATE TABLE ext_emp
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile data_dir:'empxt%a_%p.bad'
logfile data_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
LOCATION ('empxt1.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
#外部表その２
CREATE TABLE sales_transactions_ext
(
employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE★
CHARACTERSET AL32UTF8
PREPROCESSOR data_dir:'zcat'★
FIELDS TERMINATED BY ","
(
employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
location ('empxt2.dat.gz')
)REJECT LIMIT UNLIMITED;
#外部表その３
SH@orcl > CREATE TABLE test_pump
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_DATAPUMP
5 DEFAULT DIRECTORY data_dir
6 LOCATION ('table2.dmp')
7 )
8 AS SELECT * FROM sales_transactions_ext where EMPLOYEE_ID=401;
Table created.
#確認
SH@orcl > select * from test_pump;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID
----------- -------------------- ------------------------- ---------- ----------
HIRE_DAT SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
-------- ---------- -------------- ------------- -------------------------
401 Jesse Cromwell HR_REP 203
01-05-17 7000 0 40 jcromwel
#deleteとか出来ちゃうか確認。外部表なので、いじれません。
SH@orcl > delete from test_pump;
delete from test_pump
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
＜参考マニュアル＞
Oracle® Database管理者ガイド
20 表の管理
外部表の管理
http://docs.oracle.com/cd/E16338_01/server.112/b56301/tables.htm#i1007424
http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
Oracle® Databaseユーティリティ
11gリリース2 (11.2)
15 ORACLE_DATAPUMPアクセス・ドライバ
http://docs.oracle.com/cd/E16338_01/server.112/b56303/et_dp_driver.htm#i1006773
0 件のコメント:
コメントを投稿