This is not going to scale.

This statement:

'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED

Is selecting ALL of the rows from T-SI_CHANGED.... before you even insert anything.

So what happens after a month when T_SI_CHANGED has a zillion records and your trigger is run a dozen times to insert some records? It selects the entire T_SI_CHANGED table a dozen times on the Oracle side.

Possible solutions to this:

1. Change it to this, so that no rows need to be selected on the Oracle side

'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED WHERE 1=0

2. Use this instead (which does not even require a select on the Oracle side:

INSERT INTO [VPHIN].owner.T_SI_CHANGED (col1,col2) SELECT COl1, Col2 from INSERTED

However linked servers in general can unreliable, especially to Oracle are unreliable. So if this Oracle insert fails, your trigger fails, and then the change on the SQL Server side failes.

I suggest that you instead write the changes to a log table on the SQL Server side, then use a scheduled job to send over the logged records to the Oracle table in a batch (via SSIS or linked servers). This disconnects the 'data transfer' part from the 'trigger' part, and will stop errors occuring when you insert on the SQL side (due to your trigger failing)