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.