Oracle 11g r2 新建空表不分配semgent
Oracle 11g r2 新建空表不分配semgent
Oracle 11g r2的新特性,延迟段创建 ,就是说从11GR2开始默认创建的表不会立及分配segment,不会占用磁盘空间,这听上去也是很合理的,当第一条数据insert时才会分配空间试验一把
- sys@ANBOB> conn anbob/anbob
- Connected.
- anbob@ANBOB> select * from v$version;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- PL/SQL Release 11.2.0.1.0 - Production
- CORE 11.2.0.1.0 Production
- TNS for Linux: Version 11.2.0.1.0 - Production
- NLSRTL Version 11.2.0.1.0 - Production
- anbob@ANBOB> create table testnew(id int primary key,name varchar2(10));
- Table created.
- anbob@ANBOB> create table testnew_IME(id int primary key,name varchar2(10)) segment creation immediate;
- Table created.
- anbob@ANBOB> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred;
- Table created.
- anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%';
- SEGMENT_NAME
- ---------------------------------------------------------------------------------
- TESTNEW_IME
- anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW';
- INDEX_NAME TABLE_OWNER
- ------------------------------ ------------------------------
- SYS_C0010903 ANBOB
- anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_IME';
- INDEX_NAME TABLE_OWNER
- ------------------------------ ------------------------------
- SYS_C0010904 ANBOB
- anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_DEF';
- INDEX_NAME TABLE_OWNER
- ------------------------------ ------------------------------
- SYS_C0010905 ANBOB
- anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010903';
- no rows selected
- anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010904';
- SEGMENT_NAME
- ---------------------------------------------------------------------------------
- SYS_C0010904
- anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010905';
- no rows selected
- anbob@ANBOB> insert into testnew values(1,'anbob.com');
- 1 row created.
- anbob@ANBOB> commit;
- Commit complete.
- anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%';
- SEGMENT_NAME
- ---------------------------------------------------------------------------------
- TESTNEW
- TESTNEW_IME
- anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW';
- INDEX_NAME TABLE_OWNER
- ------------------------------ ------------------------------
- SYS_C0010903 ANBOB
- anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010903';
- SEGMENT_NAME
- ---------------------------------------------------------------------------------
- SYS_C0010903
- anbob@ANBOB> truncate table testnew;
- Table truncated.
- anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%';
- SEGMENT_NAME
- ---------------------------------------------------------------------------------
- TESTNEW
- TESTNEW_IME
- anbob@ANBOB> conn sys/oracle as sysdba
- Connected.
- sys@ANBOB> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred;
- create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred
- *
- ERROR at line 1:
- ORA-14223: 此表不支持延迟创建段
sys@ANBOB> conn anbob/anbob Connected. anbob@ANBOB> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production anbob@ANBOB> create table testnew(id int primary key,name varchar2(10)); Table created. anbob@ANBOB> create table testnew_IME(id int primary key,name varchar2(10)) segment creation immediate; Table created. anbob@ANBOB> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred; Table created. anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%'; SEGMENT_NAME --------------------------------------------------------------------------------- TESTNEW_IME anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW'; INDEX_NAME TABLE_OWNER ------------------------------ ------------------------------ SYS_C0010903 ANBOB anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_IME'; INDEX_NAME TABLE_OWNER ------------------------------ ------------------------------ SYS_C0010904 ANBOB anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_DEF'; INDEX_NAME TABLE_OWNER ------------------------------ ------------------------------ SYS_C0010905 ANBOB anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010903'; no rows selected anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010904'; SEGMENT_NAME --------------------------------------------------------------------------------- SYS_C0010904 anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010905'; no rows selected anbob@ANBOB> insert into testnew values(1,'anbob.com'); 1 row created. anbob@ANBOB> commit; Commit complete. anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%'; SEGMENT_NAME --------------------------------------------------------------------------------- TESTNEW TESTNEW_IME anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW'; INDEX_NAME TABLE_OWNER ------------------------------ ------------------------------ SYS_C0010903 ANBOB anbob@ANBOB> select segment_name from user_segments where segment_name='SYS_C0010903'; SEGMENT_NAME --------------------------------------------------------------------------------- SYS_C0010903 anbob@ANBOB> truncate table testnew; Table truncated. anbob@ANBOB> select segment_name from user_segments where segment_name like 'TESTNEW%'; SEGMENT_NAME --------------------------------------------------------------------------------- TESTNEW TESTNEW_IME anbob@ANBOB> conn sys/oracle as sysdba Connected. sys@ANBOB> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred; create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred * ERROR at line 1: ORA-14223: 此表不支持延迟创建段
note:
11g r2默认是使用segment creation deferred建立,新建的无记录表不分配sement,当insert 第一条记录时分配段空间,不会因truncate而回收,并且在sys schema里不支持,听说exp 也不会导出
评论暂时关闭