So far, you’ve had a brief
look at tables, views, tablespaces, and a handful of stored objects,
such as views, triggers, procedures, packages, and functions. Let’s
round out this introduction to Oracle Database 11g
architecture by covering a few other items commonly encountered. The
following discussion is a hodgepodge of things that are necessary for a
person’s understanding of the Oracle Database 11g
architecture and operations. Keep in mind that you must also spend a
bit of time looking at the role of the database administrator,
affectionately called the DBA, who is the gatekeeper of the database and the person responsible for its smooth operation.
Indexes
Tables are made up of rows and columns, which are the basis of all objects in Oracle Database 11g.
As applications interact with the database, they often retrieve vast
amounts of data. Suppose MyYP, a fictitious Internet company, provided
Yellow Pages listings for North America, and the data was stored
primarily in a table called YP_MASTER. Each row in the YP_MASTER table
is uniquely identified by a combination of company name, municipality,
and geographic location (state or province). As words are retrieved from
the database to satisfy online queries, indexes would provide a quick
access path to the qualifying data. Specific index characteristics are
relevant to the power they deliver in Oracle Database 11g. For instance:
They are built on one or more columns in a table using simple SQL statements.
They
are separate from the tables that they are built on and can be dropped
without affecting the data in the table itself. On the contrary, when a
table is dropped, any indexes it has disappear with the table.
The
function they perform can be likened to the index in a book. If one
were looking for a specific topic in a textbook, the best place to start
would be the index—it provides a shortcut to the information being
sought. If one imagined that YP_MASTER were a book rather than a table,
finding Y&M Plumbing in Pensacola, Florida would be faster using the
index than reading the book from the start to the 25th letter of the alphabet. The names on the corner of the pages in a phone book are like an index.
Indexes
occupy space in the database; even though there are ways to keep their
space to a minimum, extra space is required and must be pre-allocated.
Users
Most of us are familiar
with usernames and passwords from our experience logging into corporate
networks and other secure systems. Oracle Database 11g
implements the same mechanism with login credentials and privileges
given out by the database administrator. Once accounts are created,
people initiate connections to Oracle Database 11g
and work with their own data and other users’ data where the
appropriate privileges have been given out. We discuss object privileges
in the “Work with Object and System Privileges” section immediately
following this one.
Note
With Oracle Database 11g, the terminology user, account, and schema are used synonymously.
Once an account is created, it is often given the rights to occupy space in one or more Oracle Database 11g tablespaces. This is discussed in the next section.
Tablespace Quotas
As additional nonsystem
tablespaces are created, the database administrator gives out quotas
that allow users to occupy space therein. Tablespace quotas are given out using an SQL statement with three parts:
The username to whom the quota is being given.
The name of the tablespace within which the username is being permitted to create tables.
The
amount of that quota—whether it’s mentioned in absolute bytes (for
example, 500,000) or more commonly in quantities of megabytes (500MB,
for instance). Unlimited quotas can be allowed using the keyword unlimited.
Regardless of how a quota is given out, the SQL statement passed to Oracle Database 11g resembles the following:
SQL*Plus: Release 11.1.0.1.0 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release 11.1.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> alter user hr quota 500m on hr_data;
User altered.
SQL> alter user ap quota unlimited on ap_idx;
User altered.
Synonyms
You’ll remember that in the “Work with Tables” section we discussed that the key was passing Oracle Database 11g
the create table keywords. In a nutshell, table creation is undertaken
after establishing a successful connection to the database, and then,
with appropriate privileges in place, defining a table. One of the key
concepts with all database management systems is sharing data. Since it
is key to only have one copy of a table and to have its contents shared
amongst applications, synonyms are a way to reference other people’s data.
Suppose you wanted to use the
PART_MASTER table in an application owned by a user other than the
owner. That owner would permit us to work with the table’s data, and
then we would create a synonym to reference its contents. The code would
resemble the following:
SQL*Plus: Release 11.1.0.1.0 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release 11.1.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> create synonym part_master for inv.part_master;
Synonym created.
SQL> select count(*)
2 from part_master
3 where in_stock is not null;
COUNT(*)
-------------
13442
The preceding SQL
statement references an object called part_master. Depending on how your
access is defined, the way that you reach the physical table may be
different. When you are the owner of the table and you use the table’s
name in the from clause, Oracle understands that you would like to use
your own table. If you do not own a table by that name, Oracle then
looks in a list of table synonyms or pointers to a table with that name
owned by someone else. This process of using synonyms is a transparent
operation. If you do not own the table by the name or no synonym exists,
you will receive an error. There are actually two kinds of synonyms:
Private synonyms are created in one account and are only usable by the creator.
Public synonyms are created by a central privileged user and are available to anyone able to connect to Oracle Database 11g.
Note
One
needs the appropriate object privileges to be able to work with someone
else’s data using a private or public synonym. The synonym itself does
not imply that the appropriate privileges can be circumvented.
Roles
Often it makes sense to group similar users together to streamline the organization of people who use Oracle Database 11g.
Using roles, the DBA can logically lump personnel together and give out
object privileges to roles rather than individual users. Roles can be
password protected, though in most implementations they do not have this
level of complexity.
Default User Environments
As accounts are created by the
DBA, users are given a default environment to use unless some specifics
are coded as they interact with Oracle Database 11g. Users are commonly set up with the following default environment settings:
The default tablespace is where tables are placed unless the create table statement explicitly points at a nondefault tablespace that the user has a quota for.
Temporary tablespaces are the tablespaces where users perform sort and merge operations while the Oracle Database 11g engine is processing queries.
Users can be given
membership in one or more roles and have their default profile changed
as well. As users are created, they do not automatically inherit a
default tablespace; one must be manually given out during or following
the user creation statement. Users do automatically point at a temporary
tablespace, unless manually pointed elsewhere.
Note
With Oracle Database 11g one
is now able to set the default tablespace for the entire database
instance. This is done via an “ALTER DATABASE DEFAULT TABLESPACE tablespace-name;” command. You will need to have the privileges to be able to perform this operation.
