32

I know how to do this in SQL Server thanks to this clever bit of code 

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] 
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

SOURCE: Recover unsaved SQL query scripts

Is there a way to do this in Oracle SQL Developer?

Community
  • 1
  • 1
Matt
  • 14,906
  • 27
  • 99
  • 149

6 Answers6

57

This has saved my butt several times.

It is really a problem when you lose unsaved code. For about a month I have been working on a big procedure, and forgot to save the code in SVN. If you read this and remember have such unsaved code, commit it immediately! :) Because everything could happen with your test db. Ok. you're lucky if you were using Oracle SQL Developer, because this program has a great feature - it saves your code in its sql history inspite of eating a big party of your RAM. Open your file explorer and locate this folder:

C:\Users\%USERNAME%\AppData\Roaming\SQL Developer\SqlHistory

You'll find a lot of XML files, and if you're twice lucky, you'll find your lost code. It's wonderful. :) . If you're using another program, try to find a feature like this and maybe it helps you. My condolences if this post doesn't help you, in any case, try to find something good among the next: 1) write your code again, and it will be better than before as you did it once 2) commit your code, so you'll not face such problem in the future

Audwin Oyong
  • 2,247
  • 3
  • 15
  • 32
mmmmmpie
  • 2,908
  • 1
  • 18
  • 26
  • 1
    This one saved me a lot of redo work today. Thanks! – James Drinkard Jun 16 '17 at 13:19
  • 4
    I think this history is the same history that is accessible via the GUI using the steps shown by Alberto Cerqueira above "View -> SQL History" (F8 keyboard shortcut). – GaZ Sep 04 '19 at 11:06
52

If you have the privileges then: 

SELECT * FROM v$sql

If not then press F8 to bring up a list of previously ran queries.

Matt
  • 14,906
  • 27
  • 99
  • 149
  • this and all other solutions will provide only ```select``` statements that you ran. Any other commands ```alter``` for instance will not be found – Kay Apr 06 '20 at 12:11
  • @Kay, this is not correct. I am using SQL Developer version 21.4 and it does show all the commands executed including SELCT, ALTER, truncate, etc. – A N Jul 15 '22 at 13:37
20

You can try too, so you can get the unsaved SQL.

View > SQL History, for example, look this picture: enter image description here

Stas BZ
  • 1,184
  • 1
  • 17
  • 36
Alberto Cerqueira
  • 1,339
  • 14
  • 18
3

This is using SQLDeveloper's history, like in Matt's answer, but if you want to search through the history files for specific query fragments you remember, they are located as .xml files in /home/username/.sqldeveloper/SqlHistory. From there, enter:

find -type f -mtime -1 -print0 | xargs -0 grep -l <text>

(where -mtime -1 means no sooner than one day ago).

Stephen C
  • 96
  • 7
3

Through View> SQL History or by pressing F8 is a great way to do it, it lets you search for content or database connection, really a good implementation!

SQL History

Santiago Coria
  • 31
  • 2
0

Use the undo button, as shown in the image

picture of undo and redo buttons

Adriaan
  • 17,741
  • 7
  • 42
  • 75
shay quinn
  • 1