oracle 实际 表大小,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