{"id":620,"date":"2009-03-19T18:08:12","date_gmt":"2009-03-19T16:08:12","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=620"},"modified":"2009-03-19T18:08:12","modified_gmt":"2009-03-19T16:08:12","slug":"is-data-in-dba_hist_sqltext-aged-out","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2009\/03\/19\/is-data-in-dba_hist_sqltext-aged-out\/","title":{"rendered":"Is data in DBA_HIST_SQLTEXT aged out?"},"content":{"rendered":"

This question came up during the excellent presentation of Dave Abercrombie – A Tour of the AWR Tables at the Hotsos Symposium 2009.<\/p>\n

Nobody knew the answer but curious as I am, I wanted to know:<\/p>\n

Where is the data stored?<\/strong><\/p>\n

SQL>\u00a0 select owner, object_name, object_type from dba_objects where object_name = ‘DBA_HIST_SQLTEXT’;<\/p>\n

OWNER\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_TYPE
\n———- —————————— ——————-
\nSYS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DBA_HIST_SQLTEXT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VIEW
\nPUBLIC\u00a0\u00a0\u00a0\u00a0 DBA_HIST_SQLTEXT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SYNONYM<\/p>\n

SQL> select text from dba_views where view_name = ‘DBA_HIST_SQLTEXT’;<\/p>\n

TEXT
\n——————————————————————————–
\nselect dbid, sql_id, sql_text, command_type
\nfrom WRH$_SQLTEXT<\/p>\n

SQL> select owner, object_name, object_type, object_id, data_object_id\u00a0 from dba_objects where object_name = ‘WRH$_SQLTEXT’;<\/p>\n

OWNER\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_ID DATA_OBJECT_ID
\n———- —————————— ——————- ———- ————–
\nSYS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WRH$_SQLTEXT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TABLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8996\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8996
\n=> Not partitioned.<\/p>\n

SQL> select min(snap_id), max(snap_id) from wrh$_SQLTEXT;<\/p>\n

MIN(SNAP_ID) MAX(SNAP_ID)
\n———— ————
\n3040\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3274<\/p><\/blockquote>\n

We could guess that snap_ids before 3040 were removed but I want to know. (BAAG)\u00a0 I could monitor the table over a period of time and check min(snap_id) but I can also query the backed up object statistics, specifically the minimum value for column snap_id:<\/p>\n

select to_char(savtime,’DD.MM.YYYY’), minimum, maximum, distcnt,\u00a0 sample_size
\nfrom WRI$_OPTSTAT_HISTHEAD_HISTORY where obj# = 8996 and intcol# = 1;
\n2
\nTO_CHAR(SA\u00a0\u00a0\u00a0 MINIMUM\u00a0\u00a0\u00a0 MAXIMUM\u00a0\u00a0\u00a0 DISTCNT SAMPLE_SIZE
\n———- ———- ———- ———- ———–
\n16.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2229\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2461\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 114\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 455
\n17.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2296\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2535\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 122\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 471
\n18.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2326\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2559\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 114\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 469
\n19.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2346\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2584\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 119\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 476
\n20.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2370\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2607\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 108\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 448
\n21.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2394\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2632\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 112\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 437
\n23.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2417\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2640\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 104\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 412
\n24.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2471\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2703\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 113\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 400
\n25.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2495\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2728\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 394
\n26.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2516\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2750\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 101\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 396
\n27.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2536\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2776\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 103\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 322
\n28.02.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2560\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2799\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 93\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 310
\n02.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2585\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2807\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 88\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 303
\n03.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2637\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2871\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 92\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 289
\n04.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2693\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2895\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 85\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 287
\n05.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2693\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2919\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 91\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 298
\n06.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2704\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2943\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 86\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 294
\n09.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2730\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2967\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 92\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 303
\n10.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2801\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3039\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 104\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 303
\n11.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2836\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3063\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 304
\n12.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2856\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3087\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 303
\n13.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2872\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 101\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 301
\n14.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2896\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3135\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 299
\n16.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2921\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3144\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 95\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 292
\n17.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2976\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3206\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 292
\n18.03.2009\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3003\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3232\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 305<\/p>\n

26 rows selected.<\/p><\/blockquote>\n

We can see that at the stats gathering during the last 26 days, every day, the min value increased. So, the answer is yes.<\/p>\n


\n<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

This question came up during the excellent presentation of Dave Abercrombie – A Tour of the AWR Tables at the Hotsos Symposium 2009. Nobody knew the answer but curious as I am, I wanted to know: Where is the data stored? SQL>\u00a0 select owner, object_name, object_type from dba_objects where object_name = ‘DBA_HIST_SQLTEXT’; OWNER\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_TYPE […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-620","post","type-post","status-publish","format-standard","hentry","category-10g"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/620","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=620"}],"version-history":[{"count":4,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/620\/revisions"}],"predecessor-version":[{"id":624,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/620\/revisions\/624"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}