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的大小（包含空塊及塊頭等資訊）
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dba_tables 和 dba_segments 表中 blocks 的區別BloC
- Find and Fix the Mismatch Between DBA_SEGMENTS and DBA_EXTENTS ViewsView
- dba_tables，dba_all_tables,user_tables,all_tables有什麼區別
- Dba_segments詳解
- 查詢DBA_SEGMENTS，bytes＝0
- dba_segments之segment_type='TEMPORARY'
- 從dba_extents返回結果為空 - Oracle10gOracle
- avg_space of dba_tables是如何被計算出來的！
- 和區別
- #include ＜＞ ““區別
- lamplnmplnamp區別LAMPLNMP
- 在dba_tables 這個資料字典查不到某個表
- __weak與__block區別，深層理解兩者區別BloC
- MyISAM InnoDB 區別
- ??與？：的區別
- save() create()區別
- 蜂蜜的區別
- if …if 和if …else if 區別
- 和 的區別
- ojdbc版本區別JDBC
- as 和 with的區別
- &與&&, |與||區別
- UNDO REDO 區別
- GPL、GA區別
- 歸檔區別
- in 和 exists區別
- django版本區別Django
- ||和??的區別
- /*和/**的區別
- dba_tables中的avg_row_len是如何被計算的？
- 欄位avg_row_len of dba_tables是如何被計算的！
- Ora-600 [15160] Joining Dba_objects and Dba_segmentsObject
- LinkedList和ArrayList的區別、Vector和ArrayList的區別
- http和https的區別/get和post的區別HTTP
- js基本型別和引用型別區別JS型別
- 值型別與引用型別的區別型別
- JAVA 基本型別與 引用型別區別Java型別
- UIModalPresentationStyle 各種型別的區別UI型別