闪回时间查询
SELECT employee_id, salary FROM employees
AS OF TIMESTAMP
WHERE employee_id = 200

select salary from emp as of timestamp
to_timestamp('2014-08-06 14:50:00','yyyy-mm-dd hh24:mi:ss')
;

select systimestamp from dual; 查看当前时间timestamp

查看之前版本：

SELECT versions_xid, salary FROM employees
VERSIONS BETWEEN TIMESTAMP and
WHERE employee_id = 200;

select versions_xid,salary from emp versions between timestamp
to_timestamp('2014-08-06 14:50:00','yyyy-mm-dd hh24:mi:ss')
and systimestamp;

col 字段名 for a30 设置字段显示宽度

闪回表：
ALTER TABLE employees ENABLE ROW MOVEMENT;首先开启行移动，才支持
FLASHBACK TABLE hr.departments TO TIMESTAMP
TO_TIMESTAMP('2007-04-05?21:00:00',
'YYYY-MM-DD?HH24:MI:SS');
用EM闪回

闪回数据归档

CREATE FLASHBACK ARCHIVE fda1
TABLESPACE fda_tbs1 QUOTA 10M RETENTION 1 YEAR;
创建表空间闪回归档

select * from session_privs查看用户拥有权限
where privilege like。。。。

-- create the Flashback Data Archive
CREATE FLASHBACK ARCHIVE DEFAULT fla1
TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR;

-- Specify the default Flashback Data Archive
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

-- Enable Flashback Data Archive
ALTER TABLE inventory FLASHBACK ARCHIVE;
ALTER TABLE stock_data FLASHBACK ARCHIVE;

SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMP TO_TIMESTAMP ('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

ALTER FLASHBACK ARCHIVE fla1
ADD TABLESPACE tbs3 QUOTA 5G;

ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;

ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day);

DROP FLASHBACK ARCHIVE fla1;

FLASHBACK TABLE emp TO TIMESTAMP
TO_TIMESTAMP('2014-08-06 15:25:07',
'YYYY-MM-DD HH24:MI:SS');

ORA-30052: invalid lower limit snapshot expression
//这个是我在实验过程中遇到的一个错误，搞了半天老是报这个错误，后来官方文档读了很久才意识到可能是undo_retention出了问题。察看了一下，只有
900，立马改成10800...后先用scn试了一下，可以后把scn转换成timestamp又试了一下，终于可以了...这个东西可真难用啊!!!

SQL>alter system set undo_retention=10800 scope=both;

SQL> show parameter undo_retention;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800

select * from flashback_transaction_query 闪回事务查询
database add supplemental log data;开启undo_sql记录

闪回事务
基本前提：
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
grant execute on dbms_flashback to hr;
grant select any transaction to hr;

PURGE {TABLE |INDEX }

PURGE TABLESPACE [USER ]

PURGE [USER_|DBA_]RECYCLEBIN
DROP TABLE [PURGE] ;

DROP TABLESPACE [INCLUDING CONTENTS] ;
DROP USER [CASCADE] ;

ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;

SELECT owner, original_name, object_name,
type, ts_name, droptime, related, space
FROM dba_recyclebin
WHERE can_undrop = 'YES';

SQL> SELECT original_name, object_name, ts_name, droptime FROM user_recyclebin WHERE can_undrop = 'YES';

ORIGINAL_NAME OBJECT_NAME TS_NAM DROPTIME
------------- ----------------------- ------ -------------------
EMPLOYEES2 BIN$NE4Rk64w...gbpQ==$0 USERS 2007-07-02:15:45:13

SQL> SHOW RECYCLEBIN

闪回数据库

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER SYSTEM SET
2 DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

create restore point rs_test; 创建还原点

RMAN> FLASHBACK DATABASE TO TIME =
2> "TO_DATE('2009-05-27 16:00:00',
3> 'YYYY-MM-DD HH24:MI:SS')";

RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE
2> TO SEQUENCE=223 THREAD=1;

SQL> FLASHBACK DATABASE
2 TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;

SQL> SELECT estimated_flashback_size,
2 flashback_size 3 FROM V$FLASHBACK_DATABASE_LOG;

SQL> SELECT oldest_flashback_scn,
2 oldest_flashback_time
3 FROM V$FLASHBACK_DATABASE_LOG;

SQL> SELECT *
2 FROM V$FLASHBACK_DATABASE_STAT;

ITPub博客

闪回数据库

最新文章