On my article about auto increment columns in Oracle Stephane asked about the use of dual in Oracle.

Dual is sort-of a dummy table. It’s a real table, but not one that should ever get updated. It exists in every Oracle database and is useful for troubleshooting and development.

If you describe dual you will see it’s definition:

SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)

Dual has only one row, but you can select the value of it.

SQL> select * from dual;

D
-
X

Dual can be selected by any user. Since it contains just the one row you can use it to return a single result to you, like

SQL> select sysdate from dual;

SYSDATE
---------
11-AUG-06

Now you could have done this against any single-row table. You could even create a single row table to select results like this, but since Oracle provides the dual table everyone tends to use it.

Thanks for the question Stephane. I think many people use Dual without knowing (or even wondering) what it is.

oracle, database administration, dba, database development

Oracle Shell Scripting by Jon EmmonsAs of today I have gone under contract with Rampant TechPress to write the book Oracle Shell Scripting: Linux and UNIX Programming for Oracle. This book explores shell scripting as a means to automate and improve the environment in which many of us run Oracle.

Shell scripting is necessary for automating a broad range of administration tasks on Oracle servers. Many Oracle administrators and developers lack a strong system administration background and therefore manually perform these tasks at the cost of time, money and sustainability.

This book will discuss how to automate many of the common Oracle administration tasks as well as the methods necessary to automate site-specific tasks. Special attention will be given to writing industry strength scripts which free the Oracle professional from daily maintenance tasks yet provide proper tracking and feedback to ensure the best possible operation.

While this will be quite a diversion from blogging (in more than one way) I am quite excited about publishing a longer work. I have always taken a quiet pride in my shell scripts as they chug away nightly backing up databases and monitoring the system. The opportunity to give such an essential topic a full treatment should be quite rewarding.

Having been a Solaris and Linux system administrator for quite some time and now beeing a full-time DBA I don’t know how I could survive without my army of shell scripts. Even more I remember how hard it was to learn shell scripting and UNIX in general. Now my hope is that I can lend some of my experience, and some pre-fab scripts to others.

Speaking of pre-fab scripts, the book will come with over 50 working shell scripts for a broad range of purposes. Read all about the book and even check out the table of contents at Rampant’s site.

Well, the book won’t hit shelves until May of next year, but you can expect to hear more about it from me as that date gets closer.

Burleson Consulting is offering this free poster of the Oracle 10g data dictionary views

10g Data Dictionary Poster


This is a nice compliment to the Tusc V$Views poster. You pay $5.99 for shipping, but believe me, the first time you use this in a pinch to find the name of one of those pesky dictionary tables it will have paid itself off.

Get yours now from Burleson Consulting and while you’re there check out Burleson Consulting’s Daily Oracle News

oracle, data dictionary, database administration, dba, dbms

Syam asked a while ago how we could find something like the first Monday or third Saturday in a month. Well Syam, it’s taken me a while to respond but here we go.

For this example we’ll use sysdate as input but any Oracle date will work. You can also substitute any other day of the week for Monday.

The first day of the month is probably a good place to start:

SQL> select sysdate from dual;

SYSDATE
---------
18-JUL-06

SQL> select trunc(sysdate, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-06

Now that we’ve got that we can find the first Monday with the next_day function. Of course we need to remember the next_day function looks for the next named day after the date provided so we subtract 1 day from the date in case the first is a Monday.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;

NEXT_DAY(
---------
03-JUL-06

Now that we have the first Monday of the month we can add 7 days to find the second Monday or 14 to find the third.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+7 FROM dual;

NEXT_DAY(
---------
10-JUL-06

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;

NEXT_DAY(
---------
17-JUL-06

So from here you can change the day you’re looking for or the week number you want it in.

oracle, date functions, sql, database, database development, pl/sql

Despite new “self tuning” features in recent versions of Oracle, database tuning continues to be an essential part of the DBA skill set, but where do we acquire these skills? There is no substitute for experience, but once in a while there’s a roadmap for it.

Oracle Tuning: The Definitive ReferenceIn their new book Oracle Tuning: The Definitive Reference, Alexey B. Danchenkov and Donald K. Burleson reveal a holistic, platform agnostic approach to tuning the Oracle RDBMS. Both proactive and reactive tuning are given ample treatment while always conveying the “why” and not just the “how”. The techniques presented are complimented by a free copy of the Workload Interface Statistics Engine (WISE) tool (available via download), written by Danchenkov, which provides an interface into the tuning tables and views in Oracle.

The authors, clearly tempered by years of experience, take a very realistic approach to database tuning. They acknowledge that the DBA may not have the time, ability or influence to bring upon an application rewrite or change in server architecture. The bulk of the book focuses on tuning methods within the realm of the database administrator (though all areas affecting Oracle performance are covered.) While focusing on Oracle Database 10g the authors present tuning concepts and techniques in a way that many of the techniques and nearly all the concepts are applicable to all Oracle RDBMS versions.

Thoroughly covering everything from disk to SQL the book is littered with the exact commands you will be running in the field including example output and analysis. The authors have also included several pages of “Silver Bullet” tuning examples. These examples demonstrate how a quick diagnosis and the right tweak can save the day.

Testing a hypothesis on a large active database is like trying to tune a car while it’s flying down the freeway at 75 miles per hour.

This book is not for the beginner. If you do not feel confident about your knowledge of the Oracle architecture you will feel overwhelmed by this book. Of course if you do not feel confident about your knowledge of the Oracle architecture you should not be tuning a database.

For those comfortable with Oracle but new to tuning there will be many paragraphs you will read, re-read, then read again, but Danchenkov and Burleson have not missed a step. On almost every topic there are a couple notes on common pitfalls and how to avoid them. The authors have really taken great care to shepherd you safely through all steps of tuning the database.

In barely less than 1,000 pages, Danchenkov and Burleson have compiled the definitive reference for Oracle tuning. Coupled with a good background in Oracle, this book contains everything you need to tune almost every aspect of the Oracle database. I highly recommend it to the Oracle professional looking to learn about tuning or the experienced tuner looking for a good reference. The type of tuning presented in this book could easily lower your hardware costs and make you a rock-star DBA.

Oracle Tuning: The Definitive Reference
By Donald K. Burleson and Alexey B. Danchenkov
Copyright 2005 by Rampart TechPress. All rights reserved.
Kittrell, North Carolina, USA.

Available at Rampart-Books.com

« Previous PageNext Page »