Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Is there a way to capture complete query along with the bind values in oracle ?

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 =
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 ?
Answers
-
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.
-
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 =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.
-
Can you suggest how can we do this through other methods ?
-
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?
-
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.
-
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.
-
Thats right, we need to do it manually without using any products .
-
Can you elaborate more on this please?
-
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
-
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 ?