Common SQL for Oracle---(2)Check TableSpace

-----Check TableSpace-----

---Check DataFile

SELECT TABLESPACE_NAME "TABLESPACE",

       FILE_NAME "DATAFILE",

       ROUND(BYTES / (1024 )) "FILE_SIZE(KB)"

  FROM DBA_DATA_FILES

 WHERE TABLESPACE_NAME LIKE ‘&TABLESPACE‘

 ORDER BY DATAFILE DESC;

---Add DataFile 

alter tablespace ODS_100M1A_DTS add datafile ‘/oradata31/odsf10/ods_100m1a_dp196.dbf‘ size 2048064k;

---Check TableSpace Usage

SELECT DF.TABLESPACE_NAME "TABLESPACE",

       100 - ROUND(SUM(FS.BYTES) * 100 / DF.BYTES) "USED_PERCENT(%)",

       ROUND(SUM(FS.BYTES) / (1024 * 1024)) "FREE_SIZE(MB)",

       ROUND(DF.BYTES / (1024 * 1024)) "TOTAL_SIZE(MB)"

  FROM DBA_FREE_SPACE FS,

       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) DF

 WHERE FS.TABLESPACE_NAME(+) = DF.TABLESPACE_NAME

 GROUP BY DF.TABLESPACE_NAME, DF.BYTES

 ORDER BY 3;

SELECT *

  from (SELECT a.tablespace_name tableSpaceName,

               round(NVL(a.BYTES / 1024 / 1024/1024,0),2) "totalSize(G)",

               round(NVL(b.bytes / 1024 / 1024/1024,0),2) "freeSize(G)",

               round(NVL((a.BYTES - b.BYTES) / 1024 / 1024/1024,0),2) "usedSize(G)",

               round(NVL((a.BYTES - b.BYTES) / a.BYTES * 100, 0), 2) usedPercent

          from (SELECT tablespace_name, sum(BYTES) bytes

                  FROM dba_data_files

                 GROUP BY tablespace_name) a,

               (SELECT tablespace_name, sum(BYTES) bytes, max(BYTES) largest

                  FROM dba_free_space

                 GROUP BY tablespace_name) b

         WHERE a.tablespace_name = b.tablespace_name

         ORDER BY a.tablespace_name)

UNION

SELECT d.tablespace_name tableSpaceName,

       round(NVL(a.BYTES / 1024 / 1024/1024, 0),2) "totalSize(G)",

       round(NVL(a.BYTES / 1024 / 1024/1024, 0),2) - round(NVL(t.BYTES, 0) / 1024 / 1024/1024,2) "freeSize(G)",

       round(NVL(t.BYTES, 0) / 1024 / 1024/1024,2) usedSize,

       round(NVL(t.BYTES / a.BYTES * 100, 0), 2) "usedSize(G)"

  FROM SYS.dba_tablespaces d,

       (SELECT tablespace_name, SUM(BYTES) BYTES

          FROM dba_temp_files

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM(bytes_cached) BYTES

          FROM v$temp_extent_pool

         GROUP BY tablespace_name) t

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = t.tablespace_name(+)

   AND d.extent_management LIKE ‘LOCAL‘

   AND d.CONTENTS LIKE ‘TEMPORARY‘;

---check undo usage

SELECT UNDO_TYPE,TYPE_SIZE_MB,ROUND(100*RATIO_TO_REPORT(TYPE_SIZE_MB) OVER(),2) TYPE_PCT

  FROM (SELECT STATUS UNDO_TYPE,ROUND(SUM(BYTES)/1024/1024) TYPE_SIZE_MB

          FROM DBA_UNDO_EXTENTS

         GROUP BY STATUS

         UNION ALL

        SELECT ‘FREE‘,ROUND(SUM(DFS.BYTES)/1024/1024)

          FROM DBA_FREE_SPACE DFS,DBA_TABLESPACES TBS

         WHERE DFS.TABLESPACE_NAME=TBS.TABLESPACE_NAME

           AND TBS.CONTENTS=‘UNDO‘);

----undo usage by session

SELECT S.SID,S.SERIAL#,S.SQL_ID,S.USERNAME,S.OSUSER,S.MACHINE,S.PROGRAM,S.EVENT,T.START_DATE,(SYSDATE-T.START_DATE)*24*60*60 UNDO_SECONDS,

       T.USED_UBLK/1024/1024*(SELECT BLOCK_SIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=R.TABLESPACE_NAME) UNDO_SIZE_MB,T.STATUS

  FROM DBA_ROLLBACK_SEGS R,V$TRANSACTION T,V$SESSION S

 WHERE R.SEGMENT_ID=T.XIDUSN

   AND T.ADDR=S.TADDR

 ORDER BY UNDO_SIZE_MB DESC;

---check temp tablespace by session

SELECT SE.USERNAME,

       SU.BLOCKS * 8 /1024 "M",

       SU.SQL_ID,

       SU.SQLHASH,

       SA.SQL_TEXT,

       SA.SQL_FULLTEXT,

       se.SID

  FROM V$SORT_USAGE SU, V$SQLAREA SA, V$SESSION SE

 WHERE SU.SQL_ID = SA.SQL_ID

   AND se.SERIAL# = su.SESSION_NUM

 ORDER BY 2 DESC;

SELECT * FROM DBA_TEMP_FILES;

本文出自 “在路上” 博客，谢绝转载！