[ORACLE] 시스템 SQL

1. Table space 사이즈 체크
 
SELECT TABLESPACE_NAME                                           ,
       ((EXTEND_BYTES/1024)/1024)||'M'                        EXT,
       ((USED_BYTES/1024)/1024)||'M'                         USED,
       ROUND(((USED_BYTES)/EXTEND_BYTES)*100,2) || '%'   USE_RATE,
       ((FREE_BYTES/1024)/1024)                              FREE,
       ROUND((1-((USED_BYTES)/EXTEND_BYTES))*100,2) || '%' FREE_RATE
  FROM (SELECT T1.TABLESPACE_NAME,
               T1.EXTEND_BYTES,
               T1.EXTEND_BYTES - T2.FREE_BYTES USED_BYTES,
               T2.FREE_BYTES
         FROM (SELECT TABLESPACE_NAME,
                      SUM(BYTES) EXTEND_BYTES
                 FROM DBA_DATA_FILES
                GROUP BY TABLESPACE_NAME ) T1,
              (SELECT TABLESPACE_NAME,
                      SUM(BYTES) FREE_BYTES
                 FROM DBA_FREE_SPACE
                GROUP BY TABLESPACE_NAME) T2
        WHERE T2.TABLESPACE_NAME = T1.TABLESPACE_NAME )
 ORDER BY TABLESPACE_NAME







2. Table space 사이즈 체크(다른것)

SELECT A.TABLESPACE_NAME,
       ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
       ROUND(MAXBYTES/1048576,2) MAX_SIZE
  FROM ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES) BYTES_ALLOC,
                SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
           FROM DBA_DATA_FILES F
                GROUP BY TABLESPACE_NAME) A,
                ( SELECT F.TABLESPACE_NAME,
                         SUM(F.BYTES)  BYTES_FREE
                    FROM DBA_FREE_SPACE F
                   GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
UNION
SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
       ROUND(SUM(BYTES_FREE) / 1048576,2),
       ROUND(SUM(BYTES_USED) / 1048576,2),
       ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
       100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
       ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
  FROM SYS.V_$TEMP_SPACE_HEADER
 GROUP BY TABLESPACE_NAME
 ORDER BY 1;

 3. 테이블 사이즈 확인
SELECT SEGMENT_TYPE     ,
       SEGMENT_NAME    ,
       TABLESPACE_NAME ,
       (BYTES/1024) KB
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 ORDER BY SEGMENT_TYPE,SEGMENT_NAME;

4.테이블스페이스 파일 크기 확인
select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files 

5.Tablespace내 table 확인
 select * from tabs
 where tablespace_name = 'SYSTEM'

6.오라클 버전 확인
select * from v$version