Back Up Next

Chapter 4 *

Creating Other Database Objects in Oracle *

Table and Constraint Modifications *

Adding and Modifying Columns *

Exercises *

Modifying Integrity Constraints *

Exercises *

Enabling or Disabling Constraints *

Exercises *

Dropping Tables *

Exercises *

Truncating Tables *

Exercises *

Changing Names of Objects *

Exercises *

Viewing Dictionary Comments on Objects *

Exercises *

Sequences *

Role of Sequences *

Exercises *

Creating Sequences *

Exercises *

Using Sequences *

Exercises *

Modifying a Sequence Definition *

Exercises *

Removing Sequences *

Exercises *

Views *

Data Dictionary Views *

Exercises *

Creating Simple and Complex Views *

Exercises *

Creating Views that Enforce Constraints *

Exercises *

Modifying Views *

Exercises *

Removing Views *

Exercise *

Indexes *

Manual and Automatic Indexes *

Exercises *

Uses for Indexes *

Exercises *

Index Structure and Operation *

Exercises *

Creating Indexes *

Exercises *

Removing Indexes *

Exercises *

Guidelines for Creating Indexes *

Exercises *

User Access Control *

Database Security Model *

Exercises *

Granting System Privileges *

Exercises *

Using Roles to Manage Database Access *

Exercises *

Granting Object Privileges *

Exercises *

Changing Passwords *

Exercise *

Granting and Revoking Object Privileges *

Using Synonyms for Database Transparency *

Exercises *

Chapter Summary *

Two-Minute Drill *

Chapter Questions *

Answers to Chapter Questions *


Chapter 4

Creating Other Database Objects in Oracle

In this chapter, you will understand and demonstrate knowledge in the following areas:

Altering tables and constraints
Creating sequences
Creating views
Creating indexes
Controlling user access

At this point, the user should know how to select data from a database, model a business process, design a set of database tables from that process, and populate those tables with data. These functions represent the cornerstone of functionality that Oracle can provide in an organization. However, the design of a database does not stop there. There are several features in the Oracle architecture that allows the user to give richer, deeper meaning to the databases created in Oracle. These features can make data "transparent" to some users but not to others, speed access to data, or generate primary keys for database tables automatically. These features are collectively known as the advanced database features of the Oracle database. This chapter covers material in several different areas tested in the OCP Exam 1. The material in this chapter consists of 17 percent of the material covered on the exam.

Table and Constraint Modifications

In this section, you will cover the following topics related to altering tables and constraints:

Adding and modifying columns
Modifying integrity constraints
Enabling or disabling constraints
Dropping tables
Truncating tables
Changing names of objects
Dictionary comments on objects

Once a table is created, any of several things can happen to make the needs of the database change such that the table must be changed. The database developer and DBA will need to understand how to implement changes on the database in an effective and nondisruptive manner. Consider the implications of this statement. For example, there are two ways to cure an ingrown toenail. One is to go to a podiatrist and have the toenail removed. The other is to chop off the toe. Although both approaches work, the second one produces side effects that most people can safely do without. The same concept applies to database changes. The developer or DBA can do one of two things when a request to add some columns to a table comes in. One is to add the column, and the other is to re-create the entire table from scratch. Obviously, there is a great deal of value in knowing the right way to perform the first approach.

Adding and Modifying Columns

Columns can be added and modified in the Oracle database with ease using the alter table statement and its many options for changing the number of columns in the database. When adding columns, a column added with a NOT NULL constraint must have data populated for that column in all rows before the NOT NULL constraint is enabled, and only one column of the LONG datatype can appear in a table in Oracle7. The following code block is an example of using the alter table statement.

ALTER TABLE products
ADD (color VARCHAR2(10));

If the developer or the DBA needs to add a column that will have a NOT NULL constraint on it, then several things needs to happen. The column should first be created without the constraint, then the column should have a value for all rows populated. After all column values are NOT NULL, the NOT NULL constraint can be applied to it. If the user tries to add a column with a NOT NULL constraint on it, the developer will encounter an error stating that the table must be NULL.

Only one column in the table may be of type LONG within a table. That restriction includes the LONG RAW datatype. This restriction, as well as the entire usage of datatypes to store large amounts of data, was removed and remodeled as part of Oracle8. More details appear in Unit V. It is sometimes useful in Oracle7 databases to have a special table that contains the LONG column and a foreign key to the table that would have contained the column in order to reduce the amount of data migration and row chaining on the database. More details about row chaining and migration will appear in Unit IV.

Another important facet about table columns is the configuration of the datatype that is permitted for storage in the column. On a table called PRODUCTS, there is a column called SERIAL# of type VARCHAR2(10). The retailer has just begun to carry a new line of products whose serial number is substantially longer than the serial numbers of other products the store carries. The DBA is called in to determine if the longer serial number will present a problem to the database. As it turns out, the average serial number for this new line of products is 23 characters long. In order to resolve the issue, the DBA can issue a statement that will make the column length longer.

ALTER TABLE products
MODIFY (serial# VARCHAR2(25));

Several conditions apply to modifying the datatypes of existing columns or to adding columns to a table in the database. The general rule of thumb is that increases are generally OK, while decreases are usually a little trickier. Some examples of increases that are generally acceptable are listed as follows:

Increases to the size of a VARCHAR2 or CHAR column
Increases in size of a NUMBER column
Increasing the number of columns in the table

Decreasing the size of various aspects of the table, including some of the column datatypes or the actual number of columns in the table, requires special steps to accomplish. Usually, the effort involves making sure that the relevant column (or columns) has all NULL values in it before executing the change. In order to execute these types of operations on columns or tables that contain data, the developer or DBA must find or create some sort of temporary storage place for the data in the column. One acceptable method is creating a table using the create table as select statement where the select statement used draws data from the primary key and the column in question that will be altered. Another method is spooling the data in a table to a flat file and reloading later using SQL*Loader. More information about this method will be discussed in Unit II. The following list details the allowable operations that decrease various aspects of the database:

Reducing the number of columns in a table (empty table only)
Reducing the size of a NUMBER column (empty column for all rows only)
Reducing the length of a VARCHAR2 or CHAR column (empty column for all rows only)
Changing the datatype of a column (empty column for all rows only)


  1. What statement is used to change the definition of a table?
  2. What process is used to change a nullable column to one with a NOT NULL constraint?

Modifying Integrity Constraints

There are several different changes that can be made to constraints. These changes include altering the constraint, disabling, enabling, or removing the constraint from the column or table of the database. These processes allow the DBA or developer to create, modify, or remove the business rules that constrain data. The first activity that a DBA or developer may need to do related to supporting constraints on a database is to add constraints to a database. This process can be easy or difficult, depending on the circumstances. If a constraint cannot be created with the database, it can be added to the database before data is populated into the database with the most ease of any scenario in which a constraint must be added to the database.

ALTER TABLE products

ALTER TABLE products
ADD (CONSTRAINT pk_products _01 PRIMARY KEY (product#));

ALTER TABLE products
ADD (CONSTRAINT fk_products _02 FOREIGN KEY (color)

ALTER TABLE products
ADD (UNIQUE (serial#));

ALTER TABLE products
ADD(size CHECK (size in ‘P,S,M,L,XL,XXL,XXXL’));

Notice that in the first statement in the list of examples above that the modify clause is used to add a NOT NULL constraint to the column, while the add clause is used to add all other types of integrity constraints. The column must already exist in the database table. No constraint can be created for a column that does not exist in the table. Some of the restrictions on creating constraints are listed below:

Primary keys Column cannot be NULL and must have all unique values.
Foreign keys Referenced column in other tables must contain values corresponding to all values in the referring column or all column values must be NULL.
Unique constraints Column must contain all unique values or NULL.
Check constraints The new constraint will only be applied to data added or modified after the constraint is created.
NOT NULL Column cannot be NULL.

If any of the conditions for the constraints listed above are not met for the respective constraint to which the rule applies, then creation of the constraint will fail. The proper procedure the DBA should take to correct the situation where the creation of a constraint fails is detailed in Unit II.


  1. What are some of the ways integrity constraints can be changed on a table?
  2. What are some rules that must be adhered to for modification of each type of constraint?

Enabling or Disabling Constraints

What happens to a constraint if the creation of the constraint fails? This question is answered by examining the concept of enabling or disabling a constraint. Think of a constraint as a switch. When the switch is enabled, the constraint will do its job in enforcing business rules on the data entering the table. If the switch is disabled, the rules defined for the constraint are not enforced, rendering the constraint as ineffective as if it had been removed. Examine the process of enabling a disabled constraint. This process may be executed after the DBA has taken steps to correct the reason the integrity constraint failed during creation in the first place. When the problem has been corrected or when the load completes, the DBA may want to take steps to put the constraints back in order again.

ALTER TABLE products
ENABLE pk_products_01;

ALTER TABLE products
ENABLE uk_products_03;

Note that in this situation, only constraints that have been defined and are currently disabled can be enabled by the code above. A constraint that has not been created cannot be enabled. As discussed above, a constraint that fails on creation will automatically be disabled. However, there are situations where the DBA may want to disable a constraint for some general purpose. BE CAREFUL WHEN USING THIS APPROACH, HOWEVER! If data is loaded into a table that violates the integrity constraint while the constraint was disabled, the DBA’s attempt to enable the constraint later will fail. Precautions should be taken to ensure that data loaded into a table that has disabled constraints on it does not violate the constraint rules so that the enabling of the constraint later will be a smooth process. If there are dependent foreign keys on a primary key, the cascade option is required for disabling or dropping a primary key.

ALTER TABLE products

ALTER TABLE products

ALTER TABLE products

TIP: Disabling a constraint leaves the table vulnerable to inappropriate data being entered into the table. Care should be taken to ensure that the data loaded during the period the constraint is disabled will not interfere with the DBA’s ability to enable the constraint later.

The final aspect of constraint manipulation to be discussed is the removal of a constraint. There is generally nothing about a constraint that will interfere with the DBA’s ability to remove a constraint, so long as the person attempting to do so is either the owner of the table or granted the appropriate privilege to do so. A full discussion of system and object privileges in Oracle appears in Unit II. When a constraint is dropped, any associated index with that constraint (if there is one) is also dropped.

ALTER TABLE products
DROP CONSTRAINT uk_products_01;

ALTER TABLE products

TIP: Several anomalies can be found when adding, enabling, disabling, or dropping NOT NULL constraints. Generally, the alter table modify clause must be used in all situations where the NOT NULL constraints on a table must be altered.


  1. How does the DBA enable a disabled constraint?
  2. What are some restrictions on enabling constraints?

Dropping Tables

Sometimes, the "cut off your toe" approach to database alteration is required to make sweeping changes to a table in the database. All requirements to executing that approach have been discussed so far except one—eliminating the offending table. In order to delete a table from the database, the drop table command must be executed.

DROP TABLE products;

If there are foreign key constraints on the table to be dropped, then the DBA can use cascade constraints. The constraints in other tables that refer to the table being dropped are also dropped with cascade constraints. There are usually some associated objects that exist in a database along with the table. These objects may include the index that is created by the primary key or the unique constraint that is associated with columns in the table. If the table is dropped, Oracle automatically drops any index associated with the table as well.

DROP TABLE products


  1. How is a table dropped?
  2. What special clause must be used when dropping a table when other tables have foreign key constraints against it?
  3. What happens to associated objects like indexes when a table is dropped?

Truncating Tables

There is a special option available in Oracle that allows certain users to delete information from a table quickly. Remember, in the last chapter the delete statement was discussed. One limitation of the delete statement is the fact that it uses the transaction processing controls that were also covered in the last chapter. Sometimes, in large tables or when the DBA or privileged developer is sure he or she wants to remove the data in a table, the delete option is an inefficient one for accomplishing the job.

As an alternative, the DBA or developer may use the truncate option to the alter table statement. The alter table truncate statement is a part of the data definition language of the Oracle database, unlike the delete statement, which is part of the DML. Truncating a table removes all row data from a table quickly while leaving the definition of the table intact, including the definition of constraints and indexes on the table. The truncate statement is a high-speed data deletion that bypasses the transaction controls available in Oracle for recoverability in data changes. Truncating a table is almost always faster than executing the delete statement without a where clause to delete all rows from the table, but once complete, the data cannot be recovered without having a backed up copy of the data. More information about backup and recovery will be discussed in Unit III.



  1. What are two options for deleting data from a table?
  2. Is truncating a table part of DML or DDL? Explain.

Changing Names of Objects

The command to change object names in Oracle is accomplished using the rename command. This command allows the DBA to change the name of one table to another using a utility that takes data from one table and automatically moves it to another that is called something else. Use of rename can take a long time, depending on the size of table involved in the operation.

RENAME products TO objects;

The effect here can be duplicated through the use of synonyms. A synonym gives users an alternate name with which they can use to refer to the existing table. No actual data movement takes place, as in the rename command, which physically moves data in the Oracle database to an object with the new name. However, this activity can be detrimental to performance, especially if the object being renamed is large.

TIP: Synonyms in Oracle are used to offer an alternate name to the table without altering the details of the table’s definition. Synonyms can be public or private, as Oracle supports data privacy.


  1. How is a database object name changed? What are some of the effects of renaming a table?
  2. What is another way to duplicate the effect of renaming a table?

Viewing Dictionary Comments on Objects

The Oracle data dictionary carries many different items about the table, including the description of the columns in the table. This is provided by the data dictionary with use of the describe command. The next object information found in the data dictionary is the use of object commenting. Comments are useful for recording data modeling information or any other information about the database objects directly within the data dictionary. To add a comment to a table or column, use the comment on statement. To view these comments, query the ALL_TAB_COMMENTS for tables, or ALL_COL_COMMENTS for columns on tables.

COMMENT ON TABLE product IS your_comment;
COMMENT ON COLUMN product.serial# IS your_comment;


  1. How can table remarks be entered and where are they stored?
  2. How can the DBA reference comments on a database object?


In this section, you will cover the following topics related to creating sequences:

The use of sequences
Creating sequences
Using sequences
Modifying the sequence definition
Removing sequences

In database development, sometimes it becomes necessary to populate a column with a series of integers on an ongoing basis. These integers may be used as numbers to identify the records being entered as unique. For example, a doctor’s office may have a client tracking system that assigns each new patient a unique integer ID to identify their records. There are several ways to produce this integer ID through programmatic means, but the most effective means to do it in Oracle is through sequences.

Role of Sequences

A sequence is a special database object that generates integers according to specified rules at the time the sequence was created. Sequences have many purposes in database systems, the most common of which is to generate primary keys automatically. This task is common in situations where the primary key is not important to use for accessing data to store in a table. The common use of sequences to create primary keys has some drawbacks, though. With the use of sequences for this purpose, the primary key itself and the index it creates is rendered somewhat meaningless. One other area of use for sequences is for random number generation.

Sequences operate on the following principle. Users select data from them using two special keywords to denote virtual columns in the database. The first virtual column is CURRVAL. This column can be used to see what the current value generated by the sequence is. The second virtual column is NEXTVAL. This column is the next value that the sequence will generate according to the rules developed for it. Selecting NEXTVAL on the sequence effectively eliminates whatever value is stored in CURRVAL. Data may only be drawn from a sequence, never placed into it. These virtual columns are available for select access, but users can incorporate a call on the sequence’s CURRVAL or NEXTVAL to use the value in either of the two columns for insert or update on a row of another table.

Some restrictions are placed on the types of statements that can draw on CURRVAL and NEXTVAL of sequences as well. Any update or insert statement can make use of the data in a sequence. However, it generally is not advisable to set up an insert or update statement to do so in a trigger, as this has a tendency to cause the SQL*Plus session that fires the trigger to end abnormally with the ORA-03113 error. In addition, subqueries of select statements (including those with having), views, select statements using set operations such as union or minus, or any select statement that requires a sort to be performed are not able to contain reference to a sequence.


  1. What is a sequence? What are some ways a sequence can be used?
  2. What are CURRVAL and NEXTVAL? What happens to CURRVAL when NEXTVAL is selected?

Creating Sequences

Many rules are available on sequences that allow the developer or DBA to specify how the sequence generates integers. These rules are useful for the definition of sequences that produce integers in special order, or with increments in a certain way. There is even a feature related to sequences that allows the developer to improve performance on a sequence. The explanation of each clause in the statement along with some options for configuring that clause appear in the following list:

start with n Allows the creator of the sequence the ability to identify the first value generated by the sequence. Once created, the sequence will generate the value specified by start with the first time the sequence’s NEXTVAL virtual column is referenced.
increment by n Defines the number by which to increment the sequence every time the nextval virtual column is referenced.
minvalue n Defines the minimum value that can be produced by the sequence as a value for the sequence. If no minimum value is desired, the nominvalue keyword can be used.
maxvalue n Defines the maximum value that can be produced by the sequence as a value for the sequence. If no maximum value is desired, the nomaxvalue keyword can be used.
cycle Allows the sequence to recycle values produced when the maxvalue or minvalue is reached. If recycling is not desired, the nocycle keyword can be used.
cache n Allows the sequence to cache the specified number of values at any time in order to improve performance. If caching is not desired, the nocache keyword can be used.
order Allows the sequence to assign sequence values in the order the requests are received by the sequence. If order is not desired, the noorder keyword can be specified.

Consider now some various examples for defining sequences. The integers that can be specified for sequences as they are created can be negative as well as positive. Consider the following example of a sequence that generates decreasing numbers into the negatives. The start with integer in this example is positive, but the increment by integer is negative, which effectively tells the sequence to decrement instead of incrementing. When zero is reached, the sequence will start again from the top. This sequence can be useful in countdowns for programs that require a countdown before an event will occur.

CREATE SEQUENCE countdown_20

The final example offered is useful for generating completely random numbers. When noorder is used in conjunction with no value specified for start with or increment by, then the sequence is left to its own devices for generating random numbers every time NEXTVAL is referenced. The code block below illustrates a sequence that generates random numbers between zero and 1,000 without repeating a sequence value during the life of the sequence:



  1. What statement is used for creating a sequence?
  2. What are the options used for sequence creation?

Using Sequences

Once the sequence is created, it is referenced using the CURRVAL and NEXTVAL virtual columns. This reference may occur in a few different ways. Sometimes the users of the database may want to view the current value of the sequence by means of a select statement. The next value generated by the sequence can be generated with a select statement as well. Notice the reappearance of the DUAL table. Since sequences themselves are not tables, only objects that generate integers via the use of virtual columns, the DUAL table acts as the "virtual" table to pull virtual column data from. As stated earlier, values cannot be placed into the sequence, only selected from the sequence. Once the NEXTVAL column is referenced, the value in CURRVAL becomes the value in NEXTVAL, and the prior value in CURRVAL is lost.

SELECT random_num.currval CURRENT,
random_num.nextval NEXT,
random_num.currval CURRENT
FROM dual;

---------  --------- ---------
59496      9382      9382

Generally, however, users do not use select statements to draw data from sequences. Instead, that functionality can be incorporated directly into data changes made by insert or update statements. The statements below illustrate usage of sequences directly in changes made to tables:

INSERT INTO expense(expense_no, empid, amt, submit_date)
VALUES(expense_sequence_01.nextval, 59495, 456.34, ‘21-NOV-98’);

UPDATE product
SET product_num = random_num.currval
WHERE serial_num = 34938583945;

This direct usage of sequences in insert and update statements is the most common use for sequences in a database. In the situation above where the sequence generates a primary key for all new rows entering the database table, the sequence would likely be referenced directly from the insert statement. Note however, that this approach sometimes fails when the sequence is referenced by triggers. Therefore, the best method to use when referencing sequences is within the user interface or within stored procedures.


  1. Identify a way to refer to a sequence with the select statement. Why is use of the DUAL table important in this method?
  2. Identify a way to refer to a sequence with the update and insert statements.

Modifying a Sequence Definition

Like tables, there may come a time when the sequence of a database will need its rules altered in some way. For example, in the employee expense application, the users may want to start the box numbering at some different number in order to start a new fiscal year. For another example, a sequence may have generated several primary keys for the rows in a database. When the sequence is re-created, the DBA may need to set the first value produced by the sequence in order to avoid primary key constraint violations. Any parameter of a sequence can be modified by the DBA or owner of the sequence with the issuance of the alter sequence statement.

ALTER SEQUENCE decrement_sequence_01

The effect is immediate—the statement will change the DECREMENT_SEQUENCE_01 to decrement each NEXTVAL by 4. Any parameter of a sequence that is not specified by the alter sequence statement will remain unchanged. The COUNTDOWN_20 sequence will now be changed to run through one countdown from 20 to zero only. After the sequence hits zero, no further references to COUNTDOWN_20.NEXTVAL will be allowed.

ALTER SEQUENCE countdown_20

The final example of usage for the alter sequence statement involves the RANDOM_NUMS sequence created earlier. The code block below is designed to change the range of values that can be generated by the sequence from 1,000 to 10,000.

ALTER SEQUENCE random_nums

Modification of sequences is a relatively simple process. However, the main concern related to changing sequences is monitoring the effect on tables or other processes that use the values generated by the sequence. For example, resetting the value returned by the sequence from 1,150 back to zero is not a problem to execute. Once performed, there could be repercussions if the sequence was used to generate primary keys for a table, of which several values between zero and 1,150 were already generated. When the sequence begins generating values for insert statements that depend on the sequence for primary keys, there will be primary key constraint violations on the table inserts. Although these problems don’t show up when the sequence is altered, the only way to solve the problem (other than deleting the records already existing in the table) is to alter the sequence again.


  1. What statement is used to modify a sequence definition?
  2. When do changes to a sequence take effect?

Removing Sequences

Removing a sequence may be required when the sequence is no longer needed. In this case, the DBA or owner of the sequence can issue the drop sequence statement. Dropping the sequence renders its virtual columns CURRVAL and NEXTVAL unusable. However, if the sequence was being used to generate primary key values, the values generated by the sequence will continue to exist in the database. There is no cascading effect on the values generated by a sequence when the sequence is removed.

DROP SEQUENCE random_nums;


  1. How are sequences dropped?
  2. What are the effects of dropping a sequence?


In this section, you will cover the following topics related to creating views:

Data dictionary views
Creating simple and complex views
Creating views that enforce constraints
Modifying views
Removing views

It has been said that eyes are the windows to the soul. That statement may or may not be true. What is definitely true is that eyes can be used to view the data in a table. In order to make sure the right eyes see the right things, however, some special "windows" on the data in a table can be created. These special windows are called views. A view can be thought of as a virtual table. In reality, a view is nothing more than the results of a select statement stored in a memory structure that resembles a table. To the user utilizing the view, manipulating the data from the view seems identical to manipulating the data from a table. In some cases, it is even possible for the user to insert data into a view as though the view was a table. The relationship between tables and views is illustrated in Figure 4-1.

Table or Tables

View (distills data from table; masks complexity)

Fig04-01.jpg (14881 bytes)

Figure 1: Tables and views

Data Dictionary Views

The use of views in the data dictionary prevents the user from referring to the tables of the data dictionary directly. This additional safeguard is important for two reasons. First, it underscores the sensitivity of the tables that store dictionary data. If something happens to the tables that store dictionary data that should cause either data to be lost or the table to be removed, the effects could seriously damage the Oracle database, possibly rendering it completely unusable. Second, the dictionary views distill the information in the data dictionary into something highly understandable and useful. Those views divide information about the database into neat categories based on viewing scope and objects referred to.

Dictionary views are useful to draw data from the data dictionary. Some of the following examples illustrate selection of data from the data dictionary views that have already been identified in the previous chapter as ones containing information about the objects covered in this chapter:

SELECT * FROM all_sequences;
SELECT * FROM dba_objects;
SELECT * FROM user_tables;

Other dictionary views provide information about the views themselves. Recall that a view is simply the resultant dataset from a select statement, and that the data dictionary actually contains the select statement that creates the view. As shown, view definitions can be quite complex. There are several functions specified in the select statement that produce ALL_TABLES.

SET LONG 9999;
SELECT text FROM all_views WHERE view_name = ‘ALL_TABLES’;

t.pctfree$, t.pctused$,
t.initrans, t.maxtrans,
s.iniexts * ts.blocksize, s.extsize * ts.blocksize,
s.minexts, s.maxexts, s.extpct,
decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
decode(bitand(t.modified,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln,
lpad(decode(t.spare1, 0, '1', 1, 'DEFAULT', to_char(t.spare1)), 10),
lpad(decode(mod(t.spare2, 65536), 0, '1', 1, 'DEFAULT',
to_char(mod(t.spare2, 65536))), 10),
lpad(decode(floor(t.spare2 / 65536), 0, 'N', 1, 'Y', '?'), 5),
decode(bitand(t.modified, 6), 0, 'ENABLED', 'DISABLED')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co,$ t, sys.obj$ o
where o.owner# = u.user#
and o.obj# = t.obj#
and t.clu# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and (o.owner# = userenv('SCHEMAID')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol from x$kzsro))
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-50 /* DELETE ANY TABLE */)))


  1. Why are views used by Oracle in the data dictionary?
  2. What are two reasons for using views, both in the data dictionary and elsewhere?

Creating Simple and Complex Views

One example statement for creating a view has already been identified. To delve further into the requirements for creating views, the following discussion is offered. Creating a view is accomplished by using the create view statement. Once created, views are owned by the user who created them. They cannot be reassigned by the owner unless the owner has the create any view system privilege. More about privileges will be covered in a later section of this chapter.

There are different types of views that can be created in Oracle. The first type of view is a simple view. This type of view is created from the data in one table. Within the simple view, all single-row operations are permitted. In addition, data can be placed in specific order or into groups by the group by or order by clause of the select statement. The only option that is not allowed for a simple view is reference to more than one table. The following code block demonstrates creation of a simple view.

CREATE VIEW employee_view
AS (SELECT empid, lastname, firstname, salary
FROM employee
WHERE empid = 59495);

Users of a simple view can insert data in the underlying table of the view if the creator of the view allows them to do so. A few restrictions apply. First, the data that the user attempts to insert into an underlying table via the view must be data that the user would be able to select via the view if the data existed in the table already. However, updating or inserting data on rows or columns on a table that the view itself would not allow the user to see is only permitted if the with check option is not used. The following statement demonstrates data change via a view.

UPDATE employee_view
SET salary = 99000
WHERE empid = 59495;

The restrictions on inserting or updating data to an underlying table through a simple view are listed below:

The user may not insert, delete, or update data on the table underlying the simple view if the view itself is not able to select that data for the user if the with check option is used.
The user may not insert, delete, or update data on the table underlying the simple view if the select statement creating the view contains group by or order by, or a single-row operation.
No data may be inserted to simple views that contain references to any virtual column such as ROWID, CURRVAL, NEXTVAL, and ROWNUM.
No data may be inserted into simple views that are created with the read only option.

User will have problems inserting data into views if the underlying table has NOT NULL constraints on it. This can be eliminated with use of a default value for the NOT NULL column in the table definition.

Complex views have two major differences from simple views. Complex views draw data from more than one table in addition to possibly containing single-row operations and/or references to virtual columns. No data may be inserted, updated, or deleted from underlying tables for complex views under most circumstances. Complex views are excellent for hiding complicated data models and/or conversion operations behind a simple name for the user to reference the view. The complex view allows data to be joined from multiple tables in addition to all the features of simple views, such as using order by or group by in the select statement that creates the view.

CREATE VIEW employee_view
AS (SELECT e.empid empid, e.lastname lastname, e.firstname firstname,
e.salary salary, a.address,, a.state, a.zipcode
FROM employee e, employee_address a);

Complex views cannot allow data to be changed on the underlying table because of the join that is performed in order to obtain the result set displayed in the view. As such, it is not necessary for the creator of the view to specify the read only option on the view, as the view already is assumed to be read only.


  1. What is a simple view? How does it differ from a complex view?
  2. Which view allows the user to insert data into the view’s underlying table? Explain.

Creating Views that Enforce Constraints

Tables that underlie views often have constraints that limit the data that can be added to a table. Views have the same limitations placed on data that may enter the table. In addition, the view can define special constraints for data entry. The option used to configure view constraints is the with check option. These special constraints force the view to review the data changes made to see if the data being changed is data the view can select. If the data being changed will not be selected by the view, then the view will not let the user make the data change. The following view will now guarantee that any user who tries to insert data into EMPLOYEE_VIEW for an employee other than EMPID# 59495 will not be able to do so:

CREATE VIEW employee_view
AS (SELECT empid, lastname, firstname, salary
FROM employee
WHERE empid = 59495)


  1. How can constraints be created and enforced on views?
  2. On what principle does a view constraint operate?

Modifying Views

There may be situations where the creator of a view may need to change the view. However, views don’t follow the syntax conventions of other database objects. While there is an alter view statement in the Oracle SQL language, used to recompile or revalidate all references in the view as it exists already, the statement used to alter the definition of a view is the create or replace view statement. When a create or replace view statement is issued, Oracle will disregard the error that arises when it encounters the view that already exists with that name, overwriting the definition for the old view with the definition for the new. The following code block illustrates the use of the create or replace view statement.

AS (SELECT empid, lastname, firstname, salary
FROM employee
WHERE empid = user)

TIP: If a view should become invalid due to the redefinition or deletion of a table that underlies the view, the view will become invalid. To fix, the creator of the view must either re-create the underlying table and issue the alter view command, or modify the view with the create or replace view statement.

The invalidation of a view as a result of the removal of the underlying table illustrates an example of object dependency in the Oracle database. That is to say, certain objects in Oracle depend on others in order to work. Some examples of object dependency that have been presented so far are indexes depending on the existence of the corresponding tables and views depending on the existence of underlying tables.


  1. What statement is used to recompile or revalidate an existing view definition?
  2. What statement is used to alter the definition of a view?
  3. What is object dependency?

Removing Views

Like other database objects, there may come a time when the view creator needs to remove the view. The command for executing this function is the drop view statement. There are no cascading scenarios that the person dropping a view must be aware of. The following code block illustrates the use of drop view for deleting views from the database:

DROP VIEW employee_view;


  1. How are views dropped?


In this section, you will cover the following topics related to creating indexes:

Manual and automatic indexes
Uses for indexes
Index structure and operation
Creating indexes
Removing indexes
Guidelines for creating indexes

Indexes are synonymous with performance on the Oracle database. Especially on large tables, indexes are the difference between an application that drags its heels and an application that runs with efficiency. However, there are many performance considerations that must be weighed before making the decision to create an index. This discussion focuses on introducing the usage of indexes on the database. Some usage of indexes has already been presented with the discussion of constraints. However, the indexes that are created along with constraints are only the beginning. In Oracle7, indexes can be created on any column in a table except for columns of the LONG datatype. However, performance is not improved simply by throwing a few indexes on the table and forgetting about it. The following section will discuss the usage of indexes.

Manual and Automatic Indexes

So far, the indexes that have been presented have been ones that are created automatically via the primary key or unique constraints on tables. Those indexes are identified in the data dictionary in the DBA_INDEXES view. Their name corresponds to the name of the primary key or unique constraint that can be given if the creator of the table chooses to name indexes. Alternately, if the creator of the table chooses to use unnamed constraints (possible for unique constraints only), then the name given to the constraint and the index will be something akin to SYS_CXXXXX, where XXXXX is an integer. However, there are many more indexes that can exist on a database. These indexes are the manual indexes that are created when the table owner or the DBA issues the create index command to bring indexes into existence. Once created, there is little to distinguish an index that was created automatically by Oracle from an index that was created manually by the DBA.

The most commonly used way to distinguish automatic from manual indexes is through naming conventions. Take, for example, the table EMPLOYEE. The primary key constraint on this table might be named EMPLOYEE_PKEY_01, while an index created on some other column in the table might be called EMPLOYEE_INDX_01. In this fashion, it is easier for the DBA or creator of the database objects to distinguish which objects are which when selecting dictionary data.

Another way for the DBA to distinguish manually created indexes from automatically created ones is by looking at the actual columns in the index. The information about the columns in an index can be found in the DBA_CONS_COLUMNS data dictionary view. The columns in an index can give some indication as to whether the index was created automatically to someone who is familiar with the design of the database tables. Finding indexes automatically created for columns that have unique constraints can be trickier. It may require an in-depth knowledge of the application or an additional call to the DBA_CONSTRAINTS table to verify the name of the constraint generated automatically by Oracle, if not named explicitly by the creator of the table.


  1. What are some differences between manual and automatic indexes?
  2. How can the DBA distinguish between indexes created manually and those created automatically?

Uses for Indexes

Indexes have multiple uses on the Oracle database. Indexes can be used to ensure uniqueness on a database. Indexes also boost performance on searching for records in a table. This improvement in performance is gained when the search criteria for data in a table includes reference to the indexed column or columns. So far, all uses for indexes discussed involved unique indexes, where all the values in the column indexed are unique. However, data in this form is not required for creating an index of the table. Although the best performance improvement can be seen when a column containing all unique values has an index created on it, similar performance improvements can be made on columns containing some duplicate values or NULLS. However, there are some guidelines to ensure that the traditional index produces the performance improvements desired. The guidelines for evaluating performance improvements given by traditional indexes and whether it is worth the storage trade-off to create the index will be presented later in this discussion. Up to 16 columns in a table can be included in a single index on that table.


  1. Identify two reasons for using indexes.
  2. Must all the data in an index be unique? Explain.

Index Structure and Operation

When data in a column is indexed, a special structure is created that allows Oracle to search for values in that column quickly. This discussion will highlight the features of the index structure, explaining why it works and what works best with it. This discussion covers traditional indexes and bitmap options that are available in Oracle 7.3. The traditional index in the Oracle database is based on the principle governing a highly advanced algorithm for sorting data called a binary search tree, or a B-tree for short. A B-tree contains data placed in layered, branching order, from top to bottom, resembling an upside-down tree. The midpoint of the entire list is placed at the top of the "tree" and called the "root node." The midpoints of each half of the remaining two lists are placed at the next level, and so on, as illustrated in Figure 4-2.

It has been proven by computer scientists that this mechanism for searching data can produce a match for any given value from searching a list containing one million values in a maximum of 20 tries. By using a "divide and conquer" method for structuring and searching for data, the values of a column are only a few hops on the tree away, rather than several thousand sequential reads through the list away. However, B-tree indexes work best when there are many distinct values in the column, or when the column is unique. This algorithm works as follows:

Compare the given value to the value in the halfway point of the list. If the value at hand is greater, discard the lower half the list. If the value at hand is less, then discard the upper half.
Repeat the process on the half remaining until a value is found or the list exhausted.

Fig04-02.jpg (29503 bytes)

Figure 2: A B-tree index, displayed pictorially

Along with the data values of a column, the individual nodes of an index also store a piece of information about the column value’s row location on disk. This crucial piece of lookup data is called a "ROWID." The ROWID for the column value points Oracle directly to the location on disk in the table of the row corresponding to the column value. A ROWID consists of three components to identify the location on disk of a row—down to the row in the data block in the datafile on disk. With this information, Oracle can then find all the data associated with the row in the table.

TIP: The ROWID for a table is an address for the row on disk. With the ROWID, Oracle can search for the data on disk rapidly.

The other type of index available in Oracle is the bitmap index. The principle of a bitmap index is the use of a matrix, which has columns corresponding to all data values in the column. Thus, if the column contains only three distinct values, the bitmap index can be visualized as containing a column for the ROWID and three columns, one for each distinct value. Figure 4-3 displays a pictorial representation of a bitmap index containing three distinct values. The physical representation of the bitmap index is not far from the picture. Since each distinct value adds to the size of the index, bitmap indexes work best when there are few distinct values allowed for a column. Thus, the bitmap index improves performance in situations where B-tree indexes are not useful, and vice-versa.

Bit set in this column indicates row has this value set for the column

Fig04-03.jpg (23153 bytes)

Figure 3: A bitmap index, displayed pictorially


  1. What is a B-tree index? How does it work? In what situations does it improve performance?
  2. What is a bitmap index? How does it work? In what situations does it improve performance?

Creating Indexes

A DBA can create a unique index on a column manually using the create index statement. This process is the manual equivalent of creating a unique constraint or primary key on a table. Remember, unique indexes are created automatically in support of that task. The index created is a B-tree index. The create index statement used to create a unique index must contain the unique keyword. The DBA can index a column that contains NULL values as well, simply by eliminating the unique keyword. Creating a composite index with more columns named is possible as well. Finally, the DBA can create a bitmap index by substituting the unique keyword with the bitmap keyword.

-- unique indexes
CREATE UNIQUE INDEX employee_lastname_indx_01
ON employee (lastname);

-- nonunique indexes
CREATE INDEX employee_lastname_indx_01
ON employee (lastname);

-- composite indexes
CREATE UNIQUE INDEX employee_last_first_indx_01
ON employee (lastname, firstname);

-- bitmap indexes
CREATE BITMAP INDEX employee_last_first_indx_01
ON employee (empl_status);

Once created, there can be little altered about an index other than some storage parameters. In order to replace the definition of the index, the entire index must be dropped and re-created. Once the index is created, there are several different ways to find information about it. The DBA_INDEXES dictionary view displays storage information about the index, along with the name of the table to which the index is associated. The DBA_OBJECTS dictionary view displays object information about the index, including the index status. The DBA_IND_COLUMNS view displays information about the columns that are indexed on the database. This last view is especially useful for determining the order of columns in a composite index.


  1. What method is used to create a unique index? A nonunique index?
  2. How does the DBA create a bitmap index?
  3. In unique indexes containing more than one column, how do you think uniqueness is identified? Explain.

Removing Indexes

When an index is no longer needed in the database, the developer or DBA can remove it with the use of the drop index command. Once an index is dropped, it will no longer improve performance on searches using the column or columns contained in the index. No mention of that index will appear in the data dictionary any more, either. Additionally, if the index is used in relation to a primary key or unique constraint, then the index will no longer continue to enforce that uniqueness constraint. The syntax for the drop index statement is the same, regardless of the type of index being dropped. If the developer or DBA wishes to rework the index in any way, he or she must first drop the old index and then create the new one.

DROP INDEX employee_last_first_indx_01;


  1. How is a bitmap index dropped? How is a unique index dropped?
  2. What are the effects of dropping an index?

Guidelines for Creating Indexes

The usage of indexes for searching tables for information can provide incredible performance gains over searching tables using columns that are not indexed. However, care must be taken to choose the right index. Although a completely unique column is preferable for indexing using the B-tree structured index, a nonunique column will work almost as well if only about 10 percent of its rows have the same value. "Switch" or "flag" columns, such as ones for storing the sex of a person, are a bad idea for B-tree indexes. So are columns used to store a few "valid values," or columns that store a token value representing valid or invalid, active or inactive, yes or no, or any type of value such as these. Bitmap indexes are more appropriate for these types of columns.

TIP: The uniqueness of the values of a column is referred to as cardinality. Unique columns or columns that contain many distinct values have high cardinality, while columns with few distinct values have low cardinality. Use B-tree indexes for columns with high cardinality, bitmap indexes for columns with low cardinality.


  1. What is cardinality?
  2. When might the DBA use a B-tree index to improve performance? When might the DBA use a bitmap index to improve performance?

User Access Control

In this section, you will cover the following topics related to controlling user access:

The database security model
System privileges
Using roles to manage database access
Object privileges
Changing passwords
Granting and revoking object privileges
Using synonyms for database transparency

The most secure database is one with no users, but take away the users of a database and the whole point of creating a database is lost. In order to address the issues of security within Oracle, a careful balance of limiting access to the database and controlling what a user can see once connection is established is required. Oracle provides a means of doing so with its security model. The Oracle database security model consists of several options for limiting connect access to the database and controlling what a user can and cannot see once connection is established. This section will focus on the presentation of security on the Oracle database, from user creation to password administration to administering security on individual objects in the database.

Database Security Model

Oracle security consists of two parts. The first part of the Oracle database security model consists of password authentication for all users of the Oracle database. Password authentication is available either directly from the Oracle server or from the operating system supporting the Oracle database. When Oracle’s own authentication system is used, password information is stored in Oracle in an encrypted format.

The second part of the Oracle security model consists of controlling what database objects a user may access, the level of access a user may have to the object, and the authority to place new objects into the Oracle database. At a high level, these controls are referred to as privileges.

The key to giving database access is creating users. Users are created in Oracle with the create user command. Along with a password, several storage and database usage options are set up with the creation of a user. The following statement can be issued by a user with the create user privilege in Oracle to create new users:

CREATE USER athena IDENTIFIED BY greek#goddess

Security in the database is a serious matter. In most organizations, it is a set of functions handled either by the DBA or, more appropriately, by a security administrator. This person is the one with the final say over creating new users and determining the accessibility of objects in the database. As a general rule, the larger the organization is and the more sensitive the information, the more likely it is that security will be handled by a special security administrator. However, it is important that the DBA understand the options available in the Oracle security model.


  1. What are the two parts of database security?
  2. Who should manage database security such as user and password administration?

Granting System Privileges

System privileges grant the user the ability to create, modify, and eliminate the database objects in Oracle that store data for the application. In fact, in order to do anything in the Oracle database, the user must have a system privilege called connect. Within the scope of system privileges, there are two categories. The first is the set of system privileges that relate to object management. These objects include tables, indexes, triggers, sequences and views, packages, stored procedures, and functions. The three actions on objects managed by system privileges are defining or creating the object, altering definition, and dropping the object.

The other category of system privileges refers to the ability of a user to manage special system-wide activities. These activities include functions such as auditing database activity, generating statistics to support the cost-based optimizer, to setting up Oracle to allow access to the database only to users with a special system privilege called restricted session. These privileges should generally be granted only to the user or users on the database who will be performing high-level database administration tasks.

All granting of system privileges is managed with the grant command. In order to grant a system privilege, the grantor must either have the privilege granted to himself or herself with admin option, or the user must have grant any privilege granted to them. Granting a privilege with admin option signifies that the grantee may further grant or revoke the system privilege to any user on the database, with or without the with admin option. Users can create objects in their own schema with a system privilege such as create table. However, the user can create objects in any schema if the any keyword is added to the system privilege when it is granted, as in create any table.


Revoking system privileges is handled with the revoke command. In general, there are no cascading concerns related to revoking system privileges. For example, user ATHENA above created 17 tables with the create table privilege while she had it, and granted the create table privilege with and without the with admin option to several users as well. Another user revokes the privilege from her, along with the with admin option. The revocation of create table from user ATHENA would have no effect either on the tables she created or the users to which she granted the create table privilege.


  1. What is a system privilege? What abilities do system privileges manage?
  2. How are privileges granted and revoked?
  3. What does with admin option mean, and how is it used?

Using Roles to Manage Database Access

When databases get large, privileges can become unwieldy and hard to manage. DBAs can simplify the management of privileges with the use of a database object called a role. Roles act in two capacities in the database. First, the role can act as a focal point for grouping the privileges to execute certain tasks. The second capacity is to act as a "virtual user" of a database, to which all the object privileges required to execute a certain job function can be granted, such as data entry, manager review, batch processing, and others.

The amount of access to the objects of the database can be categorized using database roles to administrate the privileges that must be granted for database usage. In order to use roles, two activities must occur. The first is that the DBA must logically group certain privileges together, such as creating tables, indexes, triggers, and procedures. Using the privileges that are granted to a role can be protected with a password when a special clause, called identified by, is used in role creation.

CREATE ROLE create_procs IDENTIFIED BY creator;
GRANT create any procedure TO create_procs WITH ADMIN OPTION;

The second aspect of work the DBA must complete is logically grouping the users of a database application together according to similar needs. The most effective way to manage users is to identify the various types of users that will be using the database. The DBA determines the activities each type of user will carry out, and lists the privileges that each activity will require. These types or categories will determine the access privileges that will then be granted to roles on the database. The next step is to create roles that correspond to each activity, and to grant the privileges to the roles. Once this architecture of using roles as a "middle layer" for granting privileges is established, the administration of user privileges becomes very simply granting the appropriate role or roles to the users that need them.

CREATE ROLE empl_submt;

GRANT insert, select, update ON expense TO empl_submt;
GRANT select ON employee TO empl_submt;
GRANT empl_submt TO spanky;
GRANT empl_submt TO athena;
GRANT empl_submt TO dinah;

Roles can be altered to support the requirement of a password using the alter role identified by statement. Deleting a role is performed with the drop role statement. These two options may only be executed by those users with the create any role, alter any role, or drop any role privileges, or by the owner of the role. Privileges can be revoked from a role in the same way as they can be revoked from a user. When a role is dropped, the associated privileges are revoked from the user granted the role. Figure 4-4 show how privileges can be managed with roles.

Fig04-04.jpg (16580 bytes)

Figure 4: Using roles to manage privileges

In order to use the privileges granted to a user via a role, the role must be enabled for that user. In order for the role to be enabled, it must be the default role for the user, or one of the default roles. The status of a role is enabled usually, unless for some reason the role has been disabled. To change the status of a role for the user, the alter user default role statement following statement can be issued. Some of the keywords that can be used in conjunction with defining roles are all, all except, and none; these keywords limit the roles defined for the alter user statement.

ALTER USER spanky DEFAULT ROLE app_dev, sys_aly, unit_mgr;


  1. What is a role? How are privileges granted to a role?
  2. What is a default role? Can a user exercise privileges granted through a role if the role is disabled? Explain.

Granting Object Privileges

Once an object in the Oracle database has been created, it can be administered by either the creator of the table or by a user who has grant any privilege available to them. Administration of a database object consists of granting privileges that will allow users to manipulate the object by adding, changing, removing, or viewing data in the database object. Sometimes, object privileges are referred to by developers as SUDI (Select, Update, Delete, Insert) privileges. Other object privileges refer to the ability to refer to database objects, or to use them in some way that will not drop or change them in any way. These object privileges are references and execute. The references privilege allows the grantee of the privilege to create foreign key constraints on the referenced column of a table. The execute privilege allows the user to run a compiled stored procedure, package, or function. Other object privileges manage the alteration and creation of certain database objects. These include the alter table, alter sequence, and index table privileges.

The object privileges for any database object belong to that user and to users with appropriate any system privileges granted to them. Object privileges can be granted to other users for the purpose of allowing them to access and manipulate the object, or to administer the privileges to other users. The latter option is accomplished via a special parameter on the privilege called with grant option.


  1. What are object privileges? Name some of the object privileges?
  2. What option is used to grant an object privilege with the ability to grant the privilege further to others?

Changing Passwords

Once the user ID is created, the users can change their own passwords by issuing the following statement:

ALTER USER athena IDENTIFIED BY black#cat;


  1. How is the user password changed?

Granting and Revoking Object Privileges

All granting of object privileges is managed with the grant command. In order to grant an object privilege, the grantor must either have the privilege granted to himself or herself with the grant option, or the user must have grant any privilege granted to them, or the user must own the object. Granting an object privilege must be managed in the following way. First, the grantor of the privilege must determine the level of access a user requires on the table. Then, the privilege is granted. Granting object privileges can allow the grantee of the privilege the ability to administer a privilege as well when with grant option is used. Administrative ability over an object privilege includes the ability to grant the privilege or revoke it from anyone, as well as the ability to grant the object privilege to another user with administrative ability over the privilege.

GRANT select, update, insert ON employee TO howlett;
GRANT references ON employee.empid TO athena;
GRANT select, update, insert ON employee TO howlett WITH GRANT OPTION;

Revoking object privileges is handled with the revoke command. In general, there are no cascading concerns related to revoking object privileges. For example, user HOWLETT creates the EMPLOYEE table and inserts several rows in it. She then grants the select privilege along with the with grant option on the EMPLOYEE table to user ATHENA. User ATHENA then revokes the privilege from user HOWLETT, along with the with grant option. The revocation of these privileges from user HOWLETT would have no effect either on the data she created or on user ATHENA’s continued ability to use the privileges granted by user HOWLETT.

Using Synonyms for Database Transparency

Database objects are owned by the users who create them. The objects are available only in the user’s schema unless the user grants access to the objects explicitly to other users or to roles granted to other users. However, even when granted permission to use the object, the user must be aware of the boundaries created by schema ownership in order to access the data objects in Oracle. For example, assume the table EMPLOYEE table exists in user SPANKY’s schema, and user ATHENA attempts to access the table. Instead of returning the data associated with EMPID 96945, however, Oracle tells the user that the object does not exist. The reason this user could not see the table in the SPANKY schema is because user ATHENA did not refer to the table as being in the schema owned by SPANKY.

SELECT * FROM employee
WHERE empid = 96945;

SELECT * FROM employee
ORA-00942: table or view does not exist.

SELECT * FROM spanky.employee
WHERE empid = 96945;

If that extra piece of information seems to be unnecessary to remember, public synonyms may be used on the database. A synonym allows the users of the database to refer to the objects of a database with some word other than the official name of the object. For example, a synonym can be created on the EMPLOYEE table. After creating a synonym, user ATHENA can access the table by use of it.

-- Executed by SPANKY

-- Executed by ATHENA
SELECT * FROM employee
WHERE empid = 96945;

--------- ----------- ----------- ----------
96945     AHL         BARBARA      45000


  1. What is schema transparency?
  2. How are synonyms used to facilitate schema transparency?

Chapter Summary

This chapter covered several sections of required information for OCP Exam 1 related to the advanced creation of database objects. Some of the areas this chapter covered were altering tables and constraints, creating sequences, creating views, creating indexes, and controlling user access. The material in this chapter comprises about 17 percent of OCP Exam 1 and builds the foundation of discussion for managing the schema objects that will be presented in Unit II.

The first area of discussion for this chapter is the altering of tables and constraints. There are several activities a developer or DBA can do in order to alter tables and constraints. Some of these activities include adding columns or constraints, modifying the datatypes of columns, or removing constraints. Adding and modifying columns is accomplished with the alter table command, as are adding or modifying constraints on the table. There are several restricting factors on adding constraints, centering around the fact that adding a constraint to a column means that the data already in the column must conform to the constraint being placed upon it.

With respect to adding columns or changing the datatype of a column, there are some general rules to remember. It is easier to increase the size of a datatype for a column, and to add columns to the table. More difficult is changing the datatype of a column from one thing to another. It is not possible in Oracle7 to remove a column from the table. Generally, the column whose datatype is being altered must have NULL values for that column specified for all rows in the table. A table can be dropped with the drop table statement. Once dropped, all associated database objects like triggers and constraints, and indexes automatically created to support the constraints, are dropped as well. Indexes that were manually generated by the DBA to improve performance on the table will also be dropped.

There are several other tricks to table alteration. If the user wants to delete all data from a table but leave the definition of the table intact, the user can use the alter table truncate command. A database object can be renamed with use of the rename command. Alternatively, the DBA can create a synonym, which allows users to reference the database object using a different name. One final option offered to the DBA is to make notes in the database about objects by adding comments. Comments are added with the comment on statement.

Creation of sequences is another important area of advanced Oracle object creation. A sequence is an object that produces integers on demand according to rules that are defined for the sequence at sequence creation time. Some uses for a sequence include using a sequence to generate primary keys for a table or to generate random numbers. Creating a sequence is accomplished with the create sequence command in Oracle. To use a sequence, the user must reference two virtual columns in the sequence, known as CURRVAL and NEXTVAL. The CURRVAL column stores the current value generated by the sequence, while referencing NEXTVAL causes the sequence to generate a new number and replace the value in CURRVAL with that new number. Several rules can be used to govern how sequences generate their numbers. These rules include the first number the sequence should generate, how the sequence should increment, maximum and minimum values, whether values can be recycled, and others. Modifying the rules that govern sequence integer generation is accomplished with the alter sequence statement, while removal of the sequence is accomplished with the drop sequence statement.

Creating views is another area of database object creation covered in this chapter. Views are used to distill data from a table that may be inappropriate for use by some users. Other uses for views include the creation of views that mask the complexity of certain data (such as joins from multiple tables), data that has single-row operations performed on it, and other things. One common example of view usage is the data dictionary, which stores all data about the Oracle database in tables but disallows direct access to the tables in favor of providing views through which the user can select data. There are two categories of views, simple and complex. A simple view is one that draws data from only one table. A complex view is one that draws data from two or more tables. Simple views sometimes allow the user to insert, update, or delete data from the underlying table, while complex views never allow this to occur. A view can also have the option of enforcing a check on the data being inserted. This means that if the user tries to make a change, insertion, or deletion to the underlying table, the view will not allow it unless the view can then select the row being changed. Modifying the definition of a view requires dropping the old view and re-creating it or, alternately, creating the view again with the or replace option. The alter view statement is used for recompiling an existing view due to a problem with the object dependencies of the database. Removing a view from the database is done with the drop view statement.

Creating an index is another area covered in this chapter. There are several indexes created automatically to support enforcement of uniqueness constraints such as the primary key or the unique constraint. However, the DBA can also create nonunique indexes to support performance improvements on the database application. The traditional index consists of a binary search tree structure. The search algorithm supported by this structure operates by dividing a sorted list of elements in half and comparing the value at hand to the midpoint value, then searching the greater or lesser half depending on whether the value at hand is greater or less than the midpoint value. This process is repeated until the index values are exhausted or the value is found. Studies have shown that this algorithm can find a value from a list of one million elements in 20 or fewer tries. In order for a column to be indexed and used effectively using the B-tree index, the cardinality, or number of distinct values in the column, should be high. To change storage parameters about the index, the DBA can issue the alter index statement. To change the number of columns in an index, the index must be dropped and rebuilt. To drop an index, use the drop index statement.

Another index available in Oracle is the bitmap index. This index stores each ROWID in the table along with a series of bits, one for every distinct value in the column. The values that are not used in the column are set off, while the value that is present in the column is set on. Bitmap indexes work well for improving performance on columns with few distinct values.

Controlling user access on the database is the final area covered by this chapter. The Oracle database security model contains three major areas—user authentication, system privileges to control the creation of database objects, and object privileges to control usage of database objects. To change a password, the user can issue the alter user identified by statement, specifying the person’s username and the desired password. System privileges govern the creation of new database objects, such as tables, sequences, triggers, and views, as well as the execution of certain commands for analyzing and auditing database objects. Three general object maintenance activities are governed by system privileges, and they are the creation, change, and dropping of database objects. Object privileges govern access to an object once it is created, such as selects, updates, inserts, and deletes on tables, execution of packages or procedures, and reference of columns on tables for foreign key constraints.

In situations where there are many users and many privileges governing database usage, the management of privilege granting to users can be improved using roles. Roles act as "virtual users" of the database system. The DBA first defines the privileges a user may need, groups them logically by function or job description, then creates an appropriate role. Privileges to support the function or the job description are then granted to the role, and the role is granted to the user. Roles help to alleviate the necessity of granting several privileges each time a user is added to an application.

Finally, the use of synonyms for data transparency is discussed. Database objects are owned by users and accessible to their schema only, unless permission is explicitly granted by the owner to another user to view the data in the table. Even then, the schema owning the object must be referenced in the statement the user issues to reference the object. Public synonyms can eliminate that requirement, making the schema ownership of the database object transparent. A public synonym is created with the create public synonym statement.

Two-Minute Drill

Adding or modifying a table column is done with the alter table statement.
Columns can be added with little difficulty, if they will be nullable, using the alter table add column_name statement. If a NOT NULL constraint is desired, add the column, populate the column with data, and then add the NOT NULL constraint separately.
Column datatype size can be increased with no difficulty using the alter table (modify column_name datatype) statement. Column size can be decreased or the datatype can be changed only if the column contains NULL for all rows.
Constraints can be added to a column only if the column already contains values that will not violate the added constraint.
Adding a constraint is accomplished with the alter table add constraint_name statement.
Dropping a constraint is accomplished with the alter table drop constraint_name statement.
If a constraint is dropped that created an index automatically (primary keys and unique constraints), then the corresponding index is also dropped.
If the table is dropped, all constraints, triggers, and indexes automatically created for the table are also dropped.
If a table is dropped, all manually created indexes will still exist, but are rendered invalid because of object dependency.
Removing all data from a table is best accomplished with the alter table truncate command rather than the delete from table_name statement because alter table truncate will reset the table’s high watermark, improving performance on select count() statements issued after the truncation.
An object name can be changed with the rename statement or with the use of synonyms.
A comment can be added to the data dictionary for a database object with the comment on command. The comment can subsequently be viewed in DBA_TAB_COMMENTS or DBA_COL_COMMENTS.
A sequence generates integers based on rules that are defined by sequence creation.
Parameters that can be defined for sequences are the first number generated, how the sequence increments, the maximum value, the minimum value, whether the sequence can recycle numbers, and whether numbers will be cached for improved performance.
Sequences are used by selecting from the CURRVAL and NEXTVAL virtual columns.
The CURRVAL column contains the current value of the sequence.
Selecting from NEXTVAL increments the sequence and changes the value of CURRVAL to whatever is produced by NEXTVAL.
Modifying the rules that a sequence uses to generate values is accomplished using the alter sequence statement.
Deleting the sequence is accomplished with the drop sequence statement.
A view is a virtual table defined by a select statement.
A view is similar to a table in that it contains rows and columns, but different because the view actually stores no data.
Views can distill data from tables that may be inappropriate for some users, or hide complexity of data from several tables or on which many operations have been performed.
There are two types of views—simple and complex.
Simple views are those that have only one underlying table.
Complex views are those with two or more underlying tables that have been joined together.
Data cannot be inserted into complex views, but may be inserted into simple views in some cases.
The with check option clause on administering any object privilege allows the simple view to limit the data that can be inserted or otherwise changed on the underlying table by requiring that the data change be selectable by the view.
Modifying the data selected by a view requires re-creating the view with the create or replace view statement, or dropping the view first and issuing the create view statement.
An existing view can be recompiled if for some reason it becomes invalid due to object dependency by executing the alter view statement.
A view is dropped with the drop view statement.
Some indexes in a database are created automatically, such as those supporting the primary key and the unique constraints on a table.
Other indexes are created manually to support database performance improvements.
Indexes created manually are often on nonunique columns.
B-tree indexes work best on columns that have high cardinality, or a large number of distinct values and few duplicates in the column.
B-tree indexes improve performance by storing data in a binary search tree, then searching for values in the tree using a "divide and conquer" methodology outlined in the chapter.
Bitmap indexes improve performance on columns with low cardinality, or few distinct values and many duplicates on the column.
Columns stored in the index can be changed only by dropping and re-creating the index.
Deleting an index is accomplished by issuing the drop index statement.
The Oracle database security model consists of two parts—limiting user access with password authentication and controlling object usage with privileges.
Available privileges in Oracle include system privileges for maintaining database objects and object privileges for accessing and manipulating data in database objects.
Changing a password can be performed by a user with the alter user identified by statement.
Granting system and object privileges is accomplished with the grant command.
Taking away system and object privileges is accomplished with the revoke command.
Creating a synonym to make schema transparency is accomplished with the create public synonym command.