Can I Create Index for Table Belonging to Different User

  • by

Yes, you can, but you need a special privilege. Try the following example:

SQL> conn oe/oe@orcl
Connected.
SQL> create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                       *
ERROR at line 1:
ORA-00942: table or view does not exist

In the above, we tried to create an index in OE for the table belonging to SH, but it failed with ORA-00942. It means that you don't have the privilege to see the table that belongs to other user. Even though you grant SELECT object privilege to OE.

SQL> conn sh/sh@orcl
Connected.
SQL> grant select on customers to oe;

Grant succeeded.

Let's create the index again.

SQL> create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges

Now it's ORA-01031, which means you did't use the correct privilege to do it. In fact, the right privilege to this issue is INDEX object privilege.

Grant the privilege by SH.

SQL> grant index on customers to oe;

Grant succeeded.

Turn to OE.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);

Index created.

At least, you need INDEX object privilege to create an index for the table belonging to another user. Additionally, you may need SELECT object privilege.

To know all available object privileges, you may refer to Oracle Database 19c Object Privileges List.

Leave a Reply

Your email address will not be published. Required fields are marked *