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
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
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: http://www.orafaq.com
Author: lu wesley
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).