Oracle Flashback Query :

In my previous article i have given the best examples of SQL and explained so many concepts of SQL in depth. In this article i would like to give you some heads-up on newly introduced feature in Oracle- Oracle Flashback Query.In our day-to-day life users used to fire so many SQL queries to check the function of the SQL.There is need to check the history of the query. You will able to see the history of the query using different oracle editors.Oracle Flashback Query technology is one of the important technology which will give you idea about the past state of database object. If user wants to retrieve that database object to previous state this technology is useful.I want to explain Oracle Flashback Query with giving you multiple scenarios of the same so that user will get exact idea about this technology.

Oracle Flashback Query Features and Usages :

In this question i would like to give you brief introduction about the Oracle Flashback Query Features with its usages.A feature called Flashback Query is one of the quickest as well as  easiest way  to use multiple variations of the Oracle Flashback Query Technology.The key use of Oracle Flashback Query is to retrieve the committed data from its history.

Following are different usages of Oracle Flashback Query :

1.Returning the past data :

Sometimes there are situations where user needs to return the past data for reference. If the data is committed then there is one provision in oracle to return the past data which is called as oracle flashback feature.

2.Returning the metadata of query :

The Flash functionality gives you provision to return the metadata of the query.

3.Table Recovery :

Sometimes there is huge need to recover the tables in oracle. To recover the tables at the previous point Oracle flashback functionality is used.

4.Tracking the changes :

Oracle Flashback functionality will automatically tracks the changes and archive the transactional data.

Oracle flashback query feature is nothing but Undo management system which will provides the way to undo what you are doing in oracle.These are above four most important usages of Oracle Flashback.

Practical Usages of Oracle Flashback Query :

In above section we had discussed about the different oracle flashback features.Now let us start configuring the oracle flashback feature. User needs to configure Oracle flashback Query feature before using it.I would like to give you steps to configure oracle flashback feature :

Enable Archivelog :

The first step to setting up the Oracle Flashback is enabling the Archivelog :

ALTER DATABASE ARCHIVELOG;

The above command will enable the Archivelog

Enabling the supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

User needs to enable the supplemental logging to start with flashback feature of oracle.

Grants :

The most important part of using flashback is giving grants to the objects. There are multiple times user misses the grants to the particular objects so make sure that following grants are given properly to the database objects.

Grant 1 : Flashback any table privilege 

To flashback any table that specified user have grants of flashback any table privileges.

Grant 2 :Select any transactions

To select the flashback transactions user should have grant of flashback any transaction.

Grant 3 : DBMS_FLASHBACK package grant

User should have execute grant of DBMS_FLASHBACK package.

These are above 3 most important grants user needs to check before using flashback functionality of Oracle.

Most Common Scenarios using Oracle Flashback Query :

I want to give you some real life scenarios DBA’are facing. The most common scenario is somebody has deleted the data without using the where condition and we need to recover that critical data. I would like to give you step by step description of returning the results of this query.

Step 1 :  Create the table same as the deleted table using following query

Query :

create table S_Bcp_dba_segments as
select segment_name, tablespace_name from dba_segments where rownum<20 order by bytes desc;

Step 2 : Select the count of the Query

Select count(*) from S_Bcp_dba_Segments;

Output must be 19.

Step 3 : User needs to get current SCN Number and timestamp of the table using following query from oracle dual table.

select to_char(sysdate,’dd-mm-yyyy  hh24:mi:ss’) ddate, dbms_flashback.get_system_change_number() scn from dual;

Lets say time is 24-09-2018 7 :40 and scn number is 477444

Here Database administrator needs to ask the current scn number and timestamp to the developer.

Step 4 : Delete the table using delete and commit the transaction

Delete from S_Bcp_dba_Segments;

commit;

19 rows deleted;

Step 5 : DBA knows exact date and time of deletion of the records so use following query to retrieve the count of the table.

select  count(*)  from S_Bcp_dba_Segments as of timestamp to_timestamp(’24-09-2018 07:40:00′,’dd -mm-yyyy hh24:mi:ss’);

The output of above query will be 19. This is timestamp method of Oracle flashback query we are using to retrieve the data.

Step 6: Using SCN number

We have collected the SCN number as well before deleting the records. If user knows the SCN number of the query its pretty simple to retrieve the data using flashback method.

select * from S_Bcp_dba_Segmentsas of scn 477444;

But most of the times user dont know about the SCN number as well as timestamp.

Step 7 : Checking Ago data

Sometimes user does not know the exact time of data deletion. He just knows vague information like before half and hour the data is deleted.In that case the following query is helpful to check the data:

select  count(*)  from S_Bcp_dba_Segments as of timestamp (systimestamp -interval ’30’ minute);

The above query will give you count of table S_Bcp_dba_Segments  30 minutes ago.

The above example is most common example.I have explained it stepwise so that user will get exact idea of the same.

Oracle Flashback Query to check deleted PL SQL Objects :

The second most important use of Oracle flashback Query feature is to retrieve the PL SQL objects as well.Many times programmer unknowingly deletes the PL SQL objects.Here SCN number of object is really very important.The source of any PL SQL object will be stored in sys.source$ table. I would like to give you stepwise example of procedure. Here user needs to check the exact dependencies of PL SQL object before recovering it.

Step 1 : Create any simple procedure

create or replace Procedure P_test

v_test varchar2(30);
begin
Select Ename into V_test from Employee;

end;

/

Procedure created;

Step 2 : Take current SCN number of Specified procedure

select current_scn from v$database;

User will get the current Scn number using V$database system view of oracle.

Lets say scn number is 788999.

Step 3 : Drop the specified procedure

Drop procedure P_test;

Procedure droped;

Step 4 : Take the procedure code using following Query

select text  from dba_source  as of scn 788999  where name=’P_test’;

The above query will retrieve the procedure code.There are multiple ways with using which user can retrieve the deleted data.The best way to retrieve the data is oracle flashback query.Hope user will get idea about the oracle flashback Query in depth with reading this article.If you like this article or if you have any questions with the same kindly comment in to comments section.