DEFERRED_SEGMENT_CREATION <11g的…

首先先了解下DEFERRED_SEGMENT_CREATION 的参数信息

如下：

DEFERRED_SEGMENT_CREATION

Property	Description
Parameter type	Boolean
Default value	true
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	true | false
Basic	No

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.《如果在创建一组表前已经知道，相当数量的表不会立即被使用，则建议将该参数设为true ，这将有利于节省磁盘空间，和安装时间》

问题情形：

oracle 11.2.0.1.0

创建用户aaa，给其connect和resource角色，但回收unlimited tablespace权限：

SQL> create user aaa identified by aaa default tablespace users;

User created.

SQL> grant connect,resource to aaa;

Grant succeeded.

SQL> revoke unlimited tablespace from aaa;

Revoke succeeded.

SQL> select * from role_sys_privs where role="RESOURCE";

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO

8 rows selected.

SQL> alter user aaa quota unlimited on users;

User altered.

现在的问题是：aaa在任何表空间都有创建表的权限
[oracle@master /]$ sqlplus aaa

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 18:38:25 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create table test1(id int) tablespace users;

Table created.

SQL> create table test2 (id int) tablespace system;

Table created.

SQL> create table test3(id int) tablespace zaodian;

Table created.

表test1可以正常插入数据，test2和test3都无法插入数据，这是正常的：

SQL> insert into test1 values(1);

1 row created.

SQL> insert into test2 values(1);
insert into test2 values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'


SQL> insert into test3 values(1);
insert into test3 values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'ZAODIAN'

请问，为什么用户aaa可以在system，zaodian表空间上有创建表的权限？

这是一个相当有趣的问题

11g 你创建表，默认不创建segment的，所以你在插入数据的时候，创建segment的失败，导致插入失败

@@deferred_segment_creation<wbr> 设置为false</wbr>可以解决普通用户在其他的表空间有建表的权限