Yes, I admit it. I am sometimes not at all as structured as I would like to think. Recently I had worked on a query that had taken me many hours to get just right. When I was done I celebrated with a coffee break (I’m a Swede after all) and after it I started working on other pressing issues.

A couple of weeks later that query is desperately needed as the same test needs be be done again and the setup has to be  done all over. Now where is that script? Surely I saved it to a file. Nope, it is nowhere to be found. But didn’t I send it to a colleague to celebrate making it work, I had thought of it as I knew it would be of interest? No, no trace of it in email either. This other person I tend to discuss things with over Slack, it must be there. No, Slack has no recollection of any such message.

What on earth? Where is it? That sinking feeling is starting to get hold of me, you git you never saved it and now you will have to start from scratch. I was violently against this, not only would it be boring and accepting defeat it would also be HARD. It took a lot of fiddling to make it work the first time, it would be faster now but did I mention it would be BORING?

Surely it is in the history for SQL Developer? No, it must have been pruned. I guess I write way too many SQLs I guess.

Now, where else could it be? Wait since I ran it and I know which database I could maybe find it in V$SQL? Seriously? It was over two weeks since I ran it. But I just had to check, nope, of course not. It would have been too weird in a database that has a fair use every day.

But this got me thinking. Could this be yet one more use for ASH/AWR? It reports SQL after all. After some mining for where SQL is saved in it turns out dba_hist_sqltext has SQL AWR found of interest during a snapshot.

With that I could find my SQL with this as I knew the created table included the letters PNR, which is short for “personnummer” – the Swedish equivalent of SSN in the US.

select * 
  from dba_hist_sqltext 
 where sql_text like '%pnr%';

So there it was, all the glorious variants I had been testing. Victory was snatched from the jaws of defeat!

This is of course not a good practice for how to look up scripts as scripts that has little impact will not be saved and this will at some point be trimmed also. But if you have forgotten to save it may be worth knowing that as a last resort it could be in the AWR history.

NOTE: Access to this table requires EM Diagnostics Pack to be licensed. If you have not it flags it as a used feature and risks having to license it in the future.