Data Management

Rethink five outdated ideas about Oracle

Versions 8i and 9i of Oracle have altered the facts behind several common beliefs of DBAs. Find out why these five cherished beliefs about working with the database may no longer be true and how the changes enhance your options.


Database administrators, like most professionals, tend to keep doing those things that have worked in the past. Over time, these practices take on a life of their own, passing down from DBA to DBA-like folklore.

But all products move on, adding new features, and the Oracle database is no exception. Major new features have been introduced in versions 8i and 9i that call for a reexamination of what “everybody knows” about Oracle. Let’s take a look at five cherished beliefs Oracle DBAs hold and why these beliefs may no longer be true.

1. Block size is fixed
The fundamental unit of storage in Oracle is the block—the smallest amount of data Oracle can read or write at a time. A block size—2, 4, 8, 16, or 32 KB—is chosen when the database is initially created and is used both for the physical disk space and buffers in memory.

Most DBAs believe that once the block size is chosen, it cannot be altered without reloading the database and that it applies to all tablespaces in the database. Neither is true in Oracle9i.

According to Oracle9i New Features: Use All of the Enhanced Capabilities of Oracle9i, by Robert G. Freeman, each tablespace may now use a different block size. It’s done like this:
CREATE TABLESPACE book_data
BLOCKSIZE 8K
DATAFILE '/u3/oradata/prod/bookdata01.dbf' SIZE 100M;


You can check which block sizes are in use via the new BLOCK_SIZE column in the DBA_TABLESPACES and V$DATAFILE views.

If you use a different block size from the default for the database, you must set up a memory cache for it using the initialization parameter file. Six new parameters are defined. db_cache_size replaces db_block_buffers to indicate the size of the buffer cache for default-sized blocks. db_2k_cache_size indicates the buffer cache size for 2-KB blocks, if such blocks are nonstandard for the database. There are 4-KB, 8-KB, 16-KB, and 32-KB versions as well. Unlike their predecessor, db_block_buffers, these parameters are all measured in bytes, not the number of buffers to allocate.

2. Single extent tables are faster
Oracle DBA folklore says that the best input/output (I/O) performance is achieved when all the blocks for a table are in a single contiguous extent. This one is true—sometimes. But not for the reason you might expect.

In a white paper entitled “How to Stop Defragmenting and Start Living” (free registration required), Bhaskar Himatsingka and Juan Loaiza of Oracle Corporation argue that multiextent tables don’t necessarily perform worse than single-extent ones. What matters, they assert, is the size of the extents. Given a large enough extent size, the Oracle engine can preread efficiently even across multiple extents.

To best use this insight and save DBA time, the authors recommend that the DBA standardize on three extent sizes and use them exclusively in all tables: 128 KB for small tables, 4 MB for medium tables, and 128 MB for only the largest tables. Using 128-KB extent sizes may seem to waste space, but the cost of this wasted space is trivial compared to the cost of DBA time fussing over each individual storage allocation.

These principles have been built into the new Oracle8i feature, Locally Managed Tablespaces.

3. Export and Import are the only ways to reorganize tables
Seasoned DBAs will tell you that when a table needs to be moved, reorganized, or defragmented, it’s a painful process. First, the table must be exported to an external “dump” file. The original table must then be dropped, optionally re-created manually in some cases, and finally imported again from the dump file.

All this work may be unnecessary as of Oracle8i.

Using the new MOVE clause in the ALTER TABLE enables you to change the tablespace and/or storage parameters of an Oracle table without using EXPORT and IMPORT. It looks like this:
ALTER TABLE author MOVE
TABLESPACE book_data
STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0);


In this case, the TABLESPACE clause tells Oracle to move the author table from its current tablespace to the book_data tablespace. The STORAGE clause works the same as for CREATE TABLE, indicating how large an extent to allocate and its growth properties.

Either TABLESPACE or STORAGE is optional, enabling you to move the table without reorganizing it, and vice versa.

Caution: In version 8i, the table will be locked for the entire duration of the ALTER TABLE command, so it’s still best to do such work during nonpeak hours. In version 9i, that restriction is lifted. When you add the word ONLINE to the syntax, the table can be moved even while users are updating it.

Also, note that you must have enough disk space for two copies of the table: the old one and the new. Oracle doesn't drop the old table until the new one is completely built. If your table is too big for this approach, you'll have to do it the old-fashioned way with Export and Import.

4. Columns can’t be dropped
Oracle DBAs have gotten used to the fact that once a column has been defined for a table, it cannot be renamed or dropped. To get rid of it, you have to create a new table without the column, then load that table with data from the original. Finally, you have to reset all permissions, indexes, triggers, etc., on the new table that were on the original one.

Not anymore. You still can't rename columns, but you can drop them and add the correct one instead. The SET UNUSED and DROP clauses of the ALTER TABLE command are used for this. Here's an example:
ALTER TABLE author
DROP (birthplace, birthyear);

ALTER TABLE author
SET UNUSED (birthhospital);


Both clauses permanently delete a column. The only difference is when Oracle does its cleanup. A column that is set to unused disappears from the data dictionary, so users cannot reference it. But the physical space is still taken up until manually cleared by the DBA. With the DROP option, the reorganization is done immediately. The SET UNUSED option allows the DBA to make the column unavailable immediately, without inconveniencing users with the overhead of cleanup.

Caution: All data contained in the column is irretrievably lost when a column is marked unused or dropped. These are data definition language (DDL) commands, so there is no ROLLBACK. Be careful and have good backups!

5. Stored procedures always run as the owner
When an Oracle user is given the permission to execute a stored procedure, he or she is implicitly given permission to do whatever that stored procedure does. No matter how many tables the procedure updates, or how it updates them (even deleting rows), the user can do it. In other words, the user obtains all the rights of the owner while running the procedure.

Starting with Oracle8i, however, a stored procedure can be created with either the rights of the owner or the rights of the person executing it. You use the AUTHID clause of the CREATE PROCEDURE command for this. For example:
CREATE PROCEDURE count_authors
   (num_books OUT NUMBER)
    AUTHID CURRENT_USER
IS
    SELECT COUNT(*) INTO num_books
    FROM author;
END;


A stored procedure defined as AUTHID CURRENT_USER will only allow access to a table if the user owns the table or has been given permission to use it. Furthermore, references to unqualified table names, like author in the example above, refer to the user's copy of author, not the original owner's. The currently logged-in user's schema, or list of objects, is used to resolve references.

It ain't necessarily so
All of these situations are very common in Oracle database administration, yet in my career as a trainer, I often hear DBAs who don’t know about them. Like the saying goes: It's not what you don't know that hurts you. It's what you know that ain't so.

Editor's Picks

Free Newsletters, In your Inbox