Thursday, August 09, 2007

Flashback for source code

Recent OTN forum question made me think that I'm quite lucky. Everywhere I've worked we had one or another source control software for source code. All scripts for schema creation as well as procedural units were put and maintained there. Sometimes reading about other people's problems that they've lost the only (sic!) version of their procedure dropping the schema or just recreating the procedure gives me the shudders. So for everyone that yet hasn't understood - keeping procedural units only in the db means it is just matter of time when you'll lose them. Either all of them or just one because of erroneous create or replace but sooner or later the time will come ;)

OK but what if you are unlucky and just realized that you have overwritten the last and only version of your source code unit (procedure, package, function, etc)? If you are at least on 9i (10g as well of course) you are not completely lost. Flashback that works on regular user's tables works in the same way also on internal tables holding source code. So the only problem remains how to get it and how to get it fast! Fast because it is just like with lost extremities - surgeon can put it back only if you are acting fast. And fast because the old data can simply be overwritten if Oracle feels the necessity. And now what's the mechanism is?

There are 2 scenarios:
1. query directly from base tables owned by sys or
2. query using provided dba/all/user views i.e. dba/all/user_source.

In any case for ordinary user by default it is not possible. You need sys access to do that or grant privilege from sys to any other user. Even DBA role is not enough.

1st scenario is as follows:

1) get object_id (from dba/all/user_objects)
SELECT object_id
FROM user_objects
WHERE object_name = 'yourname'
AND object_type = 'yourtype';

2) get text from source$ as of timestamp
SELECT source
FROM sys.source$
AS OF timestamp timestamp_before_error
WHERE obj# = object_id_from_1st_step
ORDER BY line;

1st step can be done by everyone. 2nd step needs flashback on source$.

2nd scenario is as follows:

simply select text from user/all/dba_source as of timestamp.
FROM dba_source
AS OF timestamp timestamp_before_error
WHERE name = 'yourname'
AND owner = 'yourowner'
AND type = 'yourtype'
ORDER BY line;

Of course this can be done by sys. I was not able to do that by other user because user_source for example is based on source$, obj$ and fixed table x$joxfs. I was able to grant flashback on source$, obj$ to another user but for fixed tables it is not possible (quite understandable as these are actually memory structures). But it seems on the other hand without flashback one cannot make queries from view that is based both on ordinary tables and fixed tables.
Above experiments were performed both on and EE Oracle.

No comments: