在使用PL/SQL包dbms_flashback做闪回查询时,不能直接使用‘SYS’用户来调用:
SQL> exec dbms_flashback.enable_at_time(systimestamp-interval '15' minute);
BEGIN dbms_flashback.enable_at_time(systimestamp-interval '15' minute); END;
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1
切换成需要进行闪回查询的普通用户,不过在此之前需要对此用户进行授权。如不授权会出现如下错误:
SQL> exec dbms_flashback.enable_at_time(systimestamp-interval '15' minute);
BEGIN dbms_flashback.enable_at_time(systimestamp-interval '15' minute); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_FLASHBACK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
授权:
SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_flashback to scotto;
Grant succeeded.
SQL> conn scotto/scotto;
Connected.
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
注意在闪回会话模式中是不能执行写入操作的(包括DDL和update\delete等)
SQL> update test set username='ha0' where username='ha1';
update test set username='ha0' where username='ha1'
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode