溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了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的區別是什么，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
