Oracle flushback

闪回查询:
基于时间的flash back 查询
SQL> set time on;
12:09:12 SQL> delete from firefox_t01_t where x=1;
12:09:20 SQL> commit;
12:11:05 SQL> select * from firefox_t01_t as of timestamp to_timestamp('2015-03-05 12:09:12','YYYY-MM-DD hh:mi:ss');
基于SCN的闪回查询
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    5253072
SQL> insert into firefox_t01_t values(1,23);
SQL> select * from firefox_t01_t;

     X        Y
---------- ----------
       100     2000
       1       23
SQL> select * from firefox_t01_t as  of scn  5253072;
--原理读取UNDO表空间中的回滚段中的数据;
undo  retention; 参数设置:



闪回版本查询:每一次事物就是一个版本
flashback versions query

 insert into firefox_t01_t values(i,23);

declare
    i number;
BEGIN
    for i in 1 .. 10 loop
        insert into firefox_t01_t values(i,100);
    commit;
    end loop;
end;
/

闪回事物查询;
select operation,undo_sql from flashback_transaction_query
where logon_user = 'SYS' and undo_sql like '%FIREFOX_T01%'; --like 表;
select operation,undo_sql from flashback_transaction_query where
logon_user='SYS' and undo_sql like '%TEST%' and xid=HEXTORAW('ASD21123134');



闪回表:
    将表闪回到历史的某个时刻
declare
    i number;
BEGIN
    for i in 1 .. 10 loop
        insert into firefox_t01_t values(i,100);
    commit;
    end loop;
end;
/
select rowid,x,y from firefox_t01_t;
select  current_scn from v$database;
delete from firefox_t01_t;
alter table firefox_t01_t enable row movement; --允许行移动;
SQL> flashback table firefox_t01_t to scn 5258255;


闪回删除;和回收站;
DDL操作是不能使用UNDO日志进行回滚;
SQL > show parameter recyclebin;
SQL > show recyclebin;
drop table firefox_t01_t;
show recyclebin;
flashback table firefox_t01_t to before drop;

怎样清空回收站
drop table firefox_t01_t purge;
purge recyclebin;
purge table firefox_t01_t; --单独对对象进行清空;

回收站的空间管理
SQL>select username,default_tablespace from user_users;
create table t_dba_object as select * from dba_objects;
select sum(blocks) from dba_free_space where tablespace_Name = 'SYSTEM';
drop table t_dba_object;
select sum(blocks) from dba_free_space where tablespace_Name = 'SYSTEM';
select blocks from user_segments where segment_name='asdasdasd';



基于UNDO的flushback有时间的限制;

闪回归档
用实际的数据来记录数据的修改
然回复永不过期.专有的后台进程fdba异步的捕获数据:
默认5分钟
所改变的活跃程度调整;

闪回归档:
create tablespace his_data_1 datafile '/home/opt/app/oracle/11.2.0/+data'  SIZE 500M;
create flashback archive default fba1 tablespace his_data_1 RETENTION 7 DAY;
create flashback archive fba2 tablespace his_data_1 quota 200M  RETENTION 30 DAY;
闪回归档;授权:
GRANT FLASHBACK ARCHIVE ON fba1 to c##scott;
GRANT FLASHBACK ARCHIVE ON fba2 to c##scott;
创建表
create table empsal_his(empno number,ename varchar2(10),sal number)flashback archive;
alter table t FLASHBACK ARCHIVE fba2;
SQL> update t set object_id = 100;
create undo tablespace undotbs2 datafile  '/home/opt/app/oracle/11.2.0/+undodata' size 100M;
alter system set undo_tablespace='UNDOTBS2';
drop tablespace undotbs1 including contents and datafiles;
闪回:
select distinct object_id from t as of timestamp
to_timestamp ('12-NOV-2009 14:00:00','DD-MON-YYYY HH24:MI:SS');
如何查看闪回归档:
select FLASHBACK_ARCHIVE_NAME,tablespace_name,QUOTA_IN_MB from dba_flashback_archive_ts;
select FLASHBACK_ARCHIVE_NAME,to_char(create_time,'YYYY-MM-DD') created,
RETENTION_IN_DAYS,STATUS from dba_flashback_archive;
select table_name,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
FROM dba_flashback_archive_tables; -- 闪回表
select * from dba_objects where object_id = 75485 or object_id = 75193;
select object_id from dba_objects where object_name = 'T'
select table_name,tablespace_name from user_tables where tablespace_name =  'his_data_1' or  tablespace_name = 'hist_data_2';
修改闪回归档的语句:
alter FLASHBACK ARCHIVE fba1 modify tablespace his_data_1 quota 250M;
alter FLASHBACK ARCHIVE fba1 modify RETENTION 1 DAY;
drop flushback archive fba1;
alter table t no flashback archive;
当数据超过设定保留期限后,会自动删除.
允许手工删除
alter flashback archive fba1 purge all;
alter flashback archive fba1 purge before timestamp(systimestamp - interval '1' DAY);
alter flashback archive fba1 purge before scn 528967;

 

posted @ 2015-03-05 16:30  想想宝宝  阅读(361)  评论(0)    收藏  举报