Archive for August, 2011

Flashback queries for Data Dictionary

August 17, 2011

Oracle developers sometimes delete their subprograms, views, tables etc accidentally.
From Oracle 10g we have opportunity to salvage them from the undo segment.
Note that – by default – the Oracle does not guarantee the successful salvage in 100%.
(See later in this article or the documentation fore more detail)

The FLASHBACK statement – with wide range of  functionalities – ensures the possibility to restore earlier state of objects or completely recreate them.
Flashback queries appeared in Oracle 9i first.
This is well known by the DBA’s and developers.
Maybe not so popular that we can execute flashback queries against the Data Dictionary.
Average users (ie. Not SYS) can not do it without necessary object privileges.
Note the FLASHBACK ANY TABLE  system privilege is not enough for these queries!

Let’s see an example:
1. HR user created earlier a function, this was the content of the tax.sql:
CREATE OR REPLACE FUNCTION TAX(P_AMOUNT IN NUMBER)
RETURN NUMBER
IS
M  NUMBER;
BEGIN
IF P_AMOUNT <8000 THEN M:=0.08;
ELSIF P_AMOUNT <18000 THEN M:=0.25;
ELSE  M:=0.3;
END IF;
RETURN P_AMOUNT*M;
END;
/

Although HR has the original version of the function, but he modified his function in the database many times  and he did not track these modifications in the text file (in our example tax.sql).
HR user deleted his function accidentally:

DROP FUNCTION TAX;

A couple minutes later he realized that it was a mistake and his version of the function in tax.sql is too old! He asked the DBA to help him.
The DBA connected to the database instance as SYS and issued the following GRANT statement:

GRANT FLASHBACK ON USER_SOURCE TO HR;

The DBA wanted to be proactive and  helpful, so he or she  issued the second  SQL statement:

GRANT FLASHBACK ON USER_VIEWS TO HR;

Now HR will be able to issue a flashback query against the subset (only for USER_SOURCE and USER_VIEWS)  of the Data Dictionary:

SELECT TEXT FROM USER_SOURCE AS OF TIMESTAMP SYSTIMESTAMP- INTERVAL ‘&MIN’ MINUTE
WHERE NAME=’TAX’;

TEXT
————————————
FUNCTION TAX(P_AMOUNT IN NUMBER)
RETURN NUMBER
IS
M  NUMBER;
BEGIN
IF P_AMOUNT <8000 THEN M:=0.08;
ELSIF P_AMOUNT <18000 THEN M:=0.25;
ELSE  M:=0.3;
END IF;
RETURN P_AMOUNT*M;
END;

11 rows selected.

If HR redirects the output of the above query in a spool file, he can recreate  the latest version of his function.
Of course HR can issue the same select against the USER_VIEWS.

This method useful If:

  1. The Oracle object is a text type object (subprograms, views, directories, context objects, …)
  2. The DROP statement was not “too old”. This means, if there was a big traffic in the database, many transactions used the undo segment in latest couple of hours or even minutes)
  3. The DBA issued similar statements to this:
    ALTER SYSTEM SET UNDO_RETENTION = 3600;
    ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE ;

    The UKOUG annual conference is regularly rated as ‘top of the list’ for events to attend.  The International representation is very strong and with the proliferation of a global economy, this is one of those conferences where you can actually get a flavour and appreciation for the issues and concerns on a global scale.   



Advertisements