Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on Oracle 11g database and I need to list all index-organized tables. Is there a dba's view to query for that ?

Thanks

share|improve this question
    
Thanks all for your help –  tdaget Mar 13 at 12:17
add comment

4 Answers

up vote 6 down vote accepted

Here is the query you require:

SELECT TABLE_NAME 
FROM DBA_TABLES
WHERE IOT_TYPE IS NOT NULL; 

The documentation states:

If this is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If this is not an index-organized table, then IOT_TYPE is NULL.

share|improve this answer
add comment

If you lack DBA access (and responsibility for tables you lack access to) Query ALL_TABLES checking if IOT_TYPE is not null, otherwise query DBA_TABLES where IOT_TYPE is not null.

Select table_name FROM ALL_TABLES where IOT_TYPE is not null

Select table_name FROM DBA_TABLES where IOT_TYPE is not null
share|improve this answer
add comment
SELECT table_name FROM dba_tables WHERE iot_type IS NOT NULL;
share|improve this answer
add comment
SELECT table_name FROM dba_tables WHERE iot_type IS NOT NULL;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.