dba_segments、dba_extents和dba_tables的区别是什么

本篇文章为大家展示了dba_segments、dba_extents和dba_tables的区别是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

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的大小（包含空块及块头等信息）

上述内容就是dba_segments、dba_extents和dba_tables的区别是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。