Thursday, February 18, 2010

Flashback saves the day (again)

I'm posting this as much for my future reference as anything. This morning I accidentally dropped a package body from the schema I was working in - that is to say I intentionally dropped it, but then realised I shouldn't have. And since this package body was the work-in-progress of another developer who likes to build their code using Toad, directly into the database (a hanging offence if it was down to me...) there was no file containing the source that they could re-create it from.

I felt sure flashback would save me, but my first attempt failed:

SQL> select text from user_source as of timestamp (sysdate-1/24)
2 where name = 'MY_PACKAGE'
3 order by line;

no rows selected

A quick Google took me to this post where BluShadow said:

select obj# from sys.obj$ where name like 'PKG_MY_OVERWRITTEN_PKG%'

Then, using the obj# run the following (as of 3 hours ago):

select source from sys.source$ as of timestamp(sysdate-((1/24)*3)) where obj# = 1234567
order by line

You can't look at sys.obj# as of any time previously.
These tables form the basis of the ALL_SOURCE database view.

So that explains why my first attempt failed. But I still have a problem as I don't know the obj# for the dropped object. However, I was able to find it out by searching sys.source$ like this:

select * from sys.source$
as of timestamp (sysdate-1/24)
where upper(source) like '%MY_PACKAGE%';

Inspection of the results revealed the required obj#, and I was finally able to obtain the source of the dropped package body. Phew!