ORA-01950: no privileges on tablespace users in Oracle

Few days back, an error was reported in the alert log which was due to no privileges on tablespace for auto execution of job as shown below.

ORA-12012: error on auto execute of job "ORAHOW"."DB_GROWTH_JOB"
ORA-01950: no privileges on tablespace 'ORA_DATA'
ORA-06512: at "ORAHOW.PROC_DB_GROWTH_INFO", line 4


After looking into it we found that user doesn't have privileges to allocate an extent in the specified tablespace. So using the below query we get the object type and the status of the object.

SQL> select owner,object_name,object_type,status from dba_objects where  object_name='DB_GROWTH_JOB';

OWNER          OBJECT_NAM      OBJECT_TYPE       STATUS
-------- ---------- ------------------------------------
ORAHOW      DB_GROWTH_ JOB      JOB            VALID

      

Now we will check the user default tablespace on which we need to give privileges.

SQL> select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME='ORAHOW';

USERNAME                 DEFAULT_TABLESPACE
-----------------------------------------------
ORAHOW                     ORA_DATA

As you can see that user ORAHOW is using default tablespace ORA_DATA, but we also need to check the amount of space allocated for that user on that tablespace using the below query.

SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='ORAHOW';

no rows selected

Using the above query we can see that this user doesn't have access to this tablespace, so we will assign unlimited quota on this tablespace for this user.

SQL> alter user ORAHOW DEFAULT TABLESPACE ORA_DATA quota unlimited on ORA_DATA;

User altered.

If you want you can assign limited quota to that user.

ALTER USER <username> QUOTA 100M ON <tablespace name>
GRANT UNLIMITED TABLESPACE TO <username> 

Now will verify the above changes using the following query.

SQL>  select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='ORAHOW';

TABLESPACE_NAME        USERNAME         BYTES         MAX_BYTES
---------------------------------------------------------------
ORA_DATA                 ORAHOW          786432           -1
  

Finally, after assigning privileges on tablespace to the user we will execute the job.

SQL> exec DBREPORT.PROC_DB_GROWTH_INFO

PL/SQL procedure successfully completed.