标题: [原创] flashback非完全手册 [打印本页]
作者: xiaodong_1567 时间: 2008-6-25 10:51 标题: flashback非完全手册
大部分是书上，少量自己整理
欢迎大家多提意见
附件:
flashback.pdf (2008-6-25 10:51, 127.07 KB) / 该附件被下载次数 389
http://www.itpub.net/attachment.php?aid=538260
作者: xiaodong_1567 时间: 2008-6-25 11:03
相关参数
archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 10
下一个存档日志序列 12
当前日志序列 12
show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------
db_recovery_file_dest string E:OracleFilesflash_recovery_area
db_recovery_file_dest_size big integer 6000M
db_recovery_file_dest 闪回区目的地
db_recovery_file_dest_size 闪回区大小
show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 360
db_flashback_retention_target 闪回日志保留时间，期望值最终取决于闪回区中的闪回数据（单位：分钟，缺省值为1440，也就是一天）
如果闪回区空间不足会覆盖所记录的最早的闪回日志
注：以上参数是实现数据库级闪回的必要条件
作者: xiaodong_1567 时间: 2008-6-25 11:03
相关操作
启动闪回日志
shutdown immediate
startup mount
alter database flashback on;
alter database open;
select flashback_on from v$database;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
如果要进行数据库级闪回，必须启动闪回日志
col program format a18
col pid format 99
select program,pid,spid,background,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem
from v$process where program like '%RVWR%';
PROGRAM PID SPID B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------ --- ------------ - ------------ ------------- ---------------- -----------
ORACLE.EXE (RVWR) 15 3288 1 209757 361709 0 2852077
后台进程多了个RVWR（Reacvery Wirter）
select * from v$sgastat where name like '%flashback%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool flashback generation buff 3981204
SGA中自动分配了闪回缓冲区
select name,value from v$sysstat where name like '%flashback log%';
NAME VALUE
---------------------------------------------------------------- ----------
flashback log writes 152
闪回日志的写的次数
闪回数据库
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
select sysdate from dual;
drop table t1;
truncate table t2;
SQL> select sysdate from dual;
SYSDATE
-------------------
2008-06-19 12:32:01
conn / as sysdba
shutdown immediate;
startup mount;
flashback database to timestamp(to_date('2008-06-19 12:32:01','yyyy-mm-dd hh24:mi:ss'));
alter database open resetlogs;
select count(*) from t1;
select count(*) from t2;
SQL> select count(*) from t1;
COUNT(*)
----------
49834
SQL> select count(*) from t2;
COUNT(*)
----------
49835
闪回数据库，类似不完全恢复，但由于它只是应用最近的变化的数据以及REDO，比起非完全恢复快得多
不过如果数据文件物理损坏，使用此方法没法恢复
注：如果不知道确切时间，可以多次FLASHBACK，打开数据库用READ ONLY，最终确定了时间才OPEN RESETLOGS
flashback database to timestamp(to_date('2008-06-19 12:32:01','yyyy-mm-dd hh24:mi:ss'))
Thu Jun 19 12:36:43 2008
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 2 processes
Thu Jun 19 12:36:48 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 12 Reading mem 0
Mem# 0 errs 0: E:ORACLEFILESDATABASEFILESWWWREDO02.LOG
Thu Jun 19 12:36:53 2008
Incomplete Recovery applied until change 1156940
Flashback Media Recovery Complete
Thu Jun 19 12:37:03 2008
以上是警报日志的记录，整个闪回数据库花费了才20秒
drop table t1 purge;
drop table t2 purge;
作者: xiaodong_1567 时间: 2008-6-25 11:04
闪回表
闪回删除
select object_name,original_name,type from user_recyclebin;
select * from dba_free_space where tablespace_name='AAA';
create table drop_test (x number,y number);
create index drop_test_idx on drop_test(x);
insert into drop_test values(1,1);
insert into drop_test values(3,3);
insert into drop_test values(6,6);
commit;
drop table drop_test;
select object_name,original_name,type from user_recyclebin;
select * from dba_free_space where tablespace_name='AAA';
flashback table drop_test to before drop;
select object_name,original_name,type from user_recyclebin;
SQL> select object_name,original_name,type from user_recyclebin;
未选定行
SQL> select * from dba_free_space where tablespace_name='AAA';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
AAA 7 9 983040 120 7
SQL> select object_name,original_name,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$+myOPKc/RqWWiUQCEcXq2w==$0 DROP_TEST_IDX INDEX
BIN$g4DmG16USEeorizbLiI2WA==$0 DROP_TEST TABLE
SQL> select * from dba_free_space where tablespace_name='AAA';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
AAA 7 9 983040 120 7
SQL> select object_name,original_name,type from user_recyclebin;
未选定行
从USER_RECYCLEBIN可以看到，（10g）在删除表后并没有真正删除而是放入回收站，可用闪回命令恢复
从DBA_FREE_SPACE可以看到，删除表时空间就释放了，所以有新的空间请求时，原数据有可能被覆盖从而无法恢复
注：如果删除时加purge选项，代表直接删除没法恢复
删除用户及模式时也无法闪回恢复
drop table drop_test;
作者: xiaodong_1567 时间: 2008-6-25 11:04
闪回数据
set time on
create table delete_test as select * from dba_users;
select count(*) from delete_test;
delete from delete_test where rownum < 10;
commit;
alter table delete_test enable row movement;
flashback table delete_test to timestamp (to_date('2008-06-23 11:07:30','yyyy-mm-dd hh24:mi:ss'));
select count(*) from delete_test;
11:07:24 SQL> set time on
11:07:24 SQL> create table delete_test as select * from dba_users;
表已创建。
11:07:24 SQL> select count(*) from delete_test;
COUNT(*)
----------
23
11:07:25 SQL> delete from delete_test where rownum < 10;
已删除9行。
11:07:53 SQL> commit;
提交完成。
11:07:55 SQL> alter table delete_test enable row movement;
表已更改。
11:12:08 SQL> flashback table delete_test to timestamp (to_date('2008-06-23 11:07:30','yyyy-mm-dd hh24:mi:ss'));
闪回完成。
11:12:40 SQL> select count(*) from delete_test;
COUNT(*)
----------
23
把删除的数据恢复，也可根据SCN来恢复
并可使用ENABLE或DIAABLE TRIGGERS选项来决定闪回数据时是否应用触发器
闪回失败原因：
出现违反主键约束的重复值
不存在足够的和所需的撤销数据
闪回受影响的任何记录被别人锁定
表结构发生改变
注：因为flashback table是通过DML方式恢复，所以使用闪回时即需要FLASHBACK ANY TABLE权限也需要SELECT、INSERT、DELETE和ALTER权限
set time off
drop table delete_test;
作者: xiaodong_1567 时间: 2008-6-25 11:04
回收站
RECYCLEBIN参数
show parameter recyclebin
alter system set recyclebin = off;
show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
create table test (x number);
drop table test;
select object_name,original_name,type from user_recyclebin;
SQL> select object_name,original_name,type from user_recyclebin;
未选定行
把回收站给禁用了
alter system set recyclebin = on;
清空回收站
purge user_recyclebin;
purge dba_recyclebin;
清空本用户回收站和所有用户回收站（需SYS权限）
purge table ;
purge index ;
从回收站清除表和索引
注：如果清除的对象在回收站中有同名的，那么会清除在回收站中时间更久的那个对象
purge tablespace ;
清除回收站中指定表空间的所有对象
drop table purge;
直接彻底删除，不会放入回收站
SYS与闪回
conn / as sysdba
select default_tablespace from dba_users where username = 'SYS';
DEFAULT_TABLESPACE
------------------------------
SYSTEM
create table a as select * from dba_users;
drop table a;
select object_name,original_name,type from dba_recyclebin;
SQL> select object_name,original_name,type from dba_recyclebin;
未选定行
create table a tablespace users as select * from dba_users;
drop table a;
select object_name,original_name,type from dba_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$OsKzDDWDRrybMVjgkxj3Wg==$0 A TABLE
说明缺省SYSTEM表空间删除对象不放入回收站
create table delete_test as select * from dba_users;
select count(*) from delete_test;
delete from delete_test where rownum < 10;
commit;
alter table delete_test enable row movement;
flashback table delete_test to timestamp (to_date('2008-06-23 11:46:00','yyyy-mm-dd hh24:mi:ss'));
SQL> flashback table delete_test to timestamp (to_date('2008-06-23 11:46:00','yyyy-mm-dd hh24:mi:ss'));
flashback table delete_test to timestamp (to_date('2008-06-23 11:46:00','yyyy-mm-dd hh24:mi:ss'))
*
第 1 行出现错误:
ORA-08185: 用户 SYS 不支持闪回
drop table delete_test;
purge dba_recyclebin;
作者: xiaodong_1567 时间: 2008-6-25 11:04
闪回查询
基于时间和SCN闪回查询
create table t (x int);
insert into t values (3);
insert into t values (6);
commit;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1202243
delete from t where x = 3;
commit;
select count(*) from t as of scn 1202243;
SQL> select count(*) from t as of scn 1202243;
COUNT(*)
----------
2
同样也可以运用基于时间来进行闪回查询
drop table t purge;
闪回版本查询
create table ttt (x int, y int);
insert into ttt values (3,3);
commit;
insert into ttt values (6,6);
commit;
select * from ttt;
update ttt set y=66 where x=6;
commit;
delete from ttt where x = 3;
commit;
col versions_starttime format a30
col versions_endtime format a30
select versions_starttime,versions_endtime,versions_xid,versions_operation,x,y
from ttt versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V X Y
------------------------------ ------------------------------ ---------------- - ---------- ----------
24-6月 -08 01.43.26 下午 0A000A003F020000 D 3 3
24-6月 -08 01.43.26 下午 0300090093020000 U 6 66
24-6月 -08 01.43.26 下午 3 3
24-6月 -08 01.43.26 下午 6 6
显示了数据修改的版本，包含了修改时间、操作类型和事务号（可用于事务恢复）等
注：select * from ttt;在这里不可少
select undo_sql
from flashback_transaction_query
where xid = '0A000A003F020000';
UNDO_SQL
------------------------------------------------------------------------------------------
insert into "TEST"."TTT"("X","Y") values ('3','3');
根据提供的事务号来获取它的撤销语句
col logon_user format a10
col operation format a10
select xid,start_scn,start_scn,start_timestamp,commit_scn,commit_timestamp,logon_user,
undo_change#,operation,row_id
from flashback_transaction_query
where table_name = 'TTT' and table_owner = 'TEST';
XID START_SCN START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION ROW_ID
---------------- ---------- ---------- ------------------- ---------- ------------------- ---------- ------------ ---------- -------------------
02002900A9020000 1238213 1238213 2008-06-24 13:43:14 1238214 2008-06-24 13:43:14 TEST 1 INSERT AAAMusAAEAAAABwAAA
0300090093020000 1238225 1238225 2008-06-24 13:43:26 1238226 2008-06-24 13:43:26 TEST 1 UPDATE AAAMusAAEAAAABwAAB
09002300A9020000 1238215 1238215 2008-06-24 13:43:14 1238216 2008-06-24 13:43:14 TEST 1 INSERT AAAMusAAEAAAABwAAB
0A000A003F020000 1238228 1238228 2008-06-24 13:43:26 1238229 2008-06-24 13:43:26 TEST 1 DELETE AAAMusAAEAAAABwAAA
col undo_sql format a90
select xid,undo_sql
from flashback_transaction_query
where table_name = 'TTT' and table_owner = 'TEST';
XID UNDO_SQL
---------------- ------------------------------------------------------------------------------------------
02002900A9020000 delete from "TEST"."TTT" where ROWID = 'AAAMusAAEAAAABwAAA';
0300090093020000 update "TEST"."TTT" set "Y" = '6' where ROWID = 'AAAMusAAEAAAABwAAB';
09002300A9020000 delete from "TEST"."TTT" where ROWID = 'AAAMusAAEAAAABwAAB';
0A000A003F020000 insert into "TEST"."TTT"("X","Y") values ('3','3');
如果表进行了重整，那么根据ROWID作为条件的UNDO SQL无效
作者: xiaodong_1567 时间: 2008-6-25 11:05
其它
SCN与TIMESTAMP
select scn_to_timestamp(1211921) from dual;
SCN_TO_TIMESTAMP(1211921)
---------------------------------------------------------------------------
23-6月 -08 03.10.47.000000000 下午
select timestamp_to_scn(to_date('2008-06-23 15:16:02','yyyy-mm-dd hh24:mi:ss')) from dual;
select timestamp_to_scn(to_date('2008-06-23 15:16:03','yyyy-mm-dd hh24:mi:ss')) from dual;
select timestamp_to_scn(to_date('2008-06-23 15:16:04','yyyy-mm-dd hh24:mi:ss')) from dual;
select timestamp_to_scn(to_date('2008-06-23 15:16:05','yyyy-mm-dd hh24:mi:ss')) from dual;
SQL> select timestamp_to_scn(to_date('2008-06-23 15:16:02','yyyy-mm-dd hh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('2008-06-2315:16:02','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1212194
SQL> select timestamp_to_scn(to_date('2008-06-23 15:16:03','yyyy-mm-dd hh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('2008-06-2315:16:03','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1212194
SQL> select timestamp_to_scn(to_date('2008-06-23 15:16:04','yyyy-mm-dd hh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('2008-06-2315:16:04','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1212194
SQL> select timestamp_to_scn(to_date('2008-06-23 15:16:05','yyyy-mm-dd hh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('2008-06-2315:16:05','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1212195
select time_dp from (select min(time_dp) time_dp from sys.smon_scn_time);
select timestamp_to_scn(to_date('2005-08-30 13:50:07','yyyy-mm-dd hh24:mi:ss')) from dual;
select timestamp_to_scn(to_date('2005-08-30 13:50:06','yyyy-mm-dd hh24:mi:ss')) from dual;
TIME_DP
-------------------
2005-08-30 13:50:07
SQL> select timestamp_to_scn(to_date('2005-08-30 13:50:07','yyyy-mm-dd hh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('2005-08-3013:50:07','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
4
SQL> select timestamp_to_scn(to_date('2005-08-30 13:50:06','yyyy-mm-dd hh24:mi:ss')) from dual;
select timestamp_to_scn(to_date('2005-08-30 13:50:06','yyyy-mm-dd hh24:mi:ss')) from dual
*
第 1 行出现错误:
ORA-08180: 未找到基于指定时间的快照
ORA-06512: 在 "SYS.TIMESTAMP_TO_SCN", line 1
（10g）SCN与TIMESTAMP之间可以转换，通过SYS.SMON_SCN_TIME每3秒记录的SCN和TIMESTAMP来进行转换
作者: xiaodong_1567 时间: 2008-6-25 11:05
相关数据表
监视闪回日志
V$FLASHBACK_DATABASE_LOGFILE
col name format a80
select name,log#,thread#,sequence#,bytes
from v$flashback_database_logfile;
NAME LOG# THREAD# SEQUENCE# BYTES
-------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MKJNJM_.FLB 1 1 1 8192000
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MLY9NZ_.FLB 2 1 2 4096000
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MM8SSD_.FLB 3 1 3 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MMWR0H_.FLB 4 1 4 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MPJ7YR_.FLB 5 1 5 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MR9TF5_.FLB 6 1 6 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MR9WVC_.FLB 7 1 7 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MRTRRW_.FLB 8 1 8 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MTLOYR_.FLB 9 1 9 3981312
C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAWWWFLASHBACKO1_MF_45MXHC4D_.FLB 10 1 10 3981312
select log#,first_change#,first_time from v$flashback_database_logfile;
LOG# FIRST_CHANGE# FIRST_TIME
---------- ------------- -------------------
1 1148518 2008-06-19 10:36:06
2 1151412 2008-06-19 11:00:25
3 1152000 2008-06-19 11:06:01
4 1152987 2008-06-19 11:16:39
5 1155060 2008-06-19 12:01:11
6 1156781 2008-06-19 12:31:53
7 1156796 2008-06-19 12:31:56
8 1158739 2008-06-19 12:40:56
9 1161049 2008-06-19 13:10:45
10 1163323 2008-06-19 14:00:10
列出闪回日志文件的名称、大小、创建时的时间间和SCN等信息
V$FLASHBACK_DATABASE_LOG
select oldest_flashback_scn,oldest_flashback_time,retention_target,flashback_size,
estimated_flashback_size
from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
1148024 2008-06-19 10:36:06 360 44138496 47831040
OLDEST_FLASHBACK_SCN 保留的最低系统改变号
LDEST_FLASHBACK_TIME 最低系统改变号的时间
RETENTION_TARGET 闪回日志保留时间（单位：时间）
FLASHBACK_SIZE 当前闪回日志的大小（单位：字节）
ESTIMATED_FLASHBACK_SIZE 预估满足保留时间所需要的空间大小（单位：字节）
V$FLASHBACK_DATABASE_STAT
select begin_time,end_time,flashback_data,db_data,redo_data,estimated_flashback_size
from v$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2008-06-19 13:44:10 2008-06-19 14:33:39 5046272 9199616 2281984 0
2008-06-19 12:36:26 2008-06-19 13:44:10 8347648 35160064 4133376 49655808
监控各间隔时间内的输出闪回日志
BEGIN_TIME 一个时间间隔的开始
END_TIME 一个时间间隔的结束
FLASHBACK_DATA 在此时间间隔内写闪回日志大小（单位：字节）
DB_DATA 在此时间间隔内数据库读写大小（单位：字节）
REDO_DATA 在此时间间隔内输出在线日志的大小（单位：字节）
ESTIMATED_FLASHBACK_SIZE 在时间间隔的结束时预估满足保留时间所需要的空间大小（单位：字节）
select begin_time,end_time,round(flashback_data/1024/1024,2) flashback_data_mb,
round(db_data/1024/1024,2) db_data_mb,round(redo_data/1024/1024,2) redo_data_mb,
round(estimated_flashback_size/1024/1024,2) estimated_flashback_size_mb
from v$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA_MB DB_DATA_MB REDO_DATA_MB ESTIMATED_FLASHBACK_SIZE_MB
------------------- ------------------- ----------------- ---------- ------------ ---------------------------
2008-06-19 14:44:11 2008-06-19 15:11:00 3.94 5.89 1.39 0
2008-06-19 13:44:10 2008-06-19 14:44:11 5.66 9.92 2.48 44.12
2008-06-19 12:36:26 2008-06-19 13:44:10 7.96 33.53 3.94 47.36
以MB为单位，显示数据
作者: xiaodong_1567 时间: 2008-6-25 11:05
管理闪回区
V$RECOVERY_FILE_DEST
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ----------------- ---------------
C
racleproduct10.2.0/flash_recovery_area 2147483648 319592448 0 15
SPACE_LIMIT 闪回区的空间限制（单位：字节）
SPACE_USED 已使用空间
SPACE_RECLAIMABLE 可回收空间大小
NUMBER_OF_FILES 闪回区内文件数量
V$FLASH_RECOVERY_AREA_USAGE
select file_type,percent_space_used,percent_space_reclaimable,number_of_files
from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 12.46 0 3
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 2.43 2.24 12
FILE_TYPE 文件类型
PERCENT_SPACE_USED 空间使用的百分比
PERCENT_SPACE_RECLAIMABLE 空间可回收的百分比
NUMBER_OF_FILES 此类文件的数量
相关程序包
DBMS_FLASHBACK
ENABLE_AT_TIME、DISABLE和ENABLE_AT_SYSTEM_CHANGE_NUMBER过程
create table t (x int);
insert into t values (3);
insert into t values (6);
commit;
select sysdate from dual;
SYSDATE
-------------------
2008-06-24 15:39:43
delete from t;
commit;
exec dbms_flashback.enable_at_time(timestamp '2008-06-24 15:40:00');
select count(*) from t;
select timestamp_to_scn(to_date('2008-06-24 15:40:00','yyyy-mm-dd hh24:mi:ss')) from dual;
exec dbms_flashback.disable;
exec dbms_flashback.enable_at_system_change_number(1247121);
select count(*) from t;
exec dbms_flashback.disable;
SQL> select count(*) from t;
COUNT(*)
----------
2
TIMESTAMP_TO_SCN(TO_DATE('2008-06-2415:40:00','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1247121
SQL> select count(*) from t;
COUNT(*)
----------
2
本会话所有查询获取输入SCN或TIMESTAMP的一致性版本
第 1 行出现错误:
ORA-08184: 试图在闪回模式下重新启用闪回
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 12
ORA-06512: 在 line 1
直接切换输入时会出错，须先DISABLE
GET_SYSTEM_CHANGE_NUMBER
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248300
获取当前SCN
（完）
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13515953/viewspace-604938/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13515953/viewspace-604938/
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
