CODEWITHSUNDEEP
oracle-database
Nidheesh
Nidheesh

Reputation: 4562

Count from USER_INDEXES and ALL_INDEXES

I was trying to execute the following query to get the count of index from my schema.

select count(*) from USER_INDEXES; --which gave me a count of 397

But select count(*) from ALL_INDEXES where table_owner ='MY_SCHEMA'; -- gave me 357.

What it sounds? Both should be same right?

When checked from Oracle SQL developer by counting the indexes myself, gave me 397

Upvotes: 9

Views: 12881

Answers (1)

Dba
Dba

Reputation: 6639

This is because, the view ALL_INDEXES contains all the indexes the current user has the ability to modify. You will not see the LOB index in this view because LOB indexes cannot be renamed, rebuilt, or modified.

While USER_INDEXES view contains all the indexes that the user owns. The LOB index will be in this view if the user querying it is the same user that created it.

Upvotes: 10

Related Questions