这篇文章主要为大家展示了“Oracle如何查看对象空间使用情况”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Oracle如何查看对象空间使用情况”这篇文章吧。

–工具源码

CREATEORREPLACEPROCEDUREshow_space(p_segnameINVARCHAR2,p_ownerINVARCHAR2DEFAULTUSER,p_typeINVARCHAR2DEFAULT'TABLE',p_partitionINVARCHAR2DEFAULTNULL)ASl_free_blksNUMBER;l_total_blocksNUMBER;l_total_bytesNUMBER;l_unused_blocksNUMBER;l_unused_bytesNUMBER;l_lastusedextfileidNUMBER;l_lastusedextblockidNUMBER;l_last_used_blockNUMBER;l_segment_space_mgmtVARCHAR2(255);l_unformatted_blocksNUMBER;l_unformatted_bytesNUMBER;l_fs1_blocksNUMBER;l_fs1_bytesNUMBER;l_fs2_blocksNUMBER;l_fs2_bytesNUMBER;l_fs3_blocksNUMBER;l_fs3_bytesNUMBER;l_fs4_blocksNUMBER;l_fs4_bytesNUMBER;l_full_blocksNUMBER;l_full_bytesNUMBER;PROCEDUREp(p_labelINVARCHAR2,p_numINNUMBER)ISBEGINdbms_output.put_line(rpad(p_label,40,'.')||to_char(p_num,'999,999,999,999'));END;BEGINEXECUTEIMMEDIATE'selectts.segment_space_managementfromdba_segmentsseg,dba_tablespacestswhereseg.segment_name=:p_segnameand(:p_partitionisnullorseg.partition_name=:p_partition)andseg.owner=:p_ownerandseg.tablespace_name=ts.tablespace_name'INTOl_segment_space_mgmtUSINGp_segname,p_partition,p_partition,p_owner;--exception--whentoo_many_rows--then--dbms_output.put_line('Thismustbeapartitionedtable,usep_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('UnformattedBlocks',l_unformatted_blocks);p('FS1Blocks(0-25)',l_fs1_blocks);p('FS2Blocks(25-50)',l_fs2_blocks);p('FS3Blocks(50-75)',l_fs3_blocks);p('FS4Blocks(75-100)',l_fs4_blocks);p('FullBlocks',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);ENDIF;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('TotalBlocks',l_total_blocks);p('TotalBytes',l_total_bytes);p('TotalMBytes',trunc(l_total_bytes/1024/1024));p('UnusedBlocks',l_unused_blocks);p('UnusedBytes',l_unused_bytes);p('LastUsedExtFileId',l_lastusedextfileid);p('LastUsedExtBlockId',l_lastusedextblockid);p('LastUsedBlock',l_last_used_block);END;12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182

–用法演示

createtabletest_spaceASselect*fromdba_tables;/1234

SYS@orcl>execshow_space('TEST_SPACE');1

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

PL/SQL 过程已成功完成。

SYS@orcl>deletefromtest_space;1

已删除3044行。

SYS@orcl>commit;1

提交完成。

SYS@orcl>execshow_space('TEST_SPACE');1

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

PL/SQL 过程已成功完成。

SYS@orcl>truncatetabletest_space;1

表被截断。

SYS@orcl> 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

PL/SQL 过程已成功完成。

droptabletest_space;

以上是“Oracle如何查看对象空间使用情况”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！