Oracle

Understanding DDL in Oracle with Syntax and Examples

Introduction

In Oracle Database, DDL (Data Definition Language) commands are used to define, modify, and remove database objects such as.

  • Tables
  • Columns
  • Constraints
  • Indexes

Unlike DML (Data Manipulation Language), which works on data, DDL affects the structure of the database (also called the schema).

In this blog, you’ll learn.

  • All key DDL commands
  • Syntax and usage
  • Real-time examples and tips

Common DDL Commands in Oracle

Command Purpose
CREATE Create new database objects
ALTER Modify existing objects
RENAME Rename database objects
TRUNCATE Quickly remove all rows from a table
DROP Delete objects permanently

1. CREATE — Create New Tables or Objects

The CREATE command is used to define new tables or other database objects.

Example. Create a New Table.

CREATE TABLE EMP_IND (
  empno     NUMBER PRIMARY KEY,
  ename     VARCHAR2(50),
  nickname  VARCHAR2(50),
  email     VARCHAR2(100)
);

Explanation: This creates a table named EMP_IND with 4 columns, where empno is set as the primary key.

2. ALTER — Modify Existing Table Structures

The ALTER command lets you make structural changes to an existing table.

2.1 Add Column(s)

Add Single Column

ALTER TABLE EMP_IND 
ADD gender VARCHAR2(10);

Add Multiple Columns

ALTER TABLE EMP_IND 
ADD (
  age        NUMBER,
  department VARCHAR2(50)
);

2.2 Modify Column(s)

Modify Single Column

ALTER TABLE EMP_IND
MODIFY ename VARCHAR2(100);

Modify Multiple Columns

ALTER TABLE EMP_IND 
MODIFY (
  nickname VARCHAR2(100),
  email    VARCHAR2(150)
);

2.3 Drop Column(s)

Drop Single Column

ALTER TABLE EMP_IND
DROP COLUMN gender;

Drop Multiple Columns

ALTER TABLE EMP_IND 
DROP (age, department);

2.4 Rename a Column

ALTER TABLE EMP_IND
RENAME COLUMN nickname TO short_name;

Note. You can rename only one column at a time.

3. RENAME — Rename Table Names

Used to rename the entire table.

Example

ALTER TABLE EMP_IND 
RENAME TO EMPLOYEE_MASTER;

Effect: The table EMP_IND is now renamed to EMPLOYEE_MASTER.

4. TRUNCATE — Quickly Remove All Records from a Table

Removes all rows from a table, retaining its structure for future use.

Example

TRUNCATE TABLE EMPLOYEE_MASTER;

Key Points

  • Faster than DELETE
  • Cannot be rolled back
  • Keeps the table definition intact

5. DROP — Delete Tables Permanently

Deletes the entire table, including its data and structure.

Example

DROP TABLE EMPLOYEE_MASTER;

Effect: The table is permanently removed from the database (unless Flashback features are enabled).

Summary Table of DDL Commands

Command Purpose Rollback Allowed?
CREATE Create new database objects No
ALTER Modify table structure No
RENAME Rename the table or columns No
TRUNCATE Quickly remove all data from a table No
DROP Permanently delete a table No

Important Notes

  • DDL operations are auto-committed
  • Changes are saved immediately and cannot be undone.
  • Always take a backup before running destructive DDL commands, such as TRUNCATE or DROP.
  • Use DDL carefully in production environments to prevent accidental data loss.

Conclusion

Mastering DDL commands is essential for every Oracle developer and DBA. These commands allow you to.

  • Define and change table structures.
  • Manage columns, constraints, and indexes.
  • Control schema changes safely and effectively.
Membership not found