Skip to Main Content
  • Questions
  • Track information using database link

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Antonio.

Asked: June 01, 2022 - 5:53 pm UTC

Last updated: June 14, 2022 - 1:42 pm UTC

Version: 11 g R2

Viewed 1000+ times

You Asked

Hi Tom !

I have 2 databases, A and B. Created a database link from A pointing to B.
Let's call it "dblinkB".

In database B there's a table "mytable" with a trigger, that captures information from the instance using V$SESSION.

I make inserts from A in this table "mytable" like "INSERT INTO MYTABLE@dblinkB VALUES('myvalues');".

I would like to track the database identification from which database this INSERT was made, for logging purposes.

Tried to use SYS_CONTEXT variables, but all the information I receive is concerned to database B.

Thank you !

Antonio


and Chris said...

You can use:

sys_context ( 'USERENV', 'DBLINK_INFO' )


Which, as the docs say:

Returns the source of a database link session. Specifically, it returns a string of the form:

SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid

where:

* dblink_src_global_name is the unique global name of the source database
* dblink_name is the name of the database link on the source database
* dblink_src_audit_sessionid is the audit session ID of the session on the source database that initiated the connection to the remote database using dblink_name


https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.