IT评测·应用市场-qidao123.com技术社区

标题: 达梦dba_segments指定表名查询到的大小都包含哪些数据 [打印本页]
作者: 十念    时间: 2022-10-13 20:05
标题: 达梦dba_segments指定表名查询到的大小都包含哪些数据
一、结论

dba_segments指定表名查询到的段大小包含索引、约束、表字段数据（包含LOB字段）
（1）表（不包含LOB字段）创建默认分配2个簇，1个簇用于存放表结构及字段数据，1个簇用于存放cluster类型的索引
（2）创建索引默认会分配2个簇，用于存放normal索引数据
（3）创建约束不会额外分配空间，除非是主键或唯一约束
（4）添加LOB字段默认分配2个簇，用于存放lob字段数据
（5）表（包含LOB字段）创建默认分配4个簇，1个簇用于存放表结构及字段数据，1个簇用于存放cluster类型的索引，2和簇用于存放lob字段数据
二、测试过程

（一）创建测试表，查看初始大小
  1. create table test01(id number,name varchar2(10));
  3. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  5. OWNER  SEGMENT_NAME BYTES                BLOCKS               EXTENTS              INITIAL_EXTENT      
  6. ------ ------------ -------------------- -------------------- -------------------- --------------------
  7. SYSDBA TEST01       2097152              64                   2                    1048576
  9. SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';
  11. OWNER  INDEX_NAME    INDEX_TYPE INITIAL_EXTENT
  12. ------ ------------- ---------- --------------
  13. SYSDBA INDEX33566761 CLUSTER    1
  15. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761');
  16. 未选定行
复制代码
（二）添加索引，查看空间变化
  1. create index idx_test01_name on test01(name);
  3. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  5. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  6. ------ ------------ -------------------- -------------------- -------------------- --------------------
  7. SYSDBA TEST01 4194304 128 4 1048576
  9. SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';
  11. OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
  12. ------ --------------- ---------- --------------
  13. SYSDBA INDEX33566761 CLUSTER 1
  14. SYSDBA IDX_TEST01_NAME NORMAL 1
  16. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761','IDX_TEST01_NAME');
  18. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  19. ------ --------------- -------------------- -------------------- -------------------- --------------------
  20. SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576
复制代码
（三）添加主键，查看空间变化
  1. alter table test01 add constraint pk_test01_id primary key(id);
  3. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  5. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  6. ------ ------------ -------------------- -------------------- -------------------- --------------------
  7. SYSDBA TEST01 6291456 192 6 1048576
  9. SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';
  11. OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
  12. ------ --------------- ---------- --------------
  13. SYSDBA INDEX33566761 CLUSTER 1
  14. SYSDBA INDEX33566763 NORMAL 1
  15. SYSDBA IDX_TEST01_NAME NORMAL 1
  17. SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner='SYSDBA' and table_name='TEST01';
  19. CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
  20. --------------- --------------- ------- -------------
  21. PK_TEST01_ID P ENABLED INDEX33566763
  23. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761','IDX_TEST01_NAME','INDEX33566763');
  25. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  26. ------ --------------- -------------------- -------------------- -------------------- --------------------
  27. SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576
  28. SYSDBA INDEX33566763 2097152 64 2 1048576
复制代码
（四）创建约束，查看空间变化
  1. alter table test01 add constraint ck_test01_id check(id>=1);
  3. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  5. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  6. ------ ------------ -------------------- -------------------- -------------------- --------------------
  7. SYSDBA TEST01 6291456 192 6 1048576
  9. SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner='SYSDBA' and table_name='TEST01';
  11. CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
  12. --------------- --------------- ------- -------------
  13. CK_TEST01_ID C ENABLED NULL
  14. PK_TEST01_ID P ENABLED INDEX33566763
复制代码
（五）增加lob列和普通列，查看空间变化
  1. alter table test01 add content clob;
  3. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  5. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  6. ------ ------------ -------------------- -------------------- -------------------- --------------------
  7. SYSDBA TEST01 8388608 256 8 1048576
  9. alter table test01 add city varchar2(10);
  11. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';
  13. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  14. ------ ------------ -------------------- -------------------- -------------------- --------------------
  15. SYSDBA TEST01 8388608 256 8 1048576
  18. SQL> select id,name,crtdate from sysobjects where name='TEST01' and schid in(select id from sysobjects where name='SYSDBA' and type$='SCH');
  20. ID NAME CRTDATE
  21. ----------- ------ --------------------------
  22. 5751 TEST01 2022-02-22 14:55:03.347000
  24. SQL> SELECT TABLE_USED_LOB_PAGES('SYSDBA','TEST01');
  26. TABLE_USED_LOB_PAGES('SYSDBA','TEST01')
  27. ---------------------------------------
  28. 6 ---这个返回的是数据块，2个簇实际在用的数据块数量只有6个
复制代码
（六）创建包含LOB字段的表
  1. SQL> create table test02(id number,name varchar2(10),content clob);
  2. SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST02';
  4. OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
  5. ------ ------------ -------------------- -------------------- -------------------- --------------------
  6. SYSDBA TEST02 4194304 128 4 1048576
复制代码
出处：https://www.cnblogs.com/huzei/
本博客所有文章仅用于学习、研究和交流目的，欢迎非商业性质转载。
由于博主的水平有限，不足和错误之处在所难免，希望大家能够批评指出。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4