Tom wrote a good tool show_space. This tool is actually a storage process. This stored procedure can be used to analyze space usage. With this tool, you don't have to look at each record or table by writing SQL statements. It is very convenient to take up the size of the table.
The specific use process 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 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 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_fi le_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 to perform the above statements A Procedure will be generated under the current user, and the current user is a SYS user.
SQL> CREATE TABLE T AS SELECT * from ALL_USERS; (Create Table T)
SQL> EXEC SHOW_SPACE ('t'); (View Table T Table Space Size) Free Blocks ........................... 0Total Blocks ............................ 15total Bytes ................... ........ 61440UNUSED blocks ................................................................................................. ................. 53248Last Used Ext FileID ............................................................................................................... ................ 61782last Used block ....................... 2 results come out soon It used to query DBA_TABLES before you can get the result, visible, this tool is convenient.
In addition, this tool has several versions. At present, the above version is only suitable for the table space as non-ASSM, because DBMS_SPACE.FREE_BLOCKS is not allowed to operate on ASSM, the solution is as follows:
For ASSM, may be used dbms_space.space_usage, may be added in this section in show_space: select ts.segment_space_managementinto t_segment_space_managementfrom dba_segments seg, dba_tablespaces tswhere seg.segment_name = t_segnameand seg.owner = t_ownerand seg.tablespace_name = ts.tablespace_name; - if t_segment_space_management = 'AUTO'thendbms_space.space_usage (t_owner, t_segname, t_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_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); elsedbms_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); endiff;
A lot of versions of this tool are available on ITPUB, and there are also the following versions!
xzh2000 provides final mixing super completely invincible version: 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); 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 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 change p_owner: = Upper (p_owner_1); p_type: = p_type_1; if (p_ TYPE_1 = 'I' or ') 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 ('25% - 50% free space block, l_fs2_blocks);
P ('25% - 50% free space bytes ', l_fs2_bytes); p ('50% - 75% free space block, l_fs3_blocks); p ('50% - 75% free space bytes', l_fs3_bytes) P ('75% - 100% free space blocks', l_fs4_blocks); p ('75% - 100% 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 Table SQL> EXEC SHOW_SPACE (' T ',' Auto '); Total Blocks ............................ 512 Total Bytes ................... ........ 4194304 unused blocks ..................................................... ..................... 638976 Last Used EXT FILEID .................... 9 Last Used EXT Blockid ................. 25608 Last Used block ....................... 50 PL / SQL Procedure SuccessFully Completed. ASSM Type Index SQL> EXEC SHOW_SPACE ('T_index', 'Auto', 'I'); Total Blocks ................... ......... 80 Total Bytes ............................ 655360 Unused blocks ...... ..................... 5 Un Used bytes .............................. 40960 Last Used Ext FileId ............... ..... 9 Last Used Ext Blockid ................. 25312 Last Used block ................. ........ 3 PL / SQL Procedure SuccessFully Completed. Segment for Analyze can be such SQL> EXEC SHOW_SPACE ('T', 'Auto', 'T', 'Y');
