Oracle Live SQL revised

2 min read

Sep 7, 2018 12:00:00 AM

If you're not familiar with the Oracle Live SQL environment, then you should definitely try it. I wrote a blog some time ago on what it is and how to work with execution plans there. Here I will provide an overview of some of the new features added since that time. Let's start with the interface. In the earlier release it was impossible to select and execute only one query among others. Now you can do just that. You are now able to select a query from the list and push the "Run" button:

It really helps with different tests and development when you have to multiply statements and run them in a different order. The second is about execution plans. In the past you had to create a plan table on your own and select from it. That time is over and now you can use the familiar interface of the dbms_xplan package. Creating a test table.

create table t1 as select rownum id, rpad('x',50,'x') str from dual connect by level <10001;

Run the "explain plan for" statement for the query we want to check.

explain plan for select /*+ gather_plan_statistics */ id from t1 where str='y';
 select * from table(dbms_xplan.display(format=>'ALL'));

It works pretty well and only formatting needs to be fixed. So far it replaces tabs by spaces and it breaks the output from the dbms_xplan. I've written to Oracle with a request to fix that issue and as I am writing this blog post I've gotten confirmation from the Oracle team that it's going to be fixed in the next release. So, we are going to see the proper formatting pretty soon. Design tool. Now even if you don't know SQL you can try to use the design tab and create objects using the examples provided.

This may be useful for those who are taking their very fist steps in Oracle and need help with creating objects. Supplied schemas. If you don't want to create your own tables you can use the supplied schemas. These schemas are read only for you, but can be used to test your queries or to create your own objects. The schemas include familiar HR, SCOTT,OE and some new like WORLD, OLYM and others. You can read more thoroughly about the schemas using the help page.