Mastering the HISTORY Command in SQLPlus

Did you know that SQLPlus has a handy feature that can significantly boost your productivity and enhance your experience? Ever find yourself retyping the same command over and over again, the one you just typed a few minutes ago?
Think of HISTORY as your personal SQL scratchpad in your current session. This command keeps running a list of the SQL and PLSQL commands you entered before, and allows you to recall, re-execute, edit or delete these commands.

What things can you do with the HISTORY or HIST command and its helpers?

1. you can peek into your past
2. you can time travel
3. tweak and conquer
4. keep it clean

I hope I got your attention with these catchy titles! Let’s dive in and see how you can do each of these activities!

1. Peeking into Your Past: The Basic HISTORY Command

Easiest way, after you entered a few commands is SQLPlus, just type in HISTORY, and see what happens. SQLPlus displays a numbered list of commands you executed in your current session. The list shows the order in which you executed them, with the bottom one being the most recent one. Please note that the HISTORY command will not show the results of any of the statements. It shows you the statement itself.

SQL> HISTORY
      1  SELECT * FROM hr.employees WHERE department_id = 20;
      2  DESCRIBE hr.employees;
      3  SELECT COUNT(*) FROM hr.orders WHERE order_date > SYSDATE - 7;
      4  HISTORY

2. Time Traveler: Re-executing Previous Commands with RUN and @

With the help of the RUN command or the “@” you can execute a command from HISTORY using its number:

SQL> RUN 1

--or

SQL> @1

This will re-run the first command in your history. In our example, it will run the following: SELECT * FROM hr.employees WHERE department_id = 20; Your history in your session will be different than mine.

3. Tweak and Conquer: Editing History with EDIT command

SQL> EDIT 3

This will open command number 3 in your default text editor; make your changes, save the file, and when you exit the editor, the modified command will be loaded into your SQLPlus buffer. You can then execute it with RUN or /. The default text editor is usually defined by the _EDITOR environment variable.

SQL> DEFINE _EDITOR = vi

4. Keeping it Clean: Deleting Commands with DEL and CLEAR commands

Over time, your history might accumulate commands you no longer need. The DEL command allows you to remove specific entries from your history list:

SQL> DEL 2

This command will delete the second command from history, in this case DESCRIBE hr.employees;
You can also use the CLEAR command, which will delete the whole history.

Any of the above commands you can run on one line as well:

SQL>HISTORY  5 run
SQL>HISTORY  3 edit
SQL>HISTORY  2 delete
SQL>HISTORY  clear

You can set HISTORY ON or OFF, by default it is ON. You can check the status of the HISTORY command. Also you can tell Oracle how many commands to keep in history; by default it is 100 commands.

SQL> set HISTORY on
SQL> show HISTORY
History is ON and set to "100"
SQL> set HISTORY off
SQL> show HISTORY
History is OFF
SQL> set HISTORY 50
SQL> show HISTORY
History is ON and set to "50"

Final thoughts on the HISTORY command:

• HISTORY is session bound, it is specific to your current SQLPlus session. Once you disconnect, the history is gone.
• it has basic functionality, however you cannot search the history across past sessions, or other sessions.
• consider configuring your editor in the _EDITOR environment variable, so you can edit your history seamlessly.

The next time you find yourself about to retype a command, remember your trusty HISTORY is just a few keystrokes away!