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.
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..
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.
The data dictonary views are created by the catalog.sql script located at $ORACLE_HOME/rdbms/admin/
The data dictonary is composed of:
The scripts catproc.sql is used to install the procedural Option (or PL/SQL objects) and its supporting database structures.
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:
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.
A trigger is executed implicitly whenever the triggering event happens, and the trigger doesnt accept arguments. Triggers are used to
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]
The database assings rollback segments on a round-robin basis.They support the use of rollback command to restore the previous image of modified records. They also rollback transactions that are aborted prior to completion.
During queries, rollback segments are used to construct a consisten "before" image of the data that has changed-but not commited- prior to the execution of the query. The round-robin fashion in which rollback segments are assigned is designed to distribute the transaction load being carried by the rollback segments. Even though a single rollback segment can be created it is not recommended due to contention in the rollback segment,conversely, you may choose to create many small rollback segments, so that each transaction will have it own rollback segment, this also run into performance problems, because if the rollback segments are to small they will likely be dynamically extended in order to service the transaction.
The design process involves determining the transaction volume and estimating the number and type of transactions. There are two types of entries to be considered:
The key to managing rollback segments is to minimize the amount of inactiv in use (IIU) entre data. Unfortunatly their existence only becomes evident when the error "ORA-1555 snapshot to old" is reported.
To solve this problem, the large queries should be isolated to run at times when there is very little transaction occuring. In order to determine the amount of writes made to a rollback segment the V$ROLLSTAT view is used, this view used in conjunction with the V$ROLLNAME which is used to determine the amount of activity in each rollback segment.
In order to determine the activity for a unique transaction, you need to direct the transaction to a specific rollback segment set transaction use rollback segment SEGMENT_NAME.
NOTE: The previous views V$ROLLNAME are reset once the database is shutdown.
The parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT does not limit the number of transactions that can use a rollback segment.Rather, it determines the number of rollback segments an instance attempts to acquire when opening a database.
The parameter ROLLBACK_SEGMENTS specifies the rollback segments per NAME to be loaded at instance startup.
| Number of Current Transactions | Number of Rollback Segments |
|---|---|
| n <16 | 4 |
| 16 <= n <32 | 4 |
| 32 <= n | 8 |
A rollback segment can be in any of the following states:
The data dictonarytable DBA_ROLLBACK_SEGS lists the state of each rollaback segment allong wight other rollback information.
A PARTLY AVAILABLE rollback segment is being used by an in-doubt distributed transaction that cannot be resolved because of a network failure.
A NEEDS RECOVERY rollback segment is being used by a transaction (local or distributed ) that cannot be resolved because of local media failure, such as a missing or corrupted datafile , or is itslef corrupted.
Oracle or a DBA can bring a PARTLY AVAILABLE rollback segment online. In contrast you must take a NEEDS RECOVERY rollback segment OFFLINE before it can be brought online.(If you recover the database and there by resolve the transaction , Oracle automatically changes the state of the NEEDS RECOVERY rollback segment to OFFLINE.
A DBA can drop a NEEDS RECOVERY rollback segment ( This allows the DBA to drop corrupted segments) A PARTLY AVAILABLE segment cannot be dropped; you must first resolve the in-doubt transaction, either automatically by the RECO process or manually.
If you bring a PARTIALLY AVAILABLE rollback segment online ( by a command or during instance startup ), Oracle can use it for new transactions. However, the in-doubt transaction still holds some of its transaction table entries, so the number of new transactions that can use the rollback segment is limited.
Also , until you resolve the in-doubt transaction, the transaction continues to hold the extents it acquired in the rollback segment, preventing other transactions from using them. Thus, the rollback segment might need to acquire new extents for the active transactions, and therefore grow. To prevent the rollback segment from growing, a DBA might prefer to create a new rollback segment for transactions to use until the in-doubt transaction is resolved, rather than bring the PARTLY AVAILABLE segment online.
When a tablspace goes offline so that transactions cannot be rolled back immediately , Oracle writes a deferred rollback segment . The deferred rollback segment contains the rollback entries that could not be applied to the tablespace, so that they can be applied when the tablspeace comes back online. These segments disappear as soon as the tablespace is brought back online and recovered. Oracle automatically creates deferred rollback segments in the SYSTEM tablespace.
The table should ideally fit in one sgement, see other information in this section for sizing.
These are managed by the storage parameters specified at creation MAXEXTENTS, MINEXTENTS, PCTINCREASE, FIRST, NEXT, PCTUSED, PCINCREASE, the value for these parameters can be queried at USER_OBJECTS or USER_TABLES.
Row migration ocurrs when an update to a row makes the length of the row larger than the space available to it in the block. When this happens Oracle tries to find another block with enough free space to hold the entire row, if such a block is available ,Oracle moves the entire row to the new block. If the row is too large to fit into any available block, the row is split into multiple pieces and stores each piece in a separate block.
The problems that occur are that:
Oracle has to perform at least one additional I/O read every time it has to fetch a migrated row. It must read each block that contains either a portion of the rows data or a pointer to another block that contains row data. Oracle also has to store additional pointers along with row data to accomodate the data migration mechanism. While usually not substantial, this wastes some disk space.
Eliminating Chained and Migrated RowsChained and migrated row impose a serious problem to overall database performance. To check for chained or migrated rows you issue the command:
ANALYZE TABLE <table_name> LIST CHAINED ROWS <into table_to_insert_results>
The table <into table_to_insert_results> should be the same as the chained_rows table defined in the utlchain.sql, if no table_to_insert_results is specified the command looks for the chained_rows table. If after querying a table it shows that rows are migrated then you can follow these steps:
CREATE TABLE cadena_intermedia
AS SELECT *
FROM tabla_migrada
WHERE ROWID IN
( SELECT head_rowid
FROM chained_rows
WHERE table_name= 'tabla_migrada');
DELETE FROM tabla_migrada
WHERE ROWID IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'tabla_migrada');
INSERT INTO tabla_existente
SELECT *
FROM cadena_intermedia
DELETE FROM chained_rows
WHERE tabel_name=tabla_migrada
The dynamic views USER_TABLES, USER_OBJECTS , AND USER_CONSTRAINTS hold information regarding the tables among others.
The ROWID is used to uniquely identify a row in a regular Oracle Index (B-Tree) and in used throught the Oracle8 kernel. The row-id uses a base-64 encoding , and is 16 characters wide. This means that there are 64 to the 18 possible rows in any given Oracle 8 database (more than enough!). The components of the ROWID are:
| Data Object | Relative File# | Block # | Slot |
|---|---|---|---|
| OOOOOO | FFF | BBBBBB | SSS |
The first six characters represent the data object number, the next thre represent the relative file number within the tablespace, and the next six represent the block number within the file, and the last three represent the (row) slot number within the block.
ROWID version Comparison| Version | Description | Bytes | Display | File Nos. |
|---|---|---|---|---|
| 7.x | restricted | 6 | 2 dots | absolute |
| 8.x | extended | 10 | no dots | relative |
The tablspace-realtive id addressing is the foundation of Oracle support for VLDB (Very Large Databases). Oracle8 uses 10 bytes to store the ROWID as opposed to 6 bytes for Oracle7.x .
When relative ROWID is sufficient, the restricted ROWID is used. When absolute ROWID is required, as with Global indexes on partitioned tables,the extendad (full) ROWID is used. Restricted ROWIDs are sufficient for nonpartitioned indexes on nonpartitioned tables, equipartitiones indexes on partitioned tables, and chaining and migration pointers accros blocks. Extended ROWID are necessary for Global Indexes on partitioned tables, kernel use, and stored forms of ROWID.
Application poratability arises only if the applcation either partially stores or retrieves ROWID rows. On the other hand, those application using ROWIDs on the whole should be unaffected.
When Oracle 7 tables are either exported or migrated to Oracle 8, stored ROWID column widths are automatically widened to accomodate new, wider Oracle 8 ROWIDs.
The make the conversion the package DBMS_ROWID is used, which is created by the dbmsutil.sql script called by the catproc.sql script.
update los_carros set
rowid=dbms_rowid.rowid_to_restricted(rowid,'OCP','LOS_CARROS',0);
update los_carros set
rowid=dbms_rowid.rowid_to_extended(rowid,'OCP','LOS_CARROS',0);
primary key or unique constraint. This type of index holds the value of the columns plus its ROWID. When Oracle queries for information on a specific row in a table, it looks up the ROWID in the index and then pulls the data directly from the table.B-Tree Index:
create index CODIGO_POSTAL on ESTADOS(Codigo_Postal,Descripcion)
This index is created on two columns in the ESTADOS table.
Index Organized Table:
create table ESTADOS(
Codigo_postal CHAR(15) primary key,
Descripcion VARCHAR2(25)
)
organization index;
Bitmap Index:
create bitmap index EMPLEADO_CON_SEGURO on empleados(seguro_medico);
The command alter index ... rebuild can be used to recreate or change the location and initial storage sized of the index.
alter index EMPLEADOS_CON_SEGURO rebuild
tablespace INDEXES
storage (initial 2M next 2M pctincrease 0);
create table comida_rapida
( Tipo_de_comida varchar2(25),
Costo number,
Lugar_de_restaurant varchar2(40),
constraint UBICACION primary key (Lugar_de_Restaurant)
using index tablespace INDEXES
storage (initial 2M next 2M pctincrease 0));
alter index CAMBIO rename to NOMBRE_NUEVO; alter index CHANGE rename to NEW_NAME;
DROP INDEX <index name>
Information on the indexes can be queried in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES