Tried to display "Script" (DDL) of a table in Toad for Oracle, we saw 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.
Secondly, uncheck "Audit Statements" option.
Now, we can display its DDL without audit related statements. However, if you do care about "Audit statements", you should update your software.
Thank you. This allowed me to get the DDL from my tables in 19c
Good for you!
Tks!!!!!!!!!!!!!!!!!!
You’re welcome!
thank ya
You’re welcome!
Thank you, this worked for me!!!
I’m glad it worked.