new_show_space.sql.zip
REM
REM based on previous show_space script, now it can
REM identify all partition_name for table or index-
REM automatically without specifying partition_name.
REM
REM Usage:
REM exec show_space('TABLE','OWNER','TABLE_NAME');
REM or
REM exec show_space('TABLE PARTITION','TEST','P_TAB','PART1');
REM
REM exec show_space('INDEX','TEST','IDX_TAB');
REM or
REM exec show_space('INDEX PARTITION','TEST','IDX_TAB','IDX_PART1');
REM
REM Edited by mx at 2020/03/27
REM
-- based on previous procedure show_space from Internet.
set serveroutput on
CREATE
OR REPLACE PROCEDURE show_space (
v_segment_type IN VARCHAR2 DEFAULT 'TABLE',
v_segment_owner IN VARCHAR2 DEFAULT USER,
v_segment_name IN VARCHAR2,
v_partition_name IN VARCHAR2 DEFAULT NULL,
v_space IN VARCHAR2 DEFAULT 'AUTO',
v_analyzed IN VARCHAR2 DEFAULT 'Y'
) AS
p_segment_type VARCHAR2 ( 30 );
p_segment_owner VARCHAR2 ( 30 );
p_segment_name VARCHAR2 ( 50 );
p_partition_name VARCHAR2 ( 30 );
p_partitioned VARCHAR2 ( 5 );
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;
l_free_blks NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_LastUsedExtFileId NUMBER;
l_LastUsedExtBlockId NUMBER;
l_LAST_USED_BLOCK NUMBER;
PROCEDURE print ( p_label IN VARCHAR2, p_num IN NUMBER ) IS BEGIN
dbms_output.put_line ( rpad( p_label, 40, '.' ) || p_num );
END;
PROCEDURE analyze_space (
f_segment_type IN VARCHAR2,
f_segment_owner IN VARCHAR2,
f_segment_name IN VARCHAR2,
f_partition_name IN VARCHAR2
) IS BEGIN
dbms_space.unused_space (
segment_owner => f_segment_owner,
segment_name => f_segment_name,
segment_type => f_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_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK,
partition_name => f_partition_name
);
IF
v_space = 'MANUAL'
OR ( v_space <> 'auto' AND v_space <> 'AUTO' ) THEN
dbms_space.free_blocks (
segment_owner => f_segment_owner,
segment_name => f_segment_name,
segment_type => f_segment_type,
freelist_group_id => 0,
free_blks => l_free_blks,
partition_name => f_partition_name
);
print ( 'Free Blocks', l_free_blks );
END IF;
IF
( f_partition_name IS NULL ) THEN
dbms_output.put_line (
'--' || rpad( f_segment_owner || '.' || f_segment_name, 45, '-' )
);
ELSE dbms_output.put_line (
'--' || rpad(
f_segment_owner || '.' || f_segment_name || '.' || f_partition_name,
45,
'-'
)
);
END IF;
print ( 'Total Blocks', l_total_blocks );
print ( 'Total Bytes', l_total_bytes );
print ( 'Unused Blocks', l_unused_blocks );
print ( 'Unused Bytes', l_unused_bytes );
print ( 'Last Used Ext FileId', l_LastUsedExtFileId );
print ( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
print ( 'Last Used Block', l_LAST_USED_BLOCK );
IF
v_analyzed = 'Y' THEN
dbms_space.space_usage (
segment_owner => f_segment_owner,
segment_name => f_segment_name,
segment_type => f_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 => f_partition_name
);
dbms_output.put_line ( 'The segment is analyzed.' );
print ( '0% -- 25% free space blocks', l_fs1_blocks );
print ( '0% -- 25% free space bytes', l_fs1_bytes );
print ( '25% -- 50% free space blocks', l_fs2_blocks );
print ( '25% -- 50% free space bytes', l_fs2_bytes );
print ( '50% -- 75% free space blocks', l_fs3_blocks );
print ( '50% -- 75% free space bytes', l_fs3_bytes );
print ( '75% -- 100% free space blocks', l_fs4_blocks );
print ( '75% -- 100% free space bytes', l_fs4_bytes );
print ( 'Unused Blocks', l_unformatted_blocks );
print ( 'Unused Bytes', l_unformatted_bytes );
print ( 'Total Blocks', l_full_blocks );
print ( 'Total bytes', l_full_bytes );
dbms_output.put_line ( rpad( ' ', 48, '-' ) );
END IF;
END;
BEGIN
p_segment_name := upper( v_segment_name );
p_segment_owner := upper( v_segment_owner );
p_segment_type := upper( v_segment_type );
p_partition_name := upper( v_partition_name );
IF
( v_segment_type = 'i' OR v_segment_type = 'I' ) THEN
p_segment_type := 'INDEX';
END IF;
IF
( v_segment_type = 't' OR v_segment_type = 'T' ) THEN
p_segment_type := 'TABLE';
END IF;
IF
( v_segment_type = 'c' OR v_segment_type = 'C' ) THEN
p_segment_type := 'CLUSTER';
END IF;
SELECT
partitioned INTO p_partitioned
FROM
(
SELECT
partitioned
FROM
all_tables
WHERE
owner = p_segment_owner
AND table_name = p_segment_name UNION
SELECT
partitioned
FROM
all_indexes
WHERE
owner = p_segment_owner
AND index_name = p_segment_name
);
IF
( p_segment_type = 'TABLE' AND p_partitioned = 'YES' ) THEN
p_segment_type := 'TABLE PARTITION';
FOR t IN (
SELECT
partition_name
FROM
all_tab_partitions
WHERE
table_owner = p_segment_owner
AND table_name = p_segment_name
ORDER BY
to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) )
)
loop
analyze_space ( p_segment_type, p_segment_owner, p_segment_name, t.partition_name );
END loop;
ELSIF ( p_segment_type = 'INDEX' AND p_partitioned = 'YES' ) THEN
p_segment_type := 'INDEX PARTITION';
FOR i IN (
SELECT
partition_name
FROM
all_tab_partitions
WHERE
table_owner = p_segment_owner
AND table_name = p_segment_name
ORDER BY
to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) )
)
loop
analyze_space ( p_segment_type, p_segment_owner, p_segment_name, i.partition_name );
END loop;
ELSE analyze_space ( p_segment_type, p_segment_owner, p_segment_name, p_partition_name );
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line('Usage:');
dbms_output.put_line('- exec show_space(''table'',''owner'',''table_name'');');
dbms_output.put_line('- exec show_space(''table partition'',''owner'',''table_name'',''partition_name'');');
dbms_output.put_line('- exec show_space(''index'',''owner'',''index_name'');');
dbms_output.put_line('- exec show_space(''index partition'',''owner'',''index_name'',''partition_name'');');
END;
/
