Home » RDBMS Server » Server Administration » SQL text history (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
SQL text history [message #681032] Mon, 08 June 2020 15:01 Go to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
if the query or sql no longer exists from these v$ data dictionary views v$sqlarea, v$sqltext, and v$sql. are there any other history views where I can find the query or sql based on the sql_id that we know?

thank you.
Re: SQL text history [message #681033 is a reply to message #681032] Mon, 08 June 2020 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I told you in your previous topic:
SQL> select * from dict where lower(comments) like '%sql%hist%';
TABLE_NAME                     COMMENTS
------------------------------ ------------------------------------------------------------
DBA_HIST_SQLSTAT               SQL Historical Statistics Information
DBA_HIST_SQL_WORKAREA_HSTGRM   SQL Workarea Histogram History
V$SQL_WORKAREA_HISTOGRAM       Synonym for V_$SQL_WORKAREA_HISTOGRAM
GV$SQL_WORKAREA_HISTOGRAM      Synonym for GV_$SQL_WORKAREA_HISTOGRAM
GV$SQL_CS_HISTOGRAM            Synonym for GV_$SQL_CS_HISTOGRAM
V$SQL_CS_HISTOGRAM             Synonym for V_$SQL_CS_HISTOGRAM
Then
SQL> select view_name from dba_views where view_name like 'DBA_HIST%SQL%';
VIEW_NAME
------------------------------
DBA_HIST_COLORED_SQL
DBA_HIST_SQLBIND
DBA_HIST_SQLCOMMAND_NAME
DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_WORKAREA_HSTGRM
Note: DBA_HIST view has under Performances and Tuning paying option.
Re: SQL text history [message #681034 is a reply to message #681033] Mon, 08 June 2020 15:37 Go to previous messageGo to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
thank you Michael. apparently none has the sql_id that we are looking. how long does the sql/query stays in any those v$ views or dba tables?
Re: SQL text history [message #681037 is a reply to message #681034] Tue, 09 June 2020 00:06 Go to previous message
Michel Cadot
Messages: 67297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In V$ until they are aged out from the SGA, so it depends on its size and the instance activity.
In the DBA_HIST until:
select retention from DBA_HIST_WR_CONTROL;
But DBA_HIST does not contain all SQLs only those that have been captured during snapshots and seldom used query may not be there.

Previous Topic: Ora-48181. no space left on the device
Next Topic: How to determine the size of backup increased in last one month
Goto Forum:
  


Current Time: Thu Aug 13 01:11:46 CDT 2020