19c 闪回的局限性能
###sample 1 db_flashback_retention_target 保留时间
3、配置闪回保留时间
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
db_flashback_retention_target参数用来控制flashback log 数据保留的时间，默认值是1440，单位是minute，即24小时。
————————————————
版权声明：本文为CSDN博主「酷毙的我啊」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_35578748/article/details/113090463
select * from flashback_transaction_query a where a.table_name='DEPT';
附注：interval用法
Oracle语法:
INTERVAL '{ integer | integer time_expr | time_expr }' { { DAY | HOUR | MINUTE } [ ( leading_precision ) ] | SECOND [ ( leading_precision
[, fractional_seconds_precision ] ) ] } [ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.
INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND
表示: 20天 - 240小时 = 10天0秒
INTERVAL '30.12345' SECOND(2,4)
表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入.
查询tableA中10分钟前的数据(闪回查询，前提：undo没有被覆盖..如果undo被覆盖,查询会失败)
Select * From table dept As Of Timestamp (systimestamp - Interval '10' minute);
————————————————
版权声明：本文为CSDN博主「酷毙的我啊」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_35578748/article/details/113090463
###sample 2 只能在cdb 级别查询 dba_resource , pdb 查询dba_resource 会报告没有权限的报错，即便授权了
SQL> grant select on dba_source to dba;
SQL> grant select on dba_source to ｄｂａ container=all;
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
When using flash back query it works on a custom user table, but when querying dba_source or all_source it returns the current data not matter what timestamp or SCN is used.
The problem happens in CDB/PDB Multitenant environment
The problem does not happen in non-CDB environment.
Also the issue does not happen for normal tables (user tables) even in Multitenant environment. So the problem exists for SQLs using dictionary views in PDB.
There isn't any restriction for flashback queries in CDB/PDB after restriction was lifted from 12.1.0.2.
In CDB, the plan goes for base tables than the extended data link.
CAUSE
The problem happens with the extended data link table used for accessing dictionary views (DBA_/ALL_) from PDB.
The flashback is not enabled for extended data link tables for dictionary views when accessed from PDBs. This is due to some limitation.
When it is executed from CDB, all base tables like USER$ etc are accessed and flashback is enabled for that and gives expected results as of timestamp or SCN.
SOLUTION
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Disabling the common data view mechanism gives the expected output for the SQL using DBA_SOURCE from PDB.
alter session set "_common_data_view_enabled"=false;
Note: You can also use hint as below to disable common data view.
i.e.
FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE OWNER='<Owner_Name>' and NAME= '<Package_Name>' and type = 'PACKAGE BODY'
ORDER BY LINE;
PDB 级别这样查询，修改参数
alter session set "_common_data_view_enabled"=false;
SELECT /*+ opt_param('_common_data_view_enabled','false') */ COUNT(*)
FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1440' MINUTE)
where owner = 'IG' and name = 'PR_EAST_INIT' and type = 'PACKAGE BODY'
ORDER BY LINE;
SQL>
SELECT /*+ opt_param('_common_data_view_enabled','false') */ COUNT(*)
FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1440' MINUTE)
where owner = 'IG' and name = 'PR_EAST_INIT' and type = 'PACKAGE BODY'
4 ORDER BY LINE;
COUNT(*)
----------
0
SQL> select text from dba_source as of timestamp to_timestamp('2022-12-05 11:30:00', 'YYYY-MM-DD HH24:MI:SS') where owner = 'IGR' and name = 'PR_' order by line;
select text from dba_source as of timestamp to_timestamp('2022-12-05 11:30:00', 'YYYY-MM-DD HH24:MI:SS') where owner = 'IGRdd' and name = 'PR_' order by line
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
或者
SQL> SELECT /*+ opt_param('_common_data_view_enabled','false') */ COUNT(*)
2 FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' DAY)
3 where owner = 'IGRdd' and name = 'PR_EAST_I1' and type = 'PROCEDURE'
4 ORDER BY LINE;
COUNT(*)
----------
384
SQL> SELECT /*+ opt_param('_common_data_view_enabled','false') */ text
2 FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' DAY)
3 where owner = 'IGRdd' and name = 'PR_EAST_I1' and type = 'PROCEDURE'
4 ORDER BY LINE;
TEXT
--------------------------------------------------------------------------------
PROCEDURE PR_EAST_I1 (
p_i_date IN VARCHAR2,
p_o_result OUT VARCHAR2
)
IS
########sample 2
oracle赋值问题（将同一表中某一字段赋值给另外一个字段的语句）
https://www.bbsmax.com/topic/oracle%E5%B0%86%E5%90%8C%E4%B8%80%E4%B8%AA%E8%A1%A8%E7%9A%84%E5%AD%97%E6%AE%B5%E5%A4%8D%E5%88%B6%E5%88%B0%E5%8F%A6%E4%B8%80%E4%B8%AA%E5%AD%97%E6%AE%B5/
update jxc_ckmx ckmx1 set ckmx1.ddsl = (select ckmx2.sl from jxc_ckmx ckmx2 where ckmx2.id = ckmx1.id);
