NAME
CkSpaceBLOB.sql
DESCRIPTION
Check the space usage of BLOB column which is stored as LOBSEGMENT type segment
USAGE
CkSapceBLOB <table_owner> <table_name> <lob_column_name>
Note
1. This script is run as DBA using SQL*Plus or SQLcl.
2. Case sensitive object name has to be enclosed with double quota then single quota.
For example
Case insensitive:
SQL>@CkSapceBLOB user01 table01 column01
Case sensitive:
SQL@CkSapceBLOB '"user01"' '"table01"' '"column01"'
Source code of CkSpaceBLOB.sql,
set echo off
set feed off
set verify off
set serveroutput on
declare
f_table_owner varchar2(255):='&1';
f_table_name varchar2(255):='&2';
f_column_name varchar2(255):='&3';
f_segment_name varchar2(255);
f_data_type varchar2(128);
f_securefile varchar2(3);
f_full_col_name varchar2(255):=f_table_owner||'.'||f_table_name||'('||f_column_name||')';
f_sql varchar2(255):='select nvl(sum(nvl(dbms_lob.getlength('||f_column_name||'),0)),0) from '||f_table_owner||'.'||f_table_name;
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;
l_unused_blocks number;
l_unused_bytes number;
l_non_data_blocks number;
l_non_data_bytes number;
begin
------------------------------------------------------------------------------------
-- Convert object name to uppercase if not case sensitive (enclosed by double quota)
------------------------------------------------------------------------------------
if instr(f_table_owner,'"')<>1 or instr(f_table_owner,'"',-1)<>length(f_table_owner) then
f_table_owner:=upper(f_table_owner);
else
f_table_owner:=replace(f_table_owner,'"','');
end if;
if instr(f_table_name,'"')<>1 or instr(f_table_name,'"',-1)<>length(f_table_name) then
f_table_name:=upper(f_table_name);
else
f_table_name:=replace(f_table_name,'"','');
end if;
if instr(f_column_name,'"')<>1 or instr(f_column_name,'"',-1)<>length(f_column_name) then
f_column_name:=upper(f_column_name);
else
f_column_name:=replace(f_column_name,'"','');
end if;
------------------------------------------------------------------------------------
-- Get LOB segment name by column name
------------------------------------------------------------------------------------
begin
select a.segment_name, a.securefile, b.data_type
into f_segment_name,f_securefile,f_data_type
from dba_lobs a, dba_tab_cols b
where a.owner=f_table_owner and a.table_name=f_table_name
and a.column_name=f_column_name and b.owner=a.owner
and b.table_name=a.table_name and b.column_name=a.column_name;
if f_data_type<>'BLOB' then
dbms_output.put_line('ERROR: Column '||f_full_col_name||' is '||f_data_type||', not BLOB');
return;
end if;
exception
when others then
if SQLCODE=100 then
dbms_output.put_line('ERROR: BLOB column '||f_full_col_name||') not found!');
return;
else
raise;
end if;
end;
if f_securefile='YES' then
-- Check space usage of SECUREFILE segment
begin
dbms_space.space_usage( segment_owner =>f_table_owner,
segment_name => f_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);
l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);
l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
l_non_data_bytes := l_unused_bytes + l_expired_bytes + l_unexpired_bytes;
dbms_output.put_line('');
dbms_output.put_line('SECUREFILE BLOB column ['||f_full_col_name||']');
dbms_output.put_line('=======================================================');
dbms_output.put_line(' segment name '||f_segment_name);
dbms_output.put_line(' ------------------------------------------------');
dbms_output.put_line(' segment blocks/bytes = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
dbms_output.put_line(' unused blocks/bytes = '||l_unused_blocks||' / '||l_unused_bytes);
dbms_output.put_line(' used blocks/bytes = '||l_used_blocks||' / '||l_used_bytes);
dbms_output.put_line(' expired blocks/bytes = '||l_expired_blocks||' / '||l_expired_bytes);
dbms_output.put_line(' unexpired blocks/bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
dbms_output.put_line(' ------------------------------------------------');
dbms_output.put_line(' non data blocks/bytes = '||l_non_data_blocks||' / '||l_non_data_bytes);
end;
else
-- Check space usage of BASICFILE segment
begin
select sum(blocks),sum(bytes)
into l_segment_size_blocks,l_segment_size_bytes
from dba_extents
where segment_name=f_segment_name;
execute immediate f_sql into l_used_bytes;
l_unused_bytes := l_segment_size_bytes - l_used_bytes;
dbms_output.put_line('');
dbms_output.put_line('BASICFILE BLOB column ['||f_full_col_name||']');
dbms_output.put_line('=======================================================');
dbms_output.put_line(' segment name '||f_segment_name);
dbms_output.put_line(' ------------------------------------------------');
dbms_output.put_line(' segment blocks/bytes = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
dbms_output.put_line(' lob data using bytes = '||l_used_bytes);
dbms_output.put_line(' non lob data bytes = '||l_unused_bytes);
dbms_output.put_line(' ------------------------------------------------');
dbms_output.put_line(' The segment can be shrinked/reorganized if following SQL return records');
dbms_output.put_line(' ');
dbms_output.put_line(' select bytes, count(*)');
dbms_output.put_line(' from dba_extents');
dbms_output.put_line(' where owner = '''||f_table_owner||'''');
dbms_output.put_line(' and segment_name = '''||f_segment_name||'''');
dbms_output.put_line(' and bytes < '||l_unused_bytes);
dbms_output.put_line(' group by bytes order by 2;');
end;
end if;
end;
/
undefine 1
undefine 2
undefine 3
No comments:
Post a Comment