오라클 데이터베이스 분석 기초

오라클 데이터베이스 정보조회

신규사업이나 고도화사업을 진행하다보면 DA로서 먼저 하는 작업은 데이터베이스 분석작업이다

잠깐 스쳐가는 작업이라도 제일 먼저 DB구조파악이 우선되야한다 

그에 따른 SQL문들이다

-- [오라클 버전 확인]

SELECT * FROM PRODUCT_COMPONENT_VERSION;

-- [캐릭터 셋]

SELECT * FROM NLS_DATABASE_PARAMETERS;

-- [USER 정보]

SELECT * FROM DBA_USERS

ORDER BY USER_ID;

-- [세션 정보]

SELECT * FROM V$SESSION

ORDER BY PROGRAM, MACHINE;

-- [테이블 스페이스 정보]

SELECT A.TABLESPACE, A.TOTAL, B.USED, A.FREE

FROM (

SELECT  A.TABLESPACE_NAME AS TABLESPACE

       ,SUM(NVL(A.TOTAL,0)) AS TOTAL

       ,SUM(NVL(B.FREE,0)) AS FREE

  FROM  (SELECT  TABLESPACE_NAME, ROUND(SUM(BYTES/1024/1024),3) TOTAL

           FROM  DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) A,

        (SELECT  TABLESPACE_NAME, ROUND(SUM(BYTES/1024/1024),3) FREE

           FROM  DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) B

 WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME

 GROUP BY A.TABLESPACE_NAME

) A,

(

SELECT TABLESPACE_NAME, ROUND(SUM(BYTES/1024/1024),3) USED

  FROM DBA_SEGMENTS

GROUP BY TABLESPACE_NAME

)B

where A.TABLESPACE = B.TABLESPACE_NAME

ORDER BY 1

;

-- [테이블 스키마 정보]

SELECT

     A.OWNER

     , A.TABLE_NAME "테이블명"

  , (SELECT X.COMMENTS FROM USER_TAB_COMMENTS X WHERE X.TABLE_NAME = A.TABLE_NAME) "테이블명(한글)"

  , B.COMMENTS "컬럼명(한글)"

  , A.COLUMN_NAME "컬럼명"

  , CASE WHEN A.DATA_TYPE IN ('DATE', 'TIMESTAMP', 'NUMBER') THEN A.DATA_TYPE

    WHEN A.DATA_PRECISION IS NOT NULL THEN A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'

      ELSE A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'

    END "데이터 형식"

  , CASE WHEN A.NULLABLE = 'N' THEN 'NOT NULL' ELSE 'NULL' END "NULL 여부"

  , CASE WHEN C.COLUMN_NAME IS NOT NULL AND C.PK = 'P' THEN 'PK'

    END "PK 여부"

  , CASE WHEN C.COLUMN_NAME IS NOT NULL AND C.FK = 'R' THEN 'FK'

    END "FK 여부"

  , CASE WHEN C.COLUMN_NAME IS NOT NULL AND C.UK = 'U' THEN 'UK'

    END "UK 여부"

  , CASE WHEN EXISTS (SELECT '1' FROM SYS.ALL_IND_COLUMNS X

      WHERE A.OWNER = X.TABLE_OWNER AND A.TABLE_NAME = X.TABLE_NAME AND A.COLUMN_NAME = X.COLUMN_NAME AND ROWNUM <= 2)

    THEN 'Y'

    END "인덱스 여부"

  FROM SYS.ALL_TAB_COLS A

     , SYS.ALL_COL_COMMENTS B

     , (SELECT B.TABLE_NAME, B.COLUMN_NAME

                 , MAX(CASE WHEN A.CONSTRAINT_TYPE = 'P' THEN 'P' END) PK

          , MAX(CASE WHEN A.CONSTRAINT_TYPE = 'R' THEN 'R' END) FK

          , MAX(CASE WHEN A.CONSTRAINT_TYPE = 'U' THEN 'U' END) UK

          FROM ALL_CONSTRAINTS A

             , ALL_CONS_COLUMNS B

         WHERE A.TABLE_NAME = B.TABLE_NAME

           AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

           AND A.CONSTRAINT_TYPE IN ('P', 'R', 'U')

           --AND A.OWNER = ''

           AND A.OWNER = B.OWNER

           GROUP BY B.TABLE_NAME, B.COLUMN_NAME

       ) C

 WHERE A.TABLE_NAME = B.TABLE_NAME

   AND A.COLUMN_NAME = B.COLUMN_NAME

   AND A.TABLE_NAME = C.TABLE_NAME(+)

   AND A.COLUMN_NAME = C.COLUMN_NAME(+)

   --AND A.OWNER = ''

   AND A.OWNER = B.OWNER

--   AND A.TABLE_NAME LIKE ''

 ORDER BY A.TABLE_NAME, A.COLUMN_ID

 ;

 --[전체테이블 COUNT]

SELECT

TABLE_NAME,

  TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM '||TABLE_NAME)),'/ROWSET/ROW/C')) COUNT

FROM USER_TABLES;

-- [ 테이블별 용량 조회 ]

SELECT 

    A.SEGMENT_NAME

    , SUM(A.BYTES) AS BYTES

    , ROUND(SUM(A.BYTES)/1024) AS KB

    , ROUND(SUM(A.BYTES)/1024/1024) AS MB

    , ROUND(SUM(A.BYTES)/1024/1024/1024) AS GB

FROM DBA_SEGMENTS A, DBA_TABLES B

WHERE A.SEGMENT_NAME = B.TABLE_NAME

AND A.OWNER = '' -- 계정 ID

AND A.SEGMENT_NAME IN =''

GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE;