Home » RDBMS Server » Server Administration » In all_indexes but not in all_objects (Oracle 8i)
In all_indexes but not in all_objects [message #279040] Tue, 06 November 2007 12:58 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Is it normal for items to be in all_indexes, but not be in all_objects? Is this an 8i quirk thing? Or possibly a permissions thing? Anyone heard of this?

It is like entries are missing from all_objects. I've got object_id's in a 10046 trace file that aren't found in all_objects at all. Yet they are indexes in all_indexes.
Re: In all_indexes but not in all_objects [message #279046 is a reply to message #279040] Tue, 06 November 2007 13:20 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I just tried a few 10g databases and blew some dust off of an 8i and 7.3 database and in no case was the any item in ALL_INDEXES not in ALL_OBJECTS.

Maybe somebody did some bad things with the data dictionary at one time.

What if you rebuild the index, will it then show up in ALL_OBJECTS?
Re: In all_indexes but not in all_objects [message #279052 is a reply to message #279040] Tue, 06 November 2007 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible if you have a privilege on the table (for instance select).
Then you can see the index in all_indexes (because you have a privilege on the table) but you cannot see it in all_objects (because you have no privilege on the index).

Regards
Michel
Re: In all_indexes but not in all_objects [message #279055 is a reply to message #279052] Tue, 06 November 2007 14:40 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Michel Cadot wrote on Tue, 06 November 2007 13:57

It is possible if you have a privilege on the table (for instance select).
Then you can see the index in all_indexes (because you have a privilege on the table) but you cannot see it in all_objects (because you have no privilege on the index).

Regards
Michel



It is? How do you grant permission to an index?

Also, I'm not in a position to rebuild these indexes...but, I will check with the DBAs if there is anything weird going on...
Re: In all_indexes but not in all_objects [message #279126 is a reply to message #279055] Wed, 07 November 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't grant permissions on index and you will not have them in all_objects.

There is nothing weird, it is the expected behaviour.

Regards
Michel
Re: In all_indexes but not in all_objects [message #279244 is a reply to message #279126] Wed, 07 November 2007 08:12 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Michel is correct.

Sily me yesterday, I was checking for the existence of an index name not in all_objects, but I was doing it from a schema with DBA privilege. Doh!
Re: In all_indexes but not in all_objects [message #279247 is a reply to message #279244] Wed, 07 November 2007 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, DBA => SELECT ANY TABLE => you can see all indexes in all_objects.

Regards
Michel
Re: In all_indexes but not in all_objects [message #279467 is a reply to message #279040] Thu, 08 November 2007 11:21 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks...I too was forgetting the select any table priv.

But also, it looks like even if a user does have select granted directly on a table that is owned by another user, but has no special system privs, that user stil can't see the index in all_objects, even though they can see it in all_indexes, and can see the table with describe etc.

Which seems a little strange to me, but I guess that is just how it works. The following is on a 10gR2 db, so nothing 8i related.

MYDBA@orcl >
MYDBA@orcl > create user a identified by a;

User created.

MYDBA@orcl > create user b identified by b;

User created.

MYDBA@orcl > grant create session to a,b;

Grant succeeded.

MYDBA@orcl > grant create table to a,b;

Grant succeeded.

MYDBA@orcl > grant unlimited tablespace to a,b;

Grant succeeded.

MYDBA@orcl >
MYDBA@orcl > connect b/b
Connected.

Session altered.

B@orcl > create table b_table(id number not null primary key);

Table created.

B@orcl > grant select on b_table to a;

Grant succeeded.

B@orcl >
B@orcl > connect a/a
Connected.

Session altered.

A@orcl > create table a_table(id number not null primary key);

Table created.

A@orcl >
A@orcl > desc b.b_table
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ---------
 ID                                                          NOT NULL NUMBER

A@orcl >
A@orcl > select owner, count(*) from all_indexes where owner in ('A','B')
  2  group by rollup(owner);

OWNER                            COUNT(*)
------------------------------ ----------
A                                       1
B                                       1
                                        2

A@orcl >
A@orcl > select owner, count(*) from all_objects where owner in ('A','B')
  2  and object_type = 'INDEX' group by rollup(owner);

OWNER                            COUNT(*)
------------------------------ ----------
A                                       1
                                        1

A@orcl >
A@orcl > connect mydba/orcl
Connected.

Session altered.

MYDBA@orcl > grant select_catalog_role to a;

Grant succeeded.

MYDBA@orcl >
MYDBA@orcl > connect a/a
Connected.

Session altered.

A@orcl > select owner, count(*) from all_objects where owner in ('A','B')
  2  and object_type = 'INDEX' group by rollup(owner);

OWNER                            COUNT(*)
------------------------------ ----------
A                                       1
                                        1

A@orcl >
A@orcl > connect mydba/orcl
Connected.

Session altered.

MYDBA@orcl > revoke select_catalog_role from a;

Revoke succeeded.

MYDBA@orcl > grant select any table to a;

Grant succeeded.

MYDBA@orcl >
MYDBA@orcl > connect a/a
Connected.

Session altered.

A@orcl > select owner, count(*) from all_objects where owner in ('A','B')
  2  and object_type = 'INDEX' group by rollup(owner);

OWNER                            COUNT(*)
------------------------------ ----------
A                                       1
B                                       1
                                        2

A@orcl >
A@orcl > connect mydba/orcl
Connected.

Session altered.

MYDBA@orcl > drop user a cascade;

User dropped.

MYDBA@orcl > drop user b cascade;

User dropped.

MYDBA@orcl >
MYDBA@orcl > set echo off

Re: In all_indexes but not in all_objects [message #279554 is a reply to message #279467] Fri, 09 November 2007 00:35 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said and this is the expected behaviour.

Michel Cadot wrote on Tue, 06 November 2007 20:57

It is possible [to see the index in all_indexes and not in all_objects] if you have a privilege on the table (for instance select).
Then you can see the index in all_indexes (because you have a privilege on the table) but you cannot see it in all_objects (because you have no privilege on the index).


Regards
Michel
Previous Topic: spfile vs pfile.
Next Topic: Can't initialize OCI
Goto Forum:
  


Current Time: Thu Dec 08 12:23:30 CST 2016

Total time taken to generate the page: 0.15344 seconds