Here's a bunch of sql commands I use to check for tablespace utilization
SET LINESIZE 209
SET PAGESIZE 26
SET FEEDBACK OFF
SET HEADING ON
SET TIMING OFF
SET ECHO OFF
SET UNDERLINE =
VARIABLE xxx CHAR(200)
COLUMN "Tablespace" FORMAT A16
/* COLUMN "Initial extent(Mb)" FORMAT 999,999 */
COLUMN "Status" FORMAT a7
/* COLUMN "Next extent(MB)" FORMAT 999,999 */
/* COLUMN "Max extents" FORMAT 99,999,999,999 */
/* COLUMN "Pct_increase" FORMAT 999 */
COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
/* COLUMN "USED BLOCKS" FORMAT 999,999,999 */
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
/* COLUMN "FREE BLOCKS" FORMAT 999,999,999 */
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
/* COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999 */
BREAK ON "Tablespace"
DECLARE
CURSOR c1 IS SELECT 'Print Date:'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||' ***** Tablespace InFORMATion from DataBase :'||name||' Instance:'||instance||' *****' xxx
FROM v$database,v$thread
WHERE rownum=1;
BEGIN
OPEN c1;
FETCH c1 INTO :xxx;
CLOSE c1;
END;
/
SET PAGESIZE 1
PRINT xxx
SET PAGESIZE 24
SELECT SUBSTR(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
/* MAX(A.initial_extent)/1024 "Initial extent(Kb)", */
/* MAX(A.next_extent)/1024 "Next extent(Kb)", */
/* MAX(A.max_extents) "Max extents", */
/* MAX(A.pct_increase) "Pct_increase", */
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
/* (SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS", */
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
/* SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS", */
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)"
/* SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS" */
FROM dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
ORDER BY 1;
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON
EXIT
