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>  select owner, object_name, object_type from dba_objects where object_name = ‘DBA_HIST_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SYS        DBA_HIST_SQLTEXT               VIEW
PUBLIC     DBA_HIST_SQLTEXT               SYNONYM

SQL> select text from dba_views where view_name = ‘DBA_HIST_SQLTEXT’;

TEXT
——————————————————————————–
select dbid, sql_id, sql_text, command_type
from WRH$_SQLTEXT

SQL> select owner, object_name, object_type, object_id, data_object_id  from dba_objects where object_name = ‘WRH$_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
———- —————————— ——————- ———- ————–
SYS        WRH$_SQLTEXT                   TABLE                     8996           8996
=> Not partitioned.

SQL> select min(snap_id), max(snap_id) from wrh$_SQLTEXT;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
3040         3274

We could guess that snap_ids before 3040 were removed but I want to know. (BAAG)  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:

select to_char(savtime,’DD.MM.YYYY’), minimum, maximum, distcnt,  sample_size
from WRI$_OPTSTAT_HISTHEAD_HISTORY where obj# = 8996 and intcol# = 1;
2
TO_CHAR(SA    MINIMUM    MAXIMUM    DISTCNT SAMPLE_SIZE
———- ———- ———- ———- ———–
16.02.2009       2229       2461        114         455
17.02.2009       2296       2535        122         471
18.02.2009       2326       2559        114         469
19.02.2009       2346       2584        119         476
20.02.2009       2370       2607        108         448
21.02.2009       2394       2632        112         437
23.02.2009       2417       2640        104         412
24.02.2009       2471       2703        113         400
25.02.2009       2495       2728         94         394
26.02.2009       2516       2750        101         396
27.02.2009       2536       2776        103         322
28.02.2009       2560       2799         93         310
02.03.2009       2585       2807         88         303
03.03.2009       2637       2871         92         289
04.03.2009       2693       2895         85         287
05.03.2009       2693       2919         91         298
06.03.2009       2704       2943         86         294
09.03.2009       2730       2967         92         303
10.03.2009       2801       3039        104         303
11.03.2009       2836       3063         94         304
12.03.2009       2856       3087         99         303
13.03.2009       2872       3111        101         301
14.03.2009       2896       3135         99         299
16.03.2009       2921       3144         95         292
17.03.2009       2976       3206         94         292
18.03.2009       3003       3232         94         305

26 rows selected.

We can see that at the stats gathering during the last 26 days, every day, the min value increased. So, the answer is yes.