Thursday, 19 September 2013

Recover Dropped view's Defination

Unfortunately One of our developer had dropped one important view from the schema. And we were not taking datapump backup for that schemas. Yes we are taking the RMAN full backup but don't want to go that route if I could find easily.

Here was the solution ( assuming database have all that configuration required for "AS OF TIMESTAMP - FLASHBACK" clause )

select * from ( select view_name , text from dba_views where owner='MGR_KX' )
as of timestamp sysdate - 1 ;

and we got required result with view definition.