Skip to content

VD Computer Tuition Surat | Learning Platform

Oracle Table Management | CREATE, ALTER, & DROP

Oracle Table Management 🏗️¶

Prerequisites: SQL Intro

Mentor's Note: Creating a table is like building a room. You decide how many windows (columns) it has and what kind of floor (data types) it uses. If you build it wrong, the whole house might fall down later! 💡

🌟 The Scenario: The Startup Scaffolding 🚀¶

You are building a database for a new delivery app in Surat. - Goal: You need a table for "Orders". - The Design: Every order needs an ID (Auto-incrementing), a Status, and a Total (Automatically calculated).

💻 1. CREATE TABLE & Identity Columns¶

Oracle 12c introduced the IDENTITY clause for easy auto-incrementing IDs.

CREATE TABLE orders (
    order_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    status     VARCHAR2(20) NOT NULL,
    order_date DATE DEFAULT SYSDATE
);

💻 2. Virtual Columns (Automatic Logic) ⚙️¶

A column that doesn't store data but calculates it on the fly!

-- Scenario: Total = Price * Qty
CREATE TABLE order_items (
    qty    NUMBER,
    price  NUMBER,
    total  NUMBER GENERATED ALWAYS AS (qty * price) VIRTUAL
);

💻 3. Modifying Structures (ALTER TABLE)¶

ADD: New features.
MODIFY: Change data types.
DROP: Remove columns.
RENAME: Fix typos.

ALTER TABLE orders ADD customer_name VARCHAR2(100);
ALTER TABLE orders MODIFY status VARCHAR2(50);
ALTER TABLE orders RENAME COLUMN status TO order_status;

💻 4. Clearing Data (DROP vs TRUNCATE)¶

DROP: Deletes the structure + data. 💣
TRUNCATE: Wipes data instantly, keeps the structure. ⚡

TRUNCATE TABLE old_logs;

🏗️ Architect's Note: DDL & Locks 🛡️¶

When you run an ALTER TABLE command, Oracle takes an Exclusive Lock on the table. - The Architect's Secret: If you run ALTER on a table while users are using it, their apps will hang! - Tip: Always perform DDL changes during a maintenance window or use Oracle's Online DDL features (available in Enterprise Edition).

📈 Learning Path¶