我觉得 row_num*AVG_ROW_LEN 准确 ORACLE 用它决定HASH TABLE
show space看过一篇 脚本 用的是DBMS_SPACE 包
具体使用 下面是以前的实验
1 declare
2 a number;
3 b number;
4 c number;
5 d number;
6 e number;
7 f number;
8 g number;
9 h number;
10 i number;
11 j number;
12 k number;
13 l number;
14 begin
15 dbms_space.space_usage('XH','TEST','TABLE',a,b,c,d,e,f,g,h,i,j,k,l);
16 dbms_output.put_line(a|| ',' ||b||','||c||','||d||','||e||','||f||','||
g||','||h||','||i||','||j||','||k||','||l);
17* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
0,0,0,0,0,0,0,0,5,40960,15,122880
PL/SQL procedure successfully completed.
a 返回未格式化块数,B返回空闲空间在0~25%之间块数,C 返回 空闲空间0-25%之间字节数,D是 空闲空间在25-50%之间块数,E是空闲空间在25-50%之间字节数 ,以次类推 例 I 返回空闲空间 75-100%之间块数 为5块,J 40960返回 空闲空间 在75-100%之间字节数
K 返回满块数 15个 ,L 返回满块总字节数
SQL> select 15*8192 from dual
2 ;
15*8192
----------
122880
SQL> delete from test;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> select owner,bytes/1024/1024 ,blocks from dba_segments where segment_name=
'TEST';
OWNER BYTES/1024/1024 BLOCKS
---------- --------------- ----------
XH .1875 24
SQL> select owner,extent_id,file_id,block_id ,blocks,bytes/1024/1024 from dba_ex
tents where segment_name='TEST';
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES/1024/1024
---------- ---------- ---------- ---------- ---------- ---------------
XH 0 4 449 8 .0625
XH 1 4 457 8 .0625
XH 2 4 473 8 .0625
SQL> select owner,blocks,empty_blocks from dba_tables where table_name='TEST';
OWNER BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
XH 20 0~~~~~~~~~~~~~~
SQL> execute dbms_stats.gather_table_stats('XH','TEST');
PL/SQL procedure successfully completed.
SQL> select owner,blocks,empty_blocks from dba_tables where table_name='TEST';
OWNER BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
XH 20 0~~~~~~~~~~~~~~~~~~
1 declare
2 a number;
3 b number;
4 c number;
5 d number;
6 e number;
7 f number;
8 g number;
9 h number;
10 i number;
11 j number;
12 k number;
13 l number;
14 begin
15 dbms_space.space_usage('XH','TEST','TABLE',a,b,c,d,e,f,g,h,i,j,k,l);
16 dbms_output.put_line(a||','||b||','||c||','||d||','||e||','||f||','||g|
|','||h||','||i||','||j||','||k||','||l);
17* end;
18 /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
0,0,0,0,0,0,0,0,20,163840,0,0 一共有20个BLOCK,空闲在75%-100%的有20个BLOCK,FULL BLOCKS 0,FULL BLOCKS SIZE 0
PL/SQL procedure successfully completed.
以上是能看到的信息 我觉得是按BLOCK 来算的 FULL BLOCK的标准 我还没有实验~怎么才算FULL BLOCK,PCTFREE是否用了才算FULL BLOCK? 所以我觉得不是很准确