oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



 Using Oracle Flashback Data Archive (Total Recall)

Flashback Data Archive ( Oracle Total Recall ) introduced in 11g

Flashback Data Archive feature  is part of Oracle Total Recall technology. Flashback Data Archive feature lets you to track changes made in any number of tables for any given retention time. The retention time can be some days or months or years.

Flashback data archives retain historical data for the time duration specified using the RETENTION parameter. Historical data can be queried using the Flashback Query AS OF clause. Archived historic data that has aged beyond the specified retention period is automatically purged.

This feature is supported only Oracle 11g Enterprise Edition. It is not available in Standard or Express editions.

Lets see an example.

Step 1:-  Create Flashback data archive tablespace.

SQL> create tablespace FB_Storage datafile '/u01/oracle/oradata/orcl/fdstore.dbf'
              size 100m autoextend on segment space management auto;

Step 2:- Create Flashback Archive

SQL> create flashback archive default FB_Arch1 tablespace FB_Storage quota 1g retention 1 year

Flashback archive created

Step 3:- Turn on Flashback archive for tables you want to track.

For Example to enable flashback archive for table Scott.emp

(i) Grant Flashback archive privilege

SQL> grant flashback archive on fb_arch1 to scott;

Grant succeeded.

SQL> connect scott/tiger

(ii) Enable flashback archive for table

SQL> alter table emp flashback archive

Flashback Data Archive (FBDA) Example.

Suppose a user has deleted rows from emp by giving a delete statement like this

SQL> set time on;

21:48:40 SQL> delete from emp where deptno=10;

3 rows deleted.

21:51:38 SQL> commit;

Commit complete.

To View the state of table emp 10 minutes before

21:57:08 SQL> select * from emp as of timestamp sysdate - 10 / (24*60);

Versions Query (11gR2 only) (To see what changes are made to Emp No. 7902 between last 15 minutes

22:18:34 SQL> select * from emp versions between
                timestamp sysdate- 15 /(24*60) and sysdate 
                                    where empno=7902;

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved