微前端架构如何改变企业的开发模式与效率提升
336
2023-12-13
dba_segments、dba_extents和dba_tables的区别是什么
dba_segments、dba_extents和dba_tables的区别是什么
本篇文章为大家展示了dba_segments、dba_extents和dba_tables的区别是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
SQL> conn scott/tigerConnected.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_segmentsSQL> 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 4SQL> show parameter db_block_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_size integer 8192SQL> select 7680*8192 from dual;7680*8192---------- 62914560SQL> 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_extentsSQL> 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 60SQL> 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 8A 1 4 2240 65536 8A 2 4 2248 65536 8A 3 4 2256 65536 8A 4 4 2264 65536 8A 5 4 2272 65536 8A 6 4 2280 65536 8A 7 4 2288 65536 8A 8 4 2296 65536 8A 9 4 2688 65536 8A 10 4 2696 65536 8A 11 4 2704 65536 8A 12 4 2712 65536 8A 13 4 2720 65536 8A 14 4 2728 65536 8A 15 4 2736 65536 8A 16 4 2816 1048576 128A 17 4 2944 1048576 128A 18 4 3072 1048576 128A 19 4 3200 1048576 128A 20 4 3328 1048576 128A 21 4 3456 1048576 128A 22 4 3584 1048576 128A 23 4 3712 1048576 128A 24 4 3840 1048576 128A 25 4 3968 1048576 128A 26 4 4096 1048576 128A 27 4 102528 1048576 128A 28 4 102656 1048576 128A 29 4 102784 1048576 128A 30 4 102912 1048576 128A 31 4 103040 1048576 128A 32 4 103168 1048576 128A 33 4 103296 1048576 128A 34 4 103424 1048576 128A 35 4 103552 1048576 128A 36 4 103680 1048576 128A 37 4 103808 1048576 128A 38 4 103936 1048576 128A 39 4 104064 1048576 128A 40 4 104192 1048576 128A 41 4 104320 1048576 128A 42 4 104448 1048576 128A 43 4 104576 1048576 128A 44 4 104704 1048576 128A 45 4 104832 1048576 128A 46 4 104960 1048576 128A 47 4 105088 1048576 128A 48 4 105216 1048576 128A 49 4 105344 1048576 128A 50 4 105472 1048576 128A 51 4 105600 1048576 128A 52 4 105728 1048576 128A 53 4 105856 1048576 128A 54 4 105984 1048576 128A 55 4 106112 1048576 128A 56 4 106240 1048576 128A 57 4 106368 1048576 128A 58 4 106496 1048576 128A 59 4 106624 1048576 128A 60 4 106752 1048576 128A 61 4 106880 1048576 128A 62 4 107008 1048576 128A 63 4 107136 1048576 128A 64 4 107264 1048576 128A 65 4 107392 1048576 128A 66 4 107520 1048576 128A 67 4 107648 1048576 128A 68 4 107776 1048576 128A 69 4 107904 1048576 128A 70 4 108032 1048576 128A 71 4 108160 1048576 128A 72 4 108288 1048576 128A 73 4 108416 1048576 128A 74 4 108544 1048576 12875 rows selected.--查询视图dba_tablesSQL> 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------------------------------ ---------- ---------- ------------ ---------- -----------ASQL> 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 101SQL> select 522252*101 from dual;522252*101---------- 52747452SQL> 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的区别是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
最近发表
推荐文章
热评文章
暂时没有评论，来抢沙发吧~