我們知道Oracle有一個延遲段建立的功能，就是建立沒有資料的表的時候，不會分配空間，只有在插入第一條記錄的時候，才會為段分配空間，具體如下：

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter deferred

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation boolean TRUE

SQL> conn hr/hr

Connected.

SQL> create table seg_test(c number);

Table created.

SQL> select segment_name from user_segments where segment_name='SEG_TEST';

no rows selected

SQL> insert into seg_test values(1);

1 row created.

SQL> select segment_name,bytes from user_segments where segment_name='SEG_TEST';

SEGMENT_NAME BYTES

-------------------- ----------

SEG_TEST 65536

如果使用SYS使用者建立，具體如下：

SQL> conn / as sysdba

Connected.

SQL> create table seg_test1(c number);

Table created.

SQL> select segment_name,tablespace_name,bytes from dba_segments where segment_name='SEG_TEST1';

SEGMENT_NAME TABLESPACE_NAME BYTES

-------------------- ------------------------------ ----------

SEG_TEST1 SYSTEM 65536

SQL> create table seg_test2(c number) tablespace users;

Table created.

SQL> select segment_name,tablespace_name,bytes from dba_segments where segment_name='SEG_TEST2';

SEGMENT_NAME TABLESPACE_NAME BYTES

-------------------- ------------------------------ ----------

SEG_TEST2 USERS 65536

SQL> create table seg_test3(c number) segment creation deferred;

create table seg_test3(c number) segment creation deferred

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported for this table

強制使用segment creation deferred報錯。

可以看到建立了表之後就分配了空間，與建立在哪個表空間沒有關係。

SQL> conn hr/hr

Connected.

SQL> create table emptest3 tablespace system as select * from employees;

Table created.

SQL> select segment_name,bytes from user_segments where segment_name='EMPTEST3';

SEGMENT_NAME BYTES

-------------------- ----------

EMPTEST3 65536

關於SYS使用者無法使用延遲段建立的示例

相關文章