Oracle8i Architecture and Administration

(Previous) < 1 2 3 4 5 > (Next)

Creating the Database

Prepare the Operating System

On a UNIX system the majority of the required environment variables are stored in a file named oraenv or coraenv , on a Windows machine these are stored in registry. The main enviornment variables to be aware of are :

DISPLAY: The machine name or IP Address-X server-and-screen being used by your workstation to connect to the system where the software will be installed, if not sure what X server and screen setting should be, use 0 (zero) for both, DISPLAY=workstation_name:0.0

ORACLE_HOME: Set according to OFA (Oracle Flexible Architecture)

ORACLE_SID: System ID for the Database ( Important for specifying which database to mount)

ORACLE_BASE: Set according to OFA

ORA_NLS33: Required to be set if installing a database with a storage character set other then US7ASCII.To be set $ORACLE_HOME/ocommon/nls/admin/data

NLS_LANG: Required if installing or creating a database that uses a character set other than US7ASCII ( Recommend Mexican Spanish Character Set : WE8DEC , NLS_VALUE= esm )

LD_LIBRARY_PATH : To be updated with the location of the Oracle Libraries $ORACLE_HOME/lib

CLASSPATH : Location for Java Classes to load.

A Unix a group called dba is generated for the DBAs. An account orainstall is used to install the Oracle Software, this account must be a member of the dba group.

Prepare the parameter file :

The minimum parameters required to create the database are controlfile, db_block_size and db_name, however other recommended parameters include USER_DUMP_TEST,BACKGROUND_DUMP_TEST,NLS_DATE_FORMAT,etc..

Create the Database :

The create database command creates:

The system datafiles and tablespace, controlfiles, initial redo log groups, the system rollback segments and the internal database tables necessary for the operation of the Oracle Database. ( This command is issued after the database is in the no mount stage).

It is very important to correctly size the datafile specified with the create database command, as this datafile will be assigned to the SYSTEM Tablespace. Allocate more space to the database if the database will make heavy usage of stored packages, procedures, functions and triggers. Set the MAX parameters high as well. Example :

CREATE DATABASE "ODBC"
   maxdatafiles 254
   maxinstances 8
   maxlogfiles 32
   character set US7ASCII
   national character set US7ASCII
DATAFILE '/u01/app/oracle/oradata/ODBC/system01.dbf' SIZE 175M
logfile '/u01/app/oracle/oradata/ODBC/redo01.log' SIZE 500K, 
    '/u01/app/oracle/oradata/ODBC/redo02.log' SIZE 500K;

After the command is run the database opens automatically. At this point it is a working, although very bare database.

Create Data Dictonary Views and the Standard Packages

Construct the Data Dictonary views

The data dictonary views are created by the catalog.sql script located at $ORACLE_HOME/rdbms/admin/

Query the Data Dictionary

The data dictonary is composed of:

Internal RDBMS(X$) Tables: The tables are used by the Oracle RDBMS to keep track of internal database information. Most of them are not designed to be used directly by DBAs or users.
Data Dictonary Tables : Hold information for tables,indexes,constraints and all other database constructs. They are owned by SYS, and easily identifiable by a trailing $ in their names tab$,seg$,cons$,etc. They are generated by the script SQL.BSQ
Dynamic Performance Views : These are the mainstay of the DBA, V$ views are actully public synonyms to the V$ views owned by SYS.
Data Dictonary Views : The data dictonary views are views created on the X$ and data dictonary tables. They are divided into three categories DBA_, ALL_ , USER_ .

Prepare the PL/SQL environment using the administrative scripts.

The scripts catproc.sql is used to install the procedural Option (or PL/SQL objects) and its supporting database structures.

Administer stored procedures and packages

When a subprogram is created via the REPLACE OR CREATE command, it is stored in the database. The subprogram is stored in compiled form, which is known as p-code. The p-code has all the references in the subprogram evaluated , and the source code is translated into the form that is easily readable by the PL/SQL engine. When the subprogram is called, the p-code is read from disk, if necessary and exectued. P-code is analogouse to the object code generated by other 3GL compilers. Since the p-code has the object references in the subprogram already evaluated (this is the early binding property mentioned in PL/SQL characteristics ), executing the p-code is a comparetively inexpensive operation.

Information about the subprogram is accessible through varios dictonary views:

USER_OBJECTS: This view contains all the information on the subprograms (including all other objects ), which includes when the object was created and last modified, the type of the object (function,procedure,table,sequence,etc) and the validity of the object.
USER_SOURCE : Contains the original source_code for the object.
USER_ERRORS : Contains information about compile errors.

A package is esentially a named declarative section. Anything that can go into the declarative part of a block can go in a package. This includes procedures, functions, cursors, types, and variables. A package is composed of two parts a header (or specfication) and a body . They are both separate dictonary objects in the database.

The header contains all the elements that will conform the package: procedure specifications , function specifications , variable declarations, type definitions , exception declarations and cursor declarations. However it does not contain any code for any part.

The package body cannot be compiled unless the package header is compiled successfully. It contains the code for the forward subprogram declarations in the package header. Objects in the header that are not forward decalarations (like an exception) can be referenced in the package body without being redeclared. The package body is optional in case that the header does not contain any procedures or functions ( only variable declarations, cursors,types,etc)

The package body and header are also stored in the USER_OBJECTS where object_type is PACKAGE and PACKAGE BODY.

NOTE : To find DEPENDENCIES among OBJECTS the views: DBA_DEPENDENCIES,USER_DEPENDENCIES and ALL_DEPENDENCIES are provided.

List the types of database event triggers

A trigger is executed implicitly whenever the triggering event happens, and the trigger doesnt accept arguments. Triggers are used to

Maintain complex integrity constraints not possible through declarative constraints enabled on the table.
Auditing information in a table, by recording the changes made and who made them
Automatically signalling other programs that action needs to take place, when changes are made to a table.

The general syntax is:

 
CREATE [OR REPLACE] TRIGGER trigger_name 
[BEFORE | AFTER] triggering_event ON table_reference
[FOR EACH ROW [WHEN trigger condition]]
trigger_body;

Where trigger_event specifies when the trigger fires, table_reference is the table for which the trigger is defined, and trigger body is the main code for the trigger.

The required components of a trigger are the trigger_name, triggering_event, and the body . The WHEN clause is optional. The Namespace for trigger names is different from that of other subprograms. Subprograms (procedures,functions,tables,packages,etc) share the same namespace (Schema) which means that a certain procedure cannot have the same name as another existing object in the Schema (be it table,package,etc.). Triggers however, can have the same name as table or procedure, since they are in a separate Namespace.

TYPES OF TRIGGERS

The triggering event determines the type of the trigger. Triggers can be defined for INSERT, UPDATE, or DELETE operations. They can be fired before or after the operation, and they can also fire on row or statement operations. The values for the statement, timing and level determine the type of the trigger. There are a total of 12 possible types: 3 statements,2 timing and 2 levels.

Category	Values	Comments
Statement	INSERT, UPDATE, DELETE	Defines which kind of DML statements causes the trigger to fire
Timing	BEFORE or AFTER	Defines wether the trigger fires before the statement is executed or after the statement is executed
Level	Row or Statement	If the trigger is a row-level trigger, it fires once for each row affected by the triggering statement. If the trigger is a statement-level trigger it fires once , either before or after the statement. A row-level trigger is identified by the FOR EACH ROW clause in the trigger definition

You can drop or enable/disable the trigger with the following commands:

drop trigger <trigger_name>
alter trigger <trigger_name> [ENABLE|DISABLE]