trace queries from abap to a custom oracle database via dblink

[former_member822355]

I' m
connecting to a database by dblink (name magiap).

I
would like to know if somewhere I can trace all the queries from abap to oracle
in this specific session , to dbs ='MAGIAP'.

For istance, i would like that the query
"SELECT "DESPARTY1"


into :v_DESPARTY1


FROM "T040PARTY"


WHERE "CODPARTY" = '305142941' will
be stored some where (in a file??).

I would like that parameters - w_CODPARTY- will be substituted and stored in the trace
file with the value (305142941), as shown in the previous

Here
is the piece of code ..(a very short example of course)..

DATA : dbs LIKE dbcon-con_name,



v_CODPARTY(15),

v_DESPARTY1(60).





data : w_CODPARTY(15) value '305142941'.







dbs = 'MAGIAP'.

TRY.







EXEC SQL.



CONNECT TO :dbs



ENDEXEC.



IF sy-subrc <> 0.



EXEC SQL.



CONNECT TO :dbs



ENDEXEC.



ENDIF.







IF sy-subrc <> 0.

* RAISE err_conn_aea.



ENDIF.







EXEC SQL.



set connection :dbs



ENDEXEC.











EXEC SQL .







SELECT "DESPARTY1"



into :v_DESPARTY1



FROM "T040PARTY"



WHERE "CODPARTY" =
:w_CODPARTY







ENDEXEC.







IF sy-subrc NE 0.

* rc = 4.



ENDIF.







EXEC SQL.



DISCONNECT :dbs



ENDEXEC.





ENDTRY.

[stefan_koehler]

Hi Silvana,

at first V$SQL_BIND_CAPTURE is not a good solution (for details check this blog).

The best way (if you are already on Oracle 11g) to trace only a particular SQL is using the new diagnostic infrastructure. Details about this feature can be found here (Page 28): Oracle Diagnostic Events in 11g

Regards

Stefan

[Former Member]

Hi,

I think that V$SQL_BIND_CAPTURE is still useful. It shows the last executed bind values.

alter session set events '10046 trace name context forever, level 12';

alter system set  “_cursor_bind_capture_interval”=1;

But, I do not recommend to set this interval value over the prductioni because of the performance reasons.

Best regards,

Orkun Gedik

[Former Member]

Hi Silvana,

The SQL statements have been stored in the SQL Cursor Cache, on the database and they will be available until they have been invalidated. You can access the statements at the 'MAGIAP' side and see the last executed queries in the cache.

You can access bind variables by query on the V$SQL_BIND_CAPTURE table, also.

On the other hand, you can activate the trace by the statement, below;

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

Then, the sql statements will be available in the usertrace file. Please note that you should execute and investigate all the statements that I noted above, at the remote side. Plus, as far as I know that it is not able to distinguish the records by the "dblink name". You should check all the statements and try to figure out what queries have been executed remotely.

Best regards,

Orkun Gedik