Oracle dblink不知源的SQL

发布时间：2020-12-12 16:03:36 所属栏目：百科来源：网络整理

导读：遇到一个奇葩的问题，A通过DBLINK操作B数据库，但一直找不到是哪里执行的。 DELETE FROM "GG_PLAN_PROJECT" "A1" WHERE "A1"."PLAN_PROJECT_ID" = ANY (SELECT "A2"."PLAN_PROJECT_ID" FROM "GG_PLAN_PROJECT"@ ! "A2" WHERE EXISTS (SELECT 1 FROM "GG_PUB_

遇到一个奇葩的问题，A通过DBLINK操作B数据库，但一直找不到是哪里执行的。

DELETE FROM "GG_PLAN_PROJECT" "A1"
WHERE "A1"."PLAN_PROJECT_ID" = ANY
(SELECT "A2"."PLAN_PROJECT_ID"
FROM "GG_PLAN_PROJECT"@ ! "A2"
WHERE EXISTS
(SELECT 1
FROM "GG_PUB_CS"."TOP_USER"@ ! "A3"
WHERE "A3"."USER_ID" = "A2"."CREATOR_ID"
OR "A3"."USER_ID" = "A2"."MODIFIER_ID")
AND ("A2"."MODIFY_TIME" >
TO_DATE('2016-08-30 16:21:26','yyyy-mm-dd hh24:mi:ss') OR
"A2"."CREATE_TIME" >
TO_DATE('2016-08-30 16:21:26','yyyy-mm-dd hh24:mi:ss'))
AND "A2"."DATA_OWN_AREA" LIKE '03%')

找到是哪个用户执行的。
select * from dba_hist_active_sess_history s where s.sql_id='fr0uk254r4z8b';
select * from v$sql s where s.sql_id='fr0uk254r4z8b';
然后找到定义，下面的SQL找不到
select * from user_source s where s.text like '%ANY%';
select * from user_source s where s.text like '%CREATE_TIME%';
找到一个存储过程，但有点不像：
execute immediate 'DELETE FROM ' ||
syn_targer.target_synonym_name || '' ||
NVL(syn_object.another_name,
syn_object.object_name) || '
WHERE ' || syn_object_pk || ' IN
(SELECT ' || syn_object_pk || '
FROM ' ||
syn_object.object_name || '
WHERE ' || syn_object_rule ||
SYN_PUB_USER_CONDITION ||'
AND (MODIFY_TIME > TO_DATE(''' ||
syn_last_time ||
''',''yyyy-mm-dd hh24:mi:ss'')
OR CREATE_TIME > TO_DATE(''' ||
syn_last_time || ''',''yyyy-mm-dd hh24:mi:ss''))
AND ' ||syn_object.object_name || '.DATA_OWN_AREA LIKE ''' ||
SYN_DATA_OWN_AREA || '%'' )';
想了一下，A通过DBLINK访问B，此时SQL其实发生了转换，其实就是这个存储过程执行的。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!