Common Data Types
| Type | Use | Example |
|---|---|---|
VARCHAR2(n) | Text up to n bytes or characters | VARCHAR2(60) |
NUMBER(p,s) | Numbers, p total digits, s after the decimal | NUMBER(10,2) |
DATE | Date and time to the second | SYSDATE |
TIMESTAMP | Date and time with fractions of a second | SYSTIMESTAMP |
CLOB | Large text | Documents, JSON text |
BLOB | Large binary data | Images, files |
'' as NULL. This surprises people coming from other databases.
Constraints
| Constraint | Rule |
|---|---|
PRIMARY KEY | Unique and not null. Identifies a row. |
FOREIGN KEY | Value must exist in another table |
UNIQUE | No duplicates (NULLs allowed) |
NOT NULL | A value is required |
CHECK | A custom rule, for example price > 0 |
pk_employees). When one fails, the error message shows the name, which tells you instantly what went wrong.
ALTER TABLE
Indexes
An index works like the index of a book. Instead of reading every page (full table scan), Oracle jumps to the right place.
Sequences
Identity columns (GENERATED ALWAYS AS IDENTITY) use a sequence behind the scenes, so most new tables do not need a manual sequence.
Views
A view is a saved query that behaves like a table. Use it to hide complexity or restrict which columns people see.
Drop and Truncate
| Command | Effect | Rollback? |
|---|---|---|
DELETE FROM t | Removes rows one by one | Yes, until commit |
TRUNCATE TABLE t | Removes all rows instantly, resets space | No |
DROP TABLE t | Removes the table (goes to the recycle bin) | Can be restored with FLASHBACK |
Conclusion
Pick the right data type, name your constraints, index foreign keys and use views to simplify queries. Remember that DDL commits automatically.

