8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

SQL*Plus Enhancements in Oracle Database 12c (12.1 and 12.2)

This article provides a brief description of the enhancements to SQL*Plus in Oracle Database 12.1 and 12.2.

12c Release 1 (12.1)
12c Release 2 (12.2)

12c Release 1 (12.1)

Last Login Time

By default SQL*Plus displays the last successful login time in the banner.

$ sqlplus test/test@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 22 15:04:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 22 2017 14:45:04 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

The last successful login time can be removed from the banner using the -nologintime command-line option.

$ sqlplus -nologintime test/test@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 22 15:07:10 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

PDB Support

SQL*Plus includes support for the multitenant architecture.

The SHOW command displays information about PDBs.

SQL> SHOW CON_ID

CON_ID
------------------------------
1
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW PDBS

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL>

The STARTUP and SHUTDOWN commands now support pluggable databases, as shown here.

Miscellaneous

SQL*Plus automatically displays implicit statement results returned from stored procedures. You can see examples of this here.
SQL*Plus can display invisible columns using the DESCRIBE command by setting the COLINVISIBLE option. You can see examples of this here.
SQL*Plus supports the new SYSBACKUP, SYSDG and SYSKM user privileges introduced in 12.1, as described here.

12c Release 2 (12.2)

login.sql

In previous releases, SQL*Plus checked the current working directory for the User Profile (login.sql) file, followed by the directories specified in the ORACLE_PATH environment variable on UNIX/Linux or SQLPATH on Windows.

The 12.2 version of SQL*Plus no longer searches in the working, so the location of the "login.sql" file must be referenced in the ORACLE_PATH or SQLPATH environment variable, depending on your operating system.

# UNIX/Linux
export ORACLE_PATH=/path/to/my/scripts/

Rem Windows
set SQLPLATH=C:\path\to\my\scripts

Alternatively, include a direct reference to the script at the start of your other scripts.

@@/path/to/my/scripts/login.sql

The 12.2 behaviour may be back-ported to 12.1 in a future security patch.

Check out these resources.

Security change in Oracle Database 12.2 with login.sql
SQL*Plus 12.2.0.1.0 Change in Behavior for Search Path of Login.sql (SQL*Plus User Profile Script) (Doc ID 2241021.1)
Bug 25804573 : SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL
Configuring SQL*Plus

Command History

Before we can use the HISTORY command we have to make sure it is turned on. In the example below we attempt to use the HISTORY command with no options and we are told the HISTORY command is not enabled, so we enable it as instructed.

SQL> HISTORY
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.
SQL> SET HISTORY ON
SQL> HISTORY
SP2-1651: History list is empty.
SQL>

Checking the help for the HISTORY command, we get the following usage.

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>

Let's put some statements in the history and try a few commands.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT SYSDATE FROM dual;
SELECT 'Banana' FROM dual;

SQL> HISTORY
  1  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
  2  SELECT SYSDATE FROM dual;
  3  SELECT 'Banana' FROM dual;

SQL>


SQL> HISTORY 2 RUN

SYSDATE
--------------------
22-APR-2017 13:49:41

SQL>


SQL> HISTORY 1 DELETE
SQL> HISTORY
  1  SELECT SYSDATE FROM dual;
  2  SELECT 'Banana' FROM dual;

SQL>

Performance Settings

SQL*Plus now includes the following performance options.

SET LOBPREFETCH {0 | n}
SET ROWPREFETCH {1 | n}
SET STATEMENTCACHE {0 | n}

They can be set explicitly, or use the SQL*Plus command-line option "-f" or "-fast" that amends a number of defaults to improve performance. We can display the regular defaults and the "-fast" defaults as follows.

sqlplus / as sysdba

SQL> SHOW ARRAYSIZE LOBPREFETCH PAGESIZE ROWPREFETCH STATEMENTCACHE
arraysize 15
lobprefetch 0
pagesize 14
rowprefetch 1
statementcache is 0
SQL>


sqlplus -fast / as sysdba

SQL> SHOW ARRAYSIZE LOBPREFETCH PAGESIZE ROWPREFETCH STATEMENTCACHE
arraysize 100
lobprefetch 16384
pagesize 50000
rowprefetch 2
statementcache is 20
SQL>

MARKUP CSV

The MARKUP option now includes a CSV option.

   MARK[UP] {HTML html_option|CSV csv_option}
     html_option;
       {ON|OFF} [HEAD text] [BODY text]
       [TABLE {ON|OFF}] [ENTMAP {ON|OFF}]
       [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
     csv_option:
       {ON|OFF} [DELIM[ITER] {c}]
       [QUOTE {ON|OFF}]

Here's a quick example of it.

SET MARKUP CSV ON

SELECT level AS ID,
       'Description of ' || level AS description
FROM   dual
CONNECT BY level <= 5
ORDER BY 1;

"ID","DESCRIPTION"
1,"Description of 1"
2,"Description of 2"
3,"Description of 3"
4,"Description of 4"
5,"Description of 5"

SQL>

FEEDBACK ONLY

The FEEDBACK ONLY option displays the number of rows returned, but not the data. It can be handy when you want to check the actual execution plan of a statement without displaying all the rows, as shown below.

SET FEEDBACK ONLY

SELECT level AS ID,
       'Description of ' || level AS description
FROM   dual
CONNECT BY level <= 5
ORDER BY 1;

5 rows selected.

SQL>


SET FEEDBACK ON LINESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID	f6a46jn2dsjj9, child number 1
-------------------------------------
SELECT level AS ID,	   'Description of ' || level AS description
FROM   dual CONNECT BY level <= 5 ORDER BY 1

Plan hash value: 3764016154

------------------------------------------------------------------------------
| Id  | Operation		      | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      |      |	     |	   3 (100)|	     |
|   1 |  SORT ORDER BY		      |      |	   1 |	   3  (34)| 00:00:01 |
|   2 |   CONNECT BY WITHOUT FILTERING|      |	     |		  |	     |
|   3 |    FAST DUAL		      |      |	   1 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------


16 rows selected.

SQL>

Miscellaneous

SQL*Plus supports the new SYSRAC user privileges introduced in 12.2, as described here.
SQL*Plus supports the new long identifiers introduced in 12.2, as described here.
The "-ac" command-line option sets the Application Continuity flag for the session.
The VARIABLE command now supports input binding which can be used in SQL and PL/SQL statements, as documented here.

SQLcl

The SQLcl tool is now shipped with the database. This is and alternative to SQL*Plus that you might want to try.

$ sql test/test@pdb1

SQLcl: Release 12.2.0.1.0 RC on Sat Apr 22 15:59:49 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 22 2017 15:59:50 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

You can read more about SQLcl here.

SQLcl : All Articles

For more information see:

Hope this helps. Regards Tim...

Back to the Top.