oracle 11g使用deferred_segment_creation延迟段创建特性时遇到的问题总结

总结，下面是两个问题。问题1是用户可以在所有表空间创建表;问题2是exp不能导出空表

问题1：

版本：oracle 11.2.0.1.0

select * from v$version;

创建用户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'

解决方法：

这是因为 11g中的 deferred_segment_creation 延迟段创建特性，在CREATE TABLE DDL执行时实际不会在指定的表空间上生成segment ，

而会延迟到实际有INSERT数据后才会产生segment，因为没有实际的segment所以也就不会使用到tablespace quota 表空间配额，

但是在INSERT数据时如果没有对应表空间的quota的话仍会报 ORA-01950错误。

SQL> alter session set deferred_segment_creation=FALSE;
System altered.

conn aaa/test

SQL> create table tvs(t1 int) tablespace sysaux;
create table tvs(t1 int) tablespace sysaux
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSAUX'

如以上演示， "alter system set deferred_segment_creation=FALSE;"后禁用该11g新特性，回复到10g的状态。

问题2：

Oracle 11G在用EXPORT导出时，空表不能导11G R2中有个新特性，当表无数据时，不分配segment，以节省空间

解决方法：

方法1、insert一行，再rollback就产生segment了。该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

方法2、设置deferred_segment_creation 参数 ,该参数值默认是TRUE，当改为FALSE时，无论是空表还是非空表，都分配segment。修改SQL语句：

alter system set deferred_segment_creation=false scope=both;

需注意的是：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。如需导出之前的空表，只能用第一种方法。

用以下这句查找空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

把查询结果导出，执行导出的语句，强行修改segment值，然后再导出即可导出空表，

注意：数据库插入数据前，修改11g_R2参数可以导出空表

查找空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0

方法3、Oracle 10g以后增加了expdp和impdp工具，用此工具也可以导出空的表

下面附上一个查询一个用户在哪些表空间有unlimited tablespace权限：

/* Formatted on 6/8/2012 9:10:59 AM (QP5 v5.215.12089.38647) */
SELECT username, tablespace_name, privilege
FROM (SELECT grantee username, 'Any Tablespace' tablespace_name, privilege
FROM ( -- first get the users with direct grants
SELECT p1.grantee grantee, privilege
FROM dba_sys_privs p1
WHERE p1.privilege = 'UNLIMITED TABLESPACE'
UNION ALL
-- and then the ones with UNLIMITED TABLESPACE through a role...
SELECT r3.grantee, granted_role privilege
FROM dba_role_privs r3
START WITH r3.granted_role IN
(SELECT DISTINCT p4.grantee
FROM dba_role_privs r4, dba_sys_privs p4
WHERE r4.granted_role = p4.grantee
AND p4.privilege =
'UNLIMITED TABLESPACE')
CONNECT BY PRIOR grantee = granted_role)
-- we just whant to see the users not the roles
WHERE grantee IN (SELECT username FROM dba_users)
OR grantee = 'PUBLIC'
UNION ALL
-- list the user with unimited quota on a dedicated tablespace
SELECT username, tablespace_name, 'DBA_TS_QUOTA' privilege
FROM dba_ts_quotas
WHERE max_bytes = -1)
WHERE tablespace_name LIKE UPPER ('SYSTEM')
OR tablespace_name = 'Any Tablespace' AND username = 'TEST';