History table helper functions

Within the history tables, several of the columns contain data that use mapped values and bit masks. You can use the following helper functions within your history queries to return more readable text values and strings for those internal values:
Function Description Return value
FORMAT_QUERY_STATUS() Use this function to display text string versions of the $hist_query_epilog.status column data. One of the following status values:
  • success
  • aborted
  • canceled
  • failed parsing
  • failed rewrite
  • failed planning
  • failed execution
  • permission denied
  • failed
  • transaction aborted
FORMAT_PLAN_STATUS() Use this function to display text string versions of the $hist_plan_epilog.status column data. One of the following status values:
  • success
  • aborted
FORMAT_TABLE_ACCESS() Use this function to display text string versions of all bits set in the $hist_table_access.usage column data. A comma-separated list of one or more of the following values:
  • sel
  • ins
  • del
  • upd
  • drp
  • trc
  • alt
  • crt
  • lck
  • sts
FORMAT_COLUMN_ACCESS() Use this function to display text string versions of all bits set in the $hist_column_access.usage column data. A comma-separated list of one or more of the following values:
  • sel
  • set
  • res
  • grp
  • hav
  • ord
  • alt
  • sts
The following sample query shows how you can use these helper functions.
SELECT
    substr (querytext, 1, 50) as QUERY,
    format_query_status (status) as status,
    tb.tablename, 
    format_table_access (tb.usage),
    co.columnname, 
    format_column_access (co.usage)

from  "$hist_query_prolog_3" qp
        inner join
    "$hist_query_epilog_3"  qe    using (npsid, npsinstanceid, opid)
        inner join       
    "$hist_table_access_3"  tb      using (npsid, npsinstanceid, opid)
        inner join       
    "$hist_column_access_3"  co      using (npsid, npsinstanceid, opid)

where
  exists (select tb.dbname 
    from   "$hist_table_access_3" tb
    where tb.npsid = qp.npsid and 
        tb.npsinstanceid = qp.npsinstanceid and
        tb.opid = qp.opid and 
        tb.tablename in (^nation^, ^orders^, ^part^,
                         ^partsupp^, ^supplier^, ^lineitem^,
                         ^region^))
  and tb.tableid = co.tableid;