Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> OT - Another chapter for BDBAFH

OT - Another chapter for BDBAFH

From: lu wesley <>
Date: Thu, 03 Oct 2002 13:21:42 -0800
Message-ID: <>

The phone rings. Would this be the last call he'll ever answer?

The VP's recent communication still echoed in his ears; "market uncertainty", "difficult decisions",
"cost reductions". He recalled a recent conversation
with his manager "What do you do all day, anyway?", his manager had casually asked him.

"What do I do", he thought, as all the scripts he had
ever written as their DBA flashed before his eyes.

First there was the database login trigger - the one that prevented users from accessing the system except through the ERP application. "You're the guru", they had said when it went into production.

Besides disallowing certain connections, the login trigger also happened to save his last login date, in the start_date column of the projects table for the record with project_id of "Test". (You also have test records in your production database don't you?) This date figured importantly in many of his other scripts.

For example, his calculate statistics script, normally uses a 1% sample size for tables greater than 1Gb; 10% sample for tables greater than 128 Mb; and anything smaller uses compute. But if he hadn't logged for more than a week, then compute is used for tables greater than 1Gb. Smaller tables estimate their statistics using 1% of the rows, a couple of tables have their statistics deleted and some have their number of rows, number of blocks and average row length set to the time, the weather and the number of free Mb in the temp tablespace (dbms_stats). He remembers the comments when he returned last summer from two weeks in the country: "Performance was just terrible while you were away, the system literally fell apart, we're so glad you're back..."

Another of his favorites, was his response-time application - a script that logged in a few times a day, copied rows from a reference table to a second table, updated the new rows, deleted the new rows and then executed some SQL giving one a feel for the system's performance. His manager really loved it.

The interesting part was the reference table that was used to provide the context for the dynamic SQL that got executed. The table contained one text column.

Some of the data looked like this:

But there was some more interesting text at rowid AAAHafAAABAAAMUSAAN.
ALTER USER SYSTEM IDENTIFIED BY MANAGER, to be exact, which was run if he hadn't logged in for over two weeks. Similar SQL existed for all of the built in accounts, for disabling his login trigger and for granting DBA privileges to public. Developers would certainly pick this up. "There's no way this database can last longer than three weeks without him", he comforted himself.

But just in case, there was the RMAN command sript with the little SQL statement embedded in it. That's a story in itself. The developers were having a hard time spelling. They would often type roleback or rowback instead of rollback; comet, committ, camit instead of commit. "No problem", he had told them as he created public synonyms for each of their spelling variations pointing to synonyms, which pointed to procedures he had created. Now they could use whichever verb they liked. True, things almost got out of hand when one very bright developer asked for a synonym called: commit_this_pile_of_s#!@, but he had diallowed that, (notwithstanding the fact that the naming standards were silent on this issue.)

If he hadn't logged in for four weeks, the SQL statement in the RMAN script would simply rename the target rollback and commit procedures. Rowback, roleback etc.would issue a commit and the commit family of synonms would trigger a rollback.

He reached to pick up the phone.

Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Please see the official ORACLE-L FAQ:
Author: lu wesley

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 03 2002 - 16:21:42 CDT

Original text of this message