29 | 03 | 2024
Latest Articles
Popular Articles

11G

Flashback Data Archive

User Rating:  / 0
PoorBest 

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