History Option on Sqlplus

 Starting from oracle 12.2, SQL*Plus can keep the history of the commands executed.

You can enable or disable the HISTORY command in the current SQL*Plus session by using the SET HISTORY command.

You can view , delete and run statements easily , instead of scrolling up and down and copy then run previous SQL commands .This would be very useful when running multiple complex statements and you need to re-run them multiple times .

SQL> select name from v$database;

NAME

---------

JAYDEMO

SQL> show hist;

history is OFF

SQL> set hist on;

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

JAYDEMO.DEMO.EXAMPLE.COM

SQL> select * from dual;

D

-

X

SQL> history

  1  select * from global_name;

  2  select * from dual;

By default , history keep record of last 100 commands , if you need to increase it , you can do the following :

SQL> set history 1500

SQL> show history

History is ON and set to “1500”

How to automatically turn on this option

But Turing on history every time we login to sqlplus would be challenging. so we can leverage glogin.sql (or) login.sql which would execute the statement we defined every time we login.

The Site Profile file, glogin.sql, for site wide settings. -  $ORACLE_HOME/sqlplus/admin/glogin.sql

Additionally, the User Profile, login.sql, sets user specific settings.  (sqlplus takes the file under $ORACLE_PATH)

Please add below entries in either glogin.sql (or) login.sql 

set hist on;

set history 1000