Run as a user with SELECT_CATALOG_ROLE.
In the code below, I've set my alert level at 85% (ie. a file is asterisked if it is 85% full, or if contents are 85% of autoextensible limit). This is adjustable by changing the numbers in the "%" and "!" columns.
NB. For UNIX systems, change the '\' in the fname_root column to a '/'.
/* * * tsfreespace.sql * NR 2001-2004 * sizes in MB */ set echo off set pagesize 16 set linesize 200 col fname_root for a30 select /*+ RULE */ df.tablespace_name, substr(df.file_name,1,2) drive, df.file_id, round(df.bytes/(1024*1024),0) filesize, round(sum(fs.bytes)/(1024*1024),2) used, decode( sum(fs.bytes), 0, 0, round(100*sum(fs.bytes)/df.bytes,2)) pctused, decode( sign(85-round(100*sum(nvl(fs.bytes,1))/df.bytes,2)),-1,'*','') "%", df.autoextensible, round(df.maxbytes/(1024*1024),0) maxsize, decode( sign(85-round(100*sum(nvl(fs.bytes,1))/df.maxbytes,2)),-1,'*','') "!", substr(df.file_name, instr(df.file_name,'\',-1)+1, length(df.file_name)) fname_root from dba_data_files df, dba_extents fs where df.file_id=fs.file_id(+) group by df.tablespace_name, df.file_name, df.file_id, df.bytes, df.autoextensible, df.maxbytes order by 1,2 / set echo on
Sample output:
TABLESPACE_NAME DR FILE_ID FILESIZE USED PCTUSED % AUT MAXSIZE ! FILE_NAME ------------------------------ -- ---------- ---------- ---------- ---------- - --- ---------- - ------------ ASP_DATA G: 4 2000 1529.98 76.5 YES 3000 ASP_DAT1.DBF BULK_LOAD G: 11 3950 2356.54 59.66 YES 3999 BULKLD1.DBF DRSYS G: 3 50 1.85 3.7 YES 1000 DRSYS1.DBF EREQ_SPACE G: 12 1200 935.88 77.99 YES 3000 EREQ01.DBF FAC_EFFORT G: 14 200 2.69 1.34 YES 300 FAC_EFF1.DBF GRAVEYARD G: 6 320 229.88 71.84 YES 2000 GRAVYD01.DBF INDICES G: 5 2500 2153.61 86.14 * YES 3000 ASP_IDX1.DBF INDX G: 13 900 699.32 77.7 YES 1000 EREQIND1.DBF METADATA G: 7 44 17.09 38.84 YES 1024 METADATA.DBF PS_DATA G: 16 3500 2971.12 84.89 YES 3999 PSDATA1.DBF RLOG G: 17 1016 1016.05 100 * YES 3000 RLOG1.DBF ROLLBACK G: 2 2200 49.41 2.25 YES 2500 RBACK1.DBF SYSTEM G: 1 400 317.28 79.32 YES 3999 SYSTEM1.DBF TOOLS G: 15 200 45.06 22.53 YES 1000 TOOLS1.DBF USER_DATA G: 8 1400 1242.26 88.73 * YES 1600 USER1.DBF UTILITY_DATA G: 10 1000 778.16 77.82 YES 2000 UTILITY1.DBF WORK_ARCH G: 9 1200 1049.28 87.44 * YES 3000 WORKAR1.DBF