如何在oracle数据库中检索更新的脚本

Question

如何在oracle数据库中检索更新的脚本？oracle数据库中运行了一个不正确的脚本。我想把这个剧本拿回来。我该怎么做？脚本如下。

--52 record
update cons.paymentplan_detail         det set det.payment_date = to_char(ADD_MONTHS(to_Date (det.payment_date, 'YYYYMMDD'),3),'YYYYMMDD')
where det.oid in (select det.oid
from cons.instalment_instalment_loan ins,
cons.paymentplan_detail         det,
cons.paymentplan_def            def,
(select ins.loans_reference_no,min(det.payment_date) as payment_date
from cons.instalment_instalment_loan ins,
cons.paymentplan_detail         det,
cons.paymentplan_def            def
where ins.loan_state in ('OPEN', 'BLOCKED')
and ins.status = '1'
and def.status = '1'
and det.status = '1'
and ins.payment_plan_oid = det.payment_plan_oid
and det.plan_detail_status  in ('NO','LT')
and ins.OID='251pfek38q43fg00'
and def.oid = det.payment_plan_oid
and def.payment_plan_status = 'GR'
and def.credit_oid = ins.oid
group by ins.loans_reference_no) t
where ins.loan_state in ('OPEN', 'BLOCKED')
and ins.status = '1'
and def.status = '1'
and det.status = '1'
and ins.payment_plan_oid = det.payment_plan_oid
and det.plan_detail_status  in ('NO')
and ins.OID='251pfek38q43fg00'
and def.oid = det.payment_plan_oid
and def.payment_plan_status = 'GR'
and def.credit_oid = ins.oid
and t.loans_reference_no = ins.loans_reference_no
group by det.oid);

Answer 1

如果您还没有COMMIT处理数据，则使用ROLLBACK。

否则，您的解决方案可能很简单，只需运行相同的查询，但减去3个月：

update cons.paymentplan_detail det
set det.payment_date = to_char(
ADD_MONTHS(
to_Date (det.payment_date, 'YYYYMMDD'),
-3             -- Changed from +3 to -3
),
'YYYYMMDD'
)
where ...

如果这不起作用，并且您已经COMMIT处理了数据，并且数据库已经激活了闪回，那么您可以使用闪回查询来查找UPDATE之前的旧数据。

如果所有这些都失败了，那么您可以查看数据库是否有可以从中恢复的最新备份。