Script to Tablespace Usage and Quota

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 80

REM
REM || Title : show_tspace_quota.sql
REM ||
REM || Purpose : Script to Show Tablespace Usage and Quota by User
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||


TTITLE CENTER 'Database Tablespace Utilisation Summary' skip 2

COLUMN owner FORMAT A20 JUSTIFY c HEADING 'Owner'
COLUMN name FORMAT A20 JUSTIFY c HEADING 'Tablespace' trunc
COLUMN quota FORMAT A12 JUSTIFY c HEADING 'Quota (KB)'
COLUMN used FORMAT 999,999,990 JUSTIFY c HEADING 'Used (KB)'

BREAK ON OWNER skip 1

SELECT username owner,
tablespace_name name,
DECODE (GREATEST (max_bytes, -1), -1,
'Unrestricted', TO_CHAR (max_bytes / 1024, '999,999,990')) quota,
bytes / 1024 used
FROM dba_ts_quotas
WHERE max_bytes != 0
OR bytes != 0
ORDER BY 1,2

spool show_tspace_quota.lis
/
spool off

SET SERVEROUTPUT ON
SET FEEDBACK ON
SET PAGES 80

CLEAR BREAKS

TTITLE OFF
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.