DATAPUMP,CTAS,DBLINK

오라클 데이터펌프

create directory pump_dir as '/backup/datapump';

grant read,write on directory pump_dir to hr;

expdp system/oracle dumpfile=scott.dmp directory=pump_dir schemas=scott

schema import
SYS@newdb> select tablespace_name, contents from dba_tablespaces;
Schema import 작업시 export 했을때의 정보들과 명칭이 다르면 remap 작업을 해줘야합니다. 
scott 계정을 import 하기위해서  default tablespace와 
default temporary tablespace의 명칭을 맞춰줘야하니 미리 확인해봤습니다. 
기본으로 사용할  tablespace도 만들어주겠습니다.

 

SYS@newdb> create tablespace dump datafile '/opt/oracle/app/oradata/newdb/dump01.dbf' size 200m;
스키마의 기본 테이블스페이스를 미리 만들어줬습니다. 이제 import 작업을 진행해보겠습니다.

impdp system/oracle dumpfile=scott.dmp directory=pump_dir remap_tablespace=users:dump

impdp system/oracle dumpfile=hr.dmp directory=pump_dir remap_schema=hr:scott remap_tablespace=examples:dump
database link 생성
create database link expdp_net01 connect to system \
identified by oracle using 'expdp_test'

db link 확인
*Local DB에 파일 남기기

expdp system/oracle network_link=expdp_net01 directory=dump_dir \
dumpfile=expdp_net01_%U.dmp logfile=expdp_net01.log \
job_name=net_01 

※ Long TYPE 등 NETWORK_LINK로 받아지지 않는 것이 있으므로 확인 필요

*Remote DB에 파일 남기기
expdp system/oracle@expdp_test directory=dump+dir \
dumpfile=expdp_net01_%U.dmp logfile=expdp+net01.log \
jog_name =remote_exp 

※DB Link와 Network_Link를 이용하면 Local DB쪽에 dmp 파일이 생성되며,Network Alias만 이용하면
Remote DB쪽에 dmp파일이 생성된다.

 

============================================================================

database link 생성
create database link expdp_net01 connect to system \
identified by oracle using 'expdp_test'

db link 확인
*Local DB에 파일 남기기

expdp system/oracle network_link=expdp_net01 directory=dump_dir \
dumpfile=expdp_net01_%U.dmp logfile=expdp_net01.log \
job_name=net_01 

※ Long TYPE 등 NETWORK_LINK로 받아지지 않는 것이 있으므로 확인 필요

*Remote DB에 파일 남기기
expdp system/oracle@expdp_test directory=dump+dir \
dumpfile=expdp_net01_%U.dmp logfile=expdp+net01.log \
jog_name =remote_exp 

※DB Link와 Network_Link를 이용하면 Local DB쪽에 dmp 파일이 생성되며,Network Alias만 이용하면
Remote DB쪽에 dmp파일이 생성된다.​

============================================================================CTAS 활용과 주의사항

테이블의 형식만 복사하기 위한 마이그레이션 작업에 사용된다.
기존 테이블의 데이터를 새로 생성하기 위해 테이블 재구성에 사용된다.
기존 테이블이 가지고있는 기본값, 제약조건, 인덱스 등은 복사할 수 없다.
파티션 테이블일 경우엔 일반 테이블로 복사된다.

select 'create table ' ||segment_name||' as select * from '||owner||'.'||segment_name||'@'||'link'||';' 
from dba_segments
where owner='SYS' and segment_type='TABLE PARTITION' and  rownum <11;

SQL> select distinct segment_type from dba_segments;

SEGMENT_TYPE
------------------
INDEX
CLUSTER
TABLE PARTITION
LOBINDEX
TABLE SUBPARTITION
SYSTEM STATISTICS
LOBSEGMENT
INDEX PARTITION
ROLLBACK
TABLE
LOB PARTITION
NESTED TABLE
TYPE2 UNDO

select 'insert into  ' ||segment_name||' as select * from '||owner||'.'||segment_name||'@'||'link'||';' 
from dba_segments
where owner='SYS' and segment_type='TABLE PARTITION' AND rownum <11;

select segment_TYPE,COUNT(*) FROM DBA_SEGMENTS GROUP BY SEGMENT_TYPE;


select 'create table ' ||table_name||' as select * from '||owner||'.'||table_name||'@'||'link'||';' 
from dba_tables
where owner='SYS' and rownum <11;

select 'insert into  ' ||segment_name||' as select * from '||owner||'.'||segment_name||'@'||'link'||';' 
from dba_segments
where owner='SYS' and segment_type='TABLE' AND rownum <11;

select owner,segment_name,PARTITION_NAME
from dba_segments
where owner='SYS' and segment_type='TABLE' and PARTITION_NAME <> ''

 select table_name from dict where table_name like '%PARTITION%';
 
select table_owner,table_name,PARTITION_NAME,PARTITION_POSITION from dba_tab_partitions order by 1,2,4;


select table_owner,table_name,count(*) from dba_tab_partitions group by table_owner,table_name;

SQL> CREATE PUBLIC DATABASE LINK 링크이름
CONNECT TO <연결하고자 하는 user>
IDENTIFIED BY <연결하고자 하는 user password>
USING '원격 db alias'

일반유저에게 Private DB LINK 생성권한 부여
SQL> grant create database link to 유저명;


Public 으로 생성 할 수 있는 권한 부여
SQL> grant create public database link to 유저명;


dba_db_links dictionary 에서 자세한 내용 조회 가능합니다

SQL> select * from dba_db_links;


사용법은 다음 과 같습니다.
select 컬럼명 from 테이블명@db_link명

connect scott/tiger;
select dbms_metadata.get_ddl('TABLE',u.table_name )
from user_tables u;
select dbms_metadata.get_ddl('INDEX',u.index_name)
from user_indexes u;