Skip to content

How to Resolve Toad Error ORA-00904: "REF": invalid identifier

  • by
Toad Error ORA-00904: "REF": invalid identifier

Tried to display "Script" (DDL) of a table in Toad for Oracle, we saw ORA-00904: "REF": invalid identifier.

Toad Error ORA-00904: "REF": invalid identifier

Toad Error ORA-00904: "REF": invalid identifier

Typically, ORA-00904 is an unmatched column issue, it can be thrown in varying use cases. In this case, REF column is the problematic column.

This is because whenever we try to display the DDL of a table in Toad for Oracle, it queries a data dictionary DBA_OBJ_AUDIT_OPTS, but no REF column is found in the database, specifically for Oracle 19c and later releases.

In fact, the obsolete REF column is still existing in release 18c (12.2.0.2), but release 19c (12.2.0.3) has permanently removed it. That's why the feature displays DDL normally in 11g, 12c and 18c, but not in 19c and 21c.

To solve the error, you can stop Toad from querying DBA_OBJ_AUDIT_OPTS by unchecking "Audit statements" option.

Firstly, click on "Script Option" icon on the left side.

Toad - Script Option Icon

Toad - Script Option Icon

Secondly, uncheck "Audit Statements" option.

Toad - Edit Script Option - Uncheck Audit Statements

Toad - Edit Script Option - Uncheck Audit Statements

Now, we can display its DDL without audit related statements. However, if you do care about "Audit statements", you should update your software.

Leave a Reply

Your email address will not be published.