ORA-01950: no privileges on tablespace

Many times we might encounter with an error on our application log or from database alert lot for ORA-01950 error.

Which means that the user/schema user trying to perform a job where it required to extent the tablespace that is tied with the user or the objects, the error occurs because it doesn’t have the privilege to perform this activity.

First we need to check if the user has the any limit defined on the allocating space for the specific tablespace, to verify we can query “DBA_TS_QUOTAS” table to check. If there is any limit set, we can increase it by using the below command .

ALTER USER <USERNAME> QUOTA <SIZE> ON <TABLESPACE NAME> ;
Example:
alter user sloba quote 500M on sloba_data ;

If we see that the user limit is not defined we can grant unlimited .

Syntax to grant unlimited quote :

GRANT UNLIMITED TABLESPACE TO <USERNAME>;
         or
ALTER USER <USERNAME> DEFAULT TABLESPACE <TABLESAPCE NAME> QUOTA UNLIMITED ON <TABLESPACE NAME> ;

Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.