About
Articles Related
Space Usage
Sqlplus
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage('QS_DWH','SYS_IL0000084970C00036$$',
'LOBINDEX',
:unf, :unfb,
:fs1, :fs1b,
:fs2, :fs2b,
:fs3, :fs3b,
:fs4, :fs4b,
:full, :fullb);
end;
/
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;
unused_space
The UNUSED_SPACE procedure of the DBMS_SPACE package returns space information on a segment:
- the position of the high water mark (ie the last block within the extent which contains data)
- and the amount of unused space
Plsql
DECLARE
in_owner dba_segments.owner%TYPE :='QS_DWH';
in_segment_name dba_segments.segment_name%TYPE:='SYS_IL0000084970C00036$$';
in_segment_type dba_segments.segment_type%TYPE:='LOBINDEX';
in_partition_name dba_segments.partition_name%TYPE := null;
out_total_blocks INTEGER;
out_total_bytes INTEGER;
out_unused_blocks INTEGER;
out_unused_bytes INTEGER;
out_last_used_extent_file_id INTEGER;
out_last_used_extent_block_id INTEGER;
out_last_used_block INTEGER;
BEGIN
dbms_space.unused_space(
segment_owner => in_owner
,segment_name => in_segment_name
,segment_type => in_segment_type
,partition_name => in_partition_name
,total_blocks => out_total_blocks
,total_bytes => out_total_bytes
,unused_blocks => out_unused_blocks
,unused_bytes => out_unused_bytes
,last_used_extent_file_id => out_last_used_extent_file_id
,last_used_extent_block_id => out_last_used_extent_block_id
,last_used_block => out_last_used_block
);
DBMS_OUTPUT.PUT_LINE('Input Parameters:');
DBMS_OUTPUT.PUT_LINE('Owner: ' || in_owner);
DBMS_OUTPUT.PUT_LINE('Segment Name: ' || in_segment_name);
DBMS_OUTPUT.PUT_LINE('Segment Type: ' || in_segment_type);
DBMS_OUTPUT.PUT_LINE('Partition Name: ' || in_partition_name || 'Partition name of the segment to be analyzed. This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Output parameters:');
DBMS_OUTPUT.PUT_LINE('Total Blocks: ' || out_total_blocks || ' (number of blocks in the segment)');
DBMS_OUTPUT.PUT_LINE('Total Bytes: ' || out_total_bytes || ' (number of blocks in the segment, in bytes)');
DBMS_OUTPUT.PUT_LINE('Unused Blocks: ' || out_unused_blocks || '(number of blocks which are not used.)');
DBMS_OUTPUT.PUT_LINE('Unused Bytes: ' || out_unused_bytes || '(number of blocks which are not used in bytes)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent File Id: ' || out_last_used_extent_file_id || '(Returns the file ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent Block Id:' || out_last_used_extent_block_id || '(Returns the starting block ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Block: ' || out_last_used_block || '(Returns the last block within this extent which contains data)');
END;
/
sqlplus
VARIABLE total_blocks number;
VARIABLE total_bytes number;
VARIABLE unused_blocks number;
VARIABLE unused_bytes number;
VARIABLE lastextf number;
VARIABLE last_extb number;
VARIABLE lastusedblock number;
BEGIN
DBMS_SPACE.UNUSED_SPACE('QS_DWH', 'SYS_IL0000084970C00036$$', 'LOBINDEX', :total_blocks,
:total_bytes,:unused_blocks, :unused_bytes, :lastextf,
:last_extb, :lastusedblock);
DBMS_OUTPUT.PUT_LINE('Output parameters:');
DBMS_OUTPUT.PUT_LINE('Total Blocks: ' || :total_blocks || ' (number of blocks in the segment)');
DBMS_OUTPUT.PUT_LINE('Total Bytes: ' || :total_bytes || ' (number of blocks in the segment, in bytes)');
DBMS_OUTPUT.PUT_LINE('Unused Blocks: ' || :unused_blocks || '(number of blocks which are not used.)');
DBMS_OUTPUT.PUT_LINE('Unused Bytes: ' || :unused_bytes || '(number of blocks which are not used in bytes)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent File Id: ' || :lastextf || '(Returns the file ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent Block Id:' || :last_extb || '(Returns the starting block ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Block: ' || :lastusedblock || '(Returns the last block within this extent which contains data)');
END;
/