[20120927]deferred segment creation与system,sys用户.txt

今天看了一篇blog,才知道sys,system用户不能使用deferred segment creation.

http://blog.tanelpoder.com/2010/07/11/dropping-and-creating-tables-in-read-only-tablespaces-what/

自己重复了测试:

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 Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table sys.test (a int) segment creation deferred;
create table sys.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table


SQL> create table system.test (a int) segment creation deferred;
create table system.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

SQL> create table scott.test (a int) segment creation deferred;
Table created.

        There's also a parameter, deferred_segment_creation which controls the default behavior.:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

SQL>