ORA-01950: no privileges on tablespace 'TBS_NAME'

Metalink Id : 1012307.6

Scenario:
=======

When a new tablespace (SAMPLE) is created as a sysdba. Now if we are going to move some tables are indexes from existing tablespace (OLD_TBS_NAME) to that new tablesapce sample throws the following error.

Error:
=====

ORA-01950: no privileges on tablespace 'SAMPLE'

Solution:
======

Grand access to the users who have access to the old_tbs to that tablespace sample. By

This query will give the output to have a permission to that new tablespace to all the users who are having permission in the old_tbs_name. So take a output of this query and store it as .sql and run the output.

select 'alter user 'username ' quota unlimited on SAMPLE;' from dba_users where username in (select distinct owner from dba_tables where TABLESPACE_NAME='OLD_TBS_NAME');

General script:
==========

We can also select the users (more than 1 user) and give access to all tablespace to all the users by:


GRANT UNLIMITED TABLESPACE TO APPLSYS,XNP,AR,CCT,IEO,CS,AMV,IEM,XDP,APPS,IBU,JTF,OKC;

Revoke :
=======

REVOKE unlimited tablespace FROM user_name;