A Complete Guide to Creating Tables in Oracle Database [CREATE TABLE Explained]

Creating tables is one of the most fundamental skills when working with Oracle Database.
This article provides a thorough explanation of the CREATE TABLE statement—from its basic syntax and data types to constraints, tablespace assignment, and practical examples.

1. Basic Syntax of CREATE TABLE
1. 🔍 Text Diagram: Image of CREATE TABLE Structure
2. Common Data Types
3. Available Constraints
4. Specifying Tablespace (TABLESPACE clause)
5. Practical Example: EMP Table from SCOTT Schema
6. How to Install the SCOTT Schema
1. Example: Connecting via SQL*Plus
2. Create SCOTT User and Load Sample Data
7. CREATE TABLE AS SELECT (CTAS)
8. Notes and Common Pitfalls
9. Text Diagram: Table Design Thinking Flow
10. Summary

1. Basic Syntax of CREATE TABLE

CREATE TABLE table_name (
  column1 datatype [constraint],
  column2 datatype [constraint],
  ...
);

🔍 Text Diagram: Image of CREATE TABLE Structure

┌────────────────┐
│ CREATE TABLE   │
├────────────────┤
│   Column Name   │
│   Data Type     │
│   Constraint    │
├────────────────┤
│   Other Options │
└────────────────┘

2. Common Data Types

Data Type	Description
`NUMBER(p,s)`	Numeric (p = precision, s = scale)
`CHAR(n)`	Fixed-length string
`VARCHAR2(n)`	Variable-length string
`DATE`	Date and time (year/month/day hour/min/sec)
`TIMESTAMP`	Date and time (includes milliseconds)
`CLOB`	Character large object (large text data)
`BLOB`	Binary large object (e.g., image files)

3. Available Constraints

Constraints are essential for maintaining data integrity and consistency.

Constraint	Description	Example
`NOT NULL`	Disallows NULL	`ename VARCHAR2(10) NOT NULL`
`PRIMARY KEY`	Unique and non-NULL	`empno NUMBER PRIMARY KEY`
`UNIQUE`	Ensures uniqueness	`email VARCHAR2(50) UNIQUE`
`CHECK`	Validates values based on condition	`CHECK (sal > 0)`
`FOREIGN KEY`	References another table’s key	`FOREIGN KEY (deptno) REFERENCES dept(deptno)`

4. Specifying Tablespace (TABLESPACE clause)

CREATE TABLE emp (
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(20)
)
TABLESPACE users;

Defines the physical storage area of the table
Good practice for performance and manageability

5. Practical Example: EMP Table from SCOTT Schema

The following example is based on the EMP table in Oracle’s sample schema “SCOTT”.

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

6. How to Install the SCOTT Schema

※ If you are using a CDB configuration, please connect to the PDB before proceeding.

Example: Connecting via SQL*Plus

sqlplus sys/password@PDB_NAME as sysdba

Create SCOTT User and Load Sample Data

-- Create user
CREATE USER scott IDENTIFIED BY tiger
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- Grant privileges
GRANT connect, resource TO scott;

-- Run the script to create tables and insert data
@?/rdbms/admin/utlsampl.sql

7. CREATE TABLE AS SELECT (CTAS)

You can create a new table with structure and data from an existing table.

CREATE TABLE emp_copy AS
SELECT * FROM emp WHERE deptno = 10;

Data types are inferred from the SELECT result
Constraints are not copied

8. Notes and Common Pitfalls

Mistake	Tip or Prevention
Defining VARCHAR2 in bytes	Use CHAR length unit for multibyte characters
Not naming constraints	Name them explicitly for better manageability
Forgetting tablespace assignment	Specify to control data placement
Adding PRIMARY KEY afterward	Better to define it in CREATE TABLE

9. Text Diagram: Table Design Thinking Flow

[Requirement Analysis]
   ↓
[Columns/Types/Constraints] ──┬─> Choose appropriate data types
                              └─> Apply NOT NULL, PK, etc.
   ↓
[Storage Location] (tablespace), operational settings (LOGGING, PCTFREE, etc.)

10. Summary

CREATE TABLE is the foundational skill for Oracle database design.
Properly define data types, constraints, and tablespaces for performance and maintainability.
In practice, also consider default values, constraint naming, logging, and even partitioning.

[reference]
CREATE TABLE