sqlplus - new features ( 12c to 18c)

The following are important new sqlplus  features  in 12c & 18c






1)SET FEEDBACK ON SQL_ID


It will show sql_id for the sql statements we execute from sqlplus prompt


SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible);  2
Table created.


SQL_ID: cw7wvs1x99d72




2) SET ROWLIMIT 10


  it limits the number of rows returned from a sql




3) set linesize window


It will make the columns to fit into the window size




4) SET COLINVISIBLE ON


This will allow to show the invisible columns using desc




SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible);  2
Table created.



SQL> desc test_invisible_cols
 Name                                                                                                                                    Null?    Type
 --------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
 EMP_ID                                                                                                                                           NUMBER
 EMP_INFO                                                                                                                                         CHAR(20)


SQL> SET COLINVISIBLE ON


SQL> desc test_invisible_cols
 Name                                                                                                                                    Null?    Type
 --------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
 EMP_ID                                                                                                                                           NUMBER
 EMP_INFO                                                                                                                                         CHAR(20)
 EMP_ACC_NO (INVISIBLE)                                                                                                                           NUMBER
SQL>




5)  sqlplus -nologintime


from 12.2 sqlplus connection will show the last_login ( dba_users has additional column last_login).


if we want to disable the last login time show, we can use -nologintime




6) set history on - Another important feature is sqlplus history
     - we can list the history
     - we can run the command out of the history list
    - we can edit a command from history
   - we can delete all/a command from history
 ( hist clear/ hist 1 delete )





SQL> set history on



SQL> set history on
SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     60537
SQL> show sga
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes

SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FLASH01
db_recovery_file_dest_size           big integer 17793M
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

SQL> history
  1  select count(*) from dba_objects;
  2  show sga
  3  show parameter recovery


SQL> history 2 run
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes
SQL>





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.

SQL>