11g Deferred Segment: ORA-01950: no privileges on tablespace


Normally "ORA-01950: no privileges on tablespace" error occurs due to the no space quota allocated to the user on the particular tablespace. I wish to explain the difference in ORA-01950 error with deferred segment creation values with TRUE and FALSE. 

If you set deferred_segment_creation value as TRUE the error will not fire at the time of table creation as it won’t create any initial segment during the table creation. The error comes when a row insert into the table.
If you set the deferred_segment_creation value as FALSE, the error comes at the time of the table creation.  

To fix this error you have to give QUOTA to the user on the particular tablespace. The syntax is
SQL>  alter user test quota 10m on users;
SQL>  alter user test quota unlimited on users;

Example 1 with Deferred Segment Creation with TRUE
SQL> connect / as sysdba
Connected.
SQL> SHOW PARAMETER DEF

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
deferred_segment_creation            boolean     TRUE

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

User created.
SQL> grant create session to test;

Grant succeeded.
SQL> connect test/test
Connected.
SQL> create table emp (emp_id number(5), ename varchar2(100));
Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

Table is created but No segments are created because of deferred_segment_creation = TRUE while inserting a row it throws the error.
SQL> select segment_name FROM USER_segments where segment_name ='EMP';

no rows selected

SQL> INSERT INTO EMP VALUES (1,'JAMES');
INSERT INTO EMP VALUES (1,'JAMES')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Example 2 with Deferred Segment Creation with FALSE
SQL> connect / as sysdba
Connected.
SQL> ALTER SYSTEM SET deferred_segment_creation=FALSE;

System altered.

SQL> SHOW PARAMETER DEF

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
deferred_segment_creation            boolean     FALSE

SQL> connect test/test
Connected.
SQL> create table emp (emp_id number(5), ename varchar2(100));
create table emp (emp_id number(5), ename varchar2(100))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google