oracle flashback 闪回

最新推荐文章于 2024-07-25 14:55:31 发布

woshilys

最新推荐文章于 2024-07-25 14:55:31 发布

阅读量303

点赞数 6

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/woshilys/article/details/135763052

版权

本文介绍了Oracle数据库中的关键参数如db_recovery_file_dest和recyclebin，以及归档日志、闪回功能的使用，包括如何查看参数值、归档状态和执行闪回删除操作来恢复表数据。

摘要由CSDN通过智能技术生成

---查看参数
 show parameter recove

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 100M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

--已归档
SYS@orcl1> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DGFRA
Oldest online log sequence     102
Next log sequence to archive   103
Current log sequence           103
SYS@orcl1> 





select *  from a;
3	3
2	2
---2024-01-23 09:05:16  获取当前时间 
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;


insert into a values (4,4);


select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

2024-01-23 09:09:08


---查询之前的时间 可以查询到之前的数据
select *  from a as of timestamp to_timestamp('2024-01-23 09:05:16','yyyy-mm-dd hh24:mi:ss');


3	3
2	2

可以查询到之前的数据

---删除表a
SYS@orcl1> drop table liys.a;

Table dropped.

---回收站是开启的
SYS@orcl1> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on



LIYS@orcl1> col owner for a30
LIYS@orcl1> col object_name for a60
 
LIYS@orcl1> col original_name for a60
select owner,object_name,original_name,type from dba_recyclebin
  2* where original_name ='A'

OWNER                          OBJECT_NAME                                                  ORIGINAL_NAME                                                TYPE
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -------------------------
LIYS                            BIN$D5OpyKTJKorgYx84qMDH/w==$0                               A                                                            TABLE


---闪回删除的表
flashback table "BIN$D5OpyKTJKorgYx84qMDH/w==$0" to before drop ;



flashback table "BIN$D5OpyKTJKorgYx84qMDH/w==$0" to before drop ;

Flashback complete.

LIYS@orcl1> select *  from liys.a;

AH                             AB
------------------------------ ----------
3                              3
4                              4
2                              2