BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Deletion 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 command follows.

DELETE hint [FROM] [ONLY]
[TABLE select_clause][(+)]
[schema.]table_name|view_name|materialized_view[@db_link]
 sub_query [WITH READ ONLY|CHECK OPTION [CONSTRAINT con_clause]] alias
[WHERE expr]
[schema.]table_name [PARTITION (partition_name)]
[schema.]table_name [SUBPARTITION (subpartition_name)]
[returning clause]

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 from.

dblink. If the table, view, or partition is in a remote database, this is the dblink to that database.

PARTITION(partition name). Deletes from a specified (partition_name) of a partitioned table.

SUBPARTITION (subpartition_name). Deletes from a specified (subpartition_name) of a subpartitioned table.

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:

DELETE FROM 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.

DELETE PER_Remote DBA.OLD_JOBS

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 see TABLE):

DELETE TABLE (SELECT addresses
 FROM clientsv8 c
 WHERE c.customer_name = 'Joes Bar and Grill, Inc.')
 AS a
WHERE a.addrtype=1;

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:

DELETE FROM trains PARTITION (roundhouse1)
WHERE 
service_date < to_date('01-Jan-1956 00:00:00,
'DD-Mon-YYYY hh24:mi:ss');
 

If you wish to delete from a specific subpartition, this is accomplished with the SUBPARTITION clause:

DELETE FROM trains SUBPARTITION (subroundhouse1a)
WHERE 
service_date < to_date('01-Jan-1956 00:00:00,
'DD-Mon-YYYY hh24:mi:ss');

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.

TRUNCATE TABLE|CLUSTER [schema.]table_name|cluster_name
[PRESERVE|PURGE SNAPSHOT LOG]
[DROP|REUSE STORAGE] 

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 [CASCADE CONSTRAINTS]

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 tables.

Rebuilding Tables  

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.


See 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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter