Posts Tagged ‘ORA-01950’

ORA-01950: no privileges on tablespace ‘DW_INDX’

Posted: May 15, 2014 in ORA Errors
Tags: , ,
4

Welcome back, students at the school of Oracle Hard Knocks!

Yesterday, a member of the data warehouse development team attempted to run our data warehouse load in the stage environment. The execution script contained a series of procedures that are executed one a a time.

Upon running the script, it failed on line one…

BEGIN
 package_name.proc_name;
 ...
 END;
With the following error
Ora-01950: No privileges on tablespace 'DW_INDX'

The developer interpreted the ORA error to mean the user did not have privileges to run the job. I interpreted the error to mean the user did not have quota on the above tablespace. As I will explain, neither of our assumptions were correct.

  • Step one was to grant the process owner unlimited quota on the above tablespace.  When the developer restarted the script, it failed with the same error. At this point, I asked the developer to send me the script and that I would attempt to run the script as SYS.
  • Step two was to run the script as SYS. This attempt returned the same error.
  • Step three was to consult my good friend Google.  I read two or three blogs and some Oracle documentation on the ORA-01950 error.
    • All of the sites I visited suggested I add unlimited quota to the process owner except one.
    • The exception to the rule suggested that I add unlimited quota to the schema owners. Now we are getting somewhere. I executed this suggestion and added unlimited quota on the suspect tablespace on five schema owners within the procedure. Although this still didn’t solve the problem, it pointed me toward the solution.
  • Step four was to look at the tables where the load was to occur. The naming convention of the tablespace suggested that it was an index tablespace. Looking at the table definitions, a developer noticed that there was an index created in his own schema rather than the schema that contained the table. We both experienced an epiphany at this moment.

The developer who created the index in his own schema did not have quota on the ‘DW_INDX’ Tablespace.

  • Step five was to drop the index and create it in the schema in which the table resides.

This solved the problem. None of the Oracle documentation suggested this approach. If nothing you have tried so far has worked, take a look at this solution.

I hope this helps!

~Jason