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
