Usage of the show_space () Tool

Tom wrote a good tool show_space, which is actually a stored procedure for Oracle. This stored procedure can be used to analyze space usage. With this tool, you no longer need to write SQL statements to view the size of each record or table's tablespace, which is convenient to use.

The procedure is as follows:

First, you need to create a stored procedure:

D:/> sqlplus/nolog

SQL> connect/As sysdba

SQL> Create or replace procedure show_space

(P_segname in varchar2,

P_owner in varchar2 default user,

P_type in varchar2 default 'table ',

P_partition in varchar2 default null)

As

Rochelle total_blocks number;

Rochelle total_bytes number;

Rochelle unused_blocks number;

Rochelle unused_bytes number;

Rochelle lastusedextfileid number;

Rochelle lastusedextblockid number;

Rochelle last_used_block number;

Procedure P (p_label in varchar2, p_num in number)

Is

Begin

Dbms_output.put_line (rpad (p_label, 40, '.') |

P_num );

End;

Begin

Dbms_space.unused_space

(Segment_owner => p_owner,

Segment_name => p_segname,

Segment_type => p_type,

Partition_name => p_partition,

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 );

P ('total blocks ', l_total_blocks );

P ('total bytes ', l_total_bytes );

P ('unused blocks ', l_unused_blocks );

P ('unused bytes ', l_unused_bytes );

P ('Last used ext fileid', l_lastusedextfileid );

P ('Last used ext blockid', l_lastusedextblockid );

P ('Last used Block', l_last_used_block );

End;
/
Procedure created.

Execute the preceding statement to generate a procedure under the current user. The current user is the Sys user.

SQL> Create Table T as select * From all_users; (CREATE TABLE T)

SQL> exec show_space ('T'); (view the space occupied by table t)
Free blocks ......
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 61782
Last used block...

The result is immediately displayed. You must query dba_tables using an SQL statement to obtain the result. It can be seen that this tool is convenient.

In addition, this tool has several versions. Currently, this version is only applicable when the tablespace is not assm, and assm cannot be used because dbms_space.free_blocks is not allowed to operate on assm, the solution is as follows:

For assm, you can use dbms_space.space_usage. You can add this segment to show_space:

Select ts. segment_space_management
Into t_segment_space_management
From dba_segments seg
, Dba_tablespaces TS
Where Seg. segment_name = t_segname
And Seg. Owner = t_owner
And Seg. tablespace_name = ts. tablespace_name
;
--
If t_segment_space_management = 'auto'
Then
Dbms_space.space_usage (
T_owner,
T_segname,
T_type,
Rochelle unformatted_blocks,
L_unformatted_bytes,
Rochelle fs1_blocks, Rochelle fs1_bytes,
Rochelle fs2_blocks, Rochelle fs2_bytes,
Rochelle fs3_blocks, Rochelle fs3_bytes,
Rochelle fs4_blocks, Rochelle fs4_bytes,
Rochelle full_blocks, Rochelle full_bytes
);
--
P ('unformatted blocks ', l_unformatted_blocks );
P ('fs1 blocks (0-25) ', l_fs1_blocks );
P ('fs2 blocks (25-50) ', l_fs2_blocks );
P ('fs3 blocks (50-75) ', l_fs3_blocks );
P ('fs4 blocks (75-100) ', l_fs4_blocks );
P ('full blocks ', l_full_blocks );
Else
Dbms_space.free_blocks (
Segment_owner => t_owner,
Segment_name => t_segname,
Segment_type => t_type,
Freelist_group_id => 0,
Free_blks => l_free_blks
);
--
P ('free blocks ', l_free_blks );
End if;

Many versions of the tool are provided on itpub. The following versions are available!

The ultimate hybrid Super invincible version provided by xzh2000:

Create or replace procedure show_space
(P_segname_1 in varchar2,
P_space in varchar2 default 'manual ',
P_type_1 in varchar2 default 'table ',
P_analyzed in varchar2 default 'n ',
P_owner_1 in varchar2 Default User)
As
P_segname varchar2 (100 );
P_type varchar2 (10 );
P_owner varchar2 (30 );

Rochelle unformatted_blocks number;
Rochelle unformatted_bytes number;
Rochelle fs1_blocks number;
Rochelle fs1_bytes number;
Rochelle fs2_blocks number;
Rochelle fs2_bytes number;
Rochelle fs3_blocks number;
Rochelle fs3_bytes number;
Rochelle fs4_blocks number;
Rochelle fs4_bytes number;
Rochelle Blocks number;
Rochelle bytes number;

Rochelle free_blks number;
Rochelle total_blocks number;
Rochelle total_bytes number;
Rochelle unused_blocks number;
Rochelle unused_bytes number;
Rochelle lastusedextfileid number;
Rochelle lastusedextblockid number;
Rochelle last_used_block number;

Procedure P (p_label in varchar2, p_num in number)
Is
Begin
Dbms_output.put_line (rpad (p_label, 40, '.') |
P_num );
End;
Begin
P_segname: = upper (p_segname_1); -- rainy changed
P_owner: = upper (p_owner_1 );
P_type: = p_type_1;

If (p_type_1 = 'I' or p_type_1 = 'I') then -- rainy changed
P_type: = 'index ';
End if;

If (p_type_1 = 'T' or p_type_1 = 'T') then -- rainy changed
P_type: = 'table ';
End if;

If (p_type_1 = 'C' or p_type_1 = 'C') then -- rainy changed
P_type: = 'cluster ';
End if;

Dbms_space.unused_space
(Segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_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 );

If p_space = 'manual' or (p_space <> 'auto' and p_space <> 'auto') then
Dbms_space.free_blocks
(Segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_type,
Freelist_group_id => 0,
Free_blks => l_free_blks );

P ('free blocks ', l_free_blks );
End if;

P ('total blocks ', l_total_blocks );
P ('total bytes ', l_total_bytes );
P ('unused blocks ', l_unused_blocks );
P ('unused bytes ', l_unused_bytes );
P ('Last used ext fileid', l_lastusedextfileid );
P ('Last used ext blockid', l_lastusedextblockid );
P ('Last used Block', l_last_used_block );

/* If the segment is analyzed */
If p_analyzed = 'y' then
Dbms_space.space_usage (segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_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 );
Dbms_output.put_line (rpad ('', 50 ,'*'));
Dbms_output.put_line ('the segment is analyzed ');
P ('0% -- 25% free space blocks ', l_fs1_blocks );
P ('0% -- 25% free space bytes ', l_fs1_bytes );
P ('1970 -- 25% free space blocks ', l_fs2_blocks );
P ('1970 -- 25% free space bytes ', l_fs2_bytes );
P ('1970 -- 50% free space blocks ', l_fs3_blocks );
P ('1970 -- 50% free space bytes ', l_fs3_bytes );
P ('1970 -- 75% free space blocks ', l_fs4_blocks );
P ('1970 -- 75% free space bytes ', l_fs4_bytes );
P ('unused blocks ', l_unformatted_blocks );
P ('unused bytes ', l_unformatted_bytes );
P ('total blocks ', l_full_blocks );
P ('total bytes ', l_full_bytes );

End if;

End;

Assm tables

SQL> exec show_space ('T', 'auto ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25608
Last used block...

PL/SQL procedure successfully completed.

Assm-type index

SQL> exec show_space ('t_ Index', 'auto', 'I ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25312
Last used block...

PL/SQL procedure successfully completed.

You can perform the segment operation on analyze.

SQL> exec show_space ('T', 'auto', 't', 'y ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25608
Last used block...
**************************************** *********
The segment is analyzed
0% -- 25% free space blocks ...... 0
0% -- 25% free space bytes ...... 0
25% -- 50% free space blocks ...... 0
25% -- 50% free space bytes ...... 0
50% -- 75% free space blocks ...... 0
50% -- 75% free space bytes ...... 0
75% -- 100% free space blocks ...... 0
75% -- 100% free space bytes ...... 0
Unused blocks ......
Unused bytes...
Total blocks ......
Total Bytes...

PL/SQL procedure successfully completed.