ORA-31608 when creating DDL statement in SQL Developer

Always check out the original article at http://www.oraclequirks.com 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.

ORA-12008: error in materialized view refresh path

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may incur in this exception when attempting to refresh a materialized view using procedure DBMS_VIEW.REFRESH, but i guess that also autorefreshing snapshots may suffer the same problem, although i could not verify this. ORA-12008 is a catch-all exception that is followed in the error stack by the real error message that was raised during the execution of the underlying query, in the following example ORA-01476.

begin
dbms_mview.refresh('VIEW_NAME','C');
end;

ORA-12008: error in materialized view refresh path
ORA-01476: divisor is equal to zero

Typically materialized views are not trivial queries so, in order to find out the exact spot that is giving troubles, you might have to run the query interactively. In my case the problem must be caused by a division expression, so i identified all the expressions containing a division and then i selectively commented them out, until i found the "culprit".
Thereafter i had to understand why an expression that is never zero became zero, but this was an entirely different exercise.

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

ORA-23401: materialized view OWNER.MVIEW does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see the error

ORA-23401: materialized view OWNER.MVIEW does not exist

when you execute a procedure like:

begin
dbms_mview.refresh('OWNER.MVIEW','C');
end;

or

begin
dbms_mview.refresh('MVIEW','C');
end;

Clearly there can be a variety of reasons, with the most trivial one being that you mistyped the materialized view name. Please note that if the object was created with a case sensitive name, you must take care of writing the name using the proper casing:

Suppose that you create a materialized view with a name like this:

create materialized view "Ugly_Name"
refresh complete on demand
with rowid
as
select * from dual;

If you execute:

begin
dbms_mview.refresh('Ugly_Name','C');
end;

ORA-23401: materialized view "USER"."UGLY_NAME" does not exist

bacause you need to surround the name with double quotes:

begin
dbms_mview.refresh('"Ugly_Name"','C');
end;

Note that if you are trying to do this on a materialized view residing in a different schema, you may get ORA-23401 if you omit entirely or use an improper letter casing for the schema name:

begin
dbms_mview.refresh('OTHER_USER."Ugly_Name"','C'); -- it should be "Other_User"
end;

or even if the user name is case sensitive and you mistype it...

begin
dbms_mview.refresh('"Other_user"."Ugly_Name"','C'); -- it should be "Other_User"
end;

But, this is not enough, in order to succeed you must hold the ALTER ANY MATERIALIZED VIEW system privilege, otherwise you'll get ORA-01031 eventually.

See message translations for ORA-23401 and search additional resources