Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.