Databases Reference
In-Depth Information
6.
Test the space occupied by the BLOB data; the
DBMS_SPACE.SPACE_USAGE
procedure returns information about free data blocks in an object whose segment
space management is
AUTO
:
SET SERVEROUTPUT ON
declare
l_segment_name VARCHAR2(30);
l_segment_size_blocks NUMBER;
l_segment_size_bytes NUMBER;
l_used_blocks NUMBER;
l_used_bytes NUMBER;
l_expired_blocks NUMBER;
l_expired_bytes NUMBER;
l_unexpired_blocks NUMBER;
l_unexpired_bytes NUMBER;
begin
select segment_name into l_segment_name from user_lobs where
table_name = 'MYCUSTOMERS';
DBMS_OUTPUT.put_line('segment name: ' || l_segment_name);
DBMS_SPACE.SPACE_USAGE(
segment_owner => USER,
segment_name => l_segment_name,
segment_type => 'LOB',
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes);
DBMS_OUTPUT.put_line('used_blocks ' || l_used_blocks);
DBMS_OUTPUT.put_line('used_bytes ' || l_used_bytes);
end;
