11G
Flashback Data Archive
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Wednesday, 24 March 2010 23:18
-
Last Updated on Tuesday, 20 August 2013 14:06
-
Published on Wednesday, 24 March 2010 23:18
-
Written by Guy Lambregts
-
Hits: 3709
Flashback Data Archive
PLATINUM:sys> select name from v$tablespace;
NAME
SYSTEM
TEMP
SYSAUX
UNDOTBS1
USERS
USERS_ARCHIVE_01
USERS_ARCHIVE_02
PLATINUM:pm> connect / as sysdba
Connected.
PLATINUM:sys> create flashback archive employee tablespace users_archive_01 quota 1024G retention 10 year;
Flashback archive created.
PLATINUM:sys> alter flashback archive employee add tablespace users_archive_02 quota 1024G;
Flashback archive altered.
PLATINUM:sys> alter flashback archive employee purge before timestamp systimestamp;
Flashback archive altered.
PLATINUM:sys> select * from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
CREATE_TIME
LAST_PURGE_TIME
STATUS
EMPLOYEE
1 3650
27-JUL-09 09.57.07.000000000 PM
27-JUL-09 09.57.07.000000000 PM
PLATINUM:sys> grant flashback archive on employee to pm;
Grant succeeded.
PLATINUM:sys> connect pm/pm
Connected.
PLATINUM:pm> create table employee (emp_no number,emp_name varchar2(100),emp_name_family varchar2(100),emp_salary number(6,0),emp_time timestamp default systimestamp) tablespace users;
Table created.
PLATINUM:pm> alter table employee add constraint pk_employee primary key (emp_no) using index tablespace users initrans 4;
Table altered.
PLATINUM:pm> alter table employee add constraint pk_employee primary key (emp_no) using index tablespace users initrans 4;
Table altered.
PLATINUM:pm> alter table employee modify (emp_name not null);
Table altered.
PLATINUM:pm> alter table employee modify (emp_name_family not null,emp_salary not null,emp_time not null);
Table altered.
PLATINUM:pm> alter table employee flashback archive employee;
Table altered.
PLATINUM:pm> select * from user_flashback_archive_tables;
TABLE_NAME OWNER_NAME
FLASHBACK_ARCHIVE_NAME
ARCHIVE_TABLE_NAME
EMPLOYEE PM
EMPLOYEE
SYS_FBA_HIST_77533
PLATINUM:pm> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2(100)
EMP_NAME_FAMILY NOT NULL VARCHAR2(100)
EMP_SALARY NOT NULL NUMBER(6)
EMP_TIME NOT NULL TIMESTAMP(6)
PLATINUM:pm> insert into employee (emp_no,emp_name,emp_name_family,emp_salary) values (1,'Guy','Lambregts',100);
1 row created.
PLATINUM:pm> commit;
Commit complete.
PLATINUM:pm> select emp_salary from employee as of timestamp to_timestamp('27/07/2009 22:25','DD/MM/YYYY HH24:Mi') where emp_no=1;
EMP_SALARY
100
PLATINUM:pm> update employee set emp_salary=120 where emp_no=1;
1 row updated.
PLATINUM:pm> commit;
Commit complete.
PLATINUM:pm> update employee set emp_salary=125 where emp_no=1;
1 row updated.
PLATINUM:pm> commit;
Commit complete.
PLATINUM:pm> update employee set emp_salary=130 where emp_no=1;
1 row updated.
PLATINUM:pm> commit;
Commit complete.
PLATINUM:pm> select segment_name from user_segments where tablespace_name='USERS_ARCHIVE_01';
SEGMENT_NAME
SYS_FBA_HIST_77533
SYS_FBA_TCRV_77533
SYS_FBA_TCRV_IDX_77533
SYS_FBA_DDL_COLMAP_77533
PLATINUM:pm> select object_id,data_object_id from user_objects where object_name='EMPLOYEE';
OBJECT_ID DATA_OBJECT_ID
77533 77533
PLATINUM:pm> select * from sys_fba_hist_77533 order by startscn;
RID
STARTSCN ENDSCN XID O EMP_NO
EMP_NAME
EMP_NAME_FAMILY
EMP_SALARY
EMP_TIME
AAAS7dAAMAAAACHAAA
3283889 3284547 05000600AF080000 I 1
Guy
Lambregts
100
27-JUL-09 10.21.46.761000 PM
AAAS7dAAMAAAACHAAA
3284547 3284589 030002000E080000 U 1
Guy
Lambregts
120
27-JUL-09 10.21.46.761000 PM
AAAS7dAAMAAAACHAAA
3284589 3285072 030021000D080000 U 1
Guy
Lambregts
125
27-JUL-09 10.21.46.761000 PM
PLATINUM:pm> select emp_salary,emp_time from sys_fba_hist_77533 where emp_no=1
2 union
3 select emp_salary,emp_time from employee where emp_no=1;
EMP_SALARY
EMP_TIME
100
27-JUL-09 10.21.46.761000 PM
120
27-JUL-09 10.21.46.761000 PM
125
27-JUL-09 10.21.46.761000 PM
130
27-JUL-09 10.21.46.761000 PM