This tutorial section focuses on Oracle 19c DDL commands for ALTER TABLE, DROP TABLE, and TRUNCATE TABLE. Learn how to modify table structures, remove tables entirely, and quickly clear table data efficiently for your Oracle database management.

Altering and Dropping Tables

ALTER TABLE

The ALTER TABLE statement in Oracle 19c allows you to modify the structure of an existing table. This includes adding, modifying, or dropping columns, as well as managing constraints and renaming table components.

Example 1: ALTER TABLE Add Column

ALTER TABLE employees
ADD (hire_date DATE); -- Adds a new column named 'hire_date' of DATE type to the employees table

Explanation This SQL command adds a new DATE column named hire_date to the employees table, allowing for storing employee hiring dates.

Example 2: ALTER TABLE Modify Column

ALTER TABLE employees
MODIFY (first_name VARCHAR2(30)); -- Modifies the 'first_name' column to increase its length to 30 characters

Explanation This statement alters the employees table to increase the maximum length of the first_name column to 30 characters, accommodating longer names.

Example 3: ALTER TABLE Drop Column

ALTER TABLE employees
DROP COLUMN hire_date; -- Removes the 'hire_date' column from the employees table

Explanation This command permanently removes the hire_date column and all its data from the employees table, a DDL operation.

Example 4: ALTER TABLE Add Primary Key Constraint

ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id); -- Adds a primary key constraint on the 'employee_id' column

Explanation This SQL adds a primary key constraint named pk_employees to the employees table, ensuring uniqueness and non-nullability for employee_id.

Example 5: ALTER TABLE Rename Column

ALTER TABLE employees
RENAME COLUMN first_name TO given_name; -- Renames the 'first_name' column to 'given_name'

Explanation This command renames the first_name column in the employees table to given_name, updating its identifier in the table structure.

DROP TABLE

The DROP TABLE statement is used to remove a table and all its associated data, indexes, and constraints from the database. This operation is permanent and cannot be rolled back.

Example 1: DROP TABLE Basic

DROP TABLE old_records; -- Deletes the table 'old_records' permanently

Explanation This command permanently deletes the table named old_records from the database, including all its data and related objects.

Example 2: DROP TABLE with CASCADE CONSTRAINTS

DROP TABLE departments CASCADE CONSTRAINTS; -- Deletes 'departments' table and all dependent foreign key constraints

Explanation This statement drops the departments table. CASCADE CONSTRAINTS ensures that any foreign key constraints referencing departments from other tables are also dropped.

Example 3: DROP TABLE and Recycle Bin

DROP TABLE temp_data; -- Moves the table to the recycle bin by default in 19c

Explanation By default in Oracle 19c, dropping a table sends it to the recycle bin, allowing for potential recovery.

Example 4: Purging Dropped Table from Recycle Bin

PURGE TABLE temp_data; -- Permanently removes 'temp_data' from the recycle bin

Explanation This command permanently removes the temp_data table from the recycle bin, making it unrecoverable.

Example 5: DROP TABLE IF EXISTS (PL/SQL Block for Safety)

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE customer_staging CASCADE CONSTRAINTS';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN -- ORA-00942: table or view does not exist
      RAISE;
    END IF;
END;
/

Explanation This PL/SQL block safely attempts to drop the customer_staging table. It catches the "table does not exist" error, preventing the script from failing if the table is already gone.

TRUNCATE TABLE

The TRUNCATE TABLE statement is a Data Definition Language (DDL) command used to quickly remove all rows from a table, effectively resetting it. Unlike DELETE, it does not generate undo information, making it much faster for large tables.

Example 1: TRUNCATE TABLE Basic

TRUNCATE TABLE transaction_logs; -- Removes all rows from 'transaction_logs' quickly

Explanation This command efficiently deletes all data from the transaction_logs table, performing a high-speed row removal operation.

Example 2: TRUNCATE TABLE with Storage Reuse

TRUNCATE TABLE temp_sessions REUSE STORAGE; -- Truncates and reuses allocated space

Explanation This statement truncates the temp_sessions table and instructs Oracle to reuse the previously allocated storage space, preventing immediate release to the tablespace.

Example 3: TRUNCATE TABLE and Indexes

-- Indexes on the truncated table are not dropped but become unusable until the table is used again.
-- TRUNCATE TABLE large_dataset;
-- Indexes associated with 'large_dataset' remain but are refreshed upon next insert.

Explanation When a table is truncated, its associated indexes remain but are reset and become ready for new data insertion.

Example 4: TRUNCATE TABLE vs. DELETE (Performance)

-- TRUNCATE TABLE is a DDL operation, faster for large datasets due to minimal overhead.
-- DELETE FROM transaction_logs WHERE 1=1; -- DELETE is DML, slower as it generates undo/redo.
-- For millions of rows, TRUNCATE is significantly faster.

Explanation TRUNCATE TABLE is a DDL command that performs a rapid deletion of all rows, making it much faster and more resource-efficient than DELETE for large tables, which is a DML command.

Example 5: TRUNCATE TABLE and ROLLBACK

TRUNCATE TABLE audit_trail; -- This operation cannot be rolled back
-- ROLLBACK; -- This ROLLBACK command will not revert the TRUNCATE operation

Explanation TRUNCATE TABLE is a DDL operation, meaning it commits immediately and cannot be rolled back using a ROLLBACK command.