Database Software

Hi All,

I've discovered a frustrating little problem relating to index ownership on tables where public synonyms exist. Consider the following scenario:

Database with two users: SYSTEM (owns all tables) and FRED a user with RESOURCE and DBA roles.

1. Log on as SYSTEM and create a test table
SQL> create table test (ID NUMBER(1) NOT NULL);
Table created

2. Create a public synonym
SQL> create public synonym test for test;

Synonym created

3. Now index the base table
SQL> create index test1 on test(id);

Index created

SQL> commit;

Commit complete

SQL> quit

4. Now log on as FRED and create an index against the synonym with the same name
SQL> create index test1 on test(id);

Index created

SQL> commit;

5. Verify both indexes exist
SQL> select owner, index_name, table_name, table_owner from all_indexes where index_name like 'TEST%';
OWNER | INDEX_NAME | TABLE_NAME | TABLE_OWNER
--------------------------------+------------
FRED | TEST1 | TEST | FRED
SYSTEM | TEST1 | TEST | SYSTEM

2 row(s) returned

6. Drop FRED's index
SQL> drop index test1;
[POL-4005] null class object is not allowed <--- ?????

7. Try it qualified
SQL> drop index fred.test1;
[POL-4005] null class object is not allowed <--- ?????

8. Try SYSTEM's index
SQL> drop index system.test1;

Index dropped

9. Try FRED's again
SQL> drop index test1;
[POL-4005] null class object is not allowed

SQL> drop index fred.test1;
[POL-4005] null class object is not allowed

It seems no matter what I do I can now not drop FRED's index. Can anyone spot a flaw in my process of give an explanation as to what's happening?

Many thanks

Andy