这里得明确几个概念：
一 归档 (检查:archive log list)
二 闪回
三 闪回数据归档Flashback Data Archive (11g才出现）
--启用数据库归档：
Alter database archivelog ;
--启用数据库闪回：
Alter database flashback on
--设置闪回区大小和恢复路径
alter system set db_recovery_file_dest_size=3g scope=both;
alter system set db_recovery_file_dest='e:\flash_recovery_area' scope=both;
查看归档是否启用：
archive log list;
查看数据据闪回 和归档是否启用：
select log_mode,flashback_on from v$database;
Select file_type from v$flash_recovery_area_usage;
1.flashback table tablename to scn/timestamp(a timestamp);
oracle falshback transaction query回闪事务查询
oracle10回闪事务功能提供对过去某段时间内所完成的事务的查询和撤销！当然首先得:
alter table a enable row movement; --使表可以闪回相关函数:
select timestamp_to_scn(systimestamp) from dual;
select scn_to_timestamp(76185574) from dual;
select current_scn from v$database
查询SCN
SQL> select to_char(sysdate-1, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
------------------- ----------------------------------------
2011-03-28 10:02:11 76145312
这里我获得昨天此时的SCN
select * from DIS.TBRISKTYPE as of SCN 76145312
是有数据的.但当我用如下查法时:
select * from DIS.TBRISKTYPE as of timestamp sysdate-10/12
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small
ora-01555无情的面向了我,关于scn和timestamp这里有得整了.
查询闪回事务：select * from FLASHBACK_TRANSACTION_QUERY where logon_user='SCOTT' and start_timestamp>sysdate-10/1440
在10g里rowid和undosql是默认打开记录了的。--而在11g里，需要手动打开(有待进一步验证）。
alter database add supplemental log data;
select segment_name,status,tablespace_name from dba_rollback_segs;
SELECT RN.NAME,
RS.RSSIZE / 1024,
RS.GETS,
RS.WAITS,
(RS.WAITS / RS.GETS) * 100,
RS.SHRINKS,
RS.EXTENDS
FROM SYS.V_$ROLLNAME RN, SYS.V_$ROLLSTAT RS
WHERE RN.USN = RS.USN
查询事务提交过程:
select undo_sql,table_name from flashback_transaction_query x where x.table_owner=upper('zxt') and x.xid ='0500110006030000';
select * from DIS.TBRISKTYPE versions between SCN 76154971 and 76156767
select * from flashback_transaction_query where table_name='TBRISKTYPE' order by start_timestamp 相关视图:v$transaction
select * from v$fixed_view_definition where view_name='V$TRANSACTION'
select ADDR , XIDUSN , XIDSLOT , XIDSQN , UBAFIL , UBABLK , UBASQN , UBAREC , STATUS , START_TIME , START_SCNB , START_SCNW ,
START_UEXT , START_UBAFIL , START_UBABLK , START_UBASQN , START_UBAREC , SES_ADDR , FLAG , SPACE , RECURSIVE , NOUNDO , PTX ,
NAME,PRV_XIDUSN , PRV_XIDSLT , PRV_XIDSQN , PTX_XIDUSN , PTX_XIDSLT , PTX_XIDSQN , "DSCN-B" , "DSCN-W" , USED_UBLK , USED_UREC ,
LOG_IO , PHY_IO , CR_GET , CR_CHANGE, START_DATE, DSCN_BASE, DSCN_WRAP, START_SCN, DEPENDENT_SCN, XID, PRV_XID, PTX_XID
from gv$transaction
where inst_id = USERENV('Instance')
select inst_id,ktcxbxba,kxidusn,kxidslt,kxidsqn,ktcxbkfn,kubablk, kubaseq,kubarec,
decode(ktcxbsta,0,'IDLE',1,'COLLECTING',2,'PREPARED',3,'COMMITTED', 4,'HEURISTIC ABORT',5,'HEURISTIC COMMIT',
6,'HEURISTIC DAMAGE',7,'TIMEOUT',9,'INACTIVE', 10,'ACTIVE',11,'PTX PREPARED',12,'PTX COMMITTED',
'UNKNOWN'), ktcxbstm,ktcxbssb,ktcxbssw, ktcxbsen,ktcxbsfl,ktcxbsbk,ktcxbssq,ktcxbsrc, ktcxbses,
ktcxbflg, decode(bitand(ktcxbflg,16),0,'NO','YES'), decode(bitand(ktcxbflg,32),0,'NO','YES'),
decode(bitand(ktcxbflg,64),0,'NO','YES'), decode(bitand(ktcxbflg,8388608),0,'NO','YES'),
ktcxbnam, ktcxbpus,ktcxbpsl,ktcxbpsq, ktcxbpxu,ktcxbpxs,ktcxbpxq, ktcxbdsb, ktcxbdsw, ktcxbubk,
ktcxburc,ktcxblio,ktcxbpio,ktcxbcrg,ktcxbcrc, to_date(ktcxbstm,'MM/DD/RR HH24:MI:SS',
'NLS_CALENDAR=Gregorian'), ktcxbdsb, ktcxbdsw, ktcxbssc, ktcxbdsc, ktcxbxid, ktcxbpid,
ktcxbpxi
from x$ktcxb where bitand(ksspaflg,1)!=0 and bitand(ktcxbflg,2)!=0 也就是xid由x$ktcxb的ktcxbxid得来
没写完.....继续写...