Entries by Zohar Elkayam

,

PL/SQL Teaser: A Valid Package With No Callable Subprograms

Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):

https://twitter.com/sfonplsql/status/583365900373377024

The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question and I found myself thinking about it while working.

Since it is really had some good things we could learn from it, let’s go over my thought process and get to know this unique PL/SQL behavior.

Read more →

/0 Comments/by
, , ,

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

NoSQL Joke and DBA humor

Once, a very long time ago, I told an old joke about SQL and it was a huge success (not really).
This week I heard a joke that is slightly related to that joke and it made me giggle for a couple of minutes.
Those who know me, knows that I really like to hear jokes but I’m really terrible at telling them – but let’s go for it anyway.
Read more →

/0 Comments/by

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
, ,

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
,

Introduction to Big Data, Hadoop and NoSQL

In the last few weeks I participated in the training of a DBA course in John Bryce education center in Israel.

The course is titled “Master DBA” – it’s an 8 month evening course to train new DBAs from head to tail. It’s divided into two parts; the first part is about SQL, PL/SQL, and OU “Oracle Database Administration Workshop” parts 1 and 2. The students are then encouraged to take the OCA and OCP certification exams. After finishing this part, the students learn Linux, scripting and other IT skills a DBA should have.

In the second half of the course, the students learn about Big Data (mostly Hadoop), Python (as a programming language) and NoSQL (MongoDB or CouchBase, not really sure).

Yesterday I gave a class about the introduction to Big Data, Hadoop, and NoSQL.

Read more →

/2 Comments/by