Skip to content

How to Create Index for Other User's Table

  • by

The answer is positive, you can create indexes for tables belonging to other users in your own schema, but you need more privileges. 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

1. GRANT SELECT ON or SELECT ANY TABLE

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.

Alternatively, you can also get SELECT ANY TABLE, a system privilege to see other user's table.

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

2. GRANT INDEX ON or CREATE ANY INDEX

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.

Alternatively, you can also get CREATE ANY INDEX, a system privilege to create indexes for other user's table in your own schema.

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.