DELETE vs TRUNCATE vs DROP in Oracle Database – Understanding the Differences with a Simple Analogy

As database administrators and developers, we frequently use DELETE, TRUNCATE, and DROP statements to remove data or database objects. While all three commands are related to data removal, they behave very differently in Oracle Database.

The image below provides a simple analogy using a backpack and a trash bin to illustrate the differences.

DELETE – Remove Specific Items

Imagine you have a backpack filled with books, notebooks, and other items. Instead of emptying the entire bag, you selectively remove only the items you no longer need.

Similarly, the DELETE statement removes specific rows from a table.

Example

DELETE FROM employees WHERE department_id = 10;

Key Characteristics:

Removes selected rows.

Supports a WHERE clause.

Generates undo and redo information.

Can be rolled back before commit.

Triggers associated DELETE triggers.

Table structure remains intact.

Use Case

Use DELETE when you need to remove specific records while retaining the rest of the data.

TRUNCATE – Empty the Backpack

Now imagine turning the backpack upside down and emptying everything out. The backpack itself remains available for future use.

This is exactly what TRUNCATE does.

Example

TRUNCATE TABLE employees;

Key Characteristics

Removes all rows from a table.

Cannot use a WHERE clause.

Minimal undo generation.

Much faster than DELETE.

Implicit commit before and after execution.

Cannot be rolled back.

Table structure remains intact.

Resets the High Water Mark (HWM).

Use Case

Use TRUNCATE when you need to quickly remove all data from a table while keeping the table definition, indexes, and privileges.

DROP – Remove the Entire Backpack

Finally, imagine throwing the entire backpack into the trash bin. Not only are the contents gone, but the backpack itself no longer exists.

This is what the DROP statement does.

DROP TABLE employees;

Key Characteristics

Removes the table and all its data.

Deletes associated indexes and constraints.

Frees storage space.

Cannot be rolled back.

Object becomes unavailable immediately.

Use Case

Use DROP when the table is no longer required.

Comparison Table

Fast Performance Moderate Fast Fastest

Resets High Water Mark No Yes N/A

Oracle DBA Perspective

Choosing the correct command is important for performance, recoverability, and storage management:

Use DELETE when business logic requires selective data removal.

Use TRUNCATE when clearing an entire table and performance is critical.

Use DROP when the database object is no longer needed.

Understanding these differences helps avoid accidental data loss and ensures efficient database administration.

Conclusion:

Although DELETE, TRUNCATE, and DROP may appear similar, they serve very different purposes in Oracle Database. A simple way to remember them is:

DELETE = Remove selected items from the backpack.

TRUNCATE = Empty the backpack but keep it.

DROP = Throw away the entire backpack.

Understanding when to use each command is a fundamental skill for every Oracle DBA and database developer.

Oracle E-Business Suite R12.2: Custom Table Columns Marked as UNUSED During ADOP Full Cleanup

Introduction

This blog is intended for Oracle E-Business Suite (EBS) DBAs who encounter issues where custom table columns are unexpectedly marked as UNUSED during the ADOP cleanup phase with cleanup_mode=full.

In our environment, several custom table columns were marked as UNUSED during the cleanup phase, resulting in missing columns from the corresponding Editioning Views (EVs). This blog explains the root cause, how to identify the issue, and the steps required to resolve it.

---

Issue

During an Oracle EBS R12.2 ADOP patching cycle, the following command was executed:

adop phase=cleanup cleanup_mode=full

During the cleanup process, ADOP marked several columns in custom tables as UNUSED.

Affected custom tables:

• XXCUST.ATFMWF_UAT_HISTORY

• XXCUST.ATFMWF_UAT_DATA

• XXCUST.ATFM_UAT_HEADERS_T

Sample log entries:

ad.plsql.ad_zd_table.cleanup  EVENT      Cleanup unused columns

alter table "XXCUST"."ATFMWF_UAT_HISTORY" set unused (PRICE);
alter table "XXCUST"."ATFMWF_UAT_HISTORY" set unused (SESSION_NO);
alter table "XXCUST"."ATFMWF_UAT_HISTORY" set unused (SUBSIDIARY_ORG);
alter table "XXCUST"."ATFMWF_UAT_HISTORY" set unused (TRF_PRICE_UPDATION);

alter table "XXCUST"."ATFMWF_UAT_DATA" set unused (LAST_WF_DATE);
alter table "XXCUST"."ATFMWF_UAT_DATA" set unused (LAST_WF_PR);
alter table "XXCUST"."ATFMWF_UAT_DATA" set unused (LAST_WF_PRICE);
alter table "XXCUST"."ATFMWF_UAT_DATA" set unused (LAST_WF_VENDOR);

alter table "XXCUST"."ATFM_UAT_HEADERS_T" set unused (ATTRIBUTE1);
alter table "XXCUST"."ATFM_UAT_HEADERS_T" set unused (ATTRIBUTE2);
alter table "XXCUST"."ATFM_UAT_HEADERS_T" set unused (SOURCE);

---

Root Cause

The Editioning View (EV) associated with the custom table was not updated after new columns were added.

For example, the Editioning View for:

XXCUST.ATFMWF_UAT_DATA

did not include the newly added columns:

LAST_WF_PR
LAST_WF_VENDOR
LAST_WF_PRICE

During the ADOP Full Cleanup phase, Oracle identified these columns as unused because they were not referenced in the Editioning View and subsequently marked them as UNUSED.

According to Oracle's online patching standards, whenever new columns are added to a custom table in EBS R12.2, the Editioning View must be regenerated.

Reference:

Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

---

Step 1: Verify Table Structure

Verify whether the columns exist in the base table.

DESC XXCUST.ATFMWF_UAT_DATA;

Output showed that the columns existed in the table:

LAST_WF_PR
LAST_WF_VENDOR
LAST_WF_PRICE

---

Step 2: Check Editioning View Mapping

Oracle provides the script ADZDSHOWEV.sql to display Editioning View column mappings.

Location:

$AD_TOP/sql/ADZDSHOWEV.sql

Example:

SQL> @ADZDSHOWEV.sql

Enter the table name:

ATFMWF_UAT_DATA

The output showed that the Editioning View did not contain the newly added columns.

Missing columns:

LAST_WF_PR
LAST_WF_VENDOR
LAST_WF_PRICE

This confirmed that the Editioning View was outdated.

---

Step 3: Regenerate the Editioning View

Execute the following command:

EXEC ad_zd_table.patch('XXCUST','ATFMWF_UAT_DATA');

Output:

PL/SQL procedure successfully completed.

Commit the changes:

COMMIT;

---

Step 4: Validate the Editioning View Again

Re-run:

@ADZDSHOWEV.sql

After regenerating the Editioning View, the missing columns were visible in the mapping:

LAST_WF_PR        = LAST_WF_PR
LAST_WF_VENDOR    = LAST_WF_VENDOR
LAST_WF_PRICE     = LAST_WF_PRICE

This confirmed that the Editioning View had been successfully synchronized with the base table.

---

Key Takeaway

When adding new columns to custom tables in Oracle EBS R12.2:

1. Always regenerate the Editioning View using:

EXEC ad_zd_table.patch('<SCHEMA>','<TABLE_NAME>');

2. Validate the EV mapping using:

@ADZDSHOWEV.sql

3. Ensure all newly added columns are present in the Editioning View before running ADOP cleanup phases.

Failure to synchronize the Editioning View may result in custom columns being marked as UNUSED during adop phase=cleanup cleanup_mode=full.

---

Conclusion

This issue occurred because the Editioning View was not regenerated after custom columns were added to the table. As a result, ADOP Full Cleanup treated those columns as unused and marked them accordingly.

By regenerating the Editioning View using AD_ZD_TABLE.PATCH and validating it through ADZDSHOWEV.sql, the issue was resolved successfully.

I hope this blog helps Oracle EBS DBAs troubleshoot and prevent similar issues during online patching activities.