About DATABASE

오라클 데이터펌프

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;

CTAS 를 통한 테이블 복제시 제약 조건

테스트 결과는 첨부 화일 참조 바랍니다.

1. CTAS 의 경우 Parallel Hint 는
   CT(Create Table) 에서는 사용하여도 의미가 없고(ORACLE 이 무시함),
   AS 에서만 Parallel Hint 를 사용한다.
   (As Select /*+ paralllel(a) parallel(b) */ * from tab1 a, tab2 b)
   식으로 사용한다.

2. 캐릭터셋의 다른 DBMS 간의 CTAS 사용시 Column Size 가 달리 될수 있다.
   [ 참고 - http://cafe.naver.com/prodba/2014 ]
   이 경우 _keep_remote_column_size=true 로 설정 후 DB Restart 후에 CTAS 를 사용하면 된다고함
   _keep_remote_column_size 파라미터의 의미 : remote column size does not get modified
   Default 값은 False 임  

3. 그럼 가장 빠르게 테이블 복제 [ Export / Import 제외 ]
  
  3.1 Create Table COPY_Table as select * from Source_Table@remote where 1=2
   를 통해서 테이블 껍데기만 Copy 후에
  3.2 alter session enable parallel dml;
  3.3 insert /*+ parallel(Copy_Table, 10) */ into Copy_Table nologging
      select /*+ parallel(Source_table, 10) */ * from Source_Table@remote ;
  3.4 Creae index ... Nologging PARALLEL ;
      CREATE INDEX XAK_COPY_Table ... NOLOGGING PARALLEL ;
  3.5 ALTER index ... LOGGING NOPARALLEL ;
      ALTER index XAK_COPY_Table logging noparallel ;
  3.6 통계정보 수집
     exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'COPY_Table ');
  3.7 기타 추가 작업
     Column Default Value, Index, Constraint, Grant, Synonym, Trigger 

4. CTAS 를 쓸것인가 ? Export/Import 를 쓸것인가 ?
   CTAS - Parallel 처리가 가능하다는 강점이 있다.
          단. DB Link 를 통해서 통신 하기 때문에, Network 로 근거리(한국 내 정도 ^^;)어야 하고,
              양쪽 DB 의 Characterset 이 동일 해야 한다. 기타 DB LInk 사용에 따른 제약이 없어야 한다.
   Export/Import - Dump 화일 생성을 위한 저장할 공간이 필요하다.
                   양쪽 DB 의 Characterset 이 다르더라도, Sub Set 에서 Super Set 으로 이관시 아무런
                   문제가 되지 않는다. 비교적 먼거리 시에는 Remote 에서 Dump 화일 생성, ftp 전송,
                   Local Import 수행이 가능하다.