What is the difference between dba_segments, dba_extents and dba_tables
This article shows you the difference between dba_segments, dba_extents and dba_tables. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.
SQL > conn scott/tiger
Connected.
SQL > create table an 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.
-- query View 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 768008192 from dual
768008192
-
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
Query dba_segments view records the total size of segment (including empty block header information, etc., see dba_tables view below), and HEADER_FILE (absolute file number), HEADER_BLOCK (block number), RELATIVE_FNO (relative file number)
-- query View dba_extents
SQL > select SEGMENT_NAME,sum (BYTES) / 1024 plus 1024 from DBA_EXTENTS where SEGMENT_NAME='A' and owner='SCOTT' group by SEGMENT_NAME
SEGMENT_NAME SUM (BYTES) / 1024 Compact 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 24 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.
-- query View 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 from dual
522252 0101
-
52747452
SQL > select 62914560 from dual 52747452
62914560/52747452
-
1.19275069
There are 100 empty blocks in the queried dba_tabales table. The value of NUMskills ROWSAVGROWROWLEN is the actual data occupancy size, and the size of the whole table is approximately equal to (NUM_ROWS*AVG_ROW_LEN) * 1.19, and the size of the segment (including empty blocks and block heads and other information).
What is the difference between dba_segments, dba_extents and dba_tables? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.
