Title: Observing Occupied and Free Space of Tablespaces

Author: Ari Arantes Filho, a DBA at Doctordata, in S?o Paulo/SP - Brasil.

As an Oracle Database Administrator it is helpful to know the amount of space and free space of tablespaces. The following
script creates a view called TS which shows, on the same select, the tablespace name, blocks, total space, free space, and
percentage of free space on the tablespace.

Caveats: The script must be run by the user SYS or INTERNAL. To view the Tablespace information, an Oracle user granted
DBA privileges issues this query:

select * from ts;

/* ------------------------------------------------- */
create or replace view ts_free (
  tablespace_name,
  blocks,
  bytes
) as
select ts.name,
       sum(f.length),
       sum(f.length * ts.blocksize)
from sys.fet$ f,
     sys.ts$ ts
where f.ts# = ts.ts#
group by ts.name;
create or replace view ts_size (
  tablespace_name,
  blocks,
  bytes
) as
select ts.name,
       sum(f.blocks),
       sum(ts.blocksize * f.blocks)
from sys.file$ f,
     sys.ts$ ts
where f.ts# = ts.ts# (+)
and   f.status$ = 2
group by ts.name;
create or replace view ts (
  tablespace_name,
  blocks,
  bytes,
  bytes_used,
  bytes_free,
  pct_free
) as
select substr(s.tablespace_name, 1, 20),
       s.blocks,
       s.bytes,
       s.bytes - f.bytes,
       f.bytes,
       to_char(f.bytes * 100 / s.bytes, '999.99')
from ts_size s, ts_free f
where s.tablespace_name = f.tablespace_name;
grant select on ts to dba;
create public synonym ts for ts;
/* ------------------------------------------------- */