Forum Stats

  • 3,839,996 Users
  • 2,262,557 Discussions
  • 7,901,120 Comments

Discussions

Is there a way to capture complete query along with the bind values in oracle ?

Sai Challa
Sai Challa Member Posts: 15
edited Sep 10, 2015 9:08AM in SQL & PL/SQL

I am trying to capture all the queries being executed on a table in the database. These queries will be stored in a table and will be later executed on a different database to keep both the databases synchronized. In the trigger we are using the below select statement to capture the query :

select sq.sql_text from v$open_cursor oc,v$sql sq where sid = sys_context('USERENV','SID') order
by sq.last_active_time desc ;

But, if the queries are being executed as prepared statements then the above query being used is capturing the sql statements as:

UPDATE <TABLE_NAME> SET COL1_NAME = :1   WHERE COL2_NAME =:2   AND COL3_NAME = :3

I am unable to get the values of the above fields in my query. Is there any other way that I can achieve this and get the complete query ?

Tagged:
«1

Answers

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Sep 9, 2015 1:32PM

    you can create a sql trace with event 10046 and a sufficient level: Event 10046 – Full List of Levels | Striving for Optimal Performance. But I would not use this to synchronize databases.

  • Unknown
    edited Sep 9, 2015 1:45PM
    8ae0046f-173b-4759-a637-cef1da3bc23b wrote:
    
    I am trying to capture all the queries being executed on a table in the database. These queries will be stored in a table and will be later executed on a different database to keep both the databases synchronized. In the trigger we are using the below select statement to capture the query :
    
    select sq.sql_text from v$open_cursor oc,v$sql sq where sid = sys_context('USERENV','SID') order
    by sq.last_active_time desc ; But, if the queries are being executed as prepared statements then the above query being used is capturing the sql statements as: UPDATE <TABLE_NAME> SET COL1_NAME = :1   WHERE COL2_NAME =:2   AND COL3_NAME = :3 I am unable to get the values of the above fields in my query. Is there any other way that I can achieve this and get the complete query ?

    There are easier ways to keep table data in synch.

    In fact, I doubt that anyone could devise a more convoluted method than what you are proposing.

    When doing DML against this table, does the result set ever depend upon data contained in any other table?

    If so, exactly how to you ensure that data matches what is/was in the SOURCE database at the time the DML executed?

    I choose not to provide you a bigger shovel to allow you to produce a larger hole faster.

  • Sai Challa
    Sai Challa Member Posts: 15
    edited Sep 10, 2015 5:24AM

    Can you suggest how can we do this through other methods ?

  • Sai Challa
    Sai Challa Member Posts: 15
    edited Sep 10, 2015 5:26AM

    Yes, there are scenarios in which the data in one table may be depending on the others. But, the trigger will be created on all the tables in the schema such that any modifications will be captured and then executed to achieve synchronization. Can you suggest any other methods through which I can achieve this requirement?

  • vijayrsehgal-Oracle
    vijayrsehgal-Oracle Member Posts: 767 Red Ribbon
    edited Sep 10, 2015 7:09AM

    do you want to do it without using products available to do this?  goldengate is one such product which can help you achieve databases in sync.

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Sep 10, 2015 7:55AM

    depends on what you actually try to achieve. Materialized Views using a db link to access the data from the source node could be an option.

  • Sai Challa
    Sai Challa Member Posts: 15
    edited Sep 10, 2015 7:58AM

    Thats right, we need to do it manually without using any products .

  • Sai Challa
    Sai Challa Member Posts: 15
    edited Sep 10, 2015 8:02AM

    Can you elaborate more on this please?

  • sgalaxy
    sgalaxy Member Posts: 5,708 Bronze Trophy
    edited Sep 10, 2015 8:13AM

    Hi,

    Defining auditing in xml extended format :

    http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams017.htm

    Is this acceptable?

    Did you try it?

    Greetings,

    Sim

  • Sai Challa
    Sai Challa Member Posts: 15
    edited Sep 10, 2015 8:44AM

    Please correct me if I am wrong here, I think auditing is going to capture all the records being run in the database. What I want actually is only the modifications(insert/update/delete) on certain database tables in the schema. In such a case would auditing be helpful ?

This discussion has been closed.