[Oracle] 외부 테이블(External Table) 생성 - data pump

[이론1] external table이 사용하는 오라클 엔진 2가지

1. SQL*Loader
2. pump

---

[실습1] pump를 이용해서 외부 테이블 생성하기

#1.  ora19 쪽 scott 유져에서 emp 와 dept 테이블을 조인한 결과로
#    pump 파일을 생성하면서 바로 외부 테이블을 생성합니다. 

scott>  create  table  ext_emp_dept
          ( ename, sal, loc )
          organization  external
          ( 
             type   oracle_datapump
             default  directory  emp_dir
             location('empdept.dmp')  )
       as 
               select  e.ename, e.sal, d.loc
                 from  emp  e, dept  d
                 where  e.deptno = d.deptno; 

scott> select * from ext_emp_dept;

$ ls -l empdept.dmp

---

문제1. emp와 salgrade를 조인해서 이름과 월급, 급여등급 출력하는데 외부 테이블을 ext_emp_grade 라는 이름으로 생성하고 pump 파일도 empsalgrade.dmp 로 생성하시오

salgrade 테이블

drop  table  salgrade;
 
create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );
 
insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);
 
commit;

답:

create  table  ext_emp_grade
( ename, sal, grade )
organization  external
( 
 type   oracle_datapump
 default  directory  emp_dir
 location('empsalgrade.dmp')  )
as 
   select e.ename, e.sal, s.grade
     from  emp  e, salgrade  s 
     where e.sal between  s.losal  and s.hisal;
     
select * from ext_emp_grade;

---

문제2. emp와 dept와 salgrade를 가지고 다양하게 조인해서 ext_kbm으로 외부 테이블을 생성하고 pump 파일을 kbm.dmp로 생성해서 다른 유저에게 scp로 전송하시오

scp   kbm.dmp   oracle@192.168.13.??:/home/oracle  
scp  external_create_script_kbm.txt  oracle@192.168.13.??:/home/oracle

리눅스 ip주소는 다른유저 주소

scott> create  table  ext_kbm
( ename, sal, grade )
organization  external
( 
 type   oracle_datapump
 default  directory  emp_dir
 location('kbm.dmp')  )
as 
   select e.ename, e.sal, s.grade
     from  emp  e, salgrade  s 
     where e.sal between  s.losal  and s.hisal;
     
select * from ext_kbm;

[oracle@ora19c ~]$ vi kbm_script.sql
[oracle@ora19c ~]$
[oracle@ora19c ~]$
[oracle@ora19c ~]$ ls -l kbm.dmp
-rw-r-----. 1 oracle oinstall 12288  9월 15 15:12 kbm.dmp
[oracle@ora19c ~]$
[oracle@ora19c ~]$ scp kbm_script.sql oracle@192.168.13.??:/home/oracle
oracle@192.168.13.00's password:
kbm_script.sql                                          100%  282    83.0KB/s   00:00
[oracle@ora19c ~]$
[oracle@ora19c ~]$ scp kbm.dmp oracle@192.168.13.??:/home/oracle
oracle@192.168.13.??'s password:
kbm.dmp                                        100%   12KB   6.0MB/s   00:00
[oracle@ora19c ~]$

---

문제3. 다른 유저에게 받은 pump 파일로 external 테이블을 생성합니다.

desc emp;
desc dept;
desc salgrade;


create  table  ext_abc
  ( ename  varchar2(10), 
    sal  number(7,2), 
    loc varchar2(13),
    grade  number(10) )
  organization  external
  (
   type   oracle_datapump
   default  directory  emp_dir
   location('abc.dmp')  );
   
   
select * from ext_abc;

---

문제4. 다른 유저의 펌프 파일로 만든 외부 테이블을 가지고 view 를 만들 수 있는지 확인하시오.

--> view는 만들어짐

select * from ext_abc;

create view ext_view
as
 select ename, sal from ext_abc;
 
select * from ext_view;

-- 뷰는 만들어지지만 다음과 같이 인덱스는 만들어지지 X
create index ext_abc_idx
on ext_abc(ename);