So I’ve been using Oracle 12cR2 for a couple of weeks now (getting ready to ilOUG meetup in a few weeks), and I decided to share my favorite non-important feature of the new version: the History command for SQLPlus.

As most of you already know, I’m a huge fan of SQLcl (aka SQL Developer Command Line Interface). I’ve written about it a little, and talked about it in conferences a lot. In the last conference someone came up and asked me, “Okay, sqlplus can do most of those stuff – but what do you use the most” and I answered that the history command actually changed the way I work.

That was the case until Oracle 12cR2. Starting 12.2.0.1, SQLPlus now has the ability to show list of last commands. Now, this might sound like a small change, but it’s so useful I’m surprised SQLPlus took only 31 years to implement it…

How to use it

In order to use the HIST[ORY] command, we first need to turn it on. Without it, we will get an error message:

SQL> history
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.
SQL> set history on

I recommend setting this parameter in the glogin.sql ($ORACLE_HOME/sqlplus/admin/glogin.sql) so it will be turned on automatically when you connect to a database.

Once the feature is on, you can use HISTORY (or HIST for short) to show the previous commands:

SQL> history
SP2-1651: History list is empty.
SQL> select * from dual;

D
-
X

SQL> select 1 from dual;

         1
----------
         1

SQL> history
  1  select * from dual;
  2  select 1 from dual;

If we want to use a former command, we can either load it (using hist [n] edit command) or just run it (using hist [n] run command):

SQL> hist 1 edit
SQL> hist 1 run

D
-
X

There are some cases when we would like to be careful with the history command. Since sqlplus now keep the old commands we used, it might have some sensitive data in it. For example, passwords:

SQL> hist
  1  select * from dual;
  2  select 1 from dual;
  3  select * from dual;
  4  create user c##zohar identified by zohar;
  5  alter user c##zohar identified by zoohar;

In that case, we might want to delete the sensitive commands, or clear the history cache

SQL> hist 4 del
SQL> hist 4 del
SQL> hist
  1  select * from dual;
  2  select 1 from dual;
  3  select * from dual;
SQL> hist clear
SQL> hist
SP2-1651: History list is empty.

History help command

This is a pretty straightforward feature – but here is the help command anyway if you get confused or something:

SQL> help history

 HISTORY
 -------

 Stores, lists, executes, edits of the commands
 entered during the current SQL*Plus session.

 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]

 N is the entry number listed in the history list.
 Use this number to recall, edit or delete the command.

 Example:
 HIST 3 RUN - will run the 3rd entry from the list.

 HIST[ORY] without any option will list all entries in the list.