Oracle Live SQL revised

Posted in: Oracle, Technical Track

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.

You can run select from the schema’s objects directly.

And I should mention that you are going to work using the latest 18c Oracle release and it is absolutely free of charge. Happy coding everyone.

email

Interested in working with Gleb? Schedule a tech call.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *