Oracle View object Space usage Show_space

Tom the Great God wrote a to view the use of Oracle database object space.
The following shows how to use:

– Tool Source

CREATE ORREPLACEprocedure  show_space                                         (p_segname in  VARCHAR2,                                       P_owner in  VARCHAR2 default  USER, P_type in  VARCHAR2 defaul T   "TABLE" , p_partition in  VARCHAR2 default  NULL)  as           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; L_segment_space_mgmt VARCHAR2 (255);  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; PROCEDURE P(P_label  in VARCHAR2, P_num  in number) is
      BEGINdbms_output. Put_Line                         (Rpad (P_label, Max, '. ') | | To_char (P_num, ' 999,999,999,999 '));        END;BEGINEXECUTE IMMEDIATE' Select ts.segment_space_management from dba_segments seg,dba_tablespaces ts where seg.segment_name =:p _segname and (:p _partition is null or seg.partition_name =:p _partition) and Seg.owner =:p _owner and Seg.tablespace_name=ts.tablespace _name '     intoL_segment_space_mgmtUSINGP_segname, P_partition, P_partition, P_owner; --Exception--When Too_many_rows-- Then--Dbms_output.put_line (' This must is a partitioned table,use p_partition = ');  --return; --End;IFL_SEGMENT_SPACE_MGMT =' AUTO '  ThenDbms_space.space_usage (P_owner, P_segname, P_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_partition); 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 = p_owner, segment_name = p_segname, Segment_type = > p_type, freelist_group_id =0, free_blks = l_free_blks);END IF; 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 =&gt ;  L_last_used_block); P' Total Blocks ', l_total_blocks); P' Total Bytes ', l_total_bytes); P' Total MBytes ', Trunc (L_total_bytes/1024x768/1024x768)); 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;

– Usage Demo

create table test_spaceASselect * from dba_tables;/

SYS@orcl> exec show_space(‘TEST_SPACE‘);

Total Blocks ......... ........... 112
Total Bytes ......... ........... 917,504
Total MBytes ......... ........... 0
Unused Blocks ......... ........... 3
Unused Bytes ......... ........... 24,576
Last used Ext FileId ......... ..... 1
Last used Ext blockid ......... ..... 115,608
Last used Block ......... .......... 5

The PL/SQL process has completed successfully.

SYS@orcl> delete from test_space;

3044 rows have been deleted.

SYS@orcl> commit;

Submit complete.

SYS@orcl> exec show_space(‘TEST_SPACE‘);

Total Blocks ......... ........... 112
Total Bytes ......... ........... 917,504
Total MBytes ......... ........... 0
Unused Blocks ......... ........... 3
Unused Bytes ......... ........... 24,576
Last used Ext FileId ......... ..... 1
Last used Ext blockid ......... ..... 115,608
Last used Block ......... .......... 5

The PL/SQL process has completed successfully.

SYS@orcl> truncate table test_space;

The table is truncated.

[Email protected]> exec show_space (' test_space ');
Total Blocks ......... ........... 8
Total Bytes ......... ........... 65,536
Total MBytes ......... ........... 0
Unused Blocks ......... ........... 7
Unused Bytes ......... ........... 57,344
Last used Ext FileId ......... ..... 1
Last used Ext blockid ......... ..... 101,872
Last used Block ......... .......... 1

The PL/SQL process has completed successfully.

drop table test_space;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle View object Space usage Show_space