create table进阶学习(三)

最新推荐文章于 2022-05-12 10:47:17 发布

clg10051

最新推荐文章于 2022-05-12 10:47:17 发布

阅读量189

点赞数

文章标签：数据库

自oracle11g开始,提供延迟段创建功能,即创建表(未插入实际数据)是否马上为其分配segment

-----测试场景
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

-----创建表
SQL> create table t_segment(a int);

Table created

SQL> desc user_segments;
Name             Type         Nullable Default Comments
---------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME     VARCHAR2(81) Y                Name, if any, of the segment
PARTITION_NAME   VARCHAR2(30) Y                Partition/Subpartition Name, if any, of the segment
SEGMENT_TYPE     VARCHAR2(18) Y                Type of segment: "TABLE", "CLUSTER", "INDEX", "ROLLBACK", "DEFERRED ROLLBACK", "TEMPORARY", "SPACE HEADER", "TYPE2 UNDO" or "CACHE"
SEGMENT_SUBTYPE VARCHAR2(10) Y                SubType of Lob segment: "SECUREFILE", "ASSM", "MSSM", NULL
TABLESPACE_NAME VARCHAR2(30) Y                Name of the tablespace containing the segment
BYTES            NUMBER       Y                Size, in bytes, of the segment
BLOCKS           NUMBER       Y                Size, in Oracle blocks, of the segment
EXTENTS          NUMBER       Y                Number of extents allocated to the segment
INITIAL_EXTENT   NUMBER       Y                Size, in bytes, of the initial extent of the segment
NEXT_EXTENT      NUMBER       Y                Size, in bytes, of the next extent to be allocated to the segment
MIN_EXTENTS      NUMBER       Y                Minimum number of extents allowed in the segment
MAX_EXTENTS      NUMBER       Y                Maximum number of extents allowed in the segment
MAX_SIZE         NUMBER       Y                Maximum number of blocks allowed in the segment
RETENTION        VARCHAR2(7) Y                Retention option for SECUREFILE segment
MINRETENTION     NUMBER       Y                Minimum Retention Duration for SECUREFILE segment
PCT_INCREASE     NUMBER       Y                Percent by which to increase the size of the next extent to be allocated
FREELISTS        NUMBER       Y                Number of process freelists allocated to this segment
FREELIST_GROUPS NUMBER       Y                Number of freelist groups allocated to this segment
BUFFER_POOL      VARCHAR2(7) Y                The default buffer pool to be used for blocks from this segment
FLASH_CACHE      VARCHAR2(7) Y
CELL_FLASH_CACHE VARCHAR2(7) Y

--oracle默认开启
SQL> show parameter defer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

--未插入记录,不分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';

SEGMENT_NAME INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------

SQL> insert into t_segment values(1);

1 row inserted

SQL> commit;

Commit complete

--插入记录后分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';

SEGMENT_NAME INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT 65536 TBS_HANG

---配置为false情形又如何
SQL> alter session set deferred_segment_creation=false;

Session altered

SQL> drop table t_segment purge;

Table dropped

SQL> create table t_segment(a int);

Table created

---未插入记录也分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';

SEGMENT_NAME INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT 65536 TBS_HANG

SQL> show parameter def

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

SQL> drop table t_segment purge;

Table dropped

---表级指定延迟段分配
SQL> create table t_segment(a int) segment creation deferred;

Table created

---未分配segment,虽然参数deferred_segment_creation为false,说明表级优先于参数配置
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';

SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------

--如下不再文字描述,贴测试结果
SQL> drop table t_segment purge;

Table dropped

SQL> create table t_segment(a int) segment creation immediate;

Table created

SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';

SEGMENT_NAME INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT 65536 TBS_HANG

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752491/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752491/