- external table using ORACLE_DATAPUMP
show user;
show con_name;
/*
USER is "HR"
CON_NAME
------------------------------
ORCLPDB
*/
# method 1
# create external table that create .dmp file from select statement
# 테이블이 만들어지는 순서, select >> EMP.dmp >> create table EMP_pump.
# 테이블의 데이터 타입이 없는 이유는 해당 데이터 타입은 select 문의 데이터 타입에 의해서 정해짐.
cREATE TABLE EMP_pump
(EMPLOYEE_NUMBER ,
FNAME ,
LNAME
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EMP_DIR
LOCATION ('EMP.dmp')
)
AS
SELECT EMPLOYEE_ID, FIRST_NAME,LAST_NAME
from EMPLOYEES;
/*
Table EMP_PUMP created.
[oracle@test pump]$ pwd
/u01/app/oracle/pump
[oracle@test pump]$ ls -ltr
합계 28
-rw-r--r-- 1 oracle oinstall 8 12월 20 22:42 EMP_LOAD_EXT_41785.bad
-rwxr-xr-x 1 oracle oinstall 65 12월 20 22:43 old_emp_data.csv
-rw-r--r-- 1 oracle oinstall 2663 12월 20 22:43 EMP_LOAD_EXT_41785.log
-rw-r----- 1 oracle oinstall 12288 12월 21 12:20 EMP.dmp
-rw-r--r-- 1 oracle oinstall 41 12월 21 12:20 EMP_PUMP_44662.log
[oracle@test pump]$ cat EMP_PUMP_44662.log
LOG file opened at 12/21/23 12:20:00
*/
SELECT * FROM EMP_PUMP where rownum < 10;
/*
EMPLOYEE_NUMBER FNAME LNAME
--------------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
....
*/
delete EMP_PUMP;
/*
Error starting at line : 71 in command -
delete EMP_PUMP
Error at Command Line : 71 Column : 8
Error report -
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 - "operation not supported on external organized table"
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don't do that!
*/
# method 2
# create external table that read existing ".dmp" file
CREATE TABLE EMP_PUMP_READ
(EMPLOYEE_NUMBER NUMBER ,
FNAME VARCHAR2(100),
LNAME VARCHAR2(100)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EMP_DIR
LOCATION ('EMP.dmp')
);
/*
Table EMP_PUMP_READ created.
*/
select * from EMP_PUMP_READ where rownum<10;
/*
EMPLOYEE_NUMBER FNAME LNAME
--------------- --------------- ---------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
....
*/
