6

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

13

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.

6

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
-1
SELECT table_name FROM dba_tables WHERE iot_type IS NOT NULL;
  • How is your answer different than others? – nanosoft Mar 3 '17 at 10:52
-1
SELECT table_name FROM dba_tables WHERE iot_type IS NOT NULL;
  • How is your answer different than others? – nanosoft Mar 3 '17 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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