테이블, 컬럼 정보, DB사용량 조회 등

1. 오라클 테이블 정보 조회

SELECT   A.TABLE_NAME
       , B.COMMENTS
       , (SELECT CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME = A.TABLE_NAME AND rownum= 1) AS CREATED
  FROM USER_TABLES A, USER_TAB_COMMENTS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.TABLE_LOCK = 'ENABLED'
 ORDER BY A.TABLE_NAME
;

 

2. 오라클 테이블과 컬럼의 정보를 조회하는 쿼리

 

SELECT   TABLE_NAME
       , TAB_COM
       , COLUMN_ID
       , COLUMN_NAME
       , DATA_TYPE
       , DATA_LENGTH
       , PK
       , FK     
       , NULLCHECK
       , DATA_DEFAULT
       , COL_COM        
  FROM ( 
        SELECT   A.TABLE_NAME
               , (SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER  =  ? AND TABLE_NAME = A.TABLE_NAME) TAB_COM
               , A.COLUMN_ID
               , A.COLUMN_NAME
               /*
               , CASE WHEN A.DATA_TYPE = 'NUMBER' THEN A.DATA_TYPE || CASE WHEN (A.DATA_SCALE IS NULL AND A.DATA_SCALE IS NULL)  THEN ''
                                                                           WHEN A.DATA_SCALE = 0 THEN '(' || A.DATA_PRECISION || ')'
                                                                      ELSE '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
                                                                      END
                      WHEN A.DATA_TYPE = 'DATE'  THEN A.DATA_TYPE                                                   
                      WHEN A.DATA_TYPE = 'CLOB'  THEN A.DATA_TYPE 
                 ELSE A.DATA_TYPE || '(' || A.DATA_LENGTH || ' byte)'
                 END AS DATA_TYPE */
               , A.DATA_TYPE
               , A.DATA_LENGTH
               , (SELECT 'PK'
                    FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
                   WHERE  COLS.OWNER = ?
                     AND  COLS.TABLE_NAME = A.TABLE_NAME
                     AND COLS.COLUMN_NAME = A.COLUMN_NAME
                     AND CONS.CONSTRAINT_TYPE = 'P'
                     AND CONS.OWNER = COLS.OWNER
                     AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
                     AND CONS.TABLE_NAME = COLS.TABLE_NAME
                 ) PK
               , (SELECT 'FK'
                    FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
                   WHERE  COLS.OWNER = ?
                     AND  COLS.TABLE_NAME = A.TABLE_NAME
                     AND COLS.COLUMN_NAME = A.COLUMN_NAME
                     AND CONS.CONSTRAINT_TYPE = 'R'
                     AND CONS.OWNER = COLS.OWNER
                     AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
                     AND CONS.TABLE_NAME = COLS.TABLE_NAME
                 )FK 
               , CASE WHEN NULLABLE = 'Y' THEN 'Yes' WHEN NULLABLE = 'N' THEN 'No' END NULLCHECK     
       , CASE
           WHEN A.DEFAULT_LENGTH IS NULL THEN ''
           ELSE
               extractvalue
               ( dbms_xmlgen.getxmltype
                 ( 'select data_default from user_tab_columns where table_name = ''' || A.TABLE_NAME || ''' and column_name = ''' || B.COLUMN_NAME || '''' )
               , '//text()' )
         END as data_default
         , (SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE OWNER  =  ? AND TABLE_NAME = A.TABLE_NAME AND COLUMN_NAME = A.COLUMN_NAME) COL_COM
  FROM USER_TAB_COLUMNS A,
       ALL_COL_COMMENTS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND A.TABLE_NAME = ?
   and b.owner = ?
 ORDER BY A.TABLE_NAME, A.COLUMN_ID
)
;

 

3. 최종 수정된 일시 확인 쿼리

 

  SELECT A.TABLE_NAME,
         B.COMMENTS,
         (SELECT Z.NUM_ROWS
            FROM ALL_TABLES Z
           WHERE Z.OWNER = ?
             AND Z.TABLE_NAME = A.TABLE_NAME)
            AS ROW_CNT,
         (SELECT CREATED
            FROM DBA_OBJECTS
           WHERE OBJECT_NAME = A.TABLE_NAME
             AND ROWNUM = 1)
            AS CREATED,
         (SELECT COUNT (*)
            FROM USER_TAB_COLUMNS P, ALL_COL_COMMENTS Q
           WHERE P.TABLE_NAME = Q.TABLE_NAME
             AND P.COLUMN_NAME = Q.COLUMN_NAME
             AND P.TABLE_NAME = A.TABLE_NAME
             AND Q.owner = ?
             AND Q.COLUMN_NAME = 'MOD_DTM')
            AS MOD_DTM_YN,
         (SELECT TO_CHAR (Z.TIMESTAMP, 'YYYY-MM-DD')
            FROM ALL_TAB_MODIFICATIONS Z
           WHERE Z.TABLE_OWNER = ?
             AND Z.TABLE_NAME = A.TABLE_NAME)
            AS LAST_MODIFY
    FROM USER_TABLES A, USER_TAB_COMMENTS B
   WHERE A.TABLE_NAME = B.TABLE_NAME
     AND A.TABLE_LOCK = 'ENABLED'
ORDER BY A.TABLE_NAME;

 

 

4. 데이터 베이스 사용량등 확인

-- DB 전체 용량 확인
------------------------------------------------------------------------------------------
select sum(bytes)/1024/1024 from dba_data_files;
------------------------------------------------------------------------------------------
-- DB 사용 용량 확인
------------------------------------------------------------------------------------------
select sum(bytes)/1024/1024 from dba_segments; 
------------------------------------------------------------------------------------------
-- DB 여유 용량 확인
------------------------------------------------------------------------------------------
select sum(bytes)/1024/1024 from dba_free_space;
------------------------------------------------------------------------------------------
-- 테이블스페이스와 데이터 파일 확인
------------------------------------------------------------------------------------------
select   tablespace_name
       , (bytes/1024/1024) AS MB
       , file_name
  from dba_data_files;

------------------------------------------------------------------------------------------ 

 

 

 

5. 테이블별 행수 조회

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE OWNER = '계정' 
ORDER BY TABLE_NAME;