Thursday, May 19, 2011

ORA-31608 when creating DDL statement in SQL Developer

Always check out the original article at for latest comments, fixes and updates.

If you are getting ORA-31608 when attemping to display the DDL for a table in SQL Developer 3.0, 2.1 and probably earlier, you should make sure that the table is not a materialized view.

ORA-31608: specified object of type INDEX not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 2808
ORA-06512: at "SYS.DBMS_METADATA", line 4394
ORA-06512: at line 1

In SQL Developer materialized view tables show up in the table collection as ordinary tables, so you might be tempted to export the DDL or display the SQL code from there, however the request will fail with the error shown above reported in SQL Developer's error log.

The operation will succeed if you move to the materialized view node and you open the object from there.
This is why it's always a good practice to use a naming standard for tables that allows you to quickly distinguish between special tables and ordinary ones (I usually prepend the MV prefix to the object name), indeed it helped me to figure out very fast what was wrong, imagine if I had to understand it from the error message!

Note also that by default SQL Developer includes materialized view in the list, but you can filter them out by modifying the default filter as I did shortly after (right click on the Tables node and then on "apply filter" from the pop-up menu).

See message translations for ORA-31608 and search additional resources.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles