DDL Commands
In this sub section, let us learn the usage of below DDL (Data Definition Language) commands with Oracle-specific examples and best practices.
1. CREATE TABLE
CREATE TABLE command is used to create a new table in Oracle database.
Syntax:
CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
[table_constraints]
);
Example with Oracle Data Types:
CREATE TABLE CUSTOMERS (
customer_id NUMBER(10) PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
email VARCHAR2(255) UNIQUE,
dob DATE,
created_date TIMESTAMP DEFAULT SYSTIMESTAMP,
salary NUMBER(10,2),
is_active CHAR(1) DEFAULT 'Y' CHECK (is_active IN ('Y','N')),
description CLOB,
profile_pic BLOB
);
Create Table with Constraints:
CREATE TABLE ORDERS (
order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id NUMBER(10) NOT NULL,
order_date DATE DEFAULT SYSDATE,
total_amount NUMBER(12,2),
status VARCHAR2(20) DEFAULT 'PENDING',
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES CUSTOMERS(customer_id)
);
Create Table As Select (CTAS):
CREATE TABLE CUSTOMERS_BACKUP AS
SELECT * FROM CUSTOMERS WHERE is_active = 'Y';
2. ALTER TABLE
ALTER TABLE command is used to modify the structure of an existing table.
Add Column:
ALTER TABLE CUSTOMERS ADD (
phone_number VARCHAR2(20),
address VARCHAR2(500)
);
Modify Column:
-- Change data type or size
ALTER TABLE CUSTOMERS MODIFY email VARCHAR2(500);
-- Add NOT NULL constraint
ALTER TABLE CUSTOMERS MODIFY phone_number VARCHAR2(20) NOT NULL;
-- Add DEFAULT value
ALTER TABLE CUSTOMERS MODIFY is_active DEFAULT 'N';
Drop Column:
ALTER TABLE CUSTOMERS DROP COLUMN description;
-- Drop multiple columns
ALTER TABLE CUSTOMERS DROP (profile_pic, address);
Add Constraints:
-- Add Primary Key
ALTER TABLE EMPLOYEES ADD CONSTRAINT pk_emp_id PRIMARY KEY (employee_id);
-- Add Foreign Key
ALTER TABLE ORDERS ADD CONSTRAINT fk_cust_order
FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id);
-- Add Check Constraint
ALTER TABLE CUSTOMERS ADD CONSTRAINT chk_salary
CHECK (salary > 0);
-- Add Unique Constraint
ALTER TABLE CUSTOMERS ADD CONSTRAINT uk_email UNIQUE (email);
Drop Constraints:
-- Drop constraint by name
ALTER TABLE CUSTOMERS DROP CONSTRAINT uk_email;
-- Drop Primary Key
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
-- Drop constraint with CASCADE option
ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customer CASCADE;
Enable/Disable Constraints:
-- Disable constraint
ALTER TABLE ORDERS DISABLE CONSTRAINT fk_customer;
-- Enable constraint
ALTER TABLE ORDERS ENABLE CONSTRAINT fk_customer;
3. DROP TABLE
DROP TABLE command is used to remove a table and all its data permanently.
Basic Syntax:
DROP TABLE table_name;
Drop with CASCADE CONSTRAINTS:
-- Drops table even if other tables reference it
DROP TABLE CUSTOMERS CASCADE CONSTRAINTS;
Drop with PURGE:
-- Permanently drops table (cannot be recovered from recycle bin)
DROP TABLE CUSTOMERS PURGE;
-- Drop with both options
DROP TABLE CUSTOMERS CASCADE CONSTRAINTS PURGE;
4. RENAME
RENAME command is used to rename database objects in Oracle.
Rename Table:
-- Oracle syntax for renaming table
RENAME CUSTOMERS TO CUSTOMER_INFO;
-- Alternative syntax
ALTER TABLE CUSTOMERS RENAME TO CUSTOMER_INFO;
Rename Column:
ALTER TABLE CUSTOMER_INFO RENAME COLUMN name TO customer_name;
Rename Constraint:
ALTER TABLE CUSTOMER_INFO RENAME CONSTRAINT pk_cust TO pk_customer_info;
5. TRUNCATE TABLE
TRUNCATE command removes all rows from a table but keeps the table structure intact.
Basic Syntax:
TRUNCATE TABLE table_name;
Examples:
-- Basic truncate
TRUNCATE TABLE CUSTOMERS;
-- Truncate with storage options
TRUNCATE TABLE ORDERS DROP STORAGE;
-- Truncate preserving materialized view logs
TRUNCATE TABLE CUSTOMERS PRESERVE MATERIALIZED VIEW LOG;
-- Truncate cascade (12c and later)
TRUNCATE TABLE CUSTOMERS CASCADE;
6. FLASHBACK TABLE
Oracle-specific feature to restore dropped tables (if not purged).
Restore Dropped Table:
-- Check recycle bin
SELECT * FROM RECYCLEBIN;
-- Restore table
FLASHBACK TABLE CUSTOMERS TO BEFORE DROP;
-- Restore with rename
FLASHBACK TABLE CUSTOMERS TO BEFORE DROP RENAME TO CUSTOMERS_RESTORED;
7. CREATE INDEX
Create indexes to improve query performance.
Basic Index:
CREATE INDEX idx_customer_email ON CUSTOMERS(email);
Composite Index:
CREATE INDEX idx_cust_name_dob ON CUSTOMERS(customer_name, dob);
Unique Index:
CREATE UNIQUE INDEX idx_unique_email ON CUSTOMERS(email);
Function-Based Index:
CREATE INDEX idx_upper_name ON CUSTOMERS(UPPER(customer_name));
Bitmap Index:
CREATE BITMAP INDEX idx_active ON CUSTOMERS(is_active);
8. CREATE SEQUENCE
Create sequences for generating unique numbers.
Basic Sequence:
CREATE SEQUENCE customer_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999
NOCYCLE
CACHE 20;
Using Sequence:
-- In INSERT statement
INSERT INTO CUSTOMERS (customer_id, name)
VALUES (customer_seq.NEXTVAL, 'John Doe');
-- Get current value
SELECT customer_seq.CURRVAL FROM DUAL;
9. CREATE SYNONYM
Create aliases for database objects.
Private Synonym:
CREATE SYNONYM cust FOR hr.customers;
Public Synonym:
CREATE PUBLIC SYNONYM all_customers FOR hr.customers;
10. COMMENT
Add comments to tables and columns for documentation.
Table Comment:
COMMENT ON TABLE CUSTOMERS IS 'Stores customer information';
Column Comment:
COMMENT ON COLUMN CUSTOMERS.customer_id IS 'Primary key - unique customer identifier';
COMMENT ON COLUMN CUSTOMERS.is_active IS 'Y=Active, N=Inactive';
View Comments:
-- Query table comments
SELECT * FROM USER_TAB_COMMENTS WHERE table_name = 'CUSTOMERS';
-- Query column comments
SELECT * FROM USER_COL_COMMENTS WHERE table_name = 'CUSTOMERS';
Oracle DDL Best Practices:
-
Naming Conventions:
- Use uppercase for object names (Oracle default)
- Use underscores to separate words
- Keep names under 30 characters (Oracle limit)
-
Data Types:
- Use VARCHAR2 instead of VARCHAR
- Use NUMBER for numeric data
- Use DATE or TIMESTAMP for date/time data
- Use CLOB for large text, BLOB for binary data
-
Constraints:
- Always name your constraints explicitly
- Use meaningful constraint names (e.g., fk_order_customer)
- Consider deferrable constraints for complex operations
-
Performance:
- Create indexes on foreign key columns
- Use appropriate storage parameters
- Consider partitioning for large tables
-
Safety:
- Always backup before major DDL operations
- Use CASCADE CONSTRAINTS carefully
- Test DDL changes in development first
SQL Comments:
Single-Line Comments:
-- This is a single line comment
SELECT * FROM CUSTOMERS; -- This filters active customers
Multi-Line Comments:
/*
This is a multi-line comment
Used for longer explanations
or to comment out blocks of code
*/
SELECT customer_id,
customer_name
FROM CUSTOMERS;
/* You can also use it inline */ SELECT * FROM ORDERS;