CODEWITHSUNDEEP
sqloracle-database
Benji
Benji

Reputation: 635

Problems viewing indexes

I've created a very simple index and now I just want to display the information of the index but I can't be able to do it for some reason... I know I have done it before but now I can't manage to. I've tried different methods but I just get errors (Invalid SQL statement). What I'm looking for is a view that is the same as I get when I do DESC Test_Table which like this:

enter image description here

I'm using Oracle Application Express.

The index I've created looks like this:

CREATE UNIQUE INDEX Test_Index ON Test_Table(Attribute1, Attribute2)

I tried these different methods to display the index but none of them works

SELECT Test_Index FROM dba_indexes WHERE table_name='Test_Table';

"Table or view does not exist"

exec sp_helpindex Test_Table

"Invalid SQL statement"

SELECT * FROM sysindexes WHERE id = object_id('Test_Table')

"Table or view does not exist"

Why am I getting these errors?

Upvotes: 1

Views: 1720

Answers (1)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Seems like you don't have privileges to view the dba_indexes. sysindexes don't exist on Oracle, only on SQL Server(from what I gather, anyway - I'm not versed in SQL Server)

The exec thing also is a SQL Server hangover.

Try querying user_indexes table –

select index_name from user_indexes
where table_name= 'TEST_TABLE'

Note that SQL is case sensitive whenever something is quoted, so if you pass table name as 'Test_Table' it is likely to fail

Upvotes: 2

Related Questions