Knowledge increases exponentially. Today, you probably own more books than great universities of times past—Cambridge University owned less than two hundred books in the fifteenth century. First came the invention of writing, then alphabets, then paper, then the printing press, then mechanization. Each step caused an exponential increase in the collective human knowledge. In our generation, Al Gore invented the internet and the last barriers to the spread of knowledge have been broken. Today, everybody has the ability to contribute, communicate, and collaborate. We are all caught up in a tsunami, an avalanche, a conflagration, a veritable explosion of knowledge for the betterment of humankind. This is the blog of the good folks at Database Specialists, a brave band of Oracle database administrators from the great state of California. We bid you greeting, traveler. We hope you find something of value on these pages and we wish you good fortune in your journey.

Understanding UNDO

Sooner or later, every Oracle DBA will hit a problem of some sort that is related to the concept of Oracle “Undo”. The Oracle Undo performs a few very important functions in the database; these being the support of read-consistent queries and Flashback queries, supporting transactions (COMMIT and ROLLBACK), and recovering from database crashes.
Along with [...]

Just the basics; managing Oracle disk space

Introduction
Here’s a quick quiz; of all of the problems that can beset an Oracle database, which one happens the most often? Do you think that it’s concurrency or locking issues? Issues with CPU being overwhelmed, or disk I/O rate being overwhelmed? Oracle internal bugs? Disk space issues? Problems encountered with backup & recovery? Account Security? [...]

Just the basics; Upgrading the Oracle RDBMS

Introduction
This is the first installment of a series of shorter whitepapers that just cover the basics of a particular aspect of the Oracle RDBMS. The first one is on the subject of upgrading Oracle. After doing literally hundreds of Oracle RDBMS upgrades and patches over the past 18 years, these are my recommendations.
Why upgrade?
The first [...]

A quick introduction to dbms_pipe

One thing about working with PL/SQL is the fact that it runs inside the database (Oracle Forms nonwithstanding), rather than in a normal OS process. This means that any interaction with the database server OS itself isn’t easily possible. You can create a library in ‘C’ and call that from pl/sql, but this isn’t usually [...]

Don’t Fall Into This RMAN Trap

Have you found that daily full database backups are becoming impractical for your growing databases?  Incremental backups are no longer the exception in some shops.  But with the increased complexity comes a greater responsibility for thoroughly planning and testing your backup solution.  Testing a more complicated backup solution makes for a more complicated validation procedure.  [...]

An introduction to emacs SQLi mode

Introduction

Early in my career, when I was first introduced to Unix, my mentor at the time recommended that I use a text editor called emacs, but also learn how to use vi as well. I personally like both editors, but I prefer emacs for coding. Over the decades, I’ve continued to use emacs; [...]

Why doesn’t a database role work for PL/SQL procedures?

Stacy, who is a DBA, has an interesting problem; the company that she works for just acquired another company. The newly-acquired company needs readonly SQL access to a table owned by the main application schema, called ‘HQ’. The newly-acquired company will be writing stored procedures in their own schema, which is called ‘FIELDOFFICE’, and these [...]

Patching your Oracle database – Critical Patch Update (CPU) or Patch Set Update (PSU)?

Keeping your Oracle database software up to date is a critical and time-consuming task for DBAs.  For many years now, Oracle has been releasing Critical Patch Updates on a quarterly basis.  These patches, as the name implies, contain critical updates to the software, often released in response to a newly found security vulnerability.  More recently, [...]

Unindexed foreign keys & enq:TM - contention

I was recently reminded of the chaos that can be caused by an unindexed foreign key column when there are corresponding updates or deletes from the parent table. To illustrate this particular situation here is some example DDL
CREATE TABLE parent_t
(
parent_id INTEGER NOT NULL,
parent_name VARCHAR2(30) NULL,
CONSTRAINT parent_t_pk PRIMARY KEY (parent_id)
);

INSERT INTO parent_t SELECT object_id, object_name FROM [...]

Overview of Oracle Auditing

As a Remote DBA over the years, I have implemented auditing at a number of customers and have found it to be easy to implement, low overhead and very useful.  In my opinion, every production database should have at least basic auditing enabled.  In this blog, I will give a quick overview of the different [...]