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 18.104.22.168.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):
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
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>
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"
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
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
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 ReplyWant to join the discussion?
Feel free to contribute!