of Table Data
Oracle Tips by Burleson Consulting
Deletion of a tableís data can be done by two
methods. The first method uses the DELETE command and can be
conditional. A DELETE is also able to be rolled back if you decide,
before you commit, that you goofed. The second method is the TRUNCATE
command. TRUNCATE is a DDL, not a DML, command, and itís not
conditional (itís all or nothing) and cannot be rolled back (yes, you
will spend days reentering data if you goof). Letís look at these
commands and their options.
The DELETE Command
You can delete data selectively or delete all
of a tableís data using the DELETE command. The format for this
sub_query [WITH READ ONLY|CHECK OPTION [CONSTRAINT con_clause]] alias
[schema.]table_name [PARTITION (partition_name)]
[schema.]table_name [SUBPARTITION (subpartition_name)]
where the clauses have the following definitions:
hint. Any one of the allowed hints.
schema. The schema or owner of the
table, view, or partition being deleted from. If this is left off, the
userís default schema is used.
Table_name, view_name, or materialized_view.
The name of the table, view, or materialized view to be deleted
dblink. If the table, view, or
partition is in a remote database, this is the dblink to that
PARTITION(partition name). Deletes from
a specified (partition_name) of a partitioned table.
Deletes from a specified (subpartition_name) of a subpartitioned
TABLE. Used to flatten nested tables.
The subquery following the TABLE clause tells Oracle how the
flattening should occur.
subquery. Used to tell Oracle how to
delete from the table or nested table. If the deletion is from a
nested table, the TABLE clause must be included.
alias. Used when a correlated subquery
is used to denote table hierarchy in the query/delete commands.
WHERE condition. The condition each
deleted row must meet or fail.
Returning_clause. Retrieves the rows
affected by the DELETE statement. You can retrieve only scalar, LOB,
rowid, and REF types.
TIP: You can use hints in a DELETE
statement to optimize delete subquery processing.
The table name can include an alias; if the
WHERE clause is left out, all of the rows in the table are deleted.
Four examples follow:
PER_Remote DBA.JOBS A WHERE A.JOB_STATUS = 'COMPLETE';
This command would delete all rows with the
data value COMPLETE in the column JOB_STATUS from the JOBS table owned
by the PER_Remote DBA user.
This command would remove all rows from the
table OLD_JOBS that belongs to the schema PER_Remote DBA.
To delete specific rows from a nested table,
the TABLE clause is specified (I like to think ĎFROM THE SETí when I
FROM clientsv8 c
WHERE c.customer_name = 'Joes Bar and Grill, Inc.')
In this example, note the use of the alias ďAS
aĒ; this is required.
Deleting from a single partition is
accomplished by use of the PARTITION clause:
trains PARTITION (roundhouse1)
service_date < to_date('01-Jan-1956 00:00:00,
If you wish to delete from a specific
subpartition, this is accomplished with the SUBPARTITION clause:
trains SUBPARTITION (subroundhouse1a)
service_date < to_date('01-Jan-1956 00:00:00,
The TRUNCATE TABLE Command
There is also a way to avoid the use of
rollback, thus making deletions much faster: use the TRUNCATE command.
One good feature of this command is that it can be used to reclaim the
space used by the data that was in the table. As noted in the
introduction to this section, TRUNCATE is a DDL command; once issued,
the data is gone. A TRUNCATE cannot be rolled back. The format for
this command follows.
[PRESERVE|PURGE SNAPSHOT LOG]
The DROP|REUSE STORAGE option allows you to
shrink the table back to its high-water mark or leave the table at its
current size. Both DROP and REUSE qualifiers also apply to whatever
index space is regained.
For tables, PRESERVE or PURGE SNAPSHOT options
allow control over a tableís snapshot logs as well.
Again, the TRUNCATE command is faster than the
DELETE command because it is a DDL command and generates no rollback
data. When using TRUNCATE on a clustered table, the data must be
removed from the entire cluster, not just the one table. Any
referential integrity constraints on a table must be disabled before
it can be truncated. Like a table DROP, a truncation is not
recoverable. If a table is truncated, you cannot roll back if you made
a mistake. Use TRUNCATE carefully.
Dropping a Table
To completely remove a table from the
tablespace, use the DROP TABLE command. This commandís format follows:
DROP TABLE [schema.]table_name
Oracle will drop the table regardless of its
contents. The only time a drop will fail is when a tableís primary key
is referenced by another tableís foreign key via a restraint clause.
The Remote DBA can check for this situation by looking at the Remote DBA_CONSTRAINTS
and the Remote DBA_CONS_COLUMNS views. A view called USER_CROSS_ REFS
provides this information on a user-by-user basis. Using the CASCADE
CONSTRAINTS clause will force a CASCADE DELETE to occur in all child
The Remote DBA may have to rebuild a table or tables
after maintenance, after a physical disk crash, or--the leading
cause--due to operational stupidity. If the application designers were
thoughtful enough to provide a build script, there is no problem.
However, for legacy systems, systems that have been modified and not
redocumented, or systems created on the fly, there may be no current
build scripts, if there were any to begin with. In this case, the Remote DBA
is in trouble. How can this situation be prevented? Require build
scripts for each application and keep them up to date. For existing,
undocumented systems, the script TAB_RCT9i.sql from the Wiley Web site
will create a build script for simple existing tables. For very
complex tables, I suggest implementing a utility such as TOAD from
Quest, Inc. In Oracle9i, the DBMS_METADATA can also be used to
generate the DDL for any database object. Any rebuild script generator
must be run before any loss has occurred. Due to the added complexity
of Oracle9i, it is doubtful many systems created on the fly will that
make use of the complex options and types. Over time, I will be
revising TAB_RCT9i.SQL to handle the more complex Oracle9i structures,
and newer versions will be made available at the Wiley Web site.
A similar script is referenced for indexes in
Chapter 6. The TAB_RCT9i.SQL script will rebuild straight-partitioned
or subpartitioned Oracle9i and Oracle8i relational tables only. It
will not rebuild tables with types, objects, nested tables, or varrays.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.