오라클 스크립트 백업 참고

--스크립트 가져오기

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