PLSQL for DBAs

PL/SQL for DBAs

Why, specifically, is PL/SQL important to Oracle DBAs?

The most general answer to that question is simply that DBAs are generally responsible for anything that is in (and executes in) their databases, including the code. PL/SQL is a big part of that total picture. If you are ignorant of the PL/SQL language, you will not be able to evaluate programs for their security, maintainability, or performance. You will also not be able to take full advantage of the many advanced features that Oracle has built into its database and exposes through the PL/SQL language, usually through supplied, or built-in, packages.

Let's take a closer look at these different areas.

Securing the Database

Security has always been a key DBA responsibility, and knowing how to secure your database and applications has become all the more crucial in recent years. Many elements of security are handled directly via SQL statements and database configuration processes and parametersfor example, by establishing passwords and setting roles and privileges. Other, more complex aspects of securityincluding encryption, row-level security, fine-grained auditing, and the generation of random valuesrequire the use of PL/SQL. This book describes these more complex security topics in detail, focusing on the use of Oracle's built-in security packages.

Optimizing Performance

Wouldn't it be great if all developers (a) knew the ins and outs of optimizing SQL statements, (b) kept up with the latest developments in PL/SQL performance (like BULK COLLECT and FORALL), and (c) took the time to tune their code?

Many programmers do, in fact, pay close attention to how efficiently their code runs. Others are happy to get the program "working." The bottom line, however, is that this code is passed on to you, the DBA, to put into production. Therefore, depending on the policies of your specific organizations, it may become your responsibility to make sure this code will not create a problem in your production environment. At the least, sometimes you may have to be consulted on performance issues and suggest alternative approaches. You should be comfortable enough with PL/SQL and its latest upgrades to be able to review code, identify potential bottlenecks, and suggest ways that the development team can improve performance. The chapters of this book that describe ways of optimizing cursor and table function use will be particularly helpful in this area.

Fully Leveraging Oracle Utilities and Features

There was a time when DBAs got by with "straight" SQL and database configuration commands (either on the command line in SQL*Plus or through graphical interfaces like Oracle's Enterprise Manager). Today, DBAs must be able to write at least enough PL/SQL code to implement database and schema triggers, automate many administrative tasks through the use of native dynamic SQL (NDS) and other DDL execution mechanisms, and thoroughly utilize the many new features exposed through Oracle's built-in packages (from streams to queues to replication strategies to cost-based optimizer analysis). If PL/SQL is a stumbling block for you, in terms of your ability to provide optimal database administration for your organization, you will not be able to do your job as effectively as you should.

Mentoring New Developers and DBAs

Many of the developers and DBAs now entering the world of Oracle have relatively little experience in either database design or optimized programming. The more you know about PL/SQLhow it works and how to write good codethe more effective a resource you can be in moving your coworkers forward. The more professional they become, the easier your job will be, and the more widely you will be respected. The bottom line is that you should see PL/SQL as a means to a more senior DBA position within your organization and your industry.