12cR2 New Feature -sql prompt "history"

12cR2 has really great features to use in day to day life for dba. Here we will be discussing one such feature i.e. SQL prompt "history". Many dba's use history command on UNIX prompt to analyze the things or events, Before 12c, this option was not there to see history of sql's you have executed but from 12cR2 onward this is very much possible.

Lets' try this thing out, below is the demonstration 

Check if history parameter is ON/OFF

    SQL> show history; 

    history is OFF

Set history ON

    SQL> set history ON;

    SQL> show history;

    history is ON and set to "100"

Now execute few queries

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE

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

    TECHCDB   READ WRITE

    SQL> show con_id

    CON_ID

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

    1

    SQL> select count(*) from dba_db_links;

      COUNT(*)

    ----------

             2

    SQL> select sysdate from dual;

    SYSDATE

    ---------

    28-JUL-20

 

Now check the history, you will be able see all the sql's executed.

    SQL> history

      1  show history;

      2  select name,open_mode from v$database;

      3  show con_id

      4  select count(*) from dba_db_links;

      5  select sysdate from dual;

    SQL>

set history OFF

    SQL> set history off

    SQL>

    SQL> history

    SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

this is use of "history" command. Suppose if you forget to turn off history after your work, don't worry it will be only available for that session, it will be automatically turned off.