While playing with Oracle 12.2 Database on the Oracle Cloud, I noticed that SQL*Plus has a new feature which would be very useful for all DBAs. Now SQL*Plus can keep history of the commands executed, and let you see and run these commands from the history. Its usage and functionality is very similar to Unix history tool, but do not think that pressing the UP arrow will return to previous commands 🙂

The history feature is not enabled by default, so after you start SQL*Plus, you need to enable history by running “set hist[ory] on”:

After you issue the above command, all commands (except history commands) will be stored in the history list. The default length of the history list is 100. It means it stores 100 entries, and then first entry will be deleted to make space for the 101st one. You can write a number instead of “on” keyword, so the number will be the size of the history. For example, “set hist 10” command will enable history which will keep only last 10 entries.

You can run “show hist” to be sure that the history is enabled:

You can list all entries in the history list by running “hist[ory]”, and run any command by its number:

You can delete all history by running “hist clear”:

As I see, it keeps the commands in memory, it doesn’t store it in a file like Unix shells do. So after you exit from SQL*Plus, history is cleared.

For more information, you can check oracle documentation