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
To get it running, just follow these simple steps:
Download and Install Java in case you don’t have it already
[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:
$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
— We have different formatting options (ANSICONSOLE is my favourite since adjust columns width automatically)
— 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 ~]$
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 ~]$
Comments