Summary of problems encountered when oracle 11g uses deferred_segment_creation to create a feature

The following is a summary of the problems encountered when oracle 11g uses deferred_segment_creation to create a feature. Problem 1: You can create tables in all tablespaces. Problem 2: exp cannot export empty tables. Problem 1: Version: oracle 11.2.0.1.0

Select * from v $ version; create user aaa and grant the connect and resource roles to the User, but revoke the unlimited tablespace permission: 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 zookeeper --- resource create sequence noresource create trigger noresource create cluster noresource create procedure noresource create type noresource create operator noresource create table noresource create indextype NO8 rows selected. SQL> alter user aaa quota unlimited on users; User altered.

 

The problem is that aaa has the permission to create a table in any tablespace.

[oracle@master /]$ sqlplus aaaSQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 18:38:25 2012Copyright (c) 1982, 2009, Oracle. All rights reserved.Enter password:Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> 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.

 

Table test1 can insert data normally, and neither test2 nor test3 can insert data, which is normal:

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'

 

Solution: this is because the deferred_segment_creation delay segment creation feature in 11g does not actually generate a segment on the specified tablespace during the create table ddl execution, the segment will be generated only after the actual INSERT data exists. Because there is no actual segment, the tablespace quota will not be used, however, if there is no quota for the corresponding tablespace when inserting data, the ORA-01950 error will still be reported.

SQL> alter session set deferred_segment_creation=FALSE;System altered.conn aaa/testSQL> 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' 

 

As shown in the preceding demonstration, "alter system set deferred_segment_creation = FALSE;" then disables the new feature of the 11g and returns it to the status of 10g. Problem 2: When Oracle 11G is exported using EXPORT, the empty table cannot EXPORT 11G R2 with a new feature. When the table does not have data, no segment is assigned to it to save space. solution: method 1: insert a row and then roll back to generate a segment. This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported. Method 2: Set the deferred_segment_creation parameter. The default value of this parameter is TRUE. When it is set to FALSE, segment is allocated to both empty tables and non-empty tables. Modify the SQL statement:

alter system set deferred_segment_creation=false scope=both;

It should be noted that the value setting does not affect the empty tables previously imported and cannot be exported. It can only be used for the newly added tables. To export an empty table, you can only use the first method. Use the following statement to find an empty table:

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

 

Export the query result, execute the Export Statement, forcibly modify the segment value, and then export the result to export the empty table. Note: before inserting data into the database, modify the 11g_R2 parameter to export the empty table to search for the empty table.

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

 

Method 3. After 10 Gb of Oracle, The expdp and impdp tools are added. You can use this tool to export an empty table and attach a table to query which tablespaces a user has the unlimited tablespace permission on:

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