Five things to unlearn about Oracle

More DBAs are running current versions of Oracle software but still living within the limitations of older versions. It's time to discard old habits.

As I continue to teach Oracle 9i, I find more database administrators (DBAs) running current versions of the software but still living within the limitations of older versions. Here are five more things that it’s time to unlearn about the Oracle database.

1. Only the DBA can recover data
People who work directly in the SQL language -- DBAs and IT consultants -- can corrupt or lose data with one mistyped command. In fact, user error is the most common reason for database downtime, according to Oracle. A table dropped from the production database instead of development can bring an application and all of its users to an abrupt halt. Even an improper update can corrupt the results reported from a database.

Until now, recovering from such errors was a time-consuming job that only the DBA could perform. But since Oracle 9i, users can fix many such errors themselves via SQL commands. The mechanism for this is the new 9i feature called Flashback Query.

Here’s an example using the sample data in the SCOTT schema. An employee record is deleted, and the change committed:
DELETE FROM emp WHERE empno = 7934;
COMMIT;

The row is missing from further SELECT statements, and even a ROLLBACK command cannot bring the row back. However, a Flashback Query can display the contents of the table as it was 10 minutes ago, when the deleted row still existed:
SELECT * FROM emp
   AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '10' MINUTE)
   WHERE empno = 7934;

This SELECT statement can be used as the subquery of an INSERT statement to reload the deleted data. Be aware, however, that INSERT will be subject to any constraints on the table, and that any INSERT triggers on the table will be executed.

To set the entire session to flash back to a particular point in time, use the DBMS_FLASHBACK package:
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TIMESTAMP ‘yyyy-mm-ddhh:mi:ss’);

Data accessed during a flashback session cannot be modified, only read. It’s just like science fiction stories involving time travel: You can visit the past, but you can't change it! To return the session to the present, type:
EXEC DBMS_FLASHBACK.DISABLE;

For Flashback Query to work, the database must be using Automated Undo Management (AUM), and an undo tablespace must be created. The amount of time a user can flash back is limited by the initialisation parameter undo_retention and the size of the undo tablespace.

Although this feature is a godsend for IT consultants, Oracle’s intent is for any user that directly types SQL to be able to recover from his or her own errors. Flashback is an object privilege, so it can be granted on individual tables or to all tables via the system privilege
FLASHBACK ANY TABLE

But wait: it gets better! In version 9i, Flashback is limited to Data Manipulation Language (DML) commands such as SELECT, INSERT, UPDATE, and DELETE. But in Oracle 10g, even a dropped table can be recovered via Flashback.

2. Oracle can’t store fractions of seconds
Oracle’s DATE datatype has always stored time to the nearest full second. Developers needing more precise time measurements use the NUMBER datatype instead. This practice makes computing time intervals difficult.

Starting with version 9i, Oracle includes an enhanced date/time datatype consistent with the 1999 SQL standard. To declare such a column, use the TIMESTAMP datatype and indicate the number of fractional digits desired (the default is 6):
CREATE TABLE event_ts (
 event_id  NUMBER(6)
 ,event_name VARCHAR2(40)
 ,start_time TIMESTAMP(2)
 ,elapsed_time TIMESTAMP(2)
);

A TIMESTAMP literal, like a DATE literal, must be enclosed in single quotes. Unlike dates, however, the word TIMESTAMP is required as well. The following literal represents March 23, 2004, at a half-second past midnight:
TIMESTAMP ‘2004-03-23 00:00:00.50’

Although the standard DATE literal doesn’t include time, the standard format for a TIMESTAMP literal requires it. The session parameter NLS_TIMESTAMP_FORMAT controls the format, in the same way that NLS_DATE_FORMAT sets the standard DATE format. A new conversion function, TO_TIMESTAMP, creates a TIMESTAMP from other input formats, and the TO_CHAR function has been enhanced to display a TIMESTAMP's components in any order.

To obtain the current date and time in TIMESTAMP format, use the SYSTIMESTAMP function instead of SYSDATE. For example:
SELECT SYSTIMESTAMP FROM DUAL;

3. A corrupted block requires dropping an object
IT consultants dread the Oracle error message ORA-1578, "Oracle data block corrupted." The internal structure of one of the database’s blocks is no longer correct. The message identifies the block in error by file number and block number. The cure has always been to run a query such as:
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = <filenumber>
AND <blocknumber> BETWEEN block_id AND block_id + blocks - 1;

where <filenumber> and <blocknumber> were the numbers from the error message. This query indicates which object contains the corrupted block. Then, depending on the object type, recovery is either straightforward (for indexes and temporary segments), messy (for tables), or very messy (for active rollback segments and parts of the data dictionary).

In Oracle 9i Enterprise Edition, however, a new Recovery Manager (RMAN) command, BLOCKRECOVER, can repair the block in place without dropping and recreating the object involved. After logging into RMAN and connecting to the target database, type:
BLOCKRECOVER DATAFILE <filenumber> BLOCK <blocknumber>;

A new view, V$DATABASE_BLOCK_CORRUPTION, gets updated during RMAN backups, and a block must be listed as corrupt for a BLOCKRECOVER to be performed. To recover all blocks that have been marked corrupt, the following RMAN sequence can be used:
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;

This approach is efficient if only a few blocks need recovery. For large-scale corruption, it's more efficient to restore a prior image of the datafile and recover the entire datafile, as before. As with any new feature, test it carefully before using it on a production database.

4. Columns can’t be renamed or reorganised
Renaming a table column or changing its data type usually meant creating a new table and copying the old data to it. Columns couldn’t be renamed at all, and datatypes could be changed only if they had no data (only NULL values).

Oracle 9i has not one but two ways to overcome these limitations. The ALTER TABLE command can now rename columns directly:
ALTER TABLE books RENAME COLUMN tiitle TO title;

Also, a supplied PL/SQL package called DBMS_REDEFINITION enables a DBA to change a table's column structure while the table is online and available to users. It’s a complex procedure, but in general the steps are as follows:

1. Use DBMS_REDEFINITION.CAN_REDEF_TABLE to check if the table qualifies for online redefinition, and specify if the redefinition will be by primary key (recommended) or by row IDs.
2. Create an empty table in the same schema, but with the desired layout. Omit columns you want to drop; include new columns you’d like to create.
3. Use DBMS_REDEFINITION.START_REDEF_TABLE to begin the redefinition process. The parameters to this procedure indicate the old table, the new one, and how to map the existing columns to the columns of the new table.
4. Create any constraints (disabled), triggers, indexes, and grants desired on the new table.
5. Use DBMS_REDEFINITION.FINISH_REDEF_TABLE to complete the process. The original table is locked for a short time regardless of how large or small it is, while the definitions are swapped between the two tables.
6. Drop the temporary table used in the redefinition; it is no longer needed.

Of course, redefining a table doesn’t automatically update any application code that accesses that table. Applications must be changed and tested separately. What DBMS_REDEFINITION does, however, is shorten the time that the table is unavailable to users at cutover time.

5. Only the owner of a table can grant permission to use it
When I explain Oracle security, clients can’t believe the DBA cannot grant permissions on a table unless the table’s owner has first granted it to the DBA. Historically, however, this has been the case. The restriction was part of Oracle’s design, but it made administration difficult. In Oracle 9i, a new system privilege changes this.

The DBA role now has a system privilege called GRANT ANY OBJECT PRIVILEGE. In the past, a statement like
GRANT SELECT ON scott.emp TO giselle;

would fail unless SCOTT had first granted the DBA the SELECT privilege on his table WITH GRANT OPTION. Now, that same statement will work. This privilege can also be used by lead developers to grant permission on a schema’s objects without having to log in as that schema’s owner.

Keep unlearning
As Dee Hock, the founder of Visa, says: "The problem is never how to get new, innovative thoughts into your mind, but how to get the old ones out." Keep unlearning.

TechRepubl ic is the online community and information resource for all IT professionals, from support staff to executives. We offer in-depth technical articles written for IT professionals by IT professionals. In addition to articles on everything from Windows to e-mail to firewalls, we offer IT industry analysis, downloads, management tips, discussion forums, and e-newsletters.

©2004 TechRepublic, Inc.