Schemas and Schema Objects
How Oracle Organizes Your Data Structures
🧠 What Is a Schema?
A schema is a logical container that holds data structures called schema objects.
- Each Oracle user account owns one schema with the same name as the user.
- Some database objects like users, roles, and dictionary objects are not part of any schema.
📂 Schemas vs Tablespaces
- Schema objects that use physical space (like tables and indexes) are stored in data files.
- Data files belong to tablespaces — logical storage units that hold data.
- A tablespace can contain objects from multiple schemas.
- A schema’s objects can reside in different tablespaces.
- So, schemas and tablespaces are independent in how they organize data.
📦 Main Types of Schema Objects
| Object Type | What It Does |
|---|---|
| Tables | Store data in rows. The fundamental object in a relational database. |
| Indexes | Provide fast access to rows by storing entries for each indexed row in a table or cluster. |
| Partitions | Pieces of large tables or indexes, each with a name and optional storage settings. |
| Views | Customized “stored queries” that present data from one or more tables or views. No data stored. |
| Sequences | Generate unique integers, often used for primary keys, sharable by multiple users. |
| Dimensions | Define parent-child relationships between column sets (levels), often for categorizing data like customers or time. |
| Synonyms | Aliases for other schema objects. They use no extra storage beyond their dictionary entry. |
| PL/SQL | Oracle’s procedural SQL extension: – Subprograms are named blocks you call with parameters. – Packages group related PL/SQL code, variables, and types. |
🛠️ DBA Tip of the Day
💡 Understand your schemas and schema objects well. Proper use of tables, indexes, and partitions can greatly improve database performance and manageability.