Over a million developers have joined DZone.

Oracle Tip: v$sql Table or View Does Not Exist

DZone's Guide to

Oracle Tip: v$sql Table or View Does Not Exist

· Java Zone ·
Free Resource

Take 60 minutes to understand the Power of the Actor Model with "Designing Reactive Systems: The Role Of Actors In Distributed Architecture". Brought to you in partnership with Lightbend.

If we want to analyze execution plans on the SQL console, we probably need to find a SQL_ID first, which we can the pass to the DBMS_XPLAN.DISPLAY_CURSOR function. One way to find this SQL_ID is by querying the v$sql table first, e.g.:

SELECT   last_active_time,
FROM     v$sql
WHERE    upper(sql_fulltext) LIKE '%SOME_SQL_TEXT%'
ORDER BY last_active_time DESC;

Now, often, you will then get an error message like:

ORA-00942: table or view does not exist

This simply means that you do not have the required privileges to select from that table. Have your DBA give you the required grant, or do it yourself on your local Oracle instance with:

C:\> sqlplus "/ as sysdba"


Grant succeeded

Done. You can now query v$sql

More details about how to analyse execution plans on the SQL console can be seen here.

Learn how the Actor model provides a simple but powerful way to design and implement reactive applications that can distribute work across clusters of cores and servers. Brought to you in partnership with Lightbend.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}