with t1(tm) as
(select systimestamp from dual
)
select tm as timedate_with_diff_against_UTC
      ,cast(to_timestamp_tz(tm, 'dd-mon-yy hh24.mi.ss.ff TZH:TZM') as date) as into_date
      ,(select dbtimezone from dual) as diff_against_GMT      
       --dbtimezone info is selected from remote/target db:
      ,cast(to_timestamp_tz(tm, 'dd-mon-yy hh24.mi.ss.ff TZH:TZM') at time zone ((select dbtimezone from dual)-(select dbtimezone from dual@remote_db)) 
         as date) as dynamic_TZ
  from t1;

select insertion_date at time zone dbtimezone from table_xyz;

create or replace function mytime return timestamp with time zone
is
begin
  return systimestamp;
end;
/

select mytime@database_b from dual;

select systimestamp at time zone 'utc' from dual;