Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Oracle how to view the use of object space", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Oracle how to view the use of object space" this article.

-tool source code

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_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; 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 BEGIN dbms_output.put_line (rpad (p_label, 40,'.) | | to_char (p_num, '999999999999'); END BEGIN EXECUTE 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' INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner -exception-- when too_many_rows-- then-- dbms_output.put_line ('This must be a partitioned table,use p_partition = >');-- return;-- end IF l_segment_space_mgmt = 'AUTO' THEN dbms_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) ELSE 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); 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 = > l_last_used_block) P ('Total Blocks', l_total_blocks); p (' Total Bytes', l_total_bytes); p ('Total MBytes', trunc (l_total_bytes / 1024 / 1024)); 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;12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182

-usage demonstration

Create table test_spaceASselect * from dba_tables;/1234SYS@orcl > exec show_space ('TEST_SPACE'); 1

Total Blocks... one hundred and twelve

Total Bytes... 917504

Total MBytes... 0

Unused Blocks... three

Unused Bytes... 24576

Last Used Ext FileId... .. one

Last Used Ext BlockId... . 115608

Last Used Block... . five

The PL/SQL process completed successfully.

SYS@orcl > delete from test_space;1

3044 lines have been deleted.

SYS@orcl > commit;1

The submission is complete.

SYS@orcl > exec show_space ('TEST_SPACE'); 1

Total Blocks... one hundred and twelve

Total Bytes... 917504

Total MBytes... 0

Unused Blocks... three

Unused Bytes... 24576

Last Used Ext FileId... .. one

Last Used Ext BlockId... . 115608

Last Used Block... . five

The PL/SQL process completed successfully.

SYS@orcl > truncate table test_space;1

The watch is truncated.

SYS@orcl > exec show_space ('TEST_SPACE')

Total Blocks... eight

Total Bytes... 65536

Total MBytes... 0

Unused Blocks... seven

Unused Bytes... 57344

Last Used Ext FileId... .. one

Last Used Ext BlockId... . 101872

Last Used Block... . one

The PL/SQL process completed successfully.

Drop table test_space; above is all the content of the article "how to check the usage of object space by Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.