[oracle]오라클 운영 스크립트 모음 DBA script list

#[oracle]오라클 운영 스크립트 모음 DBA script list

 

안녕하세요.

 

오라클 관련해서 오랫만에 포스팅합니다.

 

운영 스크립트 (dba script list) 모음입니다.

 

바로 시작합니다.

 

1. 테이블 스페이스 별 크기 조회

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스" ,
          SUBSTRB(FILE_NAME, 1, 50) AS "파일명" ,
          TO_CHAR(BLOCKS, '999,999,990') AS "블럭수" ,
          TO_CHAR(BYTES, '99,999,999') AS "크기"
FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME
;

 

2. 테이블 스페이스별 정보 조회

SELECT A.TABLESPACE_NAME AS "TABLESPACE" ,
          A.INITIAL_EXTENT / 1024 AS "INIT(K)" ,
          A.NEXT_EXTENT / 1024 AS "NEXT(K)" ,
          A.MIN_EXTENTS AS "MIN" ,
          A.MAX_EXTENTS AS "MAX" ,
          A.PCT_INCREASE AS "PCT_INC(%)" ,
          B.FILE_NAME AS "FILE_NAME" ,
          B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)" ,
          B.STATUS AS "STATUS"
FROM DBA_TABLESPACES A ,
         DBA_DATA_FILES B ,
         V$PARAMETER C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND C.NAME = 'db_block_size' ORDER BY 1, 2
;

 

3. 테이블 스페이스 별 사용 중인 파일의 크기 합 조회

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE ,
          TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES ,
          TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME
UNION ALL
SELECT '총계',
          TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES,
          TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
FROM DBA_DATA_FILES
;

 

4. 테이블 스페이스별 디스크 사용량 조회

SELECT A.TABLESPACE_NAME AS "TABLESPACE" ,
       A.INIT AS "INIT(K)" ,
       A.NEXT AS "NEXT(K)" ,
       A.MIN AS "MIN" ,
       A.MAX AS "MAX" ,
       A.PCT_INC AS "PCT_INC(%)" ,
       TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)" ,
       TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)" ,
       TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭" ,
       TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭" ,
       TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"
  FROM (SELECT TABLESPACE_NAME ,
               INITIAL_EXTENT / 1024 AS INIT ,
               NEXT_EXTENT / 1024 AS NEXT ,
               MIN_EXTENTS AS MIN ,
               MAX_EXTENTS AS MAX ,
               PCT_INCREASE AS PCT_INC
          FROM DBA_TABLESPACES) A ,
       (SELECT TABLESPACE_NAME,
               SUM(BYTES) AS TOTAL,
               SUM(BLOCKS) AS BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) B ,
       (SELECT TABLESPACE_NAME,
               SUM(BYTES) AS FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) C ,
       (SELECT TABLESPACE_NAME,
               SUM(BLOCKS) AS BLOCKS
          FROM DBA_EXTENTS
         GROUP BY TABLESPACE_NAME) D
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
   AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
 ORDER BY A.TABLESPACE_NAME
 ;

 

5. 테이블 스페이스 별 사용중인 테이블 명 조회

SELECT TABLESPACE_NAME,
       TABLE_NAME
  FROM USER_TABLES
 WHERE TABLESPACE_NAME = UPPER(':테이블스페이스명')
 ORDER BY TABLESPACE_NAME,
       TABLE_NAME
;

 

6. rollback segment 사용 조회

SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME ,
       SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME ,
       TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID ,
       TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT ,
       TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS ,
       TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS ,
       TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)" ,
       TO_CHAR(XACTS, '9,999') AS XACTS
  FROM DBA_ROLLBACK_SEGS A ,
       V$ROLLSTAT B
 WHERE A.SEGMENT_ID = B.USN(+)
 ORDER BY 1
 ;
 
 ## EXTENTS = 현재 할당된 EXTENT의 수 : EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수

 

7. constraint 정보 조회

SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?') AS "유형" ,
       SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME ,
       B.POSITION ,
       SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME
  FROM DBA_CONSTRAINTS A ,
       DBA_CONS_COLUMNS B
 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
   AND A.OWNER = 'E_LUCIS'
   AND A.TABLE_NAME = UPPER(':테이블명')
 ORDER BY 1,2,3
 ;

 

8. index 목록 조회

SELECT A.INDEX_NAME ,
       A.UNIQUENESS ,
       TO_CHAR(COLUMN_POSITION, '999') AS POS ,
       SUBSTRB(COLUMN_NAME, 1, 33) AS COLUMN_NAME
  FROM USER_INDEXES A ,
       USER_IND_COLUMNS B
 WHERE A.INDEX_NAME = B.INDEX_NAME
   AND A.TABLE_OWNER = UPPER('E_LUCIS')
   AND A.TABLE_NAME = UPPER(':테이블명')
 ORDER BY 1, 3
 ;

 

9. 전체 index 정보 조회

SELECT SUBSTRB(A.TABLE_NAME, 1, 22) AS TABLE_NAME ,
       SUBSTRB(A.INDEX_NAME, 1, 23) AS INDEX_NAME ,
       SUBSTRB(A.UNIQUENESS, 1, 7) AS UNIQUE ,
       TO_CHAR(COLUMN_POSITION, '999') AS POS ,
       SUBSTRB(COLUMN_NAME, 1, 20) AS COLUMN_NAME
  FROM DBA_INDEXES A ,
       DBA_IND_COLUMNS B
 WHERE A.INDEX_NAME = B.INDEX_NAME
   AND A.TABLE_OWNER = B.TABLE_OWNER
   AND A.TABLE_OWNER = 'E_LUCIS'
 ORDER BY 1, 2, 3
 ;

 

10. index별 칼럼 조회

SELECT TABLE_NAME ,
       INDEX_NAME ,
       COLUMN_POSITION ,
       COLUMN_NAME
  FROM USER_IND_COLUMNS
 ORDER BY TABLE_NAME,
       INDEX_NAME,
       COLUMN_POSITION
;

오라클을 관리하는 DBA들이 꼭 아셔야 할 기준정보 조회 쿼리들 였습니다.

 

오늘도 파이팅!!

 

by.sTricky