DBA Hub

📋Steps in this guide1/10

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.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Last Login Time

By default SQL*Plus displays the last successful login time in the banner. The last successful login time can be removed from the banner using the command-line option.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ 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>

$ 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>
2

PDB Support

SQL*Plus includes support for the multitenant architecture. The command displays information about PDBs. The and commands now support pluggable databases, as shown here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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>
3

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 command by setting the option. You can see examples of this here . - SQL*Plus supports the new , and user privileges introduced in 12.1, as described here .
4

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 environment variable on UNIX/Linux or 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 or environment variable, depending on your operating system. Alternatively, include a direct reference to the script at the start of your other scripts. 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
# UNIX/Linux
export ORACLE_PATH=/path/to/my/scripts/

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

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

Command History

Before we can use the command we have to make sure it is turned on. In the example below we attempt to use the command with no options and we are told the command is not enabled, so we enable it as instructed. Checking the help for the command, we get the following usage. Let's put some statements in the history and try a few commands.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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>

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>

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>
6

Performance Settings

SQL*Plus now includes the following performance options. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SET LOBPREFETCH {0 | n}
SET ROWPREFETCH {1 | n}
SET STATEMENTCACHE {0 | n}

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>
7

MARKUP CSV

The option now includes a option. Here's a quick example of it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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}]

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>
8

FEEDBACK ONLY

The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>
9

Miscellaneous

- SQL*Plus supports the new 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 command now supports input binding which can be used in SQL and PL/SQL statements, as documented here .
10

SQLcl

The SQLcl tool is now shipped with the database. This is and alternative to SQL*Plus that you might want to try. You can read more about SQLcl here. - SQLcl : All Articles For more information see: - SQL*Plus® User's Guide and Reference 12.1 - SQL*Plus® User's Guide and Reference 12.2 - SQLcl : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
$
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!