I have seen the future and it is glorious: SQLcl the next generation of SQL*Plus
The first time I started using commercial databases was in 1998, in my basic programming course. The course was split into segments and at the first databases lesson, if my memory serves me right, the instructor began the class with the words: “Today we will learn about databases – first, let’s connect to the database using SQLPlus”.
This amazing tool, which almost everyone who works with Oracle databases knows, is one of the simplest yet powerful command line interface for working with the database. SQLPlus has been with us for many, many years – I used to use it back when I was using Oracle 7 and that says a lot. Since this is one of the most convenient tools to understand, and while it’s not very user friendly it does work great with scripts and other automatic procedures. SQLPlus have great integration with the cluster tools – for example, if we’re using it to stop an instance (regular shutdown), the grid infrastructure is aware the shutdown was on purpose, and it will not try to start up the instance node again. It even has other Oracle tools provide its users and its capabilities often (AWR reports for example).
The fact that SQLPlus has been around for so many years could have been considered a disadvantage because we would think it might not have changed over the years but this is not the case. The tools did change over the years – features and other capabilities were added and that allows us to do cool stuff like output our result to HTML format or interact with our scripts. Unfortunately that’s pretty much everything and the basics of the tool did not change.
I Recently became aware of a new tool from Oracle which will ultimately replace SQLPlus. It’s name is SQLcl and I was really impressed.
The new tool, SQLcl (and I’m not sure if this is the final name, because his name was replaced few months ago from SDSQL… 🙂 ) is being developed as part of the SQL Developer development. It is one of the most impressive Oracle database command line tools I’ve ever seen. Although it’s been out for only few months now, it seems like an appropriate replacement to the good old SQLPlus.
Before I explain why I’m so excited about this tool, I just want to say that this tool is still in its early beta stages. Some of the things do not work as expected (the latest version which came out this month, fixed 350+ bugs), and some of the features still not implemented. After that being said, it is still very impressive tool and is (for the most part) very stable.
How do we install SQLcl
This tool (in the early adopter version) can be downloaded from here: the official download page SQL Developer Early Adopter
In order to work with the tool, we will need to unzip the file (which is only 11MB) and make sure that we have a Java JRE (not JDK) with the minimum requirement of version 7 (it seems to works well with 8 as well) on the machine. That’s about it: the tool is ready to use. There is no need for an Oracle Client or any other OCI components (unless we actually need an OCI-based authentication). If we use a Linux environment we will also need to change the sql command file to be executable – but that really about it …:)
Once the tool is deployed, we can use it to connect to databases using the EZConnect format (i.e. hostname:port/service or hostname:port:sid):
[oracle@lnx-oracle66-db11g bin]$ sql zohar@localhost:1521/orcldg
SQLcl: Release 4.1.0 Beta on Wed Mar 11 16:58:24 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Password? (**********?) *****
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>
If we do have a client and we want to use its tnsnames file, the tool will automatically look for it in our Oracle homes. It still gets it wrong sometimes but there is a way to debug and work around it. I will leave this to another time (but feel free to ask me in comments if you’re hitting this problem).
What can SQLcl do that SQLPlus can’t?
This tool should eventually be fully compatible with SQLPlus. Most of the commands should be recognized by it and work as expected.
There are tons of new commands, so here some examples (more examples and the end of the post):
Information
Let’s say we have a table but we do not know anything about it. In order to get some information we use describe – it gives us a list of columns and that’s a good start but we still know nothing about primary keys, indexes etc.. At that point we need to begin and explore the table structure through various queries to figure out its primary key, indexes, constraints and so on.
That’s too much work for, is not it?
Here is what this tool offers:
SQL> info mytab
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*OBJECT_ID NUMBER No
OWNER VARCHAR2(30 BYTE) Yes
OBJECT_NAME VARCHAR2(128 BYTE) Yes
SUBOBJECT_NAME VARCHAR2(30 BYTE) Yes
DATA_OBJECT_ID NUMBER Yes
OBJECT_TYPE VARCHAR2(19 BYTE) Yes
CREATED DATE Yes
LAST_DDL_TIME DATE Yes
TIMESTAMP VARCHAR2(19 BYTE) Yes
STATUS VARCHAR2(7 BYTE) Yes
TEMPORARY VARCHAR2(1 BYTE) Yes
GENERATED VARCHAR2(1 BYTE) Yes
SECONDARY VARCHAR2(1 BYTE) Yes
NAMESPACE NUMBER Yes
EDITION_NAME VARCHAR2(30 BYTE) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
ZOHAR.MYTAB_PK UNIQUE VALID OBJECT_ID
ZOHAR.MYTAB_IDX1 NONUNIQUE VALID OWNER, OBJECT_NAME
Pretty cool, right? but that might not be enough – what about information about statistics and histograms?
Here you go:
SQL> info+ mytab
Columns
NAME DATA TYPE NULL DEFAULT LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM
*OBJECT_ID NUMBER No 2 92856 87599 NONE
OWNER VARCHAR2(30 BYTE) Yes APEX_030200 ZOHAR2 26 NONE
OBJECT_NAME VARCHAR2(128 BYTE) Yes /1000323d_DelegateInvocationHa yCbCrSubSamplingType229_T 52264 NONE
SUBOBJECT_NAME VARCHAR2(30 BYTE) Yes $VSN_1 WRH$_WAITST_1398494861_66 132 NONE
DATA_OBJECT_ID NUMBER Yes 0 92856 9698 NONE
OBJECT_TYPE VARCHAR2(19 BYTE) Yes CLUSTER XML SCHEMA 44 NONE
CREATED DATE Yes 2011.09.17.09.46.13 2015.03.11.17.48.13 1778 NONE
LAST_DDL_TIME DATE Yes 2002.10.01.12.41.49 2015.03.11.17.48.13 2558 NONE
TIMESTAMP VARCHAR2(19 BYTE) Yes 1990-08-26:11:25:00 2015-03-11:17:48:13 2273 NONE
STATUS VARCHAR2(7 BYTE) Yes INVALID VALID 2 NONE
TEMPORARY VARCHAR2(1 BYTE) Yes N Y 2 NONE
GENERATED VARCHAR2(1 BYTE) Yes N Y 2 NONE
SECONDARY VARCHAR2(1 BYTE) Yes N Y 2 NONE
NAMESPACE NUMBER Yes 1 64 20 NONE
EDITION_NAME VARCHAR2(30 BYTE) Yes 0 NONE
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
ZOHAR.MYTAB_PK UNIQUE VALID OBJECT_ID
ZOHAR.MYTAB_IDX1 NONUNIQUE VALID OWNER, OBJECT_NAME
CTAS
Do you ever get into the situation in which you need to create a create table as select script?
Not very complicated, right? but what if it’s a partitioned table or has some unique properties to it? and what about indexes and constraints?
Here is the great solution by this tool. Using the simple command CTAS with the source and target table names and it generates a script that does everything for us!
SQL> ctas t t2
CREATE TABLE "ZOHAR"."T2"
( "DUMMY"
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
as
select * from T
SQL>
DDL
Creating table as select is cool, but what if we just want to extract the table structure to script?
We can use DBMS_METADATA but if you ever used it, you would know that it has the tendency to break some of the lines in the middle thus not creating a valid script.
The DDL command extract the full command – including table columns, default values, comments, indexes, constraints, partitions and storage parameters.
A small anecdote: I have a customer who has tables with thousands of partition and is using out of line LOBS (secure files) columns. When we tried to generate the DDL command to create one of the tables for Oracle support, we crushed the GUI tools. The DBMS_METADATA didn’t produce a valid command and we didn’t have a proper solution to the problem. Using this tool we were able to extract the DDL and got a 1.4 MB create table command script.
SQL> ddl mytab
CREATE TABLE "ZOHAR"."MYTAB"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30),
CONSTRAINT "MYTAB_PK" PRIMARY KEY ("OBJECT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CREATE INDEX "ZOHAR"."MYTAB_IDX1" ON "ZOHAR"."MYTAB" ("OWNER", "OBJECT_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Command Alias
Most of the command line DBAs I know keep a pile of ready-to-go scripts which they run a lot. Examples for such scripts can be query for a list of connected session or blocking locks. Wouldn’t it be nice if we could have given a said script a name (somewhat like using a view) and run it on anytime we wanted without referencing our scripts?
We should note that we don’t use views here – we’re just keeping the query (or multiple queries or even SQLPlus like formats) in an alias configuration and run them on demand:
SQL> alias dual=select * from dual;
SQL> dual
Command=dual
DUMMY
-----
X
We can also alias scripts with parameters we set by the user:
SQL> alias dual2=select :parameter from dual;
SQL> dual2 2
Command=dual2
:PARAMETER
--------------------------------
2
Bridge
Suppose we have a query which needs to retrieve data from multiple database – for example the status of the data guard or golden gate between sites.
In some cases (such as the data guard example) we will not want to create a database between those databases, but we would still like to pull the data from all the databases simultaneously.
The solution we use today is to produce an OS script which connect to each database separately, run the queries on multiple databases and keeps the results. Once it returns all the results we need to analyze it, parse it and get the data we need from the output.
The solution given by SQLcl is using the bridge command. The bridge command allows us to transform our client to a bridge between the two databases. The tool will then connect (in parallel) to several databases and will allow us to pull the results via a single ocmmand. The tool uses jdbc to create the connection and works currently only between Oracle databases only.
Another example bridge use is to transfer data between databases which we can’t or don’t want to have a constant connection between (i.e.: production and development environments). When using the bridge command we can create an “insert table as select” command from the two servers without a DB link.
SQL> create table zohar2.t as select 'zohar2' from dual;
Table ZOHAR2.T created.
SQL> bridge zohar_test as "jdbc:oracle:thin:zohar2/zohar2@localhost:1521:orcldg"(select * from zohar2.t);
Table zohar_test : insert succeeded
SQL> select * From zohar_test;
A
------
zohar2
SQL> select * from dual where dummy = (select a from zohar_test);
no rows selected
Summary
If you got here and you’re still not excited about the tool as much as I am, I guess one of us might be in the wrong line of business… 🙂
What I’ve listed here is just a few examples in a nutshell. I didn’t even got to mention the fact that the tool knows how to produce outputs in different formats (such as CSV, JSON or insert commands) in a single command, that it gives us a real command history (including using the up and down arrows to scroll back commands to old), that it knows how to output color output, that it has the auto-complete by using tab button feature, that it shows error messages with their full description from the Oracle documentation, or that it has XQuery queries capabilities.
So to make a long story short, this is a very cool tool. It appears that its developers had taken into consideration all the rants DBAs had on the SQLPlus for the last 10 years or so and tried to solve it with this tool. In my opinion this tool is pretty stable for its basic uses, but it might take some time to make it perfect. I added it to my favorite tools and so far it saved me a lot of time in my everyday work (but it did crush on me a few times). If you choose to try it out, please remember that some of the things still don’t work properly, or not fully implemented. This is still an early adopters versions and still doesn’t even have a place where we can report its bugs.
Bottom line: I see a bright future for this tool – I suggest you get to know it as it might be one of the more interesting tools Oracle had produced for the everyday database activities in years… 🙂
You can read more about this tool on ThatJeffSmith blog and on Kris’ blog.
Leave a Reply
Want to join the discussion?Feel free to contribute!