FBDA-关闭闪回归档会清除之前的历史数据

2018年6月6日 6037点热度 0人点赞 0条评论

■■关闭闪回归档会清除之前的历史数据
再次打开就是重来。

■建立FBDA所在的表空间likingfbda3
CREATE TABLESPACE likingfbda3 DATAFILE SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
■在表空间上建立FBDA
CREATE FLASHBACK ARCHIVE likingfbda3 TABLESPACE likingfbda3 RETENTION 1 DAY;
■授权用户scott可以使用flashback archive
GRANT FLASHBACK ARCHIVE ON likingfbda3 TO scott;
■设置table启用flashback archive
SCOTT@prod1> CREATE TABLE emp3 AS SELECT * FROM emp;
SCOTT@prod1> ALTER TABLE emp3 FLASHBACK ARCHIVE likingfbda3;
■查看空间占用情况
SELECT OWNER,TABLESPACE_NAME,SEGMENT_NAME,BYTES FROM dba_segments WHERE TABLESPACE_NAME='LIKINGFBDA3';

INSERT INTO emp3 SELECT * FROM EMP3;
UPDATE emp3 SET sal = 40000 WHERE empno = 7788;

SELECT OWNER,TABLESPACE_NAME,SUM(BYTES) BYTES FROM dba_segments WHERE TABLESPACE_NAME='LIKINGFBDA3' GROUP BY OWNER,TABLESPACE_NAME;
10420224
UPDATE emp3 SET sal = 40000 WHERE empno = 7876;
10485760

SELECT versions_startscn, versions_endscn, empno, sal
FROM emp3 versions BETWEEN TIMESTAMP(systimestamp - INTERVAL '1' MINUTE) AND maxvalue
WHERE empno = 7788
ORDER BY 1 NULLS FIRST;

关闭emp3的fbda:
ALTER TABLE scott.emp3 NO FLASHBACK ARCHIVE;
关闭之后,likingfbda3表空间的占用没有了:
SYS@prod1> SELECT OWNER,TABLESPACE_NAME,SUM(BYTES) BYTES FROM dba_segments WHERE TABLESPACE_NAME='LIKINGFBDA3' GROUP BY OWNER,TABLESPACE_NAME;
no rows selected
打开emp3的fbda:
ALTER TABLE scott.emp3 FLASHBACK ARCHIVE likingfbda3;
此时,原来的记录没有了:
SYS@prod1> SELECT OWNER,TABLESPACE_NAME,SUM(BYTES) BYTES FROM dba_segments WHERE TABLESPACE_NAME='LIKINGFBDA3' GROUP BY OWNER,TABLESPACE_NAME;
no rows selected

liking

这个人很懒,什么都没留下

文章评论