create or replace PROCEDURE SHOW_SPACE ( p_Owner IN VARCHAR2 DEFAULT USER,
p_segment_name IN VARCHAR2,
p_segment_type IN VARCHAR2 DEFAULT 'TABLE',
p_partition_name IN VARCHAR2 DEFAULT NULL
)
AUTHID CURRENT_USER
AS
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_last_used_extent_file_id NUMBER;
l_last_used_extent_block_id NUMBER;
l_last_used_block NUMBER;
l_unformatted_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_fs1_blocks NUMBER;
l_fs1_bytes NUMBER;
l_fs2_blocks NUMBER;
l_fs2_bytes NUMBER;
l_fs3_blocks NUMBER;
l_fs3_bytes NUMBER;
l_fs4_blocks NUMBER;
l_fs4_bytes NUMBER;
l_full_blocks NUMBER;
l_full_bytes NUMBER;
PROCEDURE p(p_Param1 IN VARCHAR2,p_Param2 IN VARCHAR2)
AS
BEGIN
dbms_output.put_line ( rpad(p_param1,30,'*')||' '|| p_param2);
END P;
BEGIN
dbms_space.unused_space(segment_owner=>p_Owner,
segment_name => p_segment_name,
segment_type=> p_segment_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_last_used_extent_file_id,
last_used_extent_block_id => l_last_used_extent_block_id,
last_used_block => l_last_used_block,
partition_name => p_partition_name
);
dbms_space.space_usage (segment_owner=>p_Owner,
segment_name => p_segment_name,
segment_type=> p_segment_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes,
partition_name => p_partition_name
);
p('l_total_blocks',l_total_blocks);
p('l_total_bytes',l_total_bytes);
p('l_unused_blocks',l_unused_blocks);
p('l_unused_bytes',l_unused_bytes);
p('l_last_used_extent_file_id',l_last_used_extent_file_id);
p('l_last_used_extent_block_id',l_last_used_extent_block_id);
p('l_last_used_block',l_last_used_block);
p('l_unformatted_blocks',l_unformatted_blocks);
p('l_unformatted_bytes',l_unformatted_bytes);
p('l_fs1_blocks',l_fs1_blocks);
p('l_fs1_bytes',l_fs1_bytes);
p('l_fs2_blocks',l_fs2_blocks);
p('l_fs2_bytes',l_fs2_bytes);
p('l_fs3_blocks',l_fs3_blocks);
p('l_fs3_bytes',l_fs3_bytes);
p('l_fs4_blocks',l_fs4_blocks);
p('l_fs4_bytes',l_fs4_bytes);
p('l_full_blocks',l_full_blocks);
p('l_full_bytes',l_full_bytes);
END show_space;
Procedure created.
Total Blocks..... Total blocks allocated to the table
Total Bytes...... Total bytes allocated to the table
Unused Blocks.... Blocks that have never contained data
Unused Bytes..... The above in bytes
This report does show blocks above the high water mark. Unused Blocks are exactly the
block above the high water mark.
dbms_space.space_usage Can only be used on object in a AUTO SEGMENT SPACE Managed tablespace,
for Manual Segment Space managment need to use dbms_space.free_blocks instead.
For Manual Segment Space Managment (9i & Prior version)
create or replace procedure show_space
( p_owner in varchar2 default USER ,
p_segment_name in varchar2 ,
p_segment_type in varchar2 default 'TABLE' ,
p_partition_name in varchar2 default null
)
authid current_user
as
l_free_blks number := 0;
l_total_blocks number := 0;
l_total_bytes number := 0;
l_unused_blocks number := 0;
l_unused_bytes number := 0;
l_last_used_extent_file_id number := 0;
l_last_used_extent_block_id number := 0;
l_last_used_block number := 0;
procedure p(p_param1 in varchar2,p_param2 in varchar2)
as
begin
dbms_output.put_line ( rpad(p_param1,30,'*')||' '|| p_param2);
end p;
begin
dbms_space.free_blocks ( segment_owner=> p_owner,
segment_name => p_segment_name,
segment_type => p_segment_type,
freelist_group_id => 0,
free_blks => l_free_blks,
scan_limit => null,
partition_name => p_partition_name
);
dbms_space.unused_space ( segment_owner => p_owner,
segment_name => p_segment_name,
segment_type => p_segment_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_last_used_extent_file_id,
last_used_extent_block_id => l_last_used_extent_block_id,
last_used_block => l_last_used_block,
partition_name => p_partition_name
);
p('l_free_blks',l_free_blks);
p('l_total_blocks',l_total_blocks);
p('l_total_bytes',l_total_bytes);
p('l_unused_blocks',l_unused_blocks);
p('l_unused_bytes',l_unused_bytes);
p('l_last_used_extent_file_id',l_last_used_extent_file_id);
p('l_last_used_extent_block_id',l_last_used_extent_block_id);
p('l_last_used_block',l_last_used_block);
end show_space;
scott@9iR2> SELECT tablespace_name,segment_space_management
2 FROM dba_tablespaces
3 WHERE segment_space_management ='MANUAL'
4 AND tablespace_name ='DATA1'
5 /
TABLESPACE_NAME SEGMEN
------------------------------ ------
DATA1 MANUAL
Elapsed: 00:00:00.01
scott@9iR2> exec show_space(USER,'ESR_FILES','TABLE');
l_free_blks******************* 0
l_total_blocks**************** 8
l_total_bytes***************** 65536
l_unused_blocks*************** 7
l_unused_bytes**************** 57344
l_last_used_extent_file_id**** 11
l_last_used_extent_block_id*** 148529
l_last_used_block************* 1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
No comments:
Post a Comment