Use Oracle script to check object space usage

Tom has a very good script show_space.sql for viewing object space usage, as described below.

The introduction here is relatively simple, the following demo is actually for the table space information, as for the object, I will rewrite below.

The following example shows the effect of delete and truncate on high water levels, with script Space_usage.sql.

[Oracle@maa3 ~]$ Cat Space_usage.sql

--Space_usage.sql

Declare

L_tabname varchar2: = ' &1 ';

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;

Begin

Dbms_space.space_usage (

Segment_owner => User,

Segment_name => L_tabname,

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.put_line (' 0-25% free = ' | | l_fs1_blocks| | ' Bytes = ' | | L_fs1_bytes);

Dbms_output.put_line (' 25-50% free = ' | | l_fs2_blocks| | ' Bytes = ' | | L_fs2_bytes);

Dbms_output.put_line (' 50-75% free = ' | | l_fs3_blocks| | ' Bytes = ' | | L_fs3_bytes);

Dbms_output.put_line (' 75-100% free = ' | | l_fs4_blocks| | ' Bytes = ' | | L_fs4_bytes);

Dbms_output.put_line (' Full Blocks = ' | | l_full_blocks| | ' Bytes = ' | | L_full_bytes);

End

/