CODEWITHSUNDEEP
oracle11gschemacreate-tableprivilegestablespace
Johnny Wu
Johnny Wu

Reputation: 1528

"No privilege" when creating table with Organization index

I am running Oracle 11.g and the id I am using has DBA role (full access supposedly).

When I tried to create a table under another schema, it works fine. However, when I tried to create a table with organization index, I was prompted

ORA-01950: no privileges on tablespace

I double checked my id has unlimited tablespace. My ID and the targeted schema are both in the same tablespace.

Supplemental info: I am able to run the same creat table statement w/ organization index under my own schema.

Upvotes: 1

Views: 445

Answers (2)

Jon Heller
Jon Heller

Reputation: 36902

Creating objects may require two privileges: your user needs privileges to create the objects, and the schema owner needs privileges to use resources related to that object. So while you have the ability to create a table in another schema, that schema also needs the privileges to write data to the related tablespaces:

alter user $username quota unlimited on $tablespace;

This approach is safer than granting the UNLIMITED TABLESPACE role. That role grants more than necessary, and if someone later tries to cleanup the privileges, revoking that role also undoes individual privileges, as described in this article.

Upvotes: 1

Johnny Wu
Johnny Wu

Reputation: 1528

I granted the targeted schedma unlimited tablespace and it's resolved.

Upvotes: 1

Related Questions