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 forums.oracle.com 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!