ITPub博客

dba_segments/dba_extents/dba_tables区别

原创 Oracle 作者:不一样的天空w 时间:2017-12-07 12:30:49 0 删除 编辑
SQL> conn scott/tiger
Connected.
SQL> create table a as select * from dba_objects;

Table created.

SQL> insert into a select * from dba_objects;

87042 rows created.

SQL> insert into a select * from dba_objects;

87042 rows created.

SQL> insert into a select * from dba_objects;

87042 rows created.

SQL> insert into a select * from dba_objects;

87042 rows created.

SQL> insert into a select * from dba_objects;

87042 rows created.

SQL> commit;

Commit complete.


--查询视图dba_segments


SQL> select SEGMENT_NAME,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS,RELATIVE_FNO from dba_segments where SEGMENT_NAME='A' and owner='SCOTT';

SEGMENT_NA TABLESPACE_NAME HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS RELATIVE_FNO
---------- --------------- ----------- ------------ ---------- ---------- ---------- ------------
A          USERS                     4         2234   62914560       7680         75            4

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> select 7680*8192 from dual;

 7680*8192
----------
  62914560

SQL> select SEGMENT_NAME,BYTES/1024/1024 size_m,EXTENTS from dba_segments where SEGMENT_NAME='A' and owner='SCOTT';

SEGMENT_NAME                                                                          SIZE_M    EXTENTS
--------------------------------------------------------------------------------- ---------- ----------
A                                                                                         60         75

查询dba_segments视图记录着segment的总大小(包含空块块头信息等,见下面dba_tables视图),及HEADER_FILE(绝对文件号),HEADER_BLOCK(块号),RELATIVE_FNO(相对文件号)



--查询视图dba_extents
SQL> select SEGMENT_NAME,sum(BYTES)/1024/1024 from DBA_EXTENTS where SEGMENT_NAME='A' and owner='SCOTT' group by  SEGMENT_NAME;

SEGMENT_NAME                                                                      SUM(BYTES)/1024/1024
--------------------------------------------------------------------------------- --------------------
A                                                                                                   60

SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from DBA_EXTENTS where SEGMENT_NAME='A' and owner='SCOTT';

SEGMENT_NAME                                                                       EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
A                                                                                          0          4       2232      65536          8
A                                                                                          1          4       2240      65536          8
A                                                                                          2          4       2248      65536          8
A                                                                                          3          4       2256      65536          8
A                                                                                          4          4       2264      65536          8
A                                                                                          5          4       2272      65536          8
A                                                                                          6          4       2280      65536          8
A                                                                                          7          4       2288      65536          8
A                                                                                          8          4       2296      65536          8
A                                                                                          9          4       2688      65536          8
A                                                                                         10          4       2696      65536          8
A                                                                                         11          4       2704      65536          8
A                                                                                         12          4       2712      65536          8
A                                                                                         13          4       2720      65536          8
A                                                                                         14          4       2728      65536          8
A                                                                                         15          4       2736      65536          8
A                                                                                         16          4       2816    1048576        128
A                                                                                         17          4       2944    1048576        128
A                                                                                         18          4       3072    1048576        128
A                                                                                         19          4       3200    1048576        128
A                                                                                         20          4       3328    1048576        128
A                                                                                         21          4       3456    1048576        128
A                                                                                         22          4       3584    1048576        128
A                                                                                         23          4       3712    1048576        128
A                                                                                         24          4       3840    1048576        128
A                                                                                         25          4       3968    1048576        128
A                                                                                         26          4       4096    1048576        128
A                                                                                         27          4     102528    1048576        128
A                                                                                         28          4     102656    1048576        128
A                                                                                         29          4     102784    1048576        128
A                                                                                         30          4     102912    1048576        128
A                                                                                         31          4     103040    1048576        128
A                                                                                         32          4     103168    1048576        128
A                                                                                         33          4     103296    1048576        128
A                                                                                         34          4     103424    1048576        128
A                                                                                         35          4     103552    1048576        128
A                                                                                         36          4     103680    1048576        128
A                                                                                         37          4     103808    1048576        128
A                                                                                         38          4     103936    1048576        128
A                                                                                         39          4     104064    1048576        128
A                                                                                         40          4     104192    1048576        128
A                                                                                         41          4     104320    1048576        128
A                                                                                         42          4     104448    1048576        128
A                                                                                         43          4     104576    1048576        128
A                                                                                         44          4     104704    1048576        128
A                                                                                         45          4     104832    1048576        128
A                                                                                         46          4     104960    1048576        128
A                                                                                         47          4     105088    1048576        128
A                                                                                         48          4     105216    1048576        128
A                                                                                         49          4     105344    1048576        128
A                                                                                         50          4     105472    1048576        128
A                                                                                         51          4     105600    1048576        128
A                                                                                         52          4     105728    1048576        128
A                                                                                         53          4     105856    1048576        128
A                                                                                         54          4     105984    1048576        128
A                                                                                         55          4     106112    1048576        128
A                                                                                         56          4     106240    1048576        128
A                                                                                         57          4     106368    1048576        128
A                                                                                         58          4     106496    1048576        128
A                                                                                         59          4     106624    1048576        128
A                                                                                         60          4     106752    1048576        128
A                                                                                         61          4     106880    1048576        128
A                                                                                         62          4     107008    1048576        128
A                                                                                         63          4     107136    1048576        128
A                                                                                         64          4     107264    1048576        128
A                                                                                         65          4     107392    1048576        128
A                                                                                         66          4     107520    1048576        128
A                                                                                         67          4     107648    1048576        128
A                                                                                         68          4     107776    1048576        128
A                                                                                         69          4     107904    1048576        128
A                                                                                         70          4     108032    1048576        128
A                                                                                         71          4     108160    1048576        128
A                                                                                         72          4     108288    1048576        128
A                                                                                         73          4     108416    1048576        128
A                                                                                         74          4     108544    1048576        128

75 rows selected.


--查询视图dba_tables
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='A' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- -----------
A

SQL> analyze table scott.a compute statistics;

Table analyzed.

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='A' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- -----------
A                                  522252       7580          100          0         101

SQL> select 522252*101 from dual;

522252*101
----------
  52747452

SQL> select 62914560/52747452 from dual;

62914560/52747452
-----------------
       1.19275069

查询的dba_tabales表的空块有100,NUM_ROWS*AVG_ROW_LEN的值是实际的数据占用大小,整个表的大小约等于(NUM_ROWS*AVG_ROW_LEN)*
1.19,及segment的大小(包含空块及块头等信息

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

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

注册时间:2016-09-22

  • 博文量
    719
  • 访问量
    321505