Oracle Database Administration

The first GRANT you have to give a NEW user is permission to CONNECT.

GRANT CREATE SESSION  TO jones;

The SQL command to permit users to retrieve data includes the following:

GRANT SELECT ON customers TO jones;

The SQL statement to add data includes the user names, separated by a comma, as follows:

GRANT INSERT ON products TO SMITH, BROWN;

The SQL statement to update data Includes the table name, followed by the column name(s) to update in parentheses, as follows:

GRANT UPDATE ON customers (LAST_NAME, FIRST_NAME, PHONE) TO ANDERSON;

The SQL statement to delete rows is as follows:

GRANT DELETE ON purchases TO MARTIN;

The SQL statement to indicate that all users have the privilege to retrieve data includes the special word PUBLIC, as follows:

GRANT SELECT ON products (product_id, description, product_type_id) TO PUBLIC;

We will discuss indexes and their uses in the next section. This example illustrates how to grant a user the ability to create an index. The SQL statement to create an index is as follows:

GRANT INDEX ON customers TO ROBERTS;

The SQL statement to change a table's structure is as follows-

GRANT ALTER ON customers TO THOMAS;

The SQL statement to indicate that a user has all privileges includes the use of the ALL privilege, as follows:  

GRANT ALL ON employees, customers, purchases TO wilson

The privileges that can be granted are SELECT to retrieve data, UPDATE to
change data, DELETE to delete data, INSERT to add new data, INDEX to create an
index, and ALTER to change the table structure.

Privileges usually are assigned by the database administrator. Normally, when

the database administrator grants a particular privilege to a user, the user cannot pass that privilege along to other users. If the user needs to be able to pass the privilege to another user, the GRANT statement must include the WITH GRANT OPTION clause. This clause grants the indicated privilege to the user and also permits to other users. 

Any privelege granted can be revoked later by using the REVOKE command.  Syntax for the revoke command is essentially the same as for GRANT

The SQL command to revoke a privelege is as follows:

REVOKE SELECT ON sales_rep FROM jones;

USERS/PROFILES (CREATE, ALTER, AND DROP):
One of the activities of a DBA is to create user accounts.  The DBA must also be able to give users access to the various database objects and grant users privileges to perform different operations.  

First Create a profile.  The profile allows for the following control of the end user?s session:  

      -         Session idle time.
-         Session connect time
-         Number of sessions that the user can have.
-         Amount of CPU resources the end user can use.  
-         Password retry attemps
-         How frequently user has to change password
-         Password reuse policy

A profile is created by issuing the command:

CREATE PROFILE user
SESSIONS_PER_USER 1
IDLE_TIME 20
CONNECT_TIME 600;

A profile can be altered or dropped with the usual commands.

SQL> DROP PROFILE user;
SQL> ALTER PROFILE user IDLE_TIME 100;

A user can be dropped or altered as well:
SQL> ALTER USER scott IDENTIFIED BY new-password;
SQL> DROP USER scott;

Second or Third, the User Account must be created, if it doesn?t already exist.  The SQL command for creating a user account is CREATE USER.  

CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE user
TEMPORARY TABLESPACE temp
PROFILE user
QUOTA 15M ON USER;

In the above example, we are creating the user ?scott? and assigning him the password ?tiger?.  We are also assigning the user to a DEFAULT TABLESPACE, this means that whenever user scott creates a object, the object will be created in the tablespace called ?user?.

The user scott is assigned to a default tablespace.  When the user scott sorts his data (issues a SQL select command with the SORT keyword) the data will be sorted in the tablespace called temp. 

The user scott is also assigned a quota on the default tablespace.  This means that the user scott can create as many tables or views as he wants as long as he does not exceed his 15M quota.  The user scott is also given a profile.  

At the very least all users will need the CREATE SESSION privilege just to sign on to Oracle.

ROLES & GROUPS:  It can be very tedious assigning all the necessary permissions to each user.  Oracle allows you to create a group of privileges call roles.

The benefits of roles are:

• Faster, easier, less error prone to group users and give them all the same privileges.

• All changes to roles are propagated automatically to the users within that role.

• Multiple roles can be assigned to users and/or roles.

• Passwords can be assigned to roles.

CREATE ROLE hr_manager;

GRANT SELECT, INSERT, UPDATE, DELETE ON salary_grades TO hr_manager;

GRANT hr_manager TO bob;

System Tables Related to Security:

• user_sys_privs

• user_tab_privs_made

• user_col_privs_made

• user_tab_privs_recd

• user_col_privs_recd

• user_role_privs

• role_sys_privs

• role_tab_privs

Oracle Database vs. Oracle Instance:  The Oracle database is made up of the physical files where data is stored.  An Oracle Instance is an instance of a database in memory and the supporting processes for that instance of the database.

An oracle instance can be started from either a Unix or DOS prompt by typing the following:

$ sqlplus

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> connect internal
Connected.
SQL> startup               -- To shutdown there server you would type shutdown

Multiple Instances/Starting and Stopping Instances:  You can have multiple instances running at a given time.  Each instance must have it's own ORACLE_SID and init<SID>.ora file.   For example if you had a database instance for sales, the SID might be called sales and the init<SID>.ora file would be called initsales.ora.  The init<SID>.ora file contains many startup parameters that we will talk more about later.   By default Oracle will start up it's default instance.  This is the instance denoted by the environmental variable $ORACLE_SID.  At the Unix $ prompt type:

 $ echo $ORACLE_SID
ggu8

 this will tell you which Instance is currently the default.  To change the default ORACLE_SID you must change this environmental variable by typing something like the following at the Unix prompt:

$ export ORACLE_SID=sales

The init<SID>.ora file is stored in the following location: $ORACLE_HOME/dbs/init<SID>.ora.  for example ggu8 would be: $ORACLE_HOME/dbs/initggu8i.ora.  Try the following.

$ echo $ORACLE_HOME
/oracle8i/app/oracle/product/8.1.7

$ vi $ORACLE_HOME/dbs/initggu8i.ora

If the ggu8 is the current instance and you want to startup the sales instance, do the following:

$ export ORACLE_SID=sales
$sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 3 17:06:22 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> connect internal
Connected.
SQL> startup               -- To shutdown there server you would type shutdown

You have now changed the default ORACLE_SID to sales and started up this database, now two Oracle Instances are running.

CREATING THE DATABASE:  Database's are created using the CREATE DATABASE command.  This will automatically take a copy of the default init.ora file and create an init<NewDB>.ora file.  For example if we issue the command CREATE DATABASE dw an initdw.ora file will be created for us. 

ORACLE ARCHITECTURE:
The Oracle architecture is made up of physical files (data, logfiles and archives), System Global Area (SGA) - this shared memory area, and a variety of processes that access both the SGA and Physical Files.  The various process usually move data and log information from the various cache's in the SGA to Physical files and monitor the Oracle instance.

PHYSICAL FILES:
1.  Database files:  This is were the actually data is stored.  It is made up of tablespaces which in turn is made up of Segments, Extents, and Blocks.  Database ==> Tablespaces ==> Segments ==> Extents ==> Blocks.  All databases must at least have a system tablespace.  This tablespace is contains the Oracle data-dictionary.  A database should also contain at least one other tablespace for the application.  It is not wise to put application tables on the System tablespace.  

2.  Redo Log Files:  These files contain log information of changes to the database objects, in case of failure they can be used to recover the database.

3.  Control Files:  This file is where Oracle keeps track of other files.

4.  Init<SID>.ora:  This is the parameter initialization file for each Oracle instance.

SYSTEM GLOBAL AREA (SGA):
This is the shared memory area that is accessed by Oracle processes which in turn updates Oracle files.  The SGA is made up of the following Cache areas:

1.  Database Buffer Cache:  This is where data from recent and/or commonly used queries are stored.  Each DB Buffer Cache can store up to one Oracle Block.  The size of the block is determined by the DB_BLOCK_SIZE which is stored in the init<SID>.ora file.  The primary process that interacts with this Cache is the DBWR (see below).  What block is to be kept in this area is determined by the LRU (Least Recently Used).   Each time a block accessed it is moved to the beginning of the list, meaning recently used.  There are special ways of handling full table scans other than the LRU.

2.  Redo Log Buffer Cache:  Oracle holds Redo Log changes in Cache until it is effecient to write these changes out to disk.  The process that does this is the Log Writer (LGWR).  This is triggered whenever a commit is issued or the buffer hits 1/3 of capacity as determined by the init<SID>.ora parameter: LOG_BUFFER.

3. Shared Pool:  SQL and PL/SQL commands are stored in the Library Cache.  The data-dictionary is stored in the Dictionary Cache. 

Some optional memory areas are:

1.  Large Pool:  Used for large memory request defined by the LARGE_POOL_SIZE in init<SID>.ora

2.  Program Global Area (PGA):  A private area for each user.

PROCESS ARCHITECTURE:
- Required Processes:
1.  Database Writer (DBWR): Writes modified data from the SGA to the data files. 

2.  Log Writer (LGWR):  Writes redo log files every time a commit is issued.

3.  Process Monitor (PMON):  Monitors and cleans up after users that improperly disconnected.

4.  Instance Monitor (SMON):  Monitors the instance to ensure integrity during startup.

5.  Check Point (CKPT):  Was optional prior to 8i, but is now required.   Works with the Log Writer to write data that has been check pointed.

- Optional Processes:
1.  Archiver Process (ARCn):  Although optional it is highly recommended since hot backup are not possible without it.  It is controlled by the init<SID>.ora parameter LOG_ARCHIVE_MAX_PROCESSES.

2.  Recovery Process (RECO):  Used only for distributed Architectures which we will cover in Chapter 12.

3.  Wake Up Monitor (WMON):  Used only on some operating systems to wake-up Oracle Background processes. 

4.  Snapshot Process (SNPn):  Used to support replication and scheduled jobs with the DBMS_JOB package.

5.  Lock Processes (LCKn): Used by the Oracle Parallel Server.

The tablespace is the fundamental unit of work in Oracle.  The best example of a Segment is a Table and Indexes.  So you can think of Databases as having one or more Tablespaces and Tablespaces as having one or more Table.  It is also possible to have read-only tablespaces which can make alot of sense for data-warehouses which are often read-only.  The main advantage to this is that these tablespaces do not need to be backed up. 

Partitioned Tables:  Allow you to dived large tables by some criteria.  In the case of data-warehouses this is usually date.  This is particularly helpful with DW tables because they tend to be large and organized by date.  These partitions can be dived upon multiple tablespaces, so it is possible for not only tablespaces to have multiple tables but for tables to span multiple tablespaces. 

Temporary tables are tables that are created by users temporarily and are automatically dropped after either a commit (default) or after a session is ended. 

CREATE TABLE:

CREATE [GLOBAL TEMPORARY] TABLE table_name (
        column_name TYPE [CONSTRAINTS constraints... DEFAULT default_exp]
        [,column_name TYPE [CONSTRAINTS constraints... DEFAULT default_exp]]
[ON COMMIT {DELETE | PRESERVE} ROWS]
[TABLESPACE tab_space];

System tables: user_tables, user_tab_columns

CREATE SEQUENCE:

?        Sequences Generate Unique Numbers.

?        Not an ANSI standard object.

?        Reference in SQL wherever a unique number is needed.

?        SEQ_NAME.NEXTVAL gets next number.

?        SEQ_NAME.CURRVAL gets last number used in current session.

Syntax:

CREATE SEQUENCE sequence_name  
START WITH n  
INCREMENT BY n  
MINVALUE n  
MAXVALUE n  
CACHE  

Example:

CREATE SEQUENCE customers_seq  
START WITH 100  
INCREMENT BY 1  
CACHE 25

INSERT INTO customers (customer_id, last_name, first_name)  
         VALUES (customers_seq.NEXTVAL, 'Martin', 'Elyse');  

CONSTRAINTS:  
Constraints define the conditions under which that data is valid.  Typical constraints include NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and FOREIGN KEY.

We have already talked about NOT NULL, and PRIMARY KEY, here is some information about UNIQUE, FOREIGN KEY, and CHECK.  

?        Unique key constraint: Identified in Oracle by qualifying the constraint as UNIQUE. (not UNIQUE KEY) Unique keys are similar to primary keys in that they enforce the rule such that every row in a table must have a unique combination of values for the column(s) that make up the unique key and they can also be used for referential integrity.  The difference between a unique key and a primary key is that the unique key can be null.  If you make a Unique key NOT NULL, you in effect can create a second or third primary key.  Use unique key constraints on tables that have surrogate primary keys to enforce true uniqueness.

?        Foreign key constraint. Identified in Oracle by qualifying the constraint as FOREIGN KEY.

A foreign key is a column or combination of columns whose values tie a row of a table to another table.   Foreign keys are used  relate child tables to parent tables.  Table relationships can be either optional or mandatory.  To enforce a mandatory relationship,  you must ensure that you add the NOT NULL constraint to each column participating in the foreign key of a table.  Foreign keys enforce referential integrity.  That is they ensure that orphans never exist in your database.  For example,  if you have two tables:  sales-rep and customer  and there is a relationship between them such that an instance of a sales-rep advises one or more instances of a customer,  then there will never be a customer who is advised by a sales-rep that does not exist in the sales-rep table.  Foreign key columns always have their counterparts in the parent table which the reference.  In the parent table the counterpart columns always compose the primary key of the parent table.  

For example:   In the relationship described above,  a column called slsrep_number would exist in the customer table as well as in the sales-rep table.  In the Customer Table, slsrep_number would be the foreign key.  In the sales_rep Table slsrep_number would be the primary key.

Foreign key constraints prevent the following (using our example)
?        User entering an a customer row that references a sales-rep that does not exist.
?        User deleting a sales-rep who advises customer.  When this error occurs,  the user is forced to either delete the customer or set the slsrep_number to null before he can delete the sales-rep.
?        Database administrator attempts to remove the primary key constraint on the sales_rep table before first removing the foreign key constraint on the customer table.  
?        Database administrator attempts to drop the sales-rep table before dropping the customer table.

If you stop and thing about the above, it will be clear that preventing these scenarios always ensure that no orphans will exists as table rows in the customer table.

 ?        CHECK Constraints.

Check constraints are rules you define regarding the enforcement of  a certain value condition for columns of a table be true for any given row updated/inserted in a table.  Check constraints are always row specific and cannot involve variables.  An example of a check constraint would be one that prevents an Order Line of an order to specify both a product and service.

Notes:  A frequently used naming convention for a check constraint is  tablename_description_chk where the description portion describes the value check being performed.  Brackets are important when using compound conditional expressions to determine the logical end of your expression.  To be later discussed

Constraints are put on a database object with the CREATE or ALTER statement.

ALTER  STATEMENT:

Alters an existing database object.   Use the alter statement to

?        add columns and constraints to existing tables.  

?        Modify column definitions, defaults.

?        Remove constraints from existing tables.

     What you can and cannot do with the alter statement:

BASIC SYNTAX:

1.   Adding columns and constraints:

ALTER  TABLE TableName   ADD (ColumnName       datatype(length),
         ColumnName  datatype(length ),  <constraint clause>,  <constraint clause>,?) ;

Notes:  you can add multiple columns and constraints using one alter statement. When adding more than on enclose the whole thing in parenthesis.  Otherwise you don?t have to.

Example:

  ALTER TABLE  customers ADD (credit_line NUMBER(7,2)  CONSTRAINT  customers_credit_line_chk CHECK ( credit_line  IS NULL OR credit_line  > 0));

2.   Removing constraints:

You can only remove one constraint at a time with each ALTER statement.  There are ways to drop constraints without specifically naming them. But it is much easier to do so when they have a name.  The example below relies on the fact you named your constraint.  This is also why it is highly recommended that you name your pk, uk, fk, chk constraints.

Syntax:

ALTER  TABLE  tablename  DROP   CONSTRAINT  constraintname;

Example:

ALTER TABLE  customers  DROP CONSTRAINT  customers_credit_line_chk;

Notes:   Oracle will not let you drop a primary key constraint if it referenced by an existing foreign key constraint.

9.      Modifying existing column definitions.   (check if it lets you make them smaller).

Use the qualifier MODIFY to modify the data type, length, default, NOT NULL specification of a column.  You can modify a column only one at a time. 

Syntax:

ALTER  TABLE  tablename MODIFY columnname <  change>;

Example  new default.

ALTER TABLE  customers MODIFY credit_line DEFAULT  500;

Example  larger column size. Note you have to specify the datatype along with the length.

ALTER TABLE  customers MODIFY phone   VARCHAR(50) ;

Example  making a column NOT NULL

ALTER TABLE  customers MODIFY phone NOT NULL;

THE CONSTRAINT CLAUSE:
The constraint clause will allow you to define constraint in separately from any column definition within the ALTER or CREATE statement.  It makes sense use this clause for specifying primary keys, unique keys, check constraints because all these can involve more than one column and all them benefit by being assigned a meaningful name.  

The NOT NULL constraint involves one column at most and does not need a name.  Therefore this constraint is better to be define within the column definition:

  Consider:

CREATE TABLE location
    (locid             NUMBER(5)                           NOT NULL,
     bldg_code             VARCHAR2(10)            NOT NULL,
     room                         VARCHAR2(6)         NOT NULL,
    capacity             NUMBER(5)                       NOT NULL );

 This looks fine.  Furthermore the statement looks a lot like what is returned to you when you ask for a table description in SQL:

 SQL> desc location

 Name                            Null?    Type
 ------------------------------- -------- ----
 LOCID                           NOT NULL NUMBER(5)
 BLDG_CODE                       NOT NULL VARCHAR2(10)
 ROOM                            NOT NULL VARCHAR2(6)
 CAPACITY                        NOT NULL NUMBER(5)
 

Now consider the following create statement where primary key foreign key constraints, and unique constraints are all defined next to each column defintion.  This is a mess and is diffifult to understand and debug.

CREATE TABLE faculty
        (fid NUMBER(5) CONSTRAINT faculty_fid_pk PRIMARY KEY,
        flname VARCHAR2(30) CONSTRAINT faculty_flname_nn NOT NULL,
        ffname VARCHAR2(30) CONSTRAINT faculty_ffname_nn NOT NULL,
        fmi CHAR(1),
        locid NUMBER(5) CONSTRAINT faculty_locid_fk REFERENCES location(locid),
        fphone VARCHAR2(10),
        frank VARCHAR2(8) CONSTRAINT faculty_frank_cc
        CHECK ((frank = 'ASSO') OR (frank = 'FULL') OR (frank = 'ASST') OR (frank = 'INST')),
        fpin NUMBER(4) CONSTRAINT faculty_fpin_uk UNIQUE,
        startdate DATE);

Furthermore,  in the real world,  when large amounts of proofed data need to be loaded into tables,  DBA?s like to drop constraints as they know that their data load scripts will insert data that is clean and will not violate any rules.  It is better therefore to 1) use constraint clauses to define these types of constraints 2) place them using the ALTER TABLE statement and run them in a different script than the one you use to create tables.

Syntax by each type of constraint: 

PRIMARY KEY,  UNIQUE,  FOREIGN KEY.

General Syntax of a constraint clause:

CONSTRAINT   constraint name  <TYPE>  (columname 1, columname2)

<additional syntax specific to the constraint> 

?        Notes:  naming a constraint is optional BUT HIGHLY recommended and required by most organizations in their coding conventions.

Syntax for a Constraint clause that defines  a Primary key for a table.

CONSTRAINT  ConstraintName  PRIMARY KEY (ColumnName1, ColumnName2,?)

Example:  

ALTER TABLE customers ADD CONSTRAINT customers PRIMARY KEY (customer_id);

 Notes:  
?        A frequently used naming convention for a primary key constraint is tablename_pk.
?        The above example shows a primary key that is composed of more than one column.  

Syntax for  a Constraint clause that defines  a  unique key for a table.

CONSTRAINT  ConstraintName  UNIQUE (ColumnName1, ColumnName2,?)

Example:

ALTER TABLE student ADD  CONSTRAINT student_uk1 UNIQUE (spin);

Notes:  A frequently used naming convention for a unique key constraint is tablename_ukn. Where n indicates the which.  A table can have more than one unique constraint.  

Syntax for a Constraint clause that defines a foreign key for a table.

FOREIGN KEY(nativetablecolumnname1, nativetablecolumnname2, ?)
   REFERENCES ForeignTableName(foreigntablecolumnname1, foreigntablecolumnname2,?  )

Example:

ALTER TABLE course_update
      ADD (CONSTRAINT course_update_course_fk1 FOREIGN KEY (cid)  REFERENCES course(cid),
                CONSTRAINT course_update_faculty_fk1 FOREIGN KEY (fid)   REFERENCES faculty(fid));  

ALTER TABLE purchases ADD (CONSTRAINT purchases_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id));

Notes:

?        Notice that the first portion of the constraint identifies the column in the course update table ? the native table to be the foreign key.  The second portion of the constraint identifies the primary key being referenced in the foreign or parent table.

?        A good naming convention for a foreign key constraint is Nativetablename_ForiegnTableName_fkn, where n represents which.  You can have two relationships between to tables!

Syntax for a Constraint clause that defines a value constraint or check constraint.

CONSTRAINT  ConstraintName  CHECK((< conditional expression1>) <logical operator> (<conditional expression 2>)?)

Example:

ALTER TABLE term  
   ADD  CONSTRAINT term_enrollment_chk CHECK   ((enrollment = 'OPEN') OR (enrollment = 'CLOSED') )

INDEXES:
Usually when you query a database you are searching for a row (or collection of rows) that satisfies some condition. Examining every row in a table to find the desired rows often takes too much time to be practical, especially if there are thou- sands of records in the table. Fortunately, you can create and use an index to speed up the searching process significantly.

B*Tree Indexes:  This is the most common type of index and one of two that Oracle supports.  This type of index works very well with binary searches and is most useful when the index contains a large proportion of unique values such as social-security numbers, names, phone#'s, birthdates, etc. 

an index in a DBMS is similar to an index in a book. If you want to find a discussion of a given topic in a book, you can scan the entire book from start to finish and look for references to the topic. More than likely, however, you wouldn't resort to this time-consuming method. If the book has an index, you can use it to locate the page numbers on which your topic is discussed.

For relational model systems that run on both mainframes and microcomputers, such as Oracle, the main mechanism for increasing the efficiency with which data is retrieved from the database is by using indexes.

Tables in most database?s have an extra column called a row-id.  This column is invisible to user and cannot be updated by the user.  The row-id is internally used by Oracle to perform a binary-search against the table.

The index is a separate file that contains two columns. The first column contains the coulmn you want to search on (for example customer_number), and the second column contains the number of the row in which the row is found. To find a customer, Oracle uses a binary-search to find the value in the first column. The value in the second column indicates which row to retrieve from table; then the row for the desired customer is retrieved.

The primary disadvantage to an INDEX is that whenever you make an update to the base table for that index, you must also update all of a tables corresponding indexes.  However the advantages of speed usually far out weigh the additional update time.  A general rule of thumb is that if the table has less than a 1000 rows, don?t bother with an index.  In larger database?s columns that you regularly use for retrieving and updating such as customer_number, customer_name, slsrep_number, etc. are good candidates for indexes.

The SQL command for creating an INDEX is CREATE INDEX

Example:

To create an index for customer name do the following:

CREATE INDEX custname ON customer (last, first);

Unique Indexes
When you indicate the primary key for a table, the DBMS ensures automatically that the values for the primary key are unique. An attempt to add a second customer whose number is 124, for example, would automatically be rejected because customer number 124 already exists. Thus, you don't need to take any special action to make sure that values in the primary key column are unique; the DBMS does it for you.

Occasionally, a column that is not the primary key might need unique values. For example, in the CUSTOMER table, the primary key is CUSTOMER_NUMBER.  If the CUSTOMER table also contains a column for Social Security numbers, the values in this column also must be unique. Because the Social Security number column is not the table's primary key, however, you need to take special action in order for the DBMS to ensure that values in this column do not contain duplicates.

To ensure this uniqueness, create a special type of index called a unique index by using the CREATE, UNIQUE INDEX command. To create a unique index named SSN on the SOC-SEC-NUMBER column of the CUSTOMER table, for example, the command would be as follows:

CREATE UNIQUE INDEX SSN ON CUSTOMER (SOC_SEC_NUMBER);

The unique index has all the properties of indexes already discussed along with one additional property.  The DBMS refuses to accept any update that would cause the customer whose Social Security number is the same as that of another customer in the database.

Reverse Key Index is a new type of B*Tree index introduced in Oracle8, it basically works the same way as a standard B*Tree index but reverses the order of the data-types.  This is particularly useful in data-warehouses where new values are constantly being added and old values are being purged. 

Bitmap Indexes is an alternate to B*Tree indexes and it's varieties.  It is very useful when you have only a few distinct values such as gender or marital status.  A bitmap works by storing a single bit (on|off) for each row in the table.  Several bitmaps can be compared by multiplying each bit for a row times the next bitmap since off is represented by 0 if any one of the bitmaps is false then the resulting query will be 0.  Since true is represented by one and one times itself is always one then if all the bitmaps evaluate to true than the resultant value will be 1 or true.

Function-Based Indexes:  This is a welcome addition to Oracle8i, this allows you to create indexes based on the result of a function called such as UPPER(name).  Since very often when you search for someone's name you don't care about case, the only way to achieve this is to convert all names to UPPER or lower case.  In the past with Oracle this would require an additional column to store the UPPER(name) and several triggers to keep it in synch with it's normal counterpart.  This was both a waste of space and processing.  With function based indexes this is no longer necessary.  The only draw back with function based indexes is that they require that you run statistics on a regular basis, we will talk about that later on.  Also function based indexes must always return the same value, for instance a function based index that returns a persons name in upper case is ok, but a function that returns someone's age is not because it will vary every day.

Partitioned Indexes:  Just like tables indexes can be partitioned into multiple segments.  This is also done for the same reasons as tables, to make them more manageable. 

DROPPING AN INDEX:

DROP INDEX ssn;

Index-Organized Tables  are a hybrid between a table and a B*Tree.  In this segment type the primary key becomes the rowid and the entire table is stored in a B*Tree type structure.  This saves space since the rowid is left off and the primary key is only stored once.  Data can also be compressed for additional space savings, however this is usually only done in data-warehouses and not OLTP systems.

Clusters is another way of organizing data from one or more tables based on values of a particular table.  This type of organization provides some performance boost to OLTP under certain circumstances, but is in-effecient for the full table scans that are typical of data-warehouses. 

Rollback Segments are where Oracle keeps it's undo information.  It is a before image of the data prior to a transaction.  When a rollback is issued the before image is restored to the data.

Temporary Segments where Oracle sorts data..

Extents are the next level of granularity, recall: DATABASE==>TableSpaces==>Segments==>Extents.  See Example on Page 122.

Blocks are the lowest level of granularity, recall: DATABASE==>TableSpaces==>Segments==>Extents ==> Blocks.  They may or may not correspond to a block size in the host operating system.  They are controlled by the DB_BLOCK_SIZE parameter in the init<SID>.ora.  The default is 2048 (2KB) but should be larger for data-warehouses such as 8 or 16KB.

Views:
Most database management systems, including Oracle, are capable of giving each user his or her own picture of the data in the database. In SQL this is done using views. A view in Oracle is similar to a query in MS Access.  The existing, permanent tables in a relational database are called base tables. A view is a derived table because the data in the view is derived from the base table. It appears to the user to be an actual table. In many cases, a user can interact with the database using a view. Because a view usually includes less information than the full database, using a view can represent a great simplification. Views also provide a measure of security, because omitting sensitive tables or columns from a view renders them unavailable to anyone who is accessing the database through the view. To illustrate the idea of a View, suppose that Juan is interested in the part number, part description, units on hand, and unit price of parts in item class HW. He is not interested in any other columns in the PART table, nor is he interested in any of the rows that correspond to parts in other item classes. Whereas Juan cannot change the structure of the PART table and omit some of its rows for his purposes, he can do the next best thing. He can create a view that consists of only the rows and columns that he needs. 

A View is defined by creating a defining query. The defining query is a
SQL command that indicates the rows and columns that will appear in the view.
The command to create the view for Juan, including the defining query, is
illustrated below:

CREATE [OR REPLACE] VIEW [{FORCE | NO FORCE}] VIEW view_name AS subquery
    [WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];

• FORCE: Create the view even if the base tables don't exist

• CHECK OPTION:  Only allow Modifications to rows that qualify under the sub-query.

CREATE VIEW housewares AS
SELECT PART_NUMBER, PART_DESCRIPTION, UNITS_ON_ HAND, UNIT_PRICE
FROM PART
WHERE ITEM_CLASS = 'HW';

Given the current data in the Premiere Products database, this view contains the data shown below:  

HOUSEWARES

PART_NUMBER PART_DESCRIPTION UNITS_ON_HAND         UNIT_PRICE
AX12                    Iron                                104                                 $24.95
BH22                     Cornpopper                    95                                   $24.95
CA14                    Griddle                            78                                   $39.99
CX11                    Blender                          112                                 $22.95

The data does not actually exist in this form, however, nor will it ever exist in this form. When this view is used, it is tempting to think that the query will be executed and produce some sort of temporary table, named HOUSEWARES, that the user can access at any time. This is not what happens. Instead, the query acts as a sort of 'window' into the database. As far as a user of this view is concerned.  

Once, created you can use a view just like a table, however the database will translate the VIEW QUERY into a query against the base table.

Example:
SELECT * FROM housewares
WHERE units_on_hand > 100

-         The above will be translated by the database into

 
SELECT part_number, part_description, units_on_hand, unit_price
FROM part
WHERE item_class ='HW'
AND units_on_hand > 100;

Notice that the selection is from the PART table rather than from the HOUSEWARES view; the asterisk is replaced by just those columns in the HOUSE- WARES view; and the condition includes the condition in the query entered by the user together with the condition stated in the view definition. This new query is the one that the DBMS actually executes.

The user, however, is unaware that this kind of activity takes place.

One advantage of this approach is that because the HOUSEWARES view never exists in its own right, any update to the PART table is reflected immediately in the HOUSEWARES view and is apparent to anyone accessing the database through the view. If the HOUSEWARES view were an actual stored table, this immediate update would not be the case.  

It is also possible to create a view that has different names for the columns than in the base table.  When renaming columns, you include the new column names in
parentheses following the name of the view as shown below:  

CREATE VIEW housewares (num, dsc, oh, price) AS
SELECT part_number, part_description, units_on_hand, unit_price
FROM part
WHERE item_class = 'HW';

In this case, anyone accessing the HOUSEWARES view will refer to PART-NUMBER as NUM to PART_DESCRIPTION as DSC, to UNITS-ON-HAND as OH, and to UNIT-PRICE as PRICE. If you select all columns from the HOUSEWARES view, the new column names will display as shown below:

 NUM                     DSC                      OH          PRICE
AX12                    Iron                       104          $24.95
BH22                     Cornpopper           95          $24.95
CA14                    Griddle                  78          $39.99
CX11                    Blender                 112          $22.95

Multi-Table Views:
The above example consist of a single table view, however a view can be based on a legal SQL query, including joined tables.  

CREATE VIEW sales_cust (snumb, slast, sfirst, cnumb, clast, cfirst) AS
SELECT sales_rep.slsrep_number, sales_rep.lname, sales_rep.fname,
          Customer_number, customer.lname, customer.fname
FROM sales_rep, customer
WHERE sales_rep.slsrep_number = customer.slsrep_number;

SELECT * FROM sales_cust;

SNUMB SLAST      SFIRST      CNUMB CLAST                   CFIRST      
---------- ---------- --------------- ---------- --------------- ------------ 
         3 Jones                   Mary               124  Adams              Sally                                                                      
         3 Jones                 Mary                   412  Adams                Sally
        3 Jones                Mary                   622  Marin           Dan                                                                                     12 Diaz                   Miguel                 311 Charles                Don
        12 Diaz                   Miguel                 405 Williams         Al                                                                                       12 Diaz                   Miguel                 522 Nelson         Mary 

Using views has several benefits:

1.  Views provide data independence. If the database structure changes (adding columns, changing the way objects are related, etc.), the user still can access the same view. If adding extra columns to tables in the database is the only change and these columns are not required by the user, the defining query might not need to be changed. If relationships change, the defining query might be different, but this difference is unknown to the user. The user continues to access the database through the same view, as though nothing has changed.

2.   Because each user has his or her own view, the same data can be viewed by different users in different ways. In other words, the data can be customized to meet each user's need. 

3.   A view should contain only those columns required by a given user. This practice accomplishes two things. First, because the view usually contains fewer columns than the overall database and because the view is a single table rather than a collection of tables, a view can simplify greatly the user's perception of the database. Second, views furnish a measure of security. Columns that are not included in the view are not accessible to the user. For example, omitting the BALANCE column from the view ensures that a user of the view cannot access any customer's balance.  Likewise, rows that are not included in the view are not accessible. A user of the HOUSEWARES view, for example, cannot obtain any information about sporting goods, even though both housewares and sporting goods are stored in the same base table (PART).

These benefits hold true only when views are used for retrieval purposes. When up-dating the database, the issues involved in updating data through a view depend on the type of view, as a general rule DON'T update views..

Materialized Views:  Are new to Oracle8i.  Normally when a view is queried the base tables are actually joined and queried.  This can be very ineffecient especially with very large tables.  Materialized views unlike normal views actually store the data instead of referencing the base tables.  You can setup refresh intervals to get fresh data from the base tables.  When a query is issued against the materialized view or a query is issued against the same base tables with the same group by clause Oracle will actually query the materialized view.  This is much more effecient since materialized views will normally be much smaller than the base tables.  Materialized view are very helpful for creating summary tables, in fact they replace summary tables as well as the need to refresh them. 

CREATE MATERIALIZED VIEW mv_sales_cust AS
    SELECT sales_rep.slsrep_number AS snum,
             sales_rep.lname AS slname,
             sales_rep.fname AS sfname,
             Customer_number AS cnum,
             customer.lname AS clname,
             customer.fname AS cfname
     FROM sales_rep, customer
    WHERE sales_rep.slsrep_number = customer.slsrep_number

INSERT INTO VIEWS:
Consider the row and column subset view named HOUSEWARES. There are columns in the underlying base table (PART) that are not present in the view. Thus, if you attempt to add a row with the data ('BB99','PAN',50,14.95), the system must determine how to enter the data in those columns from the PART table that are not included m the HOUSEWARES view (ITEM-CLASS and WAREHOUSE-NUMBER). in this case, it is clear what data to enter in the ITEM-CLASS column. According to the view definition, all rows are item class NW. But it is not clear what data to enter in the WAREHOUSE-NUMBER column. The only possibility would be NULL. Thus, provided that every column not included in a view can accept nulls, you can add new rows using the INSERT command. There is another problem, however. Suppose the user attempts to add a row containing the data ('AZ52','POT',25,9.95). This attempt must be rejected, because there is a part number AZ52 that already exists in the PART table. Because this part is not in item class HW, this rejection certainly will seem strange to the user, because there is no such part in the user's view.

UPDATING AND DELETING VIEWS:
Updates or deletions cause no particular problem in this view. If the description of part number CA14 changes from skillet to pan, this change is made in the PART

table. If part number CX11 is deleted, this deletion occurs in the PART table. One surprising change could take place, however Suppose that ITEM-CLASS is included as a column in the HOUSEWARES view and then a user changes the item class of part number CX11 from HW to AP. Because this item would no longer satisfy the criterion for being included in the HOUSEWARES view, part number CX11 would disappear from the user's view.

Whereas some problems do have to be overcome, it seems possible to update the database through the HOUSEWARES view. This does not imply that any row and column subset view is updateable, however.  Consider the view below (You use the word DISTINCT to omit duplicate rows from the view.)  

CREATE VIEW sales_cred AS 
SELECT DISTINCT credit_limit, slsrep_number
FROM customer ; 

How would you add the row (1000, 6) to this view? In the underlying base table

(CUSTOMER) at least one customer must be added whose credit limit is $1000 and

whose sales rep number is 6, but who is it? You can't leave the customer_number null, because it is the primary-key.  Also, even if you could make the insertion, the view would not change because there is already a view with (1000, 6)

A view that contains the primary key of the underlying base table is updateable (subject, of course, to some of the concerns we have discussed). 

JOINS:
In general, views that involve joins of base tables can cause problems at update.

Consider the relatively simple view SALES-CUST, for example, described earlier. The fact that some columns in the underlying base tables are not seen in this view

presents some of the same problems discussed earlier. Even assuming that these problems can be overcome through the use of nulls, there are more serious problems inherent in the attempt to update the database through this view. On the surface, change the row (6,'Smith',Williarn','256','Samuels','Ann?) to (6,'Baker','Nancy','256','Samuels','Ann?) might not appear to pose any problems other than some inconsistency in the data. (In the new version of the row, the name of sales rep number 6 is Nancy Baker; in the next row in the table, the name of sales

rep number 6, the same sales-rep, is William Smith.)

The problem is actually more serious than that making this change is not possible. The name of a sales rep is stored only once in the underlying SALES-REP table. Changing the name of sales rep number 6 from William Smith to Nancy Baker in this one row of the view causes the change to be made to the single row for sales rep number 6 in the SALES-REP table. Because the view simply displays data from the base tables, every row in which the sales rep number is 6 now shows the name as Nancy Baker. In other words, it appears that the same change has been made in

all the other rows. In this case this change probably would be a good thing. In general, however, the unexpected changes caused by an update are not desirable.

Not all joins create this problem. If two base tables happen to have the same primary key and the primary key is used as the Join column, updating the database is not a problem.

Statistics:
A view that involves statistics calculated from one or more base tables is the most troublesome view of all, because calculated or derived columns cannot be updated.  

Dropping a View:
When a view is no longer needed, you can remove it by using the DROP VIEW command.

DROP VIEW sales_cust;

THE SYSTEM CATALOG:
The system catalog is a relational database with tables that contain information about objects in the database including tables, indexes, users, constraints, etc.  The system catalog is sometimes also called the data-dictionary.  

There are three set?s of views of the system catalog/data-dictionary:
1.  DBA_*          Views of all objects in the database for DBA reference.
2.  ALL_*          Views that contain all objects the current user has privilege to access.
3.  USER_*          Views that contain all objects the current user owns.

Commonly Used SC/DD Views (xxx = ALL, DBA, or USER):
xxx_CATALOG            List of all database tables, views and sequences.
xxx_CONSTRAINTS    List of all constraint definitions on all tables in the database
xxx_INDEXES               List of all indexes in the database.
xxx_SEQUENCES         List of all sequences in the database.
xxx_TABLES                 List of all tables in the database.
xxx_USERS                    Information on all users of the database.
xxx_VIEWS                    List of all views in the database.  
xxx_SOURCE                Source code for PL/SQL programs
xxx_TRIGGERS            Information on triggers.

Because the system catalog is made up of tables, you can use standard SQL commands to view and update them. 

You can obtain from the system catalog information about the tables in the relational database, the columns they contain, and the views built on them. You do this by using the same SQL syntax you use to query any other table.

Example:

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
CUSTOMER
ORDERS
ORDER_LINE
PART
SALES_REP

5 rows selected.

Updating the tables in the system catalog occurs automatically when users create, alter, or drop tables or when they create or drop indexes. Users should not update the catalog directly using the update features of SQL because inconsistent results might be produced.

Meta-Data

Schema is basically the same thing as a user.  All objects in a database are owned by a schema or user.   A table with the same name may exist in the database several times so long as it has a separate owner.  For example the users bob, scott, and tim may all have a customer table.  If you want to use a specific copy of that table you must qualify it with the schema name, i.e. bob.customer, scott.customer, or tim.customer.  Additionally it is possible to access tables in other databases so long as you have permission to do so.  To access tables in another database you must use the "@"  qualifier.  For example to query the table customer owned by scott in the sales database you would have to do something like the following:  SELECT * FROM scott.customer@sales;  To simplify this Oracle provides something called a synonym.  A synonym is similar to an alias, except that it is global and persistent in nature, whereas an alias is local and temporary to the current query.  You use the CREATE SYNONYM command to do this.

SQL> CREATE PUBLIC SYNONYM scott_sales_custs FOR scott.customers@sales;

The Oracle Optimizer  reviews every SQL statement sent down to the database and decides the best path to execute it.  Oracle has two optimizers, it's originally one is the Rules based optimizer and it's new one is the Cost based optimizer.  The rules based optimizer uses a set of predefined rules to execute a query.  The cost based optimizer creates an query plan dynamically to execute a query.  This type of optimizer is more efficient for the ad-hoc type of queries that are run by data-warehouses.  The cost based optimizer does however depend upon the statistics being up to date and accurate.  Statistics should be run on a table any time large amounts of data are loaded into a table, such as a bulk load in SQL*Loader.  In any event at least once a month statistics should be run.  The commands for run statistics are:

ANALYZE TABLE <table> COMPUTE STATISTICS
ANALYZE TABLE <table> ESTIMATE STATISTICS [FOR COLUMNS]
ANALYZE INDEX can also be used to estimate statistics for indexes.

The first example scans the entire table to calculate statistics and is the most time consuming.
The second example works by sampling the table.  When the FOR COLUMNS option is used statistics are also calculated for all the columns in the form of histograms.  This allows Oracle to better estimate how many rows will be returned for a particular query.  These commands also insert/update rows into the following data-dictionary tables:

1.  DBA_TABLES
2.  DBA_INDEXES
3.  DBA_TAB_COLUMNS
4.  DBA_HISTOGRAMS

I/O Operations: OS Blocks sizes should correlate to DB Block sizes.  For full table scans Oracle uses the DB_FILE_MULTIBLOCK_READ_COUNT * the DB_BLOCK_SIZE to determine how much data is read at a time.  This is also set in the init<SID>.ora.  Several special operations including backup, recovery, direct read, or direct write operations such as in direct path SQL*Loader use the ini<SID>.ora parameter FILE_DIRECT_IO_COUNT when SORT_DIRECT_WRITES is enabled also in init<SID>.ora.

Sort Operations:  Oracle will normally try to sort data in memory, it uses the init<SID>.ora parameter SORT_AREA_SIZE to determine how much memory it can uses for sorting operations.  When disk space is needed Oracle uses the Temporary Tablespace.

Backup and Recovery can be either logical or physical, hot or cold.   The Oracle EXP utility is typically used to back up a table, but may be impractical for a data-warehouse.  Physical backups can be either hot or cold.  Cold backups are conducted by the operating system when the database is shutdown.  A cold backup when the database is up will NOT be reliable.  A hot backup on the other hand is done by Oracle itself and the database can be up at the time.  Depending on your availability requirements you may choose either.  In order to do hot backups you must have archivelog mode turned on.

Recovering the Database: Crash Recovery
The first line of defense is the database log.  This contains a log of all transactions (sql-statements) against the database.  These transactions can usually be re-applied to a baseline backup to recover the database up til the second of it's destruction.  

Point-in-time Recovery:  Restore or partial restore from a point in time, no logging is used.

Incremental Backup:  Only backup the data that has changed.

Hot-backup:  Backing up the database while it is being used.  Most modern DBMS can do this.

Non-DB Backups:  Such as O/S backups should be avoided because in order for them to be accurate they would have to be done with the database down so that the database will be frozen in time.  Otherwise there will be inconsistencies because of transactions that occur while the backup is happenning.

Roll-Foward Recovery:  The essence of the roll-foward recovery is to apply log records to a baseline backup.  This is the most common type of backup.

PHYSICAL DESIGN:

Tablespace Design:  Oracle databases are organized into several tablespaces.  The tablespace is the physical file that exist on the disk.  It may contain one to many database objects.

1.  Tables should be separated from their indexes
2.  Small tables should be separated from larger tables.
3.  Two tables that are frequently joined should be on separate tablespaces.
4.  Dimension tables should be separated from fact tables.
5.  Tables that are frequently scanned should be separated from those that are accessed randomly.
6.  Static tables and indexes should be separated from dynamic tables and indexes.
7.  Extremely large tables and indexes should be partitioned on separate tablesspaces.
8.  Oracle's special type of segments (rollback and temporary) need to be placed in dedicated tablespaces of their own.
9.  Only the data-dictionary tables should reside on the SYSTEM tablespace.
10. Files associated with each tablespace should be striped across multiple disk drives whenever possible.
11. With Raw files (Unix default) should be sized similarly.
12. All objects of a particular tablespace should share the same storage parameters.  Use the DEFAULT STORAGE clause.

TABLESPACES:  As we stated earlier the tablespace is the basic structure of storage in an Oracle database.  It can be created and stored in many ways.  Oracle has added some new features to 8 and 8i specifically for data-warehouse's.  They include Partitioning, Read-Only, and Transportable tablespaces

Most of the defaults the object belonging to a particular tablespace should be assigned at the tablespace level.  These include DEFAULT STORAGE, Minimum Extent, etc. 

CREATE TABLESPACE dimension_ts
    DATAFILE '/u002/oradata/dwpl1/dim_data_01.dbs'  SIZE 500 M
    MINIMUM EXTENT 5M
    DEFAULT STORAGE (INITIAL 5M NEXT 5M PCTINCREASE 0);

DATAFILE:  Is the physical operating system file.
MINIMUM EXTENT: Requires that each extent be evenly divisible by this number.
DEFAULT:
    INITIAL: The first extent.
    NEXT: Each subsequent extent
    PCTINCREASE: Describes the amount of the next extent that is actually used.  The default here is 50%
        This percent is fine for OLTP, but for data-warehouses that are read-only it should be 0, since additional
        Space will not be needed.

Data bases can be made to AUTOEXTEND but this is not recommended since it uses O/S files instead of raw-files which is not desirable in a Unix enviornment.  However in very table databases with many tablespaces it may not be practical to manually monitor and extend tablespaces.  A mixture can be used.

TEMPORARY TABLESPACES is where Oracle performs sorts that are too large to fit in memory, it also uses it for building indexes.  TEMPORARY TABLESPACES should be separate from permanent ones and specified as temporary with the TEMPORARY keyword.  Alternatively you can convert an existing one to temporary with the ALTER TABLESPACE command.  Specify this tablespace as the default TEMPORARY TABLESPACE for each user.  Sizing temporary tablespaces is also a hit or miss type of operation, but in general will use only a small fraction of the total database size. Temporary tablespaces can also be locally managed, to create on use the CREATE TEMPORARY TABLESPACE local_temp TEMPFILE '/u012/oradata/dwp1/local_temp_01.dbs' SIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M.

Temporary tablespace don't normally show up in the Oracle data-dictionary and although all users will use the same temporary tablespaces and tables, only the data they insert will be visible to them.  There are two views in the data-dictionary for temporary tablespaces: DBA_TEMP_FILES and V$TEMPFILE.

To add files to tablespaces use the ALTER TABLESPACE tablespace_name ADD DATAFILE file_name  Sometimes it may also just be easier to drop a tablespace and recreate it. 

Creating Tables (CREATE TABLE COMMAND):

CREATE TABLE activity (
        month             VARCHAR2(7),
        org_id             NUMBER,
        function_code  VARCHAR2(2),
        project_id        NUMBER,
        revenue            NUMBER,
        hours                NUMBER,
        expenses          NUMBER)
    TABLESPACE activity_ts
    -- All the remaining values can and should be taken from the tablespace default.

"If two objects require different values of these fundamental characteristics, it is likely that they should probably be in different tablespaces."