check.sql

accept type prompt "Enter the type of segment to check    i = index   t = table   c = cluster: "
accept ts_name prompt "Enter the tablespace name that you wish to check: "
set serveroutput on feedback off
REM  spool &ts_name..lst
spool unused_space.lst
declare
  v_total_blocks number;
   v_total_bytes number;
   v_unused_blocks number;
   v_unused_bytes number;
   v_file_id number;
   v_block_id number;
   v_last_block number;
   v_used number;
   v_owner varchar2(12);
   v_segment varchar2(80);
   v_type char(1);
   cursor index_c is
     select owner, index_name
     from sys.dba_indexes 
     where tablespace_name = upper('&ts_name');
   cursor table_c is
     select owner, table_name
     from sys.dba_tables 
     where tablespace_name = upper('&ts_name');
   cursor cluster_c is
     select owner, cluster_name
     from sys.dba_clusters 
     where tablespace_name = upper('&ts_name');
 begin
   dbms_output.enable(32000);
   v_type := '&type';
   IF v_type = 'i' or v_type = 'I' THEN 
      open index_c;
      fetch index_c into v_owner, v_segment;
      while index_c%FOUND loop
            dbms_space.unused_space(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes, 
                  v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
            dbms_output.put_line(CHR(10));
            dbms_output.put_line('Index Name                   = '||v_segment);
            dbms_output.put_line('Total Blocks                 = '||v_total_blocks);
            dbms_output.put_line('Total Bytes                  = '||v_total_bytes);
            dbms_output.put_line('.     KBytes                 = '||v_total_bytes/1024);
            dbms_output.put_line('.     MBytes                 = '||(v_total_bytes/1024)/1024);
            dbms_output.put_line('Unused Blocks                = '||v_unused_blocks);
            dbms_output.put_line('Unused Bytes                 = '||v_unused_bytes);
            dbms_output.put_line('.      KBytes                = '||v_unused_bytes/1024);
            dbms_output.put_line('.      MBytes                = '||(v_unused_bytes/1024)/1024);
            v_used := v_total_blocks - v_unused_blocks;
            dbms_output.put_line('Used Blocks                  = '||v_used);
            v_used := v_total_bytes - v_unused_bytes;
            dbms_output.put_line('Used Bytes                   = '||v_used);
            dbms_output.put_line('.    KBytes                  = '||v_used/1024);
            dbms_output.put_line('.    MBytes                  = '||(v_used/1024)/1024);
            dbms_output.put_line('Last used extents file id    = '||v_file_id);
            dbms_output.put_line('Last used extents block id   = '||v_block_id);
            dbms_output.put_line('Last used block              = '||v_last_block);
            fetch index_c into v_owner, v_segment;
       end loop;
       close index_c;
  
   ELSIF v_type = 't' or v_type = 'T' THEN 
         open table_c;
         fetch table_c into v_owner, v_segment;
         while table_c%FOUND loop
             dbms_space.unused_space(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes, 
                   v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
             v_used := v_total_bytes - v_unused_bytes;
             dbms_output.put_line(CHR(10));
             dbms_output.put_line('Table Name                   = '||v_segment);
             dbms_output.put_line('Total Blocks                 = '||v_total_blocks);
             dbms_output.put_line('Total Bytes                  = '||v_total_bytes);
             dbms_output.put_line('.     KBytes                 = '||v_total_bytes/1024);
             dbms_output.put_line('.     MBytes                 = '||(v_total_bytes/1024)/1024);
             dbms_output.put_line('Unused Blocks                = '||v_unused_blocks);
             dbms_output.put_line('Unused Bytes                 = '||v_unused_bytes);
             dbms_output.put_line('.      KBytes                = '||v_unused_bytes/1024);
             dbms_output.put_line('.      MBytes                = '||(v_unused_bytes/1024)/1024);
             v_used := v_total_blocks - v_unused_blocks;
             dbms_output.put_line('Used Blocks                  = '||v_used);
             v_used := v_total_bytes - v_unused_bytes;
             dbms_output.put_line('Used Bytes                   = '||v_used);
             dbms_output.put_line('.    KBytes                  = '||v_used/1024);
             dbms_output.put_line('.    MBytes                  = '||(v_used/1024)/1024);
             dbms_output.put_line('Last used extents file id    = '||v_file_id);
             dbms_output.put_line('Last used extents block id   = '||v_block_id);
             dbms_output.put_line('Last used block              = '||v_last_block);
             fetch table_c into v_owner, v_segment;
       end loop;
       close table_c;
   ELSIF v_type = 'c' or v_type = 'C' THEN 
         open cluster_c;
         fetch cluster_c into v_owner, v_segment;
         while cluster_c%FOUND loop
             dbms_space.unused_space(v_owner, v_segment, 'CLUSTER', v_total_blocks, v_total_bytes, 
                   v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
             v_used := v_total_bytes - v_unused_bytes;
             dbms_output.put_line(CHR(10));
             dbms_output.put_line('Cluster Name                 = '||v_segment);
             dbms_output.put_line('Total Blocks                 = '||v_total_blocks);
             dbms_output.put_line('Total Bytes                  = '||v_total_bytes);
             dbms_output.put_line('.     KBytes                 = '||v_total_bytes/1024);
             dbms_output.put_line('.     MBytes                 = '||(v_total_bytes/1024)/1024);
             dbms_output.put_line('Unused Blocks                = '||v_unused_blocks);
             dbms_output.put_line('Unused Bytes                 = '||v_unused_bytes);
             dbms_output.put_line('.      KBytes                = '||v_unused_bytes/1024);
             dbms_output.put_line('.      MBytes                = '||(v_unused_bytes/1024)/1024);
             v_used := v_total_blocks - v_unused_blocks;
             dbms_output.put_line('Used Blocks                  = '||v_used);
             v_used := v_total_bytes - v_unused_bytes;
             dbms_output.put_line('Used Bytes                   = '||v_used);
             dbms_output.put_line('.    KBytes                  = '||v_used/1024);
             dbms_output.put_line('.    MBytes                  = '||(v_used/1024)/1024);
             dbms_output.put_line('Last used extents file id    = '||v_file_id);
             dbms_output.put_line('Last used extents block id   = '||v_block_id);
             dbms_output.put_line('Last used block              = '||v_last_block);
             fetch cluster_c into v_owner, v_segment;
       end loop;
       close cluster_c;
   END IF;
 end;
 /
 spool off