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.

Read more →

/0 Comments/by /Estimated Reading Time: 10 Minutes
waterpump

How to manage lost Datapump jobs

I’ve got a customer who runs export scripts for his most important tables using Oracle data pump export. This is a pretty good solution to backup his important tables using dump file for fast recovery of specific tables (this is in no way a proper database backup) or for transferring it to his development environments. The problem is that the tables which we export are very large and the export process might take a very long time to complete (at least a few hours).

Sometimes, we find ourselves in the need to peek into the export process and see what is going on – and figure out what it is doing: is it stack, what table it is exporting or how much time is left till the process completes. Generally, since our scripts are running at the background (using timed cron) and there isn’t really an output we can look at (except the log files). The problem is that the output which goes into the log file is usually not enough to answer all the questions the customer is asking.

In another scenario, we’re running an export (full, for example) and that takes a very long time but then our connection to the server is closed for some reason. It can be a server policy or it can be that we did it by mistake but my most common reason is just loosing the network connection between my client and the server.

In this post we will look at the solution given to us by the datapump processes and understand how the fact that we’re using data pump jobs, actually makes those issues very simple to solve.

Read more →

/4 Comments/by /Estimated Reading Time: 7 Minutes

Oracle ANSI SQL and the WITH syntax

, ,

SQL language has been around for so long, that some people feel it never changes. This is obviously not true – SQL is a dynamic language that changes all the time: some changes come from the vendors themselves but often this is the result of the standard changing and Oracle adopting the new standard in a later version.

Let’s start with a short history lesson. As we all know, SQL is not a unique language for Oracle. It’s a common language with standards set by ANSI. The first version of the language standardization was presented back in 1986 with the issuance of SQL86. In 1992, a revised version of the language came out (SQL-92). When Oracle tried to implement this standard at full, they noticed that some of the things there are really different from how Oracle did things so it adopted only a part of the standard in Oracle RDBMS 8 (and 8i).
Read more →

/0 Comments/by /Estimated Reading Time: 3 Minutes

Installing Oracle 12.1.0.2 – Part 2: Creating an Instance

In the earlier post we installed the Oracle version 12.1.0.2 (software only) but still didn’t create a database instance. In this post we will go over an installation procedure of a basic CDB database instance. This will be the first instance on that database server (and I’ll explain later why this is important).

Read more →

/6 Comments/by /Estimated Reading Time: 7 Minutes

Installing Oracle 12.1.0.2 – Part 1

It’s been a few months since 12.1.0.2 came out. Here is a basic guide for installing Oracle 12c (12.1.0.2).
This is a two-part post – in the first part we will install the software and in the next part we’ll go over creating the new database instance.

Read more →

/1 Comment/by /Estimated Reading Time: 4 Minutes

Database In Memory presentation from the ilOUG SIG meeting

,

A few weeks ago, I was invited to give a lecture at the Israeli user group meeting (ilOUG DBA SIG). The presentation topic was the new Oracle 12c feature – the Database In Memory (aka In Memory Option).
Read more →

/0 Comments/by /Estimated Reading Time: 1 Minute