Users, Schemas, Tablespaces, Tables, Columns, and Data Types
Build a strong Oracle foundation by learning how schemas and storage concepts connect to table design and data modeling.
Inside this chapter
- Schema and User Thinking in Oracle
- Tablespaces and Storage Awareness
- Creating a Starter Table
- Common Oracle Data Types
Series navigation
Study the chapters in order for the clearest path from Oracle SQL basics to PL/SQL, recovery, tuning, and enterprise operations. Use the navigation at the bottom of each page to move through the full series.
Schema and User Thinking in Oracle
In Oracle DB, a user and a schema are closely related concepts. Objects such as tables, views, indexes, and procedures belong to a schema. Students should learn early that Oracle environments are often organized through users, roles, and tablespaces, not only through tables.
Tablespaces and Storage Awareness
Tablespaces are logical storage containers for database objects. Beginners do not need to master all storage internals immediately, but it is important to understand that Oracle DB separates logical objects from physical storage decisions. This matters for administration, performance, and capacity planning.
Creating a Starter Table
CREATE TABLE customers (
customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
full_name VARCHAR2(120) NOT NULL,
email VARCHAR2(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);This example introduces Oracle-friendly data types and identity-based key generation. Good design begins with clear naming, integrity constraints, and sensible defaults.
Common Oracle Data Types
| Type | Use Case | Important Note |
|---|---|---|
NUMBER | Identifiers, counts, precise numbers | Flexible and common across numeric designs |
VARCHAR2 | Names, emails, codes, labels | Preferred string type in most designs |
DATE | Date and time values | Includes time information in Oracle |
TIMESTAMP | More precise time tracking | Useful for auditing and detailed events |
CLOB | Large textual content | Used for larger documents or long text |