How to Add a Column in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Use ALTER TABLE ... ADD to add columns. A NOT NULL column with a constant DEFAULT is a fast, metadata-only add since Oracle 11g, safe on large tables.
ALTER TABLE orders ADD (discount NUMBER(5,2) DEFAULT 0);Add several columns at once
ALTER TABLE orders ADD (
notes VARCHAR2(200),
updated_at TIMESTAMP
);NOT NULL with a default
Adding a NOT NULL column normally requires backfilling every existing row before the constraint can be enforced, which locks the table for the duration on older databases. Since 11g, if you supply a constant DEFAULT alongside NOT NULL, Oracle records the default in the data dictionary instead of writing it into every row, so the ADD completes almost instantly regardless of table size.
ALTER TABLE orders ADD (status VARCHAR2(20) DEFAULT 'PENDING' NOT NULL);That fast path only applies to a literal constant default. A default built from a sequence, such as DEFAULT seq.NEXTVAL, updates every existing row rather than just recording metadata, and a function call may do the same; either can trigger a full-table update, with the redo that implies, so keep defaults on large tables to constant literals where you can.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free