段的创建表user_segments

段的创建表user_segments

分类: Oracle之二:体系结构、模式和事务管理 63人阅读 评论(0) 收藏 举报

1、段的定义及类型

Oracle中的段(segment)是占用磁盘空间的一个对象,最常见的段类型包括:

l  聚簇cluster

l  表table

l  表分区 tablepartition

l  索引 index

l  索引分区

l  Lob分区lob partition、lob子分区lobsubpartition、lob索引lobindex、lob段lob segmnent

l  嵌套表 nestedtable

l  回滚段rollback

详细介绍可见《编程艺术》P313

2、段的创建

在表、索引等创建的过程中(在11gR2中,段的创建要等到第一条数据被插入时),事实上即在创建段,从user_segment可以查看已创建的段

(1)创建一个基本表,查看所分配的段

10.2.0.4:

  1. SQL> create table t(id number);  
  2.    
  3. Table created.  
  4.    
  5. SQL> select * from user_segments;  
  6.    
  7. SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_  
  8. T                                                                                                               TABLE              USERS                               65536          8         1          65536                       1  2147483645                                        DEFAULT  
SQL> create table t(id number);
 
Table created.
 
SQL> select * from user_segments;
 
SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
T                                                                                                               TABLE              USERS                               65536          8         1          65536                       1  2147483645                                        DEFAULT

11gR2:

  1. test@IRMSDC2> create table t(idnumber);  
  2.    
  3. Table created.  
  4.    
  5. test@IRMSDC2> select * from user_segments;  
  6.    
  7. no rows selected  
  8.    
  9. test@IRMSDC2> insert into t(id)values(1);  
  10.    
  11. 1 row created.  
  12.    
  13. test@IRMSDC2> select * fromuser_segments;  
  14.    
  15. SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       SEGMENT_SUTABLESPACE_NAME                         BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION  
  16. --------------------------------------------------------------------------------------------------------------- ------------------ ---------------------------------------- ---------- ---------- ---------- ------------------------- ----------- ----------- ---------- ------- ------------   
  17. PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_ FLASH_CCELL_FL  
  18. ------------ ---------- ---------------------- ------- -------   
  19. T                                                                                                               TABLE              ASSM       USERS                                   65536          8          1          65536     1048576           1 2147483645 2147483645  
  20.                                         DEFAULTDEFAULT DEFAULT  
test@IRMSDC2> create table t(idnumber);
 
Table created.
 
test@IRMSDC2> select * from user_segments;
 
no rows selected
 
test@IRMSDC2> insert into t(id)values(1);
 
1 row created.
 
test@IRMSDC2> select * fromuser_segments;
 
SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       SEGMENT_SUTABLESPACE_NAME                         BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION
--------------------------------------------------------------------------------------------------------------- ------------------ ---------------------------------------- ---------- ---------- ---------- ------------------------- ----------- ----------- ---------- ------- ------------
PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_ FLASH_CCELL_FL
------------ ---------- ---------------------- ------- -------
T                                                                                                               TABLE              ASSM       USERS                                   65536          8          1          65536     1048576           1 2147483645 2147483645
                                        DEFAULTDEFAULT DEFAULT


由此可见,在11gR2中,只有插入数据后才会分配段空间。以下操作均在10g中进行,在11g中只要插入一条数据或者在创建表时指定segment creation immediate即可得到相同效果。

(2)创建一个带主键的表,查看所分配的段 

  1. SQL> create table t2(id numberprimary key);  
  2.    
  3. Table created.  
  4.    
  5. SQL> select * fromuser_segments;              
  6.    
  7. SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_  
  8. --------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ---------- ---------- ---------- ------------------------- ----------- ----------- ------------ ---------- ----------------------   
  9. T                                                                                                               TABLE              USERS                               65536          8         1          65536                       1  2147483645                                        DEFAULT  
  10. T2                                                                                                              TABLE              USERS                               65536          8          1          65536                       1  2147483645                                        DEFAULT  
  11. SYS_C00326438                                                                                                   INDEX              USERS                               65536          8          1          65536                       1  2147483645                                        DEFAULT  
SQL> create table t2(id numberprimary key);
 
Table created.
 
SQL> select * fromuser_segments;            
 
SEGMENT_NAME                                                                     PARTITION_NAME                SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
--------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ---------- ---------- ---------- ------------------------- ----------- ----------- ------------ ---------- ----------------------
T                                                                                                               TABLE              USERS                               65536          8         1          65536                       1  2147483645                                        DEFAULT
T2                                                                                                              TABLE              USERS                               65536          8          1          65536                       1  2147483645                                        DEFAULT
SYS_C00326438                                                                                                   INDEX              USERS                               65536          8          1          65536                       1  2147483645                                        DEFAULT

(3)创建一个有lob字段的表,查看所分配的段

  1. SQL> createtable t3(x int primary key, y clob, z blob);  
  2.    
  3. Table created.  
  4. SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;  
  5. SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS  
  6. ------------------------------------------------ ------------------------------ ---------- --------------------   
  7. T                              TABLE              USERS                               65536          8          1  
  8. T2                             TABLE              USERS                               65536          8          1  
  9. SYS_C00326438                  INDEX              USERS                               65536          8          1  
  10. T3                             TABLE              USERS                               65536          8          1  
  11. SYS_LOB0000469684C00002$$      LOBSEGMENT         USERS                               65536          8         1  
  12. SYS_IL0000469684C00002$$       LOBINDEX           USERS                               65536          8          1  
  13. SYS_LOB0000469684C00003$$      LOBSEGMENT         USERS                               65536          8          1  
  14. SYS_IL0000469684C00003$$       LOBINDEX           USERS                               65536          8          1  
  15. SYS_C00326439                  INDEX              USERS                               65536          8          1  
  16.    
  17. 9 rowsselected.  
SQL> createtable t3(x int primary key, y clob, z blob);
 
Table created.
SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS
------------------------------------------------ ------------------------------ ---------- --------------------
T                              TABLE              USERS                               65536          8          1
T2                             TABLE              USERS                               65536          8          1
SYS_C00326438                  INDEX              USERS                               65536          8          1
T3                             TABLE              USERS                               65536          8          1
SYS_LOB0000469684C00002$$      LOBSEGMENT         USERS                               65536          8         1
SYS_IL0000469684C00002$$       LOBINDEX           USERS                               65536          8          1
SYS_LOB0000469684C00003$$      LOBSEGMENT         USERS                               65536          8          1
SYS_IL0000469684C00003$$       LOBINDEX           USERS                               65536          8          1
SYS_C00326439                  INDEX              USERS                               65536          8          1
 
9 rowsselected.


由此可见,t只创建一个表的段,t2创建了一个表段以及一个索引段,t3创建了一个表段、一个索引段、2个lobindex,2个lobsegment。

结论:创建一个表时,表的信息及数据可能会分散到多个段之中,每个段保存着一部分信息,而段由区组成,区由oracle块组成,oracle块由操作系统组成。

创建一个新段时,默认由1个区、8个块组成,共计8K*8空间,默认情况下oracle块大小为8k.

  1. SQL> show parameter block_size;  
  2.    
  3. NAME                                 TYPE                           VALUE  
  4. ------------------------------------------------------------------ ------------------------------   
  5. db_block_size                        integer                        8192  
SQL> show parameter block_size;
 
NAME                                 TYPE                           VALUE
------------------------------------------------------------------ ------------------------------
db_block_size                        integer                        8192

(4)插入大量数据

在表t中插入大量数据,观察段大小的变化

  1. SQL> conn / as sysdba  
  2. Connected.  
  3. SQL> INSERT INTO test.t SELECT ROWNUMFROM dba_TABLES WHERE ROWNUM <= 10000;  
  4.    
  5. 10000 rows created.  
  6.    
  7. SQL> conn test/test_123;  
  8. Connected.  
  9. SQL> selectsegment_name, segment_type,tablespace_name,bytes,blocks,extents fromuser_segments;  
  10.    
  11. SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS  
  12. ------------------------------------------------ ------------------------------ ---------- --------------------   
  13. T                              TABLE              USERS                              196608         24          3  
  14. T2                             TABLE              USERS                               65536          8          1  
  15. SYS_C00326438                  INDEX              USERS                               65536          8          1  
  16. T3                             TABLE              USERS                               65536          8         1  
  17. SYS_LOB0000469684C00002$$      LOBSEGMENT         USERS                               65536          8          1  
  18. SYS_IL0000469684C00002$$       LOBINDEX           USERS                               65536          8          1  
  19. SYS_LOB0000469684C00003$$      LOBSEGMENT         USERS                               65536          8          1  
  20. SYS_IL0000469684C00003$$       LOBINDEX           USERS                               65536          8          1  
  21. SYS_C00326439                  INDEX              USERS                               65536          8          1  
  22.    
  23. rows selected.  
SQL> conn / as sysdba
Connected.
SQL> INSERT INTO test.t SELECT ROWNUMFROM dba_TABLES WHERE ROWNUM <= 10000;
 
10000 rows created.
 
SQL> conn test/test_123;
Connected.
SQL> selectsegment_name, segment_type,tablespace_name,bytes,blocks,extents fromuser_segments;
 
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                     BYTES     BLOCKS   EXTENTS
------------------------------------------------ ------------------------------ ---------- --------------------
T                              TABLE              USERS                              196608         24          3
T2                             TABLE              USERS                               65536          8          1
SYS_C00326438                  INDEX              USERS                               65536          8          1
T3                             TABLE              USERS                               65536          8         1
SYS_LOB0000469684C00002$$      LOBSEGMENT         USERS                               65536          8          1
SYS_IL0000469684C00002$$       LOBINDEX           USERS                               65536          8          1
SYS_LOB0000469684C00003$$      LOBSEGMENT         USERS                               65536          8          1
SYS_IL0000469684C00003$$       LOBINDEX           USERS                               65536          8          1
SYS_C00326439                  INDEX              USERS                               65536          8          1
 
9 rows selected.


可见,区增长为3个,block增长到24个,即段占用空间为24*8K。

注:字典/本地表空间管理决定了如何把空间分配给各个表,而ASSM/MSSM决定了如何把表已经拥有的空间分配给各行,以及如何管理各个区、块,如通过PCTFREE指定每个块应该预留多少空间用于将来的更新。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值