ORA-01950: no privileges on tablespace | Tips and Solution

ORA-01950: no privileges on tablespace | Tips and Solution | Oracle Database

ORA-01950: no privileges on tablespace

ORA-01950 is a normal error with a easy solution. The error simply tells you that the user who owns the object (Let's say a table) is not having privileges on the tablespace mentioned in the error description.

You can use the below mentioned query to find the quota limits for all the users in your database along with the tablespace name. (this will only display the list of USERS will limited quota).

SQL> col username for a30; SQL> set lines 150 SQL> set pages 100 SQL> select * from dba_ts_quotas; TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- --- SYSAUX AUDSYS 1572864 -1 192 -1 NO SYSAUX GSMADMIN_INTERNAL 1441792 104857600 176 12800 NO SYSAUX APPQOSSYS 0 -1 0 -1 NO SYSAUX OLAPSYS 0 -1 0 -1 NO SYSAUX FLOWS_FILES 0 -1 0 -1 NO EXAMPLE OE 10420224 -1 1272 -1 NO

OR to get the quota information for some particular USER, run

SQL> select * from dba_ts_quotas where username like 'OE'; TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- --- EXAMPLE OE 10420224 -1 1272 -1 NO

Now to resolve the error, please use any option from the below mentioned queries depending upon your requirement,

1. Provide unlimited quota on any TABLESPACE to any USER which simply please I am allowing that USER to use as much space as it wants in any tablespace.

GRANT UNLIMITED TABLESPACE TO HR;

2. Provide a limited amount of quota on some particular tablespace.

alter user HR quota unlimited on USERS;

I hope this helps !!