-----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;
本文出自 “在路上” 博客，谢绝转载！
Common SQL for Oracle---(2)Check TableSpace
原文：http://genjason.blog.51cto.com/6330670/1636611