Schemas and Schema Objects

Add W3Buddy as a preferred source on Google

Article Summary

How Oracle Organizes Your Data Structures 🧠 What Is a Schema?A schema is a logical container that holds data structures called schema objects. 📂 Schemas vs Tablespaces 📦 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 […]

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.