VIEW: SYS.DBA_HIST_SQLBIND

Source

View Text - Preformatted

select snap_id                                                 snap_id,
       dbid                                                    dbid,
       instance_number                                         instance_number,
       sql_id                                                  sql_id,
       name                                                    name,
       position                                                position,
       nvl2(cap_bv, v.cap_bv.dup_position, dup_position)       dup_position,
       nvl2(cap_bv, v.cap_bv.datatype, datatype)               datatype,
       nvl2(cap_bv, v.cap_bv.datatype_string, datatype_string) datatype_string,
       nvl2(cap_bv, v.cap_bv.character_sid, character_sid)     character_sid,
       nvl2(cap_bv, v.cap_bv.precision, precision)             precision,
       nvl2(cap_bv, v.cap_bv.scale, scale)                     scale,
       nvl2(cap_bv, v.cap_bv.max_length, max_length)           max_length,
       nvl2(cap_bv, 'YES', 'NO')                               was_captured,
       nvl2(cap_bv, v.cap_bv.last_captured, NULL)              last_captured,
       nvl2(cap_bv, v.cap_bv.value_string, NULL)               value_string,
       nvl2(cap_bv, v.cap_bv.value_anydata, NULL)              value_anydata,
       con_dbid                                                con_dbid,
       con_dbid_to_id(con_dbid)                                con_id
from
(select sql.snap_id, sql.dbid, sql.instance_number, sbm.sql_id,
        dbms_sqltune.extract_bind(sql.bind_data, sbm.position) cap_bv,
        sbm.name,
        sbm.position,
        sbm.dup_position,
        sbm.datatype,
        sbm.datatype_string,
        sbm.character_sid,
        sbm.precision,
        sbm.scale,
        sbm.max_length,
        decode(sbm.con_dbid, 0, sbm.dbid, sbm.con_dbid) con_dbid
 from   wrm$_snapshot sn, wrh$_sql_bind_metadata sbm, wrh$_sqlstat sql
 where      sn.snap_id         = sql.snap_id
        and sn.dbid            = sql.dbid
        and sn.instance_number = sql.instance_number
        and sbm.sql_id         = sql.sql_id
        and sbm.con_dbid       = sql.con_dbid
        and sn.status          = 0) v

View Text - HTML Formatted

SELECT SNAP_ID SNAP_ID
, DBID DBID
, INSTANCE_NUMBER INSTANCE_NUMBER
, SQL_ID SQL_ID
, NAME NAME
, POSITION POSITION
, NVL2(CAP_BV
, V.CAP_BV.DUP_POSITION
, DUP_POSITION) DUP_POSITION
, NVL2(CAP_BV
, V.CAP_BV.DATATYPE
, DATATYPE) DATATYPE
, NVL2(CAP_BV
, V.CAP_BV.DATATYPE_STRING
, DATATYPE_STRING) DATATYPE_STRING
, NVL2(CAP_BV
, V.CAP_BV.CHARACTER_SID
, CHARACTER_SID) CHARACTER_SID
, NVL2(CAP_BV
, V.CAP_BV.PRECISION
, PRECISION) PRECISION
, NVL2(CAP_BV
, V.CAP_BV.SCALE
, SCALE) SCALE
, NVL2(CAP_BV
, V.CAP_BV.MAX_LENGTH
, MAX_LENGTH) MAX_LENGTH
, NVL2(CAP_BV
, 'YES'
, 'NO') WAS_CAPTURED
, NVL2(CAP_BV
, V.CAP_BV.LAST_CAPTURED
, NULL) LAST_CAPTURED
, NVL2(CAP_BV
, V.CAP_BV.VALUE_STRING
, NULL) VALUE_STRING
, NVL2(CAP_BV
, V.CAP_BV.VALUE_ANYDATA
, NULL) VALUE_ANYDATA
, CON_DBID CON_DBID
, CON_DBID_TO_ID(CON_DBID) CON_ID FROM (SELECT SQL.SNAP_ID
, SQL.DBID
, SQL.INSTANCE_NUMBER
, SBM.SQL_ID
, DBMS_SQLTUNE.EXTRACT_BIND(SQL.BIND_DATA
, SBM.POSITION) CAP_BV
, SBM.NAME
, SBM.POSITION
, SBM.DUP_POSITION
, SBM.DATATYPE
, SBM.DATATYPE_STRING
, SBM.CHARACTER_SID
, SBM.PRECISION
, SBM.SCALE
, SBM.MAX_LENGTH
, DECODE(SBM.CON_DBID
, 0
, SBM.DBID
, SBM.CON_DBID) CON_DBID
FROM WRM$_SNAPSHOT SN
, WRH$_SQL_BIND_METADATA SBM
, WRH$_SQLSTAT SQL
WHERE SN.SNAP_ID = SQL.SNAP_ID
AND SN.DBID = SQL.DBID
AND SN.INSTANCE_NUMBER = SQL.INSTANCE_NUMBER
AND SBM.SQL_ID = SQL.SQL_ID
AND SBM.CON_DBID = SQL.CON_DBID
AND SN.STATUS = 0) V