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, you 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 comprises 17 percent of the material covered on the exam.

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 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 can do one of two things when a request to add some columns to a table comes in. One is to add the columns, 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.

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 Oracle. 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 need 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 empty.

Only one column in the table may be of type LONG within a table. That restriction includes the LONG RAW datatype. However, many columns of datatype BLOB, CLOB, NCLOB, and BFILE can appear in one table as of Oracle8. It is sometimes useful to emulate Oracle8 in Oracle7 databases by having 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.

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. A developer 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, you 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 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, a utility provided with Oracle for loading data into tables from flat files. 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)

There are several different changes that can be made to constraints. These changes include altering the constraint and disabling, enabling, or removing the constraint from the column or table of the database. These processes allow the developer to create, modify, or remove the business rules that constrain data. The first activity that a 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
MODIFY (color NOT NULL);

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

ALTER TABLE products
ADD (CONSTRAINT fk_products _02 FOREIGN KEY (color)
REFERENCES (AVAIL_COLORS.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 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 here:

• Primary keys Columns cannot be NULL and must have all unique values.
• Foreign keys Referenced columns in other tables must contain values corresponding to all values in the referring columns or the referring columns values must be NULL.
• Unique constraints Columns 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 Columns cannot be NULL.

If any of the conditions for the constraints just listed are not met for the respective constraint to which the rule applies, then creation of the constraint will fail.

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 you have 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, you may want to take steps to put the constraints back in order again.

ALTER TABLE products
ENABLE CONSTRAINT pk_products_01;

ALTER TABLE products
ENABLE CONSTRAINT uk_products_03;

Note that in this situation, only constraints that have been defined and are currently disabled can be enabled by this code. A constraint that has not been created cannot be enabled. As just discussed, a constraint that fails on creation will automatically be disabled. There are situations where you 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, your attempt to enable the constraint later with the alter table TABLE_NAME enable constraint exceptions into EXCEPTIONS statement will fail. The following error messages may be seen when this situation arises:

/* Values in the column do not satisfy the constraint*/
ORA-02296: cannot enable constraint table_name.constraint_name - found non-complying values

/* Constraint enabling failed because there are orphan records in a foreign key */
ORA-02298: cannot enable constraint table_name.constraint_name - parent keys not found

/* Constraint enabling failed because values in the column are not unique. */
ORA-02299: cannot enable constraint table_name.constraint_name - duplicate keys found

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. The following code block demonstrates some sample statements for your understanding:

ALTER TABLE products
DISABLE PRIMARY KEY;

ALTER TABLE products
DISABLE UNIQUE (serial#);

Furthermore, you may have a problem when you attempt to disable a primary key when foreign keys exist that depend on that primary key. The following error will ensue in this situation:

ORA-02297: Cannot disable constraint table_name.constraint_name - dependencies exist

If you try to drop a primary key when there are dependent foreign keys depending on it, the cascade option is required as part of the alter table disable constraint, as shown in the following code block:

ALTER TABLE products
DISABLE PRIMARY KEY CASCADE;

Usually, there is nothing about a constraint that will interfere with your 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. 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
DROP PRIMARY KEY CASCADE;

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;

However, dropping tables may not always be that easy. Recall from the earlier lesson that when you disable constraints like primary keys that have foreign-key constraints in other tables depending on their existence, you may have some errors. The same thing happens when you try to drop the table that has a primary key referenced by enabled foreign keys in another table. If you try to drop a table that has other tables’ foreign keys referring to it, the following error will ensue:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

When there are foreign-key constraints on other tables that reference the table to be dropped, then you 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
CASCADE CONSTRAINTS;

Alternately, you can disable or drop the foreign key in the other table first using syntax explained in the previous lesson, and then issue the drop table statement without the cascade constraints option. However, with this method you run the risk that many other tables having foreign keys that relate back to the primary key in the table you want to drop will each error out, one at a time, until you disable or drop every foreign-key constraint referring to the table. If there are several, your drop table activity may get extremely frustrating.

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 statement. The 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.

TRUNCATE TABLE products;

Changing object names in Oracle is accomplished using the rename command. This command allows you to change the name of one table to another by taking data from one table and automatically moving it to another that is called something else. The following code block demonstrates use of this command:

RENAME products TO objects;

The effect of renaming a table can be duplicated through the use of synonyms. A synonym gives users an alternate name with which they can refer to the existing table. 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. If the synonym is private, it will only be accessed by the user who owns it. If a synonym is public, it will be accessible by any user in the database. The following code block demonstrates the statements used to create private and public synonyms, respectively:

CREATE SYNONYM objects FOR products;
CREATE PUBLIC SYNONYM objects FOR products;

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. More object information that can be 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, as demonstrated in the following code block. 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';

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

• Role 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.

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 are rendered somewhat meaningless. But, if you don’t care that you’re creating a nonsense key, it is perfectly alright to do so.

Sequences operate on the following principle. Users select data from them using two special keywords to denote virtual columns or pseudocolumns in the database. The first pseudocolumn is CURRVAL. This column can be used to see what the current value generated by the sequence is. The second pseudocolumn 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 pseudocolumns 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, possibly arising from self-referencing integrity constraint violations. 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.

Many rules are available on sequences that allow the developer 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. Default for this clause is 1 if not explicitly specified.
• minvalue n Defines the minimum value that can be produced by the sequence. If no minimum value is desired or not explicitly specified, the default nominvalue keyword can or will be used.
• maxvalue n Defines the maximum value that can be produced by the sequence. If no maximum value is desired or not explicitly specified, the nomaxvalue keyword can or will be used.
• cycle Allows the sequence to recycle values produced when the maxvalue or minvalue is reached. If recycling is not desired or not explicitly specified, the nocycle keyword can or will be used.
• cache n Allows the sequence to cache a specified number of values to improve performance. If caching is not desired or not explicitly specified, the nocache keyword can or will be used.
• order Allows the sequence to assign values in the same order requests are received by the sequence. If order is not desired or not explicitly specified, the noorder keyword can or will be used.

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
START WITH 20
INCREMENT BY 1
NOMAXVALUE
CYCLE
ORDER;

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 next code block illustrates a sequence that generates numbers between zero and 1,000 without repeating a sequence value during the life of the sequence:

CREATE SEQUENCE some_num
MINVALUE 0
MAXVALUE 1000
NOCYCLE;

Once the sequence is created, it is referenced using the CURRVAL and NEXTVAL pseudocolumns. 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 some_num.currval CURRENT,
some_num.nextval NEXT,
some_num.currval CURRENT
FROM dual;

CURRENT NEXT CURRENT
------- ---- -------
1 2 2

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 here illustrate usage of sequences directly in changes made to tables:

INSERT INTO expense(expense_no, empid, amt, submit_date)
VALUES(some_nums.nextval, 59495, 456.34, '21-NOV-99');

UPDATE product
SET product_num = some_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 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.

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, you 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, you 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 issuing the alter sequence statement.

ALTER SEQUENCE countdown_20
INCREMENT BY 4;

The effect is immediate—the statement will change the COUNTDOWN_20 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
NOCYCLE;

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

ALTER SEQUENCE some_nums
MAXVALUE 10000;

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.

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 some_num;

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.

The use of views in the data dictionary prevents you 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 the 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. Review the following code blocks:

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 below, view definitions can be quite complex. There are several functions specified in the select statement that produce ALL_TABLES. Don’t worry if you don’t understand the structure of this view, you won’t need to know what the meanings of these columns are for OCP Exam 1.

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

TEXT
-----------------------------------------------------------------------------
select u.name, o.name, ts.name, co.name,
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, sys.tab$ 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 */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)))

One example statement for creating a view has already been identified—the one for creating the ALL_VIEWS dictionary view in the Oracle database. Again, though, don’t worry about understanding the minutiae of creating every Oracle data dictionary view. The most important things to remember about views can be summarized by the following bullets:

• Views add extra security to data, such as the definition of a view on the EMP_SALARY table that only shows salary information for the user performing the select against the view.
• Views hide data complexity, as in the situation of complex views (covered shortly).
• Views can hide real column names that may be hard to understand behind easier names.

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 by the order by clause of the select statement. Options not allowed for a simple view include reference to more than one table via a table join, no grouping or set operations, no group by clauses, no hierarchical queries (those queries containing a connect by clause), and no queries with the distinct keyword. 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 here:

• 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.

Users 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 some 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. Complex views can contain group by clauses. However, 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 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.city, a.state, a.zipcode
FROM employee e, employee_address a
WHERE e.empid = a.empid);

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.

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)
WITH CHECK OPTION;

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:

CREATE OR REPLACE VIEW employee_view
AS (SELECT empid, lastname, firstname, salary
FROM employee
WHERE empid = user)
WITH CHECK OPTION;

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.

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;

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.

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. Alternatively, 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 developer 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.

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 developer 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 ALL_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 ALL_CONSTRAINTS table to verify the name of the constraint generated automatically by Oracle, if not named explicitly by the creator of the table.

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 a 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.

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. The traditional index in the Oracle database is based on the principle governing a highly advanced algorithm for sorting data called a B-tree. A B-tree contains data placed in layered, branching order, from top to bottom, resembling an upside-down tree. Consider the following, slightly simplified explanation of how a traditional index works in Oracle, based on an object known in computing as a binary search 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, traditional indexes work best when there are many distinct values in the column, or when the column is unique. The binary search tree sees success in the same way. Its 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.

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.

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 traditional indexes are not useful, and vice-versa.

You 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. You 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, you 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_status_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 ALL_INDEXES dictionary view displays storage information about the index, along with the name of the table to which the index is associated. The ALL_OBJECTS dictionary view displays object information about the index, including the index status. The ALL_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.

When an index is no longer needed in the database, the developer 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 you wish to rework the index in any way, you must first drop the old index and then create the new one.

DROP INDEX employee_last_first_indx_01;

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 types of values such as these. Bitmap indexes are more appropriate for these types of columns.

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

• Oracle 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.

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 developers, DBAs, and users all understand the options available in the Oracle security model for the version of Oracle the organization uses.

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 create session. 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, and 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 herself with admin option, or she 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.

GRANT CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE TO spanky;
GRANT CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE SEQUENCE TO athena;
GRANT CREATE TABLE TO athena WITH ADMIN OPTION;

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 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.

When databases get large, privileges can become unwieldy and hard to manage. You 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 you 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 you 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. You determine the activities each type of user will carry out, and list 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 ofc_developer;

GRANT CREATE TABLE TO ofc_developer;
GRANT SELECT ANY TABLE TO ofc_developer;
GRANT DROP USER TO ofc_developer;

GRANT ofc_developer TO athena;
GRANT ofc_developer TO spanky;

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 shows how privileges can be managed with roles.

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 usually enabled, 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 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 ALL;
ALTER USER spanky DEFAULT ROLE ALL EXCEPT sysdba;
ALTER USER spanky DEFAULT ROLE app_dev, sys_aly, unit_mgr;
ALTER USER spanky DEFAULT ROLE NONE;

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 the grant any privilege system 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.

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

ALTER USER athena IDENTIFIED BY blackcat;

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 herself with the grant option, or she must have grant any privilege granted to her, or the she 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 other than the removal of a user’s ability to use the privilege. 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. User HOWLETT, however, will no longer be able to access data as she once could before the object privilege was revoked.

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 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, synonyms may be used on the database. A synonym allows the users of the database to refer to the objects of a database without prefixing the name of the owner of the object. A public synonym can be created by a privileged user to allow other users in the database to access a particular table without having to prefix the schema name to the table reference. 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
CREATE PUBLIC SYNONYM employee FOR spanky.employee;

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

EMPID LASTNAME FIRSTNAME SALARY
--------- ----------- ----------- ----------
96945 AHL BARBARA 45000

Another type of synonym is the "private synonym." This is a synonym you can create for yourself that allows only you to refer to a table in another schema by the table name only. No other user can access the table via your private synonym, they must create their own. The following code block illustrates ATHENA’s use of private synonyms to achieve the same result as before:

-- Executed by ATHENA
CREATE SYNONYM employee FOR spanky.employee;

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

EMPID LASTNAME FIRSTNAME SALARY
--------- ----------- ----------- ----------
96945 AHL BARBARA 45000

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.

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. 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.

The chapter also covered several other tricks to table alteration. If you want to delete all data from a table but leave the definition of the table intact, you can use the truncate command. Alternately, the delete from table_name command can be used, but on large tables you may see a noticeable difference in performance between these two commands. Also, you cannot issue the rollback statement after a truncate and get all your data back the way you can after a delete statement. A database object can be renamed with use of the rename command. Alternatively, you can create a synonym, which allows users to reference the database object using a different name. One final option offered to you 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, you must reference two pseudocolumns 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. There are some other differences between simple and complex views covered in the chapter, and you should be sure you understand those differences before taking OCP Exam 1. A view can also have the option of enforcing a check on the data being inserted. This means that if you try 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, alternatively, 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 B-tree structure. The search algorithm supported by this structure is similar to a binary search tree, the operation of which was covered in the chapter. 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 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, and you should understand its usage before taking OCP Exam 1. 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 select, update, insert, and delete statements 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. You first define the privileges a user may need, group them logically by function or job description, then create 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.