To script or not to script, that is the question — for DBAs

Posted on

Recently I was asked whether a person pursuing a career as an Oracle database administrator needs to have knowledge of scripting languages. One answer to that question could be no — if the potential DBA is someone who really likes performing the same basic tasks over and over, day in and day out. Said person would also need to be interested in putting in lots of extra hours each week from all of the routine maintenance tasks that are sure to pile up.

A more reasonable answer is that any database administrator who does not know how to make use of one or more scripting languages is almost certainly going to wish — eventually — that they did. Also, job listings for database administrators will often indicate knowledge of one or more scripting languages as a requirement. Most experienced Oracle database administrators use scripts to automate some of the tasks required to maintain the database. Writing code is certainly not something that most DBAs perform on a daily basis, but having the ability to write scripts of low to moderate complexity is a definite advantage.

What is a more complicated question to answer is which scripting language or languages database administrators should know. Beyond that, how can they decide on a reasonable skill level to aim for in a given language? And finally, how should they go about becoming proficient?

SQL and PL/SQL

By most definitions, these are not scripting languages. Oracle administrators, however, must have a basic proficiency in writing SQL statements in order to perform their duties. Also, many database automation tasks performed by scripting languages can be made more capable by including SQL within them. Many of the scripts I have written have used an SQL query against the database to determine what they should do. Others have used SQL to write back to the database at the end of the operation indicating when the task was performed and what the results were.

Unlike SQL, being able to write in PL/SQL is often optional for administrators. I have known a number of DBAs who could not write a PL/SQL block to save their lives. However, PL/SQL is very useful for creating intelligent automated operations inside the database.

A good minimum SQL standard for administrators is that set by any of the SQL Fundamentals exams from the Oracle Certification Program. The 11G PL/SQL exam (1Z0-144) covers all of the basics of the PL/SQL language. The level of knowledge required to pass is more than most DBAs really require. Knowing more than you really need to, on the other hand, is seldom a bad thing. Even if you decide not to take the exams, it is worthwhile to look at the topics lists of these exams to get an idea of the fundamental concepts of these languages.

Shell scripting

Shells in Unix/Linux are the command processors that interpret and act on the commands typed into a terminal window. Shell scripting is essentially gathering a number of shell commands together in a file (the script) and then calling that script so that the commands are executed in a batch. Shell scripts can be written that include variables, conditional processing and loop operations to create code with procedural logic.

There are a number of different shells available in Linux. They all perform essentially the same task, but the syntax of each is a bit different. The most common are the Bourne Shell (sh), Bash Shell (bash), C Shell (csh) and Korn shell (ksh). Developers who write a lot of shell scripts tend to have a favorite among them. For the purposes of automating administration tasks, any of the shells will work.

There are a number of books available on shell script programming. The only certification I could locate that addresses the topic, however, was one by Brain Bench for Bash shell scripting. If you learn best with a defined set of information and the goal of passing a test, you might find the test useful in directing your study process.

Perl

Perl is a general-purpose scripting language that is available in all Unix/Linux distributions and for Windows operating systems as well. The structure of Perl is derived broadly from C, and it takes many features from shell programming. It is a procedural language with variables, expressions, control structures and subroutines. Perl has many built-in functions that provide tools often used in shell programming such as sorting, and executing operating system facilities. The text-handling capabilities in Perl can be used for generating SQL queries. It also has built-in functionality that allows it to collect and process the data returned from a query.

As with shell scripting, there are many books available for Perl. The CIW certification authority offers a Perl certification: 1D0-437: CIW Perl Specialist. Even if you are not interested in earning the certification, you might look at the objectives of that exam to get an idea of what topics to study. Finally, Brain Bench has three tests that you might investigate: Perl, Perl 5.12, and Perl 5.8.

Python

Python is another general-purpose, high-level programming language that is often used for scripting. One of the cornerstones of Python is readable code (something Perl has never been accused of). Python is intended to be a highly readable language and programs written in it frequently make use of English keywords where other languages use punctuation. Python interpreters are available for many operating systems. While this is not a language I have used, a colleague who I worked with at Oracle used Python for dozens (possibly hundreds by now) of tasks interacting with the database. He often used it for jobs where I would have used PL/SQL because he was more comfortable with Python.

The only resource I could locate for certifying in Python was Brain Bench. It has a pair of exams for Python 1.5 and Python 2.4. As with the Perl offerings, the test outline may be useful to you even if you would rather not pay to take an exam.

I would not count the ability to use a scripting language as one of the core skills of database administrators. It is certainly useful knowledge and I would expect it of anyone who has worked in the field for any length of time. You should add this to the long list of skills that you should acquire as part of a career as an Oracle DBA.

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
Matthew Morris

ABOUT THE AUTHOR

Matthew Morris is an experienced DBA and developer. He holds Oracle DBA Certifications for releases 7, 8i, 9i, 10G and 11G; Expert Certifications for SQL, SQL Tuning, and Application Express; and is an Oracle Linux 6 Certified Implementation Specialist and an Oracle PL/SQL Developer Certified Professional. He is the author of several Oracle certification guides. His Web site, Oracle Certification Prep, is dedicated to providing links to resources for Oracle certification preparation.

Comment: