ORA-01950: no privileges on tablespace ‘DW_INDX’

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

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

 

Advertisements
Comments
  1. Andy M says:

    I had this exact problem! It was even odder because the index was in a different “DATA” tablespace and not any of the “INDEX” tablespaces…

  2. Joe H says:

    I add unlimited quota to the schema owners and that resolved my issue. thanks for your troubleshooting guide.

  3. Gyula P says:

    Thanks Jason. It was a very good hint to resolve my problem.
    For me it was the very same issue as for Joe H: I granted unlimited quota to the schema owner and that resolved the problem already.
    Thanks again for this hint, escpecially because – just like you said – there is hardly to be found any documentation about this on Oracle’s websites.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s