| In all_indexes but not in all_objects [message #279040] |
Tue, 06 November 2007 12:58  |
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  |
joy_division
Messages: 4963
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 #279055 is a reply to message #279052] |
Tue, 06 November 2007 14:40 |
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 #279467 is a reply to message #279040] |
Thu, 08 November 2007 11:21 |
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 |
 |
Michel Cadot
Messages: 68686
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
