Question: I want to understand the Oracle
schema definition and how it relates to Oracle database. What
is an Oracle schema definition?
Answer: A database many have many
schemas, and each user ID is sometimes called a "schema
owner" because a schema must be created under the auspices
of a dingle user ID. In general, a schema has many
tables and indexes and serves as a working piece of Oracle
to store and retrieve data.
The Oracle schema definition is stored as "metadata"
within the Oracle data dictionary, which is a schema that
collects the schema data definition language (DDL) for the
schema Once defined, a schema definition, can be
extracted, (also known as "punching" a schema) into a flat
It's fairly easy to remember the
relationships between Oracle schema definition components
once you can visualize the architecture. Here are some
A "database" component can be
thought-of as a set of xxx.dbf files
An "instance" component is a
running set of programs that read the .dbf files (Oracle
RAC allows many instances to connect to the database).
An "object" components is a table,
index, cluster table, &c
The "user" component creates
objects as the "schema owner" of those objects.
The "synonym" component allows you
to "alias" an object to make the schema owner hidden:
food for cat.food;
"public synonym" components, the table owner name ("the
user ID") is kept inside the synonym definition and
appended to the table name at runtime to remove
create public synonym
food for cat.food;
are details on
Oracle File & Tablespace
Within a database we have schemas, each
owned by a specific user_id. Hence, we can think of a USER
and a OWNER as the same in the sense that a schema owner is
a single, distinct user ID:
schema_owner = user_id
Also, we can refer to tables with their
"fully-qualified" names, where we include the schema owner.
Here we see two tables named "food", one created by the user
"cat" and the other schema owner "dog":
select stuff from dog.food;