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

advertisements

_____________________________________________________________________________________________________________________

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'

_____________________________________________________________________________________________________________________

Website Stats