ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (关于表实际大小)

oracle实验记录 (关于表实际大小)

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-07 14:17:35 0 删除 编辑

查询表实际大小
SQL> show user;
USER is "XH"
SQL> create table test (a int);

Table created.

SQL> ed
Wrote file afiedt.buf

  1   declare
  2    begin
  3    for i in 1..10000 loop
  4   insert into test values(i);
  5   end loop;
  6*   end;
SQL> /

PL/SQL procedure successfully completed.


QL> col  owner format a10
QL> select owner,extent_id,file_id,block_id ,blocks,bytes/1024/1024 from dba_ex
ents where segment_name='TEST';

WNER       EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS BYTES/1024/1024
--------- ---------- ---------- ---------- ---------- ---------------
H                  0          4        449          8           .0625
H                  1          4        457          8           .0625
H                  2          4        473          8           .0625

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,blocks,empty_blocks from dba_tables where table_name='TEST';

OWNER          BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
XH

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

SQL> execute dbms_stats.gather_table_stats('SYS','TEST');

PL/SQL procedure successfully completed.

SQL> select blocks,empty_blocks from user_tables where table_name='TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
        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    begin
 10    dbms_space.unused_space('XH','TEST','TABLE',a,b,c,d,e,f,g);
 11    dbms_output.put_line(a|| ',' ||b||','||c||','||d||','||e||','||f||','||g)
;
 12*   end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
24,196608,0,0,4,472,8

PL/SQL procedure successfully completed.
a总块数,B 总块数大小,C未用块数,D未用块数大小,E包含数据最后一个区的文件编号,F 包含数据最后一个区的第一个块号,G包含数据的最后一个区的最后一个块

  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|
|'
 17  ,'||h||','||i||','||j||','||k||','||l);
 18*  end;
SQL> /
0,0,0,0,0,0,0
,0,5,40960,15,122880

PL/SQL procedure successfully completed.


  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.

 

  1        declare
  2        a number;
  3        b number;
  4       c number;
  5       d number;
  6       e number;
  7       f number;
  8      g number;
  9    begin
 10      dbms_space.unused_space('XH','TEST','TABLE',a,b,c,d,e,f,g);
 11      dbms_output.put_line(a|| ',' ||b||','||c||','||d||','||e||','||f||','||
g)
 12  ;
 13*   end;
SQL> /
24,196608,0,0,4,472,8~~~~~~~~~~~~~~~~没变化

PL/SQL procedure successfully completed.

SQL>
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

SQL> select owner,bytes/1024/1024 ,blocks  from dba_segments where segment_name=
'TEST';

OWNER      BYTES/1024/1024     BLOCKS
---------- --------------- ----------
XH                   .1875         24

SQL>

所以dba_extents,user_tables,dba_segments, 并不能实际反映出表大小,他们只反映出HWM下的~~内容~~delete HWM 不会变

SQL> show user
USER is "XH"
SQL> alter table test move;

Table altered.

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        481          8           .0625

SQL> select owner,blocks,empty_blocks from dba_tables where table_name='TEST';

OWNER          BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
XH

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                  0            0

SQL> select owner,bytes/1024/1024 ,blocks  from dba_segments where segment_name=
'TEST';

OWNER      BYTES/1024/1024     BLOCKS
---------- --------------- ----------
XH                   .0625          8


  1          declare
  2         a number;
  3           b number;
  4        c number;
  5          d number;
  6        e number;
  7        f number;
  8       g number;
  9       begin
 10        dbms_space.unused_space('XH','TEST','TABLE',a,b,c,d,e,f,g);
 11       dbms_output.put_line(a|| ',' ||b||','||c||','||d||','||e||','||f||','|
|g)
 12    ;
 13*   end;
 14  /
8,65536,5,40960,4,480,3~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PL/SQL procedure successfully completed.

 

  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> /
0,0,0,0,0,0,0,0,0,0,0,0

PL/SQL procedure successfully completed.


  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('SYS','T1','TABLE',a,b,c,d,e,f,g,h,i,j,k,l);~~~~~~~~~~~~~~~对sys不行
 16      dbms_output.put_line(a|| ',' ||b||','||c||','||d||','||e||','||f||','|
g||','||h||','||i||','||j||','||k||','||l);
 17*   end;
SQL> /
   declare
*
ERROR at line 1:
ORA-10614: Operation not allowed on this segment***************************
ORA-06512: at "SYS.DBMS_SPACE", line 159
ORA-06512: at line 15

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-611574/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426901