oracle 物理表,oracle表的物理大小

select bytes from dba_extents where segment_name = 'XXX';

select bytes from dba_segments where segment_name = 'XXX';

SELECT BYTES FROM DBA_EXTENTS WHERE SEGMENT_NAME='表名';

注意：表名要大写

analyze table test estimate statistics;

select owner,table_name,num_rows,blocks*$db_block_size/1024/1024 "size M" from dba_tables where table_name='TEST';

$db_block_size:数据库块大小

此处记录的blocks需要表被分析后才准确，是已经使用的空间，

另和dba_segments和dba_extents里记录的大小不一样，这里的block是分配给该表的所有extents所包含的block，包括最后一个extents里未使用的block，所以这个值比dba_tables 里的要稍微大一些block。

用TOM提供的show_space函数包最方便，而且直观。 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; 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.free_blocks ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, freelist_group_id => 0, free_blks => l_free_blks ); 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( 'Free Blocks', l_free_blks ); 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; / 例如： SQL> set serveroutput on SQL> execute show_space('AAA'); Free Blocks.............................0 Total Blocks............................128 Total Bytes.............................1048576 Unused Blocks...........................127 Unused Bytes............................1040384 Last Used Ext FileId....................21 Last Used Ext BlockId...................9 Last Used Block.........................1