오라클 스크립트 백업 참고
--스크립트 가져오기
http://www.oraclejavanew.kr/bbs/board.php?bo_table=freeboard&wr_id=219
--이행
행
http://www.dator.co.kr/dataworld/textyle/91165
--메일
http://blog.nooree.com/post.cfm/shell
SELECT TABLE_NAME Y
,0 X
,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'('
FROM DBA_TABLES
WHERE OWNER = UPPER('ITSM_KYOWON')
UNION
SELECT TC.TABLE_NAME Y
,COLUMN_ID X
,RTRIM(DECODE(COLUMN_ID,1,NULL,','))
|| RTRIM(COLUMN_NAME)|| ' '
|| RTRIM(DATA_TYPE)
|| RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'('))
|| RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH, 'NVARCHAR2',DATA_LENGTH/2,'VARCHAR2', DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
|| ','
|| TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR'))
|| RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')'))
|| ' '
|| RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL))
FROM DBA_TAB_COLUMNS TC
,DBA_OBJECTS O
WHERE O.OWNER = TC.OWNER
AND O.OBJECT_NAME = TC.TABLE_NAME
AND O.OBJECT_TYPE = 'TABLE'
AND O.OWNER = UPPER('사용자')
UNION
SELECT TABLE_NAME Y
,999999 X
,')' || CHR(10)
||';'||CHR(10)||CHR(10)
FROM DBA_TABLES
WHERE OWNER = UPPER('사용자')
ORDER BY 1,2
;
NUMBER
LONG
CHAR
VARCHAR2
NVARCHAR2
TIMESTAMP(6)
DATE
CLOB
BLOB
--1 테이블 비교
SELECT *
FROM (SELECT A.TABLE_NAME AS DEPLOY_SERVER,
B.TABLE_NAME AS DEVELOPMENT_SERVER
FROM (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = '사용자') A
LEFT OUTER JOIN (SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'ITSM_DEV') B ON A.TABLE_NAME = B.TABLE_NAME)
WHERE (DEPLOY_SERVER IS NULL OR DEVELOPMENT_SERVER IS NULL)
ORDER BY 1, 2;
-- 2 컬럼 비교(누락된 컬럼찾기)
SELECT *
FROM (
SELECT
A.TABLE_NAME AS DEPLOY_SERVER_TABLENAME , A.COLUMN_NAME AS DEPLOY_SERVER_COLUMNNAME, A.DATA_TYPE AS DEPLOY_SERVER_DATA_TYPE,
CASE WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE > 0
THEN A.DATA_PRECISION||','||A.DATA_SCALE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0
THEN TO_CHAR(A.DATA_PRECISION)
ELSE TO_CHAR(A.DATA_LENGTH) END AS DEPLOY_SERVER_DATA_LENGTH,
B.TABLE_NAME AS DEVELOPMENT_SERVER_TABLENAME, B.COLUMN_NAME AS DEVELOPMENT_SERVER_COLUMNNAME, B.DATA_TYPE AS DEVELOPMENT_SERVER_DATA_TYPE,
CASE WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE > 0
THEN B.DATA_PRECISION||','||B.DATA_SCALE
WHEN B.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0
THEN TO_CHAR(B.DATA_PRECISION)
ELSE TO_CHAR(B.DATA_LENGTH) END AS DEVELOPMENT_SERVER_DATA_LENGTH
FROM (
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ITSM_KYOWON'
and substr(table_name,1,5) not in('BIN$H') --조건
) A
LEFT OUTER JOIN ( SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ITSM_DEV'
) B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
)A
WHERE (NVL(DEPLOY_SERVER_TABLENAME, 'NA') <> NVL(DEVELOPMENT_SERVER_TABLENAME, 'NA')
OR NVL(DEPLOY_SERVER_COLUMNNAME,'NA') <> NVL(DEVELOPMENT_SERVER_COLUMNNAME,'NA'))
ORDER BY DEPLOY_SERVER_TABLENAME, DEPLOY_SERVER_COLUMNNAME
-- 3. 컬럼비교(데이터타입/LENGTH/NULLBABLE비교)
SELECT CASE WHEN DEPLOY_SERVER_DATA_TYPE <> DEVELOPMENT_SERVER_DATA_TYPE THEN 'DATATYPE'
WHEN DEPLOY_SERVER_LENGTH <> DEVELOPMENT_SERVER_LENGTH THEN 'DATALENGTH'
WHEN DEPLOY_SERVER_NULLABLE <> DEVELOPMENT_SERVER_NULLABLE THEN 'NULLABLE' END AS DIFF_TP
, A.*
FROM
(
SELECT A.TABLE_NAME AS DEPLOY_SERVER_TABLE_NAME, A.COLUMN_NAME AS DEPLOY_SERVER_COLUMN_NAME,
A.DATA_TYPE AS DEPLOY_SERVER_DATA_TYPE, A.NULLABLE AS DEPLOY_SERVER_NULLABLE,
CASE WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE > 0
THEN A.DATA_PRECISION||','||A.DATA_SCALE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0
THEN TO_CHAR(A.DATA_PRECISION)
ELSE TO_CHAR(A.DATA_LENGTH) END AS DEPLOY_SERVER_LENGTH,
B.TABLE_NAME AS DEVELOPMENT_SERVER_TABLE_NAME, B.COLUMN_NAME AS DEVELOPMENT_SERVER_COLUMN_NAME,
B.DATA_TYPE AS DEVELOPMENT_SERVER_DATA_TYPE, B.NULLABLE AS DEVELOPMENT_SERVER_NULLABLE,
CASE WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE > 0
THEN B.DATA_PRECISION||','||B.DATA_SCALE
WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE = 0
THEN TO_CHAR(B.DATA_PRECISION)
ELSE TO_CHAR(B.DATA_LENGTH) END AS DEVELOPMENT_SERVER_LENGTH
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,COLUMN_ID, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ITSM_KYOWON'
) A
JOIN (
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,COLUMN_ID, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ITSM_DEV'
) B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
)A
WHERE
( DEPLOY_SERVER_DATA_TYPE <> DEVELOPMENT_SERVER_DATA_TYPE
OR DEPLOY_SERVER_LENGTH <> DEVELOPMENT_SERVER_LENGTH
OR DEPLOY_SERVER_NULLABLE <> DEVELOPMENT_SERVER_NULLABLE)
ORDER BY DEPLOY_SERVER_TABLE_NAME, DEPLOY_SERVER_COLUMN_NAME
--4. 누락된 인덱스 찾기
SELECT *
FROM
(
SELECT A.TABLE_NAME AS DEPLOY_SERVER_TABLE_NAME, A.INDEX_NAME AS DEPLOY_SERVER_INDEX_NAME,
B.TABLE_NAME AS DEVELOPMENT_SERVER_TABLE_NAME, B.INDEX_NAME AS DEVELOPMENT_SERVER_INDEX_NAME
FROM (SELECT TABLE_NAME , INDEX_NAME FROM DBA_INDEXES
WHERE OWNER = 'ITSM_KYOWON'
) A
LEFT OUTER JOIN (SELECT TABLE_NAME, INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'ITSM_DEV') B
ON A.TABLE_NAME = B.TABLE_NAME AND A.INDEX_NAME = B.INDEX_NAME
)
WHERE DEPLOY_SERVER_TABLE_NAME IS NULL OR DEPLOY_SERVER_INDEX_NAME IS NULL
ORDER BY 1,2
- 5 . 인덱스의 컬럼 비교하기
SELECT *
FROM
(
SELECT
A.TABLE_NAME AS DEPLOY_SERVER_TABLE_NAME, A.INDEX_NAME AS DEPLOY_SERVER_INDEX_NAME,
A.COLUMN_NAME AS DEPLOY_SERVER_COLUMN_NAME, A.COLUMN_POSITION AS DEPLOY_SERVER_COLUMN_POSITION,
A.COLUMN_LENGTH AS DEPLOY_SERVER_COLUMN_LENGTH , A.CHAR_LENGTH AS DEPLOY_SERVER_CHAR_LENGTH,
A.DESCEND AS DEPLOY_SERVER_DESCEND,
B.TABLE_NAME AS DEVELOPMENT_TABLE_NAME, B.INDEX_NAME AS DEVELOPMENT_INDEX_NAME,
B.COLUMN_NAME AS DEVELOPMENT_COLUMN_NAME, B.COLUMN_POSITION AS DEVELOPMENT_COLUMN_POSITION,
B.COLUMN_LENGTH AS DEVELOPMENT_COLUMN_LENGTH , B.CHAR_LENGTH AS DEVELOPMENT_CHAR_LENGTH,
B.DESCEND AS DEVELOPMENT_DESCEND
FROM (
SELECT TABLE_NAME ,INDEX_NAME ,COLUMN_NAME ,COLUMN_POSITION , COLUMN_LENGTH ,CHAR_LENGTH, DESCEND
FROM DBA_IND_COLUMNS
WHERE INDEX_OWNER = 'ITSM_KYOWON'
AND SUBSTR(TABLE_NAME,1,5) NOT IN('BIN$H') --조건
) A
LEFT OUTER JOIN (
SELECT TABLE_NAME ,INDEX_NAME ,COLUMN_NAME ,COLUMN_POSITION , COLUMN_LENGTH ,CHAR_LENGTH, DESCEND
FROM DBA_IND_COLUMNS
WHERE INDEX_OWNER = 'ITSM_DEV'
) B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.COLUMN_POSITION = B.COLUMN_POSITION
AND A.COLUMN_LENGTH = B.COLUMN_LENGTH
AND A.DESCEND = B.DESCEND
)
WHERE DEPLOY_SERVER_INDEX_NAME IS NULL OR DEVELOPMENT_INDEX_NAME IS NULL
-- 6. 오브젝트 비교
SELECT *
FROM
(
SELECT A.OBJECT_TYPE AS DEPLOY_SERVER_OBJECT_TYPE, A.OBJECT_NAME AS DEPLOY_SERVER_OBJECT_NAME,
B.OBJECT_TYPE AS DEVELOPMENT_SERVER_OBJECT_TYPE, B.OBJECT_NAME AS DEVELOPMENT_SERVER_OBJECT_NAME
FROM (
SELECT OBJECT_TYPE, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = 'ITSM_KYOWON') A
LEFT OUTER JOIN (SELECT OBJECT_TYPE, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = 'ITSM_DEV') B
ON A.OBJECT_TYPE = B.OBJECT_TYPE AND A.OBJECT_NAME = B.OBJECT_NAME
)
WHERE NVL(DEPLOY_SERVER_OBJECT_NAME,'NA') NOT LIKE 'O@_%' ESCAPE '@'
AND ( DEPLOY_SERVER_OBJECT_NAME IS NULL OR DEVELOPMENT_SERVER_OBJECT_NAME IS NULL)
ORDER BY 1,2