概述
Oracle 引入闪回技术使得一些逻辑误操作不再需要利用归档日志和数据库备份进行时间点恢复，在前面介绍的诸多闪回技术中，除了Flashback Database依赖于闪回日志外，其它闪回技术都是依赖于Undo撤销数据，都与数据库初始化参数undo_retention密切相关，所以就存在一个限制就是undo中的信息不能被覆盖。
Oracle11g中的闪回数据归档(Flashback Data Archive)与前面所说的闪回技术实现机制不同，它是通过将变化数据存储到创建的闪回归档区中，得以与undo区别开来。 同时Flashback Data Archive 并不是记录数据库的所有变化，而只是记录了指定表的数据变化，是Flashback database的补充。
1．体系结构
2．配置闪回数据归档
准备一个Oracle 11g数据库使用FBDA功能是相当简单的，只需要经过几个简单的步骤即可：
(1) 创建或指定一个或多个表空间用于FBDA保留历史数据
(2) 随意指派一个FBDA作为数据库的默认FBDA
(3) 指派一个用户账户作为FBDA管理员，授予它FLASHBACK ARCHIVE ADMINISTER系统权限
(4) 授予FBDA权限给适合的用户账号
(5) 授予FLASHBACK和SELECT权限给合适的FBDA表用户
(6) 为FBDA用户授予DBMS_FLASHBACK存储过程EXECUTE权限
3．访问历史记录数据的基本工作流：
创建闪回数据归档：
1． 创建闪回数据归档：
CREATE FLASHBACK ARCHIVE fla1 TABLESPACE tbs1 QUOTA 10GRETENTION 5 YEAR;
2． 对 FLA1 归档中的表启用历史记录跟踪：
ALTER TABLE inventory FLASHBACK ARCHIVE fla1;
3． 查看历史记录数据：
SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMPTO_TIMESTAMP ('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
使用默认闪回归档：
1．创 建 默 认 闪 回 数 据 归 档
CREATE FLASHBACK ARCHIVE DEFAULT fla2TABLESPACE tbs1 QUOTA 10G RETENTION 2 YEAR;
2．对表启用历史记录跟踪：
 ALTER TABLE stock _data FLASHBACK ARCHIVE; 
注：因为使用了默认的闪回数据归档，所以不需要提供闪回数据归档名称。
3. 禁用历史记录跟踪：
ALTER TABLE stock _data NO FLASHBACK ARCHIVE;
4．维护闪回数据归档
1. 增加空间：
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
2. 更改保留时间：
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
3. 清除数据：
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORETIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);
4. 删除闪回数据归档：
DROP FLASHBACK ARCHIVE fla1;
篇幅有限，关于闪回归档就不做演示了，大家可以自己在虚拟机环境做相关测试。
后面会分享更多关于devops和DBA方面内容，感兴趣的朋友可以关注下！

 如果对于不停机的数据归档，需要考虑数据在不停写入。
1.  修改待归档表索引名
     alter index PS_PSACCESSLOG rename to PS_PSACCESSLOG2;
2.  创建新表
     -- Create table

create table SYSADM.PSACCESSLOG_1

(

  OPRID          VARCHAR2(30) not null,

  LOGIPADDRESS   VARCHAR2(40) not null,

  LOGINDTTM      TIMESTAMP(6),

  LOGOUTDTTM     TIMESTAMP(6),

  PT_SIGNON_TYPE VARCHAR2(3) not null

)

tablespace PTTBL

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 40K

    next 104K

    minextents 1

    maxextents unlimited

  );
3.  在新表上创建原名索引（如果停机归档，可在写入数据后创建索引）
    -- Create/Recreate indexes 

create index SYSADM.PS_PSACCESSLOG on SYSADM.PSACCESSLOG_1 (OPRID, LOGIPADDRESS, LOGINDTTM)

  tablespace PSINDEX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 40K

    next 104K

    minextents 1

    maxextents unlimited

  );

4. 将新表依原表 赋权
GRANT DELETE ON PSACCESSLOG_1 TO R_EP_DML ;

GRANT INSERT ON PSACCESSLOG_1 TO R_EP_DML ;

GRANT SELECT ON PSACCESSLOG_1 TO R_EP_DML ;

GRANT UPDATE ON PSACCESSLOG_1 TO R_EP_DML ;

5. 重命名表名（将待归档表重命名归档表名，新表重命名原表名）此处对于业务上会有一定短时的影响
RENAME PSACCESSLOG TO PSACCESSLOG_20170717;

RENAME PSACCESSLOG_1 TO PSACCESSLOG;

6. 循环写入新表归档时间点后的数据
begin

  for cur in (select a.* from PSACCESSLOG_20170717 a where logindttm>=to_date('20170101','yyyymmdd')) loop

    insert into PSACCESSLOG (oprid,logipaddress,Logindttm,logoutdttm,Pt_Signon_Type) values(cur.oprid,cur.logipaddress,cur.Logindttm,cur.logoutdttm,cur.Pt_Signon_Type);

    commit;

  end loop;

end;

/

7. 搜集新表统计信息
EXEC dbms_stats.gather_table_stats(ownname => 'SYSADM',tabname => 'PSACCESSLOG',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,cascade => true,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

索引

准则：当任何单个查询要检索的行少于或等于整个表行数的10%时，就应当创建索引。 

B-树索引：索引的候选列应该是用来存储很大范围的值的列 

位图索引：包含小范围值的列



1.创建B-树索引

注意：由于性能原因，应该将索引与表存储到不同的表空间中。

假设test表包含很多行



select id,type
from test
where type='01';



create index i_test_type on test(type);

唯一索引



create unique index i_test_id on test(id);

复合索引



create index i_test_id_type on test(id, type);

2.创建基于函数的索引



select id,type
from test
where type=UPPER('SFZ');



create index i_func_test_type on test(UPPER(type));

注意：使用基于函数的索引，必须要将初始化参数query_rewrite_enabled设置为true。



3.获取有关信息的索引



select index_name, table_name, uniqueness, status
from user_indexes
where table_name='test';



4.获取列索引的信息



select index_name, table_name, column_name
from user_ind_columns
where table_name='test';



5.修改索引



alter index i_test_id_type rename to i_test_id_and_type;

6.删除索引



drop index i_test_id_and_type;

7.创建位图索引



create bitmap index i_order on order(status);



视图

注意：视图并不存储行，他们始终存储在表中。视图返回存储在表中的行



1.创建视图



connect system/systemgrant create view to zoey;
connect zoey/zoey

create view test_view asselect * from test where type='02';

使用视图



select id from test_view;

注意：简单视图支持DML操作，复杂视图不支持DML操作。



2.创建具有check option约束的视图指定对视图执行的DML操作必须满足子查询的条件。



create view test_view2 asselect * from test where type='02'
with check option constraint test_view2;



3.创建具有read only约束的视图(只读)



create view test_view3 asselect * from test where type='02'
with read only constraint test_view2;



4.获取有关视图定义的信息



describe test_view3;



5.获取有关视图的约束信息



select constraint_name, constraint_type, status , deferrable, deferred
from user_constraintswhere table_name='test';



6.修改视图



create or replace view test_view3 asselect * from test where type in ('02', '01');



7.删除视图



drop view test_view3;



五.闪回数据归档



create flashback archive test_archivetablespace example --归档在example创建
quota 1 M --限额是1M
retention 1 DAy;--保留期限是1天

停止归档



alter zoey.test no flashback archive;

清除归档中的所有数据



alter flashback archive test_archive purge all;

删除闪回归档



drop flashback archive test_archive;

当有一张表数据量很大，真正项目只用到一个月内的数据，因此把一个月前的旧数据定期归档。
解决方案如下：
1 - 创建一个新表，表结构和索引与旧表一模一样
create table table_archive like table_name;
2 - 新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除
delimiter $
create procedure sp()
begin
insert into table_archiveselect * fromtable_name where rectime < now() - interval 30 day;
delete from db_smc.table_name where rectime < now() - interval 30 day;
end
3 - 创建event，每天晚上凌晨00：00定时执行上面的存储过程
create event if not exists event_temp
on schedule every 1 day
on completion preserve
do call sp();
备注：
第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错error 1205 (hy000): lock wait timeout exceeded; try restarting transaction，应急解决方案如下：
1、执行show full processlist;查看所有mysql线程
2、执行select * from information_schema.innodb_trx\g; 查看是否有错误线程，线程id在show full processlist;的结果中状态为sleep
3、kill 进程id
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！