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
text file.
It's fairly easy to remember the
relationships between Oracle schema definition components
once you can visualize the architecture. Here are some
simple concepts:
-
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:
create synonym
food for cat.food;
-
With
"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
duplicates:
create public synonym
food for cat.food;
-
Here
are details on
Oracle File & Tablespace
Management
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 cat.food;
union
select stuff from dog.food;
