다른 명령
DBA DB 이관 절차
TOBE 테이블스페이이스 생성 (ASIS DB는 DB링크 이용 )
-- TS 생성 SELECT 'CREATE TABLESPACE '||tablespace_name||' DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE 30G LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;' FROM ( SELECT tablespace_name FROM DBA_TABLESPACES@DL_DBCAFE_DEV_DBCAFE_DBA -- DB링크 이용 ASIS DB조회 minus SELECT tablespace_name FROM DBA_TABLESPACES order by 1 ) ;
TOBE 테이블스페이이스 RESIZE
SELECT TABLESPACE_NAME, SIZEMB, FREEMB , 'ALTER TABLESPACE '||TABLESPACE_NAME||' RESIZE '||SIZEMB||'M;' FROM ( SELECT B.TABLESPACE_NAME, TBS_SIZE SIZEMB, A.FREE_SPACE FREEMB FROM ( SELECT TABLESPACE_NAME , ROUND (SUM (BYTES) / 1024 / 1024, 2) AS FREE_SPACE FROM DBA_FREE_SPACE@DL_DBCAFE_DEV_DBCAFE_DBA GROUP BY TABLESPACE_NAME ) A , ( SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 AS TBS_SIZE FROM DBA_DATA_FILES@DL_DBCAFE_DEV_DBCAFE_DBA GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 TBS_SIZE FROM DBA_TEMP_FILES@DL_DBCAFE_DEV_DBCAFE_DBA GROUP BY TABLESPACE_NAME ) B WHERE A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME ) C WHERE C.TABLESPACE_NAME LIKE 'TS%' ;
ASIS <=> TOBE 유저 비교
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA MINUS SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS ;
ASIS 사용자 비교후 생성
select USERNAME,DEFAULT_TABLESPACE , 'CREATE USER '||USERNAME||' IDENTIFIED BY "dev01!" DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;GRANT CONNECT,RESOURCE TO '||USERNAME||';ALTER USER '||USERNAME||' DEFAULT ROLE ALL;ALTER USER '||USERNAME||' QUOTA UNLIMITED ON '||DEFAULT_TABLESPACE||';ALTER USER '||USERNAME||' QUOTA UNLIMITED ON '||REPLACE(DEFAULT_TABLESPACE,'D01','I01')||';' from ( SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA minus SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS order by 1 );
ASIS 사용자명 조회
SELECT USERNAME FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA -- ASIS ORDER BY USERNAME ;
=== ASIS <=> TOBE TS 비교
-- ASIS TS 쿼터 조회 SELECT * FROM DBA_TS_QUOTAS@DL_DBCAFE_DEV_DBCAFE_DBA ORDER BY USERNAME ; -- TOBE TS 쿼터 조회 SELECT * FROM DBA_TABLESPACES;
ASIS <=> TOBE 사용자 권한 비교
-- 전환 <==> TOBE 운영 DB간 권한 비교 SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION , 'GRANT '|| PRIVILEGE || ' TO ' || GRANTEE ||';' FROM ( -- ASIS SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS@DL_DBCAFE_MIG_DBCAFE_MIG WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') MINUS -- TOBE SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') ); -- 개발 <==> TOBE 운영 DB간 비교 SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION , 'GRANT '|| PRIVILEGE || ' TO ' || GRANTEE ||';' FROM ( SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS@DL_DBCAFE_DEV_DBCAFE_DBA WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') MINUS SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') ) ;
데이터 전환
전환계정 DBA권한 부여
GRANT ALTER ANY PROCEDURE TO MIGCOMMON WITH ADMIN OPTION ; GRANT ALTER ANY TABLE TO MIGCOMMON; GRANT CREATE ANY TABLE TO MIGCOMMON; GRANT CREATE ANY VIEW TO MIGCOMMON; GRANT DELETE ANY TABLE TO MIGCOMMON; GRANT DROP ANY TABLE TO MIGCOMMON; GRANT EXECUTE ANY PROCEDURE TO MIGCOMMON; GRANT INSERT ANY TABLE TO MIGCOMMON; GRANT SELECT ANY TABLE TO MIGCOMMON; GRANT UPDATE ANY TABLE TO MIGCOMMON; GRANT CREATE DATABASE LINK TO MIGCOMMON; GRANT UNLIMITED TABLESPACE TO MIGCOMMON;
전환 <==> TOBE 운영 오브젝트 건수 비교
-- 1.USER별 TYPE 건수 -- 전환 DB SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ( -- 전환 DB SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM dba_objects@DL_DBCAFE_MIG_DBCAFE_MIG WHERE OWNER IN ( select USERNAME from dba_users@DL_DBCAFE_MIG_DBCAFE_MIG where USERNAME IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') ) AND OBJECT_NAME NOT LIKE 'SYS%' MINUS -- TOBE 운영 DB SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM dba_objects WHERE OWNER IN ( select USERNAME from dba_users where USERNAME IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP') ) AND OBJECT_NAME NOT LIKE 'SYS%' ) ;
전환 대상 테이블 초기화
-- TRUNCATE TABLE SELECT OBJECT_TYPE,OWNER,OBJECT_NAME , 'TRUNCATE '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||';' DDL -- || DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINT','') ||' ;' DDL FROM DBA_OBJECTS WHERE --OBJECT_NAME LIKE '%TR_RFND_TGT%' OWNER = 'ERPAPP' AND OBJECT_TYPE = 'TABLE';
데이터 펌프
-- datapump 잡 확인 SELECT * FROM DBA_DATAPUMP_jOBS; -- datapump 잡 세션 확인 SELECT * FROM DBA_DATAPUMP_sessions;
시퀀스 생성
-- 사용자별 시퀀스 생성 SELECT 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY -'||LAST_NUMBER||';' DDL , ' DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||'; CREATE SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY 1 START WITH '||MIN_VALUE||' MAXVALUE '||MAX_VALUE||' MINVALUE '||MIN_VALUE||' '||DECODE(CYCLE_FLAG,'Y','CYCLE','NOCYCLE')||' CACHE '||CACHE_SIZE||' NOORDER NOKEEP GLOBAL; ' , A.* FROM DBA_SEQUENCES A WHERE SEQUENCE_OWNER = 'DBCAFE' ;