Oracle Tip: v$sql Table or View Does Not Exist
Oracle Tip: v$sql Table or View Does Not Exist
Join the DZone community and get the full member experience.
Join For FreeVerify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!
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,
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.
Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue.
Like This Article? Read More From DZone
Published at DZone with permission of Lukas Eder , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}