1. 代码100分首页
  2. 代码基础

达梦dba_segments指定表名查询到的大小都包含哪些数据[亲测有效]

小小码 • • 代码基础 • 阅读 159
一键激活最新全家桶

达梦dba_segments指定表名查询到的大小都包含哪些数据[亲测有效]一、结论 dba_segments指定表名查询到的段大小包含索引、约束、表字段数据（包含LOB字段）（1）表（不包含LOB字段）创建默认分配2个簇，1个簇用于存放表结构及字段数据，1个簇用于存放clu

达梦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字段数据

二、测试过程

（一）创建测试表，查看初始大小

create table test01(id number,name varchar2(10));

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER  SEGMENT_NAME BYTES                BLOCKS               EXTENTS              INITIAL_EXTENT      
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01       2097152              64                   2                    1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner="SYSDBA" and table_name="TEST01";

OWNER  INDEX_NAME    INDEX_TYPE INITIAL_EXTENT
------ ------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER    1

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name in("INDEX33566761");
未选定行

（二）添加索引，查看空间变化

create index idx_test01_name on test01(name);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 4194304 128 4 1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner="SYSDBA" and table_name="TEST01";

OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
------ --------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER 1
SYSDBA IDX_TEST01_NAME NORMAL 1

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name in("INDEX33566761","IDX_TEST01_NAME");

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ --------------- -------------------- -------------------- -------------------- --------------------
SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576

（三）添加主键，查看空间变化

alter table test01 add constraint pk_test01_id primary key(id);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 6291456 192 6 1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner="SYSDBA" and table_name="TEST01";

OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
------ --------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER 1
SYSDBA INDEX33566763 NORMAL 1
SYSDBA IDX_TEST01_NAME NORMAL 1

SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner="SYSDBA" and table_name="TEST01";

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
--------------- --------------- ------- -------------
PK_TEST01_ID P ENABLED INDEX33566763

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");

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ --------------- -------------------- -------------------- -------------------- --------------------
SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576
SYSDBA INDEX33566763 2097152 64 2 1048576

（四）创建约束，查看空间变化

alter table test01 add constraint ck_test01_id check(id>=1);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 6291456 192 6 1048576

SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner="SYSDBA" and table_name="TEST01";

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
--------------- --------------- ------- -------------
CK_TEST01_ID C ENABLED NULL
PK_TEST01_ID P ENABLED INDEX33566763

（五）增加lob列和普通列，查看空间变化

alter table test01 add content clob;

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 8388608 256 8 1048576

alter table test01 add city varchar2(10);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST01";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 8388608 256 8 1048576


SQL> select id,name,crtdate from sysobjects where name="TEST01" and schid in(select id from sysobjects where name="SYSDBA" and type$="SCH");

ID NAME CRTDATE
----------- ------ --------------------------
5751 TEST01 2022-02-22 14:55:03.347000

SQL> SELECT TABLE_USED_LOB_PAGES("SYSDBA","TEST01");

TABLE_USED_LOB_PAGES("SYSDBA","TEST01")
---------------------------------------
6 ---这个返回的是数据块，2个簇实际在用的数据块数量只有6个

（六）创建包含LOB字段的表

SQL> create table test02(id number,name varchar2(10),content clob);
SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner="SYSDBA" and segment_name="TEST02";

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST02 4194304 128 4 1048576

原文地址：https://www.cnblogs.com/huzei/archive/2022/10/13/16788546.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/4654.html

赞 (0)
小小码小小码
0 0
上一篇 2023-06-10
下一篇 2023-06-10

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注