feespace.sql
rem tablesp.sql rem rem Creates a report showing the allocated, used, and free space for every rem tablespace rem set verify off set echo off set termout off set pagesize 9999 set feedback off rem rem Calculate blocks to bytes conversion ratio rem column dbb noprint new_value mbytes select value / 1048576 dbb from v$parameter where name = 'db_block_size'; rem rem Calculate database name rem column dbname noprint new_value sid select name dbname from v$database; rem rem Calculate current date for the report rem column now noprint new_value today select to_char(sysdate, 'MM/DD/YY') now from dual; rem set termout on create table tablesp_tmp ( tablespace_name varchar2(30), allocated number, used number, free number); declare cursor df_cursor is select tablespace_name, round(sum(blocks) * &mbytes) from sys.dba_data_files group by tablespace_name order by 1; cursor fs_cursor (p_tblsp varchar2) is select round(sum(blocks) * &mbytes) from sys.dba_free_space where tablespace_name = p_tblsp; lv_tablespace_name sys.dba_data_files.tablespace_name%TYPE; lv_allocated number; lv_free number; procedure write_out is begin insert into tablesp_tmp values ( lv_tablespace_name, lv_allocated, lv_allocated - lv_free, /* Megabytes used */ lv_free); end; begin open df_cursor; loop fetch df_cursor into lv_tablespace_name, lv_allocated; exit when df_cursor%NOTFOUND; open fs_cursor(lv_tablespace_name); fetch fs_cursor into lv_free; if fs_cursor%NOTFOUND then lv_free := 9999; end if; close fs_cursor; write_out; end loop; close df_cursor; end; / set termout on ttitle center 'Tablespace Usage report for database &sid' right '&today' skip 2 col allocated format 999999990 heading "Megabytes|Allocated" col used format 999999990 heading "Megabytes|Used " col free format 999999990 heading "Megabytes|Free " col pct format 990 heading "Percent|Free " break on report compute sum of allocated used free on report spool tablesp.lis select tablespace_name, allocated, used, free, decode(allocated, 0, 0, round(100*free/allocated)) pct from tablesp_tmp order by 1; spool off drop table tablesp_tmp; set termout on prompt Created tablesp.lis report file... set termout off --exit