Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are actually querying the data. By default, the only value that is recorded by Oracle for varrays appears to be -1.

begin
dbms_FGA.add_policy (
object_schema => 'GDB',
object_name => 'COUNTRY',
policy_name => 'COUNTRY_Aud',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'SELECT' );
end;
/

The SQL_TEXT and SQL_BIND values that are returned are:

SELECT SHAPE FROM  GDB.COUNTRY WHERE mdsys.sdo_filter(GDB.COUNTRYS.SHAPE, 
MDSYS.SDO_GEOMETRY(:gtype1,:srid1,NULL,:elem_info1,:ordinates1), 'querytype=window') =         
'TRUE' #1(4):2003 #2(4):4326 #3(-1): #4(-1):

I know that I could create a procedure to handle the auditing, but I don't know how to retrieve the actual varray.


I have tried getting the values out of the DBA_HIST_SQLBIND view, but the varray values are always null.

declare
    test mdsys.sdo_elem_info_array;
    anytest sys.anydata;
    rc pls_integer;
begin
    select VALUE_ANYDATA into anytest from DBA_HIST_SQLBIND where SQL_ID='9vu11pguf5gx9' and datatype = 123 and rownum = 1;

    if(anytest is not null) then
        rc := anytest.getcollection(test);
        DBMS_OUTPUT.PUT_LINE('Got it');
    else
        DBMS_OUTPUT.PUT_LINE('Null value');
    end if;
end;
/
share|improve this question
    
The documentation says "for an array, the values recorded are only the last set of bind values" –  FreshPhilOfSO Dec 6 '13 at 12:20
    
@Phil, is that when you bind an array to a single row or when you bind an array to a statement for multiple execution? For example, what I have above vs EXECUTE IMMEDIATE 'UPDATE bulk_collect_test SET object_id = object_id WHERE object_id = :1' USING l_tab(i); –  travis Dec 6 '13 at 18:37
1  
Could you try selecting v$sql_monitor.binds_xml from v$sql_monitor while someone is running it? sql_monitor doesn't capture every sql but when it does, it usually has pretty good capture of binds. Varrays are type struct, so we don't know how internally oracle will represent them. dba_hist_sqlbind is not useful since not all executions have their binds captured (it is by design). You might want to log a SR with oracle support and maybe they can help you. –  Raj Dec 10 '13 at 20:47
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.