Oracle Tip: v$sql Table or View does not Exist


If we want to analyse 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, 
         sql_id, 
         child_number, 
         sql_text
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"

SQL> GRANT SELECT ANY DICTIONARY TO MY_USER;

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s