Flashback table实验

本文主要是介绍Flashback table实验，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

恢复删除的表

SQL> show parameter recyclebin;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> desc foo;Name                                      Null?    Type----------------------------------------- -------- ----------------------------FOO                                                NUMBERSQL> drop table foo;Table dropped.SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FOO              BIN$h2Xc7/57I5TgUwEAAH9MVw==$0 TABLE        2019-04-26:09:29:16
SQL> select object_name, original_name, type from recyclebin;OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
TYPE
-------------------------
BIN$h2Xc7/57I5TgUwEAAH9MVw==$0
FOO
TABLESQL> flashback table foo to before drop;Flashback complete.SQL> show recyclebin;
SQL> select count(*) from foo;COUNT(*)
----------2

时间点恢复

前提：
enable row movement，因为rowid会改变
不能是sys用户

SQL> alter table foo enable row movement;Table altered.
SQL> select current_scn from v$database;CURRENT_SCN
-----------2598378SQL> insert into foo values(3);1 row created.SQL> commit;Commit complete.SQL> flashback table foo until scn 2598378;
flashback table foo until scn 2598378*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS

重新来过，建立用户appuser：

create user appuser identified by oracle;
grant create session, create table to appuser;
alter user appuser quota unlimited on users;

[oracle@oracle-12201-vagrant ~]$ rlwrap sqlplus appuser/oracle@orclpdb1SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 26 09:42:17 2019Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> create table foo (a int);Table created.SQL> alter table foo enable row movement;Table altered.

赋予用户读取scn的权限：

SQL> select table_owner, table_name from all_synonyms where synonym_name like 'V$DATABASE';TABLE_OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SYS
V_$DATABASESQL> grant select on V_$DATABASE to appuser;Grant succeeded.

赋予flash back权限：

SQL> grant flashback on foo to appuser;Grant succeeded.

记录当前SCN:

SQL> show user;
USER is "APPUSER"
SQL> select current_scn from v$database;CURRENT_SCN
-----------2601345

插入数据：

SQL> insert into foo values(1);1 row created.

恢复：

SQL> flashback table foo to scn 2601345;Flashback complete.SQL> select * from foo;no rows selected

期间还碰到一个问题，即temp表空间没有了，如下处理就好：

alter tablespace temp add tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf' size 10M autoextend on;

参考

1. http://rajiboracle.blogspot.com/2014/07/ora-25153-temporary-tablespace-is-empty.html
2. https://stackoverflow.com/questions/752382/oracle-how-to-enable-flashback-as-of-queries-for-all-developers
3. http://www.dba-oracle.com/t_rman_153_flashback_table.htm

这篇关于Flashback table实验的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---