Rem Step1 -> Setup views so that it can be monitored by a non-sys user
 
connect sys as sysdba
create view v_ftfbhc as select * from sys.x$ktfbhc;
 
grant select on v_ftfbhc to sysmisc;
 
Rem  Step 2 -> Create a view that's an alternative to dba_segments view
 
connect sysmisc
 
REM Create an alternative view to dba_segments that just looks for segments whose type are not in (ROLLBACK, UNDO, DEFERRED ROLLBACK,TEMPORARY,CACHE,SPACE HEADER,UNDEFINED)
 
 
CREATE OR REPLACE VIEW quick_segs (
   owner,
   segment_name,
   partition_name,
   segment_type,
   tablespace_name,
   bytes,
   blocks,
   extents,
   initial_extent,
   next_extent,
   min_extents,
   max_extents,
   pct_increase,
   buffer_pool )
AS
select u.name, o.name, o.subname, so.object_type, ts.name,
       dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.blocks)*ts.blocksize,
       dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.blocks),
       dbms_space_admin.segment_number_extents(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.extents),
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys.user$ u, sys.obj$ o, sys.ts$ ts,
     sys.sys_objects so, sys.seg$ s, sys.file$ f
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = u.user#
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
  and s.ts# = f.ts#
  and s.file# = f.relfile#
 
REM SQL statement that can be run to get a list of tablespaces whosse space usage has fallen below a use supplied threshold
REM This will be faster than using dba_tablespaces or dba_data_files view since we'll be going to the source directly
 
WITH tbs_listing as (select distinct segment_type,tablespace_name from quick_segs where
segment_type in ('TABLE','TABLE PARTITION','INDEX','INDEX PARTITION')),
-- Local dba_data_files to avoid extra joins with dba_tablespaces and other views
dba_data_files as (
select v.name file_name, f.file# file_id, ts.name tablespace_name,
       ts.blocksize * f.blocks bytes, f.blocks blocks,
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED') status,
       f.relfile# relative_fno, decode(f.inc, 0, 'NO', 'YES') AUTOEXTENSIBLE,
       ts.blocksize * f.maxextend MAXBYTES, f.maxextend MAXBLOCKS, f.inc INCREMENT_BY,
       ts.blocksize * (f.blocks - 1) USER_BYTES, f.blocks - 1 USER_BLOCKS,
-- Included contests column from ts$ so that an extra join with dba_tablespaces can be avoided
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
                 'PERMANENT')), 1, 'TEMPORARY') contents
from sys.file$ f, sys.ts$ ts, v$dbfile v
where v.file# = f.file#
  and f.spare1 is NULL
  and f.ts# = ts.ts#
union all
select
       v.name,f.file#, ts.name,
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#,
       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
                 'PERMANENT')), 1, 'TEMPORARY') contents
from v$dbfile v, sys.file$ f, sys.v_ftfbhc hc, sys.ts$ ts
where v.file# = f.file#
  and f.spare1 is NOT NULL
  and v.file# = hc.ktfbhcafno
  and hc.ktfbhctsn = ts.ts#)
select /*+ rule */ distinct
decode(segment_type,'TABLE PARTITION','TABLE-PARTITION','INDEX PARTITION','INDEX-PARTITION',segment_type),
nvl(a.tablespace_name,b.tablespace_name) tbs_name,floor(((nvl(sum_bytes,0)+nvl(sum(b.bytes),0))/1024)/1024) pct_free from
tbs_listing,
(select tablespace_name,sum(decode(AUTOEXTENSIBLE,'NO',bytes,maxbytes)) - sum(bytes) sum_bytes,
sum(decode(AUTOEXTENSIBLE,'NO',bytes,maxbytes)) sum2_bytes
from dba_data_files where contents='PERMANENT' and status = 'ONLINE'
group by tablespace_name) a,dba_free_space b
where a.tablespace_name=b.tablespace_name (+)
and a.tablespace_name = tbs_listing.tablespace_name
group by segment_type,nvl(a.tablespace_name,b.tablespace_name),nvl(a.sum_bytes,0),nvl(a.sum2_bytes,0)
having floor(((nvl(sum_bytes,0)+nvl(sum(b.bytes),0))/1024)/1024) <= &1;

exit