How to CREATE TABLE in Oracle

How do I use Oracle's CREATE TABLE statement?

CREATE TABLE adds a new, fully-defined table to the current Oracle schema, allocating storage and registering metadata.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CREATE TABLE do in Oracle?

CREATE TABLE defines a new table and its columns, data types, defaults, and constraints inside the current schema. Once executed, Oracle allocates storage and registers the table in the data dictionary.

What is the basic syntax?

The statement starts with CREATE TABLE table_name (column definitions) followed by optional clauses such as constraints, tablespace, storage, and AS SELECT.Only the table owner or a user with CREATE ANY TABLE can execute it.

How do I choose column data types?

Select the smallest type that fits: NUMBER for numeric values, VARCHAR2 for variable strings, DATE/TIMESTAMP for temporal data, and CLOB/BLOB for large objects. Proper types lower storage and speed queries.

How to define constraints inline vs out-of-line?

Inline constraints follow the column (customer_id NUMBER PRIMARY KEY). Out-of-line constraints appear after all columns (CONSTRAINT pk_customer PRIMARY KEY (customer_id)).Name constraints for easy debugging.

Can I create a table from a SELECT?

Yes. Append AS SELECT ... to populate the new table with query results.Oracle copies column names and types from the SELECT list unless you rename columns.

Example: Creating an Orders table

CREATE TABLE Orders ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, customer_id NUMBER NOT NULL, order_date DATE DEFAULT SYSDATE, total_amount NUMBER(10,2) CHECK (total_amount >= 0), CONSTRAINT pk_orders PRIMARY KEY (id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES Customers(id) ) TABLESPACE users;

Best practices

Name constraints, always add primary keys, set NOT NULL on required columns, and document tables with COMMENT ON TABLE/COLUMN right after creation.Version-control all DDL scripts.

How to verify the new table?

Query ALL_TABLES for its presence, DESCRIBE to inspect columns, and run SELECT COUNT(*) to confirm row counts when using AS SELECT.

Common mistakes

Creating tables without primary keys makes joins error-prone; always declare one. Forgetting NOT NULL on essential columns allows bad data; add NOT NULL for mandatory fields.

Next steps

Use ALTER TABLE to evolve structure and GRANT privileges so other users can query the new table.

Why How to CREATE TABLE in Oracle is important

How to CREATE TABLE in Oracle Example Usage


-- Copy the Products table structure without data
CREATE TABLE Products_Archive AS
SELECT * FROM Products WHERE 1 = 0;

How to CREATE TABLE in Oracle Syntax


CREATE TABLE table_name
(
   column_name  data_type [DEFAULT expr] [column_constraint]...,
   ...
   [table_constraint ...]
)
[SEGMENT CREATION {IMMEDIATE | DEFERRED}]
[TABLESPACE tablespace_name]
[PCTFREE n]
[STORAGE (storage_clause)]
[LOGGING | NOLOGGING]
[AS subquery];

Common Mistakes

Missing constraint names – unnamed constraints get system-generated identifiers that are hard to track. Always add CONSTRAINT constraint_name before the definition.
Using CHAR/VARCHAR instead of VARCHAR2 – CHAR pads spaces and VARCHAR is a synonym that may disappear. Stick to VARCHAR2 for variable-length strings.