oracle實驗記錄 （關於表實際大小）

查詢表實際大小
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/，如需轉載，請註明出處，否則將追究法律責任。