Question: What is the
dbms_space.space_usage used for? I want to get the space used
for a segment, and I need to understand how the
dbms_space.space_usage computes used space. Can
dbms_space.space_usage count-up actual row space used?
Answer: The dbms_space.space_usage
procedure is designed to show the space usage of data blocks
under the High Water Mark for that segment. Bitmap blocks,
segment header, and extent map blocks are not accounted for by
dbms_space.space_usage.
The dbms_space.space_usage
procedure can only be used on tablespaces that are created with auto
segment space management.
The
dbms_space package's space_usage procedure
can be used to
get the number of blocks in a partition:
set serveroutput on
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
v_segname varchar2(500);
begin
see code
depot for full script
dbms_space.space_usage(
segment_owner => 'SCOTT,
segment_name => 'EMP,
segment_type => 'TABLE',
fs1_bytes =>
l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.enable;
dbms_output.put_line('=============================================');
dbms_output.put_line('total blocks =
'||to_char(l_fs1_blocks + l_fs2_blocks +
l_fs3_blocks + l_fs4_blocks + l_full_blocks)||
' || total bytes = '||
to_char(l_fs1_bytes + l_fs2_bytes +
l_fs3_bytes + l_fs4_bytes + l_full_bytes));
end;
The output from dbms_space.space_usage
is a count of how many blocks have certain amounts of free space.
Through the use of dbms_space.space_usage, you can
determine which blocks can be recovered.
There is
additional useful information on the dbms_space.space_usage
function available.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
