Oracle Database 11g : Database Fundamentals - Become Familiar with Other Important Items in Oracle Database 11g

10/19/2012 9:18:18 PM
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.


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.


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.


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 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release – 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.


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 - Production on Sun Mar 11 10:29:42 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle11g Enterprise Edition Release – 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;

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.


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.


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.


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.

Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Top 10
Free Mobile And Desktop Apps For Accessing Restricted Websites
TOYOTA CAMRY 2; 2.5 : Camry now more comely
KIA SORENTO 2.2CRDi : Fuel-sipping slugger
How To Setup, Password Protect & Encrypt Wireless Internet Connection
Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
Backup & Restore Game Progress From Any Game With SaveGameProgress
Generate A Facebook Timeline Cover Using A Free App
New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th