A database schema is like a logical container that holds various data structures known as schema objects. These objects include tables, indexes, views, and more, all of which are created and managed using SQL (Structured Query Language).
Think of a schema as a personal workspace for a database user. Each user account in a database has its own schema, named after the user, where their data resides. For example:
- The hr user owns the hr schema, which contains tables like employees.
- In real-world applications, the schema owner is often a database application rather than an individual person.
Within a schema, each object must have a unique name for its type. So, hr.employees clearly identifies the employees table within the hr schema.
Types of Schema Objects
Oracle SQL supports a variety of schema objects, each serving a unique purpose in data management. Here’s a breakdown of the most common ones:
| Schema Object | Description |
|---|---|
| Tables | Store data in rows and are the foundation of a relational database. |
| Indexes | Improve query performance by allowing fast data retrieval. Some tables, like index-organized tables (IOTs), store data directly in an index structure. |
| Partitions | Divide large tables or indexes into smaller, more manageable pieces, each with its own storage settings. |
| Views | Act as virtual tables, presenting data from one or more tables without storing the data themselves. |
| Sequences | Generate unique numeric values, often used for primary keys. |
| Dimensions | Define hierarchical relationships (like time, product, or customer categories) for analytical queries. |
| Synonyms | Serve as aliases for other schema objects, simplifying access. |
| PL/SQL Subprograms & Packages | Contain procedural logic (functions, procedures, packages) written in Oracle’s PL/SQL language. |
Some database objects (like user accounts, roles, and contexts) exist outside schemas but are still managed via SQL.
How Schema Objects Are Stored
Not all schema objects store physical data—some are purely metadata (like views). However, objects like tables and indexes store data in segments, which are logical storage units.
Key storage concepts:
- A tablespace is a storage container that can hold objects from multiple schemas.
- A segment (e.g., a table or index) resides in a tablespace but cannot span multiple tablespaces.
- A data file physically stores the data on disk. A single segment can span multiple data files within the same tablespace.
For example:
- A customers table might occupy two data files in the users tablespace.
- An index on that table could be stored in the indexes tablespace.
Schema Object Dependencies
Schema objects often depend on each other. For instance:
- A view depends on the tables it queries.
- A PL/SQL procedure may rely on a table’s structure.
Oracle automatically tracks these dependencies. If a referenced object changes (e.g., a column’s data type is modified), dependent objects (like views or procedures) may become invalid and require recompilation.
Example: Dependency in Action
- A table test_table is created with two columns:
CREATE TABLE test_table (col1 INTEGER, col2 INTEGER); - A procedure test_proc queries this table:
CREATE OR REPLACE PROCEDURE test_proc AS<br>BEGIN<br> FOR x IN (SELECT col1, col2 FROM test_table) LOOP<br> <em>-- Process data</em><br> NULL;<br> END LOOP;<br>END;<br>/ - If col1’s data type changes:
ALTER TABLE test_table MODIFY col1 VARCHAR2(20);
The procedure becomes invalid until recompiled (either manually or by execution).
Administrative Schemas : SYS and SYSTEM
Every Oracle database includes two critical administrative schemas:
| Schema | Purpose |
|---|---|
| SYS | Contains core data dictionary tables and views. Never modify these manually! |
| SYSTEM | Stores additional administrative metadata. Avoid using it for non-admin data. |
These accounts are reserved for database administrators (DBAs) performing tasks like:
- Starting/stopping the database
- Managing users and security
- Optimizing storage
Sample Schemas for Learning
Oracle provides sample schemas (like hr) to help users practice common database operations. The hr schema, for example, includes:
- employees (worker details)
- departments (team structures)
- locations (office addresses)
These schemas are excellent for testing queries, joins, and PL/SQL scripts.
Final Thoughts
Understanding schemas is crucial for effective database design. They:
✔ Organize data logically
✔ Control access via user ownership
✔ Support a variety of objects (tables, views, procedures)
✔ Maintain dependencies automatically
Whether you’re a developer, DBA, or data analyst, mastering schemas will help you build efficient, scalable databases.
