Title: Reclaiming Unused Space
This Tip of the Week entry comes from Dale Ware, a database administrator for Maxim
Group in St. Louis, Missouri.
Before growing a datafile in a tablespace that shows on your space analysis reports,
search for space that can be reclaimed from an object that was poorly sized initially.
Tables, indexes and clusters can be altered with a deallocate unused, thus reclaiming
unused space above the high-water mark. This script prompts you for two pieces of
information:
1. The type of segment to retrieve, (i=indexes, t=tables & c=clusters)
2. The tablespace_name to retrieve from.
Simply put, this allows you to retrieve one of these segment types by tablespace_name.
It is important to note that deallocating unused space became available with Oracle
version 7.3.
Source/Text/Comments
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