How to get view/trigger before altered on PL-SQL?

Question

I have a view:
Yesterday | myview | select * from mat where bereich='AAA' |
Today | myview | select * from mat where bereich='BBB' AND typ='BR' |

---

someone has altered my view on prod or I forgot to back up the previous query and it has been committed

How to get my query/myview yesterday back ?

Thanks.

Answer 1

You may use user_views data dictionary view to see former text of your view. For this aim, you need some privileges such as

SQL> sqlplus / as sysdba
SQL> grant flashback on user_views to myschema;

and minimum requirements for using flashback techniques such as

SQL> select p.value 
  from v$parameter p
 where p.name = 'db_flashback_retention_target';

 VALUE
 -----
  1440 -- should be set to 1440(minute) as minimum to get the value for the day before.

provided by your DBA.

Then, connect to your schema

SQL> conn myschema/mypwd

and use the following query

SQL> select t.text 
  from user_views 
  as of timestamp systimestamp - interval '1' day t
where t.view_name = 'MYVIEW';

to get the text of your view in the past to recreate.

P.S. the keyword day might be replaced with hour, minute or second due to your need or wish.