If a table was created with a large initial extent and you want to resize that extent you can issue:
alter table schema_name.table_name deallocate unused keep 50000000;
This will deallocate the space and make it available to other objects in that tablespace. This will also reset the initial_extent and min_extents for that table. If you don't use keep all unused space above the high-water mark will be freed. If you specify keep, the number of bytes you specify will be kept above the high-water mark.
To find out what the high-water mark is, you can use the dbms_space.unused_space package:
VARIABLE total_blocks number;
VARIABLE total_bytes number;
VARIABLE unused_blocks number;
VARIABLE unused_bytes number;
VARIABLE last_used_extent_file_id number;
VARIABLE last_used_extent_block_id number;
VARIABLE last_used_block number;
begin
dbms_space.unused_space('schema_name','table_name','TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :last_used_extent_file_id, :last_used_extent_block_id, :last_used_block);
end;
/
print total_blocks total_bytes unused_blocks unused_bytes last_used_extent_file_id last_used_extent_block_id last_used_block;
Example:
VARIABLE total_blocks number;
VARIABLE total_bytes number;
VARIABLE unused_blocks number;
VARIABLE unused_bytes number;
VARIABLE last_used_extent_file_id number;
VARIABLE last_used_extent_block_id number;
VARIABLE last_used_block number;
begin
dbms_space.unused_space('MAP','ADDRESS_REFERENCE','TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :last_used_extent_file_id, :last_used_extent_block_id, :last_used_block);
end;
/
print total_blocks total_bytes unused_blocks unused_bytes last_used_extent_file_id last_used_extent_block_id last_used_block;
VARIABLE total_blocks number;
VARIABLE total_bytes number;
VARIABLE unused_blocks number;
VARIABLE unused_bytes number;
VARIABLE last_used_extent_file_id number;
VARIABLE last_used_extent_block_id number;
VARIABLE last_used_block number;
begin
dbms_space.unused_space('ADMIN','CNV_CHIMASTER_C5','TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :last_used_extent_file_id, :last_used_extent_block_id, :last_used_block);
end;
/
print total_blocks total_bytes unused_blocks unused_bytes last_used_extent_file_id last_used_extent_block_id last_used_block;
|