-
Title
Trying to "Create Script" or use the "Script" tab gives "ORA-00942: table or view does not exist". -
Description
When in the Schema Browser and then selecting "Create Script" or going to "Script" tab on the right-side, Toad gives the following error:
"ORA-00942: table or view does not exist" -
Cause
Oracle privileges or missing table\view.
-
Resolution
RESOLUTION 1:
The Export DDL or 'Create Script' option queries different tables\views in Oracle and there may be additional permissions required to access these system objects.
To review what Toad is running and what is causing the error, enable SQL Tracker while performing the script actions to get an output of where the error occurs:
1. Launch and connect Toad.
2. Enable SQL Tracker by going to Database | Monitor | SQL Tracker.
The SQL Tracker window will open
3. Select the session of 'toad.exe' you are working with and click the green arrow button to start monitoring that session.4. Go back to Toad, and replicate the issue, till it errors out.
5. Go back to SQL Tracker and check what SQL was collected.
The query that causes the error, and the error message itself should display in the log on the Tracker window.To verify the SQL and error, attempt to run the query alone in SQL Plus:
a. If the error occurs then check to see if the table\view exists where the query is invoking.
b. If the table\view exists, please check with your Oracle DBA for proper privileges to access the table\view.
c. If the table\view does not exist, check with your Oracle DBA to re-build the object. -
Additional Information
NOTE 1: Certain settings in the Export DDL will invoke certain queries, it is also possible to un-check certain settings to avoid the error. Example: In the Export DDL window there are options to check or un-check. When generating a script for a USER, there is a 'Grants to the user' option. If enabled it will invoke the SYS.DBA_JAVA_POLICY query: select kind, grantee, type_schema, type_name, name, action, enabled, seq from sys.dba_java_policy where 1=1 and grantee =order by seq; If the error is triggered because the current schema's limited privilege that does not allow access to SYS.DBA_JAVA_POLICY then disabling this option will change the script Toad runs to general script/DDL and this particular query will not be invoked. RESOLUTION 2: 1. Login to the database so user account, such as DBA or SYS, can open a Script tab without the error 2. From here select the "View/edit script options" icon 3. Under Users tab, uncheck the "Grants to the user" checkbox and hit OK Keep in mind, with the above option unchecked, object grants of the user will not be included in the script/DDL generated. Toad version 8.6.1 does not query SYS.DBA_JAVA_POLICY table. This change was added and is in Toad 9.x and newer. NOTE 2. When generating a script for a TABLE, a query is run on SYS.DBA_FLASHBACK_ARCHIVE_TABLES. The current schema will need select on ROLE_TABS role. RESOLUTION 3: Grant select on ROLE_TABS to the current schema.