OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » SQL text history (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL text history [message #681032]

Mon, 08 June 2020 15:01

wtolentino
Messages: 422
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.

Report message to a moderator

Re: SQL text history [message #681033 is a reply to message #681032]

Mon, 08 June 2020 15:13

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I told you in your previous topic:

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: SQL text history [message #681034 is a reply to message #681033]

Mon, 08 June 2020 15:37

wtolentino
Messages: 422
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?

Report message to a moderator

Re: SQL text history [message #681037 is a reply to message #681034]

Tue, 09 June 2020 00:06

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 28 15:22:37 CDT 2025