CORRECTION to "can I create private index"

Apparently, if a user has a table, this user is able to use ANY index on this table, i.e. index in his schema and all indexes in other schemas. To prove this:

CREATE USER A IDENTIFIED BY A...
CREATE USER B IDENTIFIED BY B... GRANT CONNECT TO A;
GRANT CONNECT TO B; connect A/A

CREATE TABLE t (n1 NUMBER, n2 NUMBER);

• call script which creates PLAN_TABLE: @utlxplan.sql

GRANT INDEX ON a.t(n1) TO b;

connect B/B

CREATE INDEX ind ON A.T;

connect A/A

ALTER SESSION SET OPTIMIZER_GOAL=RULE; EXPLAIN PLAN FOR
   SELECT n1,n2 FROM t WHERE n1=1;

SELECT ... FROM plan_table

produces:

OPERATION OPTIONS OBJECT_NAME TABLE ACCESS BY ROWID T
INDEX RANGE SCAN ind

So, there is no way to create "private" index in Oracle. Received on Wed Oct 15 1997 - 00:00:00 CDT