CONN / AS SYSDBA;
-- FM) CREATE DIRECTORY dirctory_name AS 'dircetory_path';
-- THE AUTHORITY TO SELECT EXTERNAL TABLE.
GRANT READ ON DIRECTORY DATA_PUMP_DIR TO SH;
-- FM) GRANT READ ON DIRECTORY dirctory_name TO schema;
-- THE AUTHORITY TO CREATE EXTERNAL TABLE.
GRANT WRITE ON DIRECTORY DATA_PUMP_DIR TO SH;
-- FM) GRANT WRITE ON DIRECTORY dirctory_name TO schema;
CONN SH/SH;
/***
*** 1. FILE --EXTERNAL TABLE--> ORACLE
*** TYPE : ORACLE_LOADER
***/
drop table sales_delta_xt purge;
-- CREATE EXTERNAL TABLE sales_delta_xt
create table sales_delta_xt (col varchar2(20))
organization external(
type oracle_loader
default directory DATA_PUMP_DIR
access parameters
(
records delimited by newline
characterset US7ASCII
badfile DATA_PUMP_DIR:'sh_sales_%p.bad'
nologfile
fields terminated by "," ldrtrim
)
location ( 'sales_delta1.dat',
'sales_delta2.dat',
'sales_delta3.dat',
'sales_delta4.dat',
'sales_delta5.dat',
'sales_delta6.dat',
'sales_delta7.dat',
'sales_delta8.dat',
'sales_delta9.dat',
'sales_delta10.dat')
)
parallel 8
reject limit unlimited;
--logfile DATA_PUMP_DIR:'sh_sales_%p.log_xt'
-- EXTERNAL TABLE 확인
select * from user_tables;
select * from sales_delta_xt;
-- PARALLEL 사용위한 명령
ALTER SESSION ENABLE PARALLEL DML;
-- CTAS STATEMENT.
CREATE TABLE T_COL1
AS SELECT * FROM SALES_DELTA_XT;
-- INSERT TO TARGET
-- insert /*+APPEND NOLOGGING*/ INTO T_COL1
-- select * from sales_delta_xt;
COMMIT;
-- 확인
select count(*) from T_COL1;
conn / as sysdba;
create user oe2_bak identified by oe2_backup;
grant connect, resource to oe2_bak;
create directory odp_dir as 'C:\oracletbs\ORCL\datapump';
grant read,write on directory odp_dir to oe;
grant read,write on directory odp_dir to oe2_bak;
-- CONNECT TO SOURCE
conn oe/oe;
/***
*** 2. TYPE : ORACLE_DATAPUMP
***/
/*** 2.1. ORACLE --EXTERNAL TABLE--> FILE --EXTERNAL TABLE--> ORACLE ***/
-- CREATE EXTERNAL TABLE INVENTORIES_XT USING CTAS STATEMENT
CREATE TABLE INVENTORIES_XT
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY odp_dir
LOCATION ('INV_XT.DMP')
)
AS SELECT * FROM INVENTORIES;
-- CHECK SOURCE EXTERNAL TABLE DATA
SELECT COUNT(*)
FROM (
SELECT * FROM INVENTORIES
MINUS
SELECT * FROM INVENTORIES_XT
);
-- CONNECT TO TARGET
conn oe2_bak/oe2_backup;
-- CREATE EXTERNAL TABLE USING EXISTING FILE
CREATE TABLE INVENTORIES_XT2
(
PRODUCT_ID NUMBER(6),
WAREHOUSE_ID NUMBER(3),
QUANTITY_ON_HAND NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY odp_dir
LOCATION ('INV_XT.DMP')
);
-- CHECK TARGET EXTERNAL TABLE DATA
SELECT * FROM INVENTORIES_XT2;
-- CREATE DUPLICATE DATA FROM EXTERNAL TABLE TO TARGET TABLE
CREATE TABLE DUP_INVENTORIES
AS SELECT * FROM INVENTORIES_XT2;
--CHECK TARGET TABLE DATA
SELECT * FROM DUP_INVENTORIES;
-- END TRANSACTION
DROP TABLE DUP_INVENTORIES PURGE;
DROP TABLE INVENTORIES_XT2 PURGE;
/*** 2.2. PARALLEL AND UNPARALLEL UNLOADING EXTERNAL TABLE ***/
conn oe/oe;
-- CREATE EXTERNAL TABLE PARALLEL 3 = 0.70S
CREATE TABLE INVENTORIES_XT3
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY odp_dir
ACCESS PARAMETERS
(
NOLOGFILE
)
LOCATION( 'INV_XT1.DMP',
'INV_XT2.DMP',
'INV_XT3.DMP'
)
)
PARALLEL 3
AS SELECT * FROM INVENTORIES;
DROP TABLE INVENTORIES_XT3 PURGE;
-- CREATE EXTERNAL TABLE NO PARALLEL = 0.44S
CREATE TABLE INVENTORIES_XT4
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY odp_dir
ACCESS PARAMETERS
(
NOLOGFILE
)
LOCATION( 'INV_XT4.DMP'
)
)
AS SELECT * FROM INVENTORIES;
DROP TABLE INVENTORIES_XT4 PURGE;
-- CREATE EXTERNAL TABLE PARALLEL 8 = 1.13S
CREATE TABLE INVENTORIES_XT8
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY odp_dir
ACCESS PARAMETERS
(
NOLOGFILE
)
LOCATION( 'INV_XT1.DMP',
'INV_XT2.DMP',
'INV_XT3.DMP',
'INV_XT4.DMP',
'INV_XT5.DMP',
'INV_XT6.DMP',
'INV_XT7.DMP',
'INV_XT8.DMP'
)
)
PARALLEL 8
AS SELECT * FROM INVENTORIES;
DROP TABLE INVENTORIES_XT8 PURGE;
/*** 2.3. UNLOAD PARTIALLY AND LOAD ALL ***/
-- UNLOAD 1 FROM DB TO FILE
CREATE TABLE INV_PART_XT
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ODP_DIR
LOCATION ('INV_P1_XT.DMP')
)
AS
SELECT * FROM INVENTORIES WHERE WAREHOUSE_ID < 5;
DROP TABLE INV_PART_XT;
-- UNLOAD 2 FROM DB TO FILE
CREATE TABLE INV_PART_XT
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ODP_DIR
LOCATION ('INV_P2_XT.DMP')
)
AS
SELECT * FROM INVENTORIES WHERE WAREHOUSE_ID >= 5;
DROP TABLE INV_PART_XT;
-- LOAD ALL FROM FILE TO DB
CREATE TABLE INV_PART_ALL_XT
(
PRODUCT_ID NUMBER(6),
WAREHOUSE_ID NUMBER(3),
QUANTITY_ON_HAND NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ODP_DIR
LOCATION ( 'INV_P1_XT.DMP',
'INV_P2_XT.DMP' )
);
-- CHECK DATA COUNT
SELECT COUNT(*) FROM INV_PART_ALL_XT;
-- END
DROP TABLE INV_PART_ALL_XT PURGE;
-- UNLOADING AND LOADING Columns Containing Final Object Types
-- 1. Unloading
CREATE TABLE INVENTORIES_OBJ_XT
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ODP_DIR
LOCATION ('INV_OBJ_XT.DMP')
)
AS
SELECT
OI.PRODUCT_ID,
DECODE(OI.WAREHOUSE, NULL, 0,1) WAREHOUSE,
OI.WAREHOUSE.LOCATION_ID LOCATION_ID,
OI.WAREHOUSE.WAREHOUSE_ID WAREHOUSE_ID,
OI.WAREHOUSE.WAREHOUSE_NAME WAREHOUSE_NAME,
OI.QUANTITY_ON_HAND
FROM OC_INVENTORIES OI;
SELECT * FROM INVENTORIES_OBJ_XT;
-- 2. Loading
CREATE TABLE OC_INVENTORIES_1
AS
SELECT PRODUCT_ID,
DECODE(WAREHOUSE, 0, NULL, WAREHOUSE_TYP(WAREHOUSE_ID, WAREHOUSE_NAME,LOCATION_ID)) WAREHOUSE,
QUANTITY_ON_HAND
FROM INVENTORIES_OBJ_XT;
DROP TABLE INVENTORIES_OBJ_XT PURGE;
SELECT * FROM OC_INVENTORIES_1;
참조 : ORACLE_LOADER - http://kr.blog.yahoo.com/hanul072/2575
ORACLE_DATAPUMP - http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/et_dp_driver.htm