SQL Plus is nice and very handy as well as very integrated with Oracle Databases now days.

If you do Database management, is impossible to think about any other simpler way to connect to the Database and make some changes or check on the system.

However, unless you add some external "plugins" like rlwrap (https://oracle-base.com/articles/linux/rlwrap)) for command history, you dont have a lot of options about output formatting or alias, history.. etc..

If you are tired of SQL Plus and want to try something new, I recommend you to check SQLcl.

https://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.htm

SQLcl Download

To get it running, just follow these simple steps:

Download and Install Java in case you don’t have it already

Java

Java Download

[root@rac5-node1 ~]# rpm -ivh jdk-8u201-linux-x64.rpm
warning: jdk-8u201-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:jdk1.8                 ########################################### [100%]
Unpacking JAR files...
    tools.jar...
    plugin.jar...
    javaws.jar...
    deploy.jar...
    rt.jar...
    jsse.jar...
    charsets.jar...
    localedata.jar...
[root@rac5-node1 ~]# su - oracle
[oracle@rac5-node1 ~]$

Download SQLcl from https://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html and Unzip it:

[oracle@rac5-node1 ~]$ unzip sqlcl-18.4.0.007.1818.zip
Archive:  sqlcl-18.4.0.007.1818.zip
   creating: sqlcl/
   creating: sqlcl/bin/
   creating: sqlcl/lib/
  inflating: sqlcl/bin/sql
[.....]
  inflating: sqlcl/lib/commons-codec.jar
  inflating: sqlcl/lib/jsch.jar
  inflating: sqlcl/lib/jline.jar
  inflating: sqlcl/lib/orajsoda.jar
[oracle@rac5-node1 ~]$

Check that it works

[oracle@rac5-node1 ~]$ /home/oracle/sqlcl/bin/sql

SQLcl: Release 18.4 Production on Sun Feb 17 09:17:45 2019

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

Username? (''?)

There are different connection strings that works with sqlcl.

These are the ones I normally use.

To connect as Any user and to a Instance running in your host

[oracle@rac5-node1 bin]$ ./sql P42/Welcome1@localhost:1521/P42

SQLcl: Release 18.4 Production on Thu Feb 14 20:35:39 2019

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

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

SQL>

To connect using SYS 

[oracle@rac5-node1 bin]$ ./sql sys/Welcome1@localhost:1521/P42 as sysdba

SQLcl: Release 18.4 Production on Thu Feb 14 20:37:22 2019

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

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

SQL>

What about connecting to a different node?

[oracle@rac5-node1 bin]$ ./sql sys/Welcome1@rac5-node2-vip:1521/P42 as sysdba

SQLcl: Release 18.4 Production on Sun Feb 17 09:23:29 2019

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

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

sys@rac5-node2-vip:1521/p42>
sys@rac5-node2-vip:1521/p42>SELECT NAME as DB_NAME,INSTANCE_NAME,CDB , HOST_NAME,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE, status from V$INSTANCE, V$DATABASE
  2  order by 2;

DB_NAME   INSTANCE_NAME        CDB HOST_NAME                      STARTUP                                  DATABASE_ROLE    OPEN_MODE            STATUS
--------- -------------------- --- ------------------------------ ---------------------------------------- ---------------- -------------------- ------------
P42       P422                 NO  rac5-node2.raclab.local        14-FEB-2019 06:01:35                     PRIMARY          READ WRITE           OPEN

sys@rac5-node2-vip:1521/p42>

There is a nicer option to avoid the long connection string using TWO_TASK:

Two Task

$export TWO_TASK=localhost:1521/P42  

[oracle@rac5-node1 bin]$ ./sql sys/Welcome1 as sysdba

SQLcl: Release 18.4 Production on Thu Feb 14 20:58:40 2019

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

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

SQL>

Now, we can get different advantages like Autocomplete using "Tab" like we have in BASH

Tab animation

— We have different formatting options (ANSICONSOLE is my favourite since adjust columns width automatically)

Format

— And what is even better, we have nice History of the recent commands: 

SQL> history
History:
  1  select * from global_name;
  2  SHOW
  3  select HOST_NAME, STAtus from V$INSTANCE;
  4  desc v$instance;
  5  select * from p42.TEST;
  6  alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
  7  drop table p42.test;
  8  alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
  9  select sysdate from dual;
 10  select count(*) from p42.test;
 11  select count(*) from p42.test1;
 12  drop table p42.test1;
 13  col file_id for 999
 14  col File_name for a80
 15  col MAX_SIZE_GB for 9,999,999,999
 16  col USERBYTES_GB for 9,999,999,999
 17  col SIZE_GB for 9,999,999,999
 18  col INCREMENT_BY_MB for 9,999,999,999
 19  select file_id, tablespace_name, File_name,round(bytes/1024/1024/1024) SIZE_GB,AUTOEXTENSIBLE, round(MAXBYTES/1024/1024
 20  col HOST_NAME for a50
 21  col HOST_NAME for a30
 22  col INSTANCE_NAME for a20
 23  col STARTUP for a40
 24  col OPEN_TIME for a40
 25  col NAME for a20
 26  SELECT NAME as DB_NAME,INSTANCE_NAME,CDB , HOST_NAME,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE
 27  k
 28  select * from V$INSTANCE;
 29  clear
 30  @tbs.sql
 31  select INSTANCE_MODE, STARTUP_TIME, HOST_NAME, INSTANCE_NUMBER from gV$INSTANCE;
SQL>

And colours!!
You can actually make sure you see any warning or issue by formatting your script 

[oracle@rac5-node1 ~]$ cat tbs.sql
set sqlformat ansiconsole
with ts_details as
(
    select rpad(tablespace_name,30, ' ')||' '||lpad(floor(used_percent), 3, ' ') as ts_line,
        used_percent
    from dba_tablespace_usage_metrics
)
select
    case
        when used_percent > 70 then '@|bg_red '||ts_line||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||'|@'
        else '@|bg_yellow '||ts_line||'|@'
    end as ts_usage_percentage
from ts_details
/
[oracle@rac5-node1 ~]$

Colours

You can also create alias so you don’t need to repeat long queries or create sql files 

[oracle@rac5-node1 bin]$ cat login.sql
set sqlformat ansiconsole

alias status=SELECT NAME as DB_NAME,INSTANCE_NAME,CDB , HOST_NAME,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE, status from GV$INSTANCE, V$DATABASE order by 2;
[oracle@rac5-node1 bin]$

[oracle@rac5-node1 bin]$ export SQLPATH=/home/oracle/sqlcl/bin/
[oracle@rac5-node1 bin]$ /home/oracle/sqlcl/bin/sql sys/Welcome1@localhost:1521/P42 as sysdba

SQLcl: Release 18.4 Production on Sun Feb 17 11:56:00 2019

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

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

SQL> alias
locks
sessions
status
tables
tables2
SQL>

SQL> status
DB_NAME   INSTANCE_NAME   CDB   HOST_NAME                 STARTUP                DATABASE_ROLE   OPEN_MODE    STATUS
P42       P421            NO    rac5-node1.raclab.local   14-FEB-2019 05:58:29   PRIMARY         READ WRITE   OPEN
P42       P422            NO    rac5-node2.raclab.local   14-FEB-2019 06:01:35   PRIMARY         READ WRITE   OPEN

SQL>

If you want to know more about SQLcl, please visit following links:

https://floobar0.wordpress.com/2018/06/14/sqlcl-login-sql-format-and-alias/
http://barrymcgillin.blogspot.com/2015/02/connections-types-in-sqlcl.html

https://mikesmithers.wordpress.com/2015/04/12/sqlcl-the-new-sqlplus/#more-6159

UPDATE

I just noted another nice advantage of using SQLCL, and is that will give you the OERR output of any error you get when executing a query:

## Using SQLPLUS:

sys@cdb1211>alter system reset db_recovery_file_dest scope=both;
alter system reset db_recovery_file_dest scope=both
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported

sys@cdb1211>

## Using SQLCL:

sys@rac5-node1/cdb121>alter system reset db_recovery_file_dest scope=both;

Error starting at line : 1 in command -
alter system reset db_recovery_file_dest scope=both
Error report -
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported
32029\. 00000 -  "resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported"
*Cause:    An attempt was made to reset an in-memory parameter value
*Action:   Specify SCOPE=SPFILE if applicable, or explicitly set the
           parameter to the desired value
sys@rac5-node1/cdb121>

[oracle@rac5-node1 ~]$ oerr ora 32029
32029, 00000, "resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported"
// *Cause:  An attempt was made to reset an in-memory parameter value
// *Action: Specify SCOPE=SPFILE if applicable, or explicitly set the
//          parameter to the desired value
[oracle@rac5-node1 ~]$
Last modified: 1 August 2020

Author

Comments

Write a Reply or Comment

Your email address will not be published.