Akadia Information Technology

Data Access

TRIM instead of RTRIM und LTRIM
TO_LOB (Converting LONG to CLOB)
SYS_CONTEXT (Schema Environment)
SYS_GUID (Generate 16-Byte Unique Identifier)
Temporary Tables (Session and Transactionlevel)
Autonomous Transactions
Bulk Binds (FORALL)
Bulk Collects (BULK COLLECT INTO)
Bulk Collects with RETURNING INTO
Call by Reference (NOCOPY)
Dynamic SQL to reference database objects that do not exist at compilation ?
EXECUTE IMMEDIATE to execute dynamic SQL
Invoker- and Definer Rights
Fine Grained Access Control
Application Context (Security Policy)

The Oracle Architecture

Read Only Databases
Multiplexed Archiving up to 5 Destinations
Standby Database
Block Checking
Using LogMiner to Analyze Online and Archived Redo Logs
Event Handling and Event Attributes
System Events with Trigger Examples
Client Events with Trigger Examples
Faster Startup after Instance Crash
Optimizer Statistics and Create Index
Manipulate Optimizer Statistics with DBMS_STATS
Preserve Execution Plans (Outlines)
Enable Parallel Automatic Tuning

Database Structure

Locally managed Tablespaces
Read Only Tablespaces
Transportable Tablespaces
Partitioning Enhancements
Online Index Creation and Rebuild
Creating a Key-Compressed Index
Function Based Indexes (FBI)
Index only Tables and Secondary Index Support
Drop Column Support
Skip locked rows with SKIP LOCKED
Resource Management

Net8

Automatic Instance Registration
Load Balancing and Client Failover
New Service Name Concept
DB_DOMAIN
Java Support
Net8 Configuration Assistant

Miscellaneous

SQL*Plus replaces SVRMGRL
Export / Import to / from several files
Export combined with a query
Export / Import with Optimizer Statistics
Materialized Views for Data Warehouses
Refreshing Materialized Views (MVIEWS)
MVIEW manual Refresh with DBMS_MVIEW
MVIEW automatic Refresh with DBMS_JOB
Automatic Fast Refresh of Materialized Views
Calculate multiple levels of subtotals with ROLLUP
Create cross-tabular reports with CUBE
GROUPING Function with ROLLUP and CUBE
Top-n / Bottom-n Queries

TRIM instead of RTRIM und LTRIM

SELECT TRIM(' One Word ') FROM dual;

TRIM('
------
One Word

TO_LOB (Converting LONG to CLOB)

In Oracle 8.1.5, there is a new simple way to convert LONG's to CLOB's and ONG RAW's ind BLOB's.

INSERT INTO tab_new SELECT TO_LOB(long_value) FROM tab_old;

SYS_CONTEXT (Schema Environment)

A Context is a set of application-defined attributes that validates and secures an application. You can even create your own contexts with CREATE CONTEXT. Oracle have defined the following pre-built Contexts.

'NLS_TERRITORY' returns the territory
'NLS_CURRENCY' returns the currency symbol
'NLS_CALENDAR' returns the NLS calendar used for dates
'NLS_DATE_FORMAT' returns the current date format
'NLS_DATE_LANGUAGE'  returns the language used for days of the week, months, and so forth, in dates
'NLS_SORT' indicates whether the sort base is binary or linguistic
'SESSION_USER' returns the name of the user who logged on
'CURRENT_USER' returns the current session user name.
'CURRENT_SCHEMA' returns the current schema name
'CURRENT SCHEMAID' returns the current schema ID
'SESSION_USERID returns the logged on user ID
'CURRENT_USERID' returns the current session user ID
'IP_ADDRESS' IP-address of the client, if the client is connected to Oracle using the TCP protocol.

The following query will retun the IP-Address of the connect client computer.

SELECT sys_context('USERENV','IP_ADDRESS') FROM dual;

SYS_GUID (Generate 16-Byte Unique Identifier)

Generate global unique Identifier, which takes care of host, process, sid.

INSERT INTO my_table VALUES ('BOB', SYS_GUID());
SELECT SYS_GUID() FROM DUAL;

Temporary Tables (Session and Transactionlevel)

If you need a temporary storage to save some results, temporary tables are a good feature, because they doesn't need a cleanup job. You have two options: ON COMMIT DELETE ROWS (Transactionlevel) or ON COMMIT PRESERVE ROWS (Sessionlevel). Each session can only see his/her own data.

CREATE GLOBAL TEMPORARY TABLE my_temp (
  t1 NUMBER(5) PRIMARY KEY,
  v1 NUMBER(2),
  v2 VARCHAR(10))
ON COMMIT DELETE ROWS;

INSERT INTO MY_TEMP VALUES (1,4,'z');
1 row created.

SELECT * FROM my_temp;
T1        V1         V2
--------- --------- ----------
1         4          z

COMMIT;
Commit complete.

SELECT * FROM my_temp;
no rows selected

Autonomous Transactions

At times, you may want to commit or roll back some changes to a table independently of a primary transaction's final outcome:

DROP PACKAGE Banking;
CREATE OR REPLACE PACKAGE Banking AS
  PROCEDURE SetSal (Id INTEGER, Amount NUMBER);
  PROCEDURE ErrHandler (Id INTEGER);
END Banking;
/

CREATE OR REPLACE PACKAGE BODY Banking AS
  PROCEDURE ErrHandler (ID INTEGER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO errlog VALUES (Id,SYSDATE,USER);
    COMMIT;
  END;
  PROCEDURE SetSal (Id INTEGER, Amount NUMBER) IS
  BEGIN
    UPDATE emp SET sal = sal + Amount
     WHERE empno = Id;
    IF (SQL%NOTFOUND) THEN
      ErrHandler(Id);
    END IF;
    COMMIT;
  END;
END Banking;
/

The COMMIT of ErrHandler() doesn't commit SetSal() COMMIT.

Bulk Binds (FORALL)

Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines, it reduces network I/O.

Example without Bulk-Binds:

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
   END LOOP;
END;
/

The same with Bulk Bind (no more looping)

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
END;
/

Bulk Collects (BULK COLLECT INTO)

In the example above (Bulk Binds), the list with empno's was statically built. With Bulk Collect you can dynamically build the entire list using BULK COLLECT INTO.

DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   Id Numlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE sal < 2000;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
END;
/

Bulk Collects with RETURNING INTO

You can even use Bulk Collects with DML-Commands to return a value to the calling procedure using RETURNING without an additional fetch.

DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   TYPE Bonlist IS TABLE OF emp.sal%TYPE;
   Id Numlist;
   Bl Bonlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE deptno = 10;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i)
   RETURNING Sal BULK COLLECT INTO Bl;
END;
/

In the PL/SQL table "Bonlist" you can now find the updated salaries.

Call by Reference (NOCOPY)

When the parameters hold large data structures, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. Remember, NOCOPY is a hint, not a directive.

DECLARE
  TYPE Numlist IS TABLE OF emp.empno%TYPE;
  Id Numlist;
  PROCEDURE GetEmp (pDeptNo IN NUMBER, pId OUT NOCOPY Numlist) IS
  BEGIN
    SELECT empno BULK COLLECT INTO pId
     FROM emp
    WHERE deptno = pDeptNo;
  END;
BEGIN
    GetEmp(10,Id);
    FOR i IN Id.FIRST..Id.LAST LOOP
      dbms_output.put_line(Id(i));
    END LOOP;
END;
/

Dynamic SQL to reference database objects that do not exist at compilation ?

Dynamic SQL can solve this problem, because dynamic SQL allows you to wait until runtime to specify the table names you need to access. For example, in the next sample you might allow a user to specify the name of the table at runtime with a dynamic SQL query.

CREATE OR REPLACE PROCEDURE ShowSal (TabName VARCHAR2) IS
  TYPE CurType IS REF CURSOR;
  cEmp     CurType;
  sQuery   VARCHAR2(200);
  nId      NUMBER := 7369;
  nSal     emp.sal%TYPE;
BEGIN
  sQuery := 'SELECT sal FROM ' || TabName || ' WHERE empno = :Id';
  OPEN cEmp FOR sQuery USING nId;
  LOOP
    FETCH cEmp INTO nSal;
    EXIT WHEN cEmp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(nSal));
  END LOOP;
  CLOSE cEmp;
END;
/

EXECUTE IMMEDIATE to execute dynamic SQL (Very good new feature)

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The following examples are from the Oracle Manual.

DECLARE
   SqlStmt    VARCHAR2(100);
   PlSqlBlock VARCHAR2(200);
   nDeptNo    NUMBER(2)    := 50;
   strDname   VARCHAR2(15) := 'MIGRATION';
   strLoc     VARCHAR2(15) := 'LUCERNE';
   EmpRec     emp%ROWTYPE;
BEGIN
   SqlStmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE SqlStmt INTO EmpRec USING 7788;

   PlSqlBlock := 'BEGIN ShowSal(:TabName); END;';
   EXECUTE IMMEDIATE PlSqlBlock USING 'emp';

   EXECUTE IMMEDIATE 'DROP TABLE special_bonus';
   EXECUTE IMMEDIATE 'CREATE TABLE special_bonus (id NUMBER, amt NUMBER)';

   SqlStmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE SqlStmt USING nDeptNo, strDname, strLoc;

   EXECUTE IMMEDIATE 'DELETE FROM dept
      WHERE deptno = :n' USING nDeptNo;

   SqlStmt := 'ALTER SESSION SET SQL_TRACE TRUE';
   EXECUTE IMMEDIATE SqlStmt;
END;
/

In the example below, a stand-alone procedure accepts the name of a database table (such as 'emp') and an optional WHERE-clause condition (such as 'sal > 2000'). If you omit the condition, the procedure deletes all rows from the table. Otherwise, the procedure deletes only those rows that meet the condition.

CREATE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition IN VARCHAR2 DEFAULT NULL) AS
   where_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGIN
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
END;
/

execute delete_rows('emp','sal > 2000');

We think, that EXECUTE IMMEDIATE is one of the most important new features in Oracle 8i

Invoker- and Definer Rights

Definer Rights (Default)

By default, stored procedures and SQL methods execute with the privileges of their definer, not their invoker. Such definer-rights routines are bound to the schema in which they reside. A user of a definer-rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer-rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The procedure's owner must have all the necessary object privileges for referenced objects. Note, that the EXECUTE privilege cannot be granted to ROLES for definer rigths procedures.

Advantage of Definer Rights

You can use definer-rights procedures to add a level of database security. By writing a definer-rights procedure and granting only EXECUTE privilege to a user, the user can be forced to access the referenced objects only through the procedure (that is, the user cannot submit ad hoc SQL statements to the database).

Invoker Rights

An invoker-rights procedure executes with all of the invoker's privileges, including enabled ROLES. A user of an invoker-rights procedure needs privileges on the underlying objects that the procedure accesses for which names are resolved in the invoker's schema.

Advantage of Invoker Rights

Invoker-rights routines let you centralize data retrieval. They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.

Example on Invoker Rights

CONNECT sys/manager;
CREATE ROLE employees;
GRANT employees TO scott;
GRANT employees TO blake;
DISCONNECT;

CONNECT scott/tiger;
CREATE SEQUENCE deptno_seq;
GRANT SELECT ON deptno_seq TO employees;

CREATE PROCEDURE create_dept (pDept VARCHAR2, pLoc VARCHAR2)
  AUTHID CURRENT_USER AS
BEGIN
  INSERT INTO dept
  VALUES (deptno_seq.NEXTVAL, pDept, pLoc);
END;
/
GRANT EXECUTE ON create_dept to EMPLOYEES;
DISCONNECT;

CONNECT sys/manager;
CREATE PUBLIC SYNONYM deptno_seq FOR scott.deptno_seq;
DISCONNECT;

CONNECT scott/tiger;
EXECUTE create_dept ('Free Climbing','Swiss Oberland');
SELECT * FROM dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH        DALLAS
       30 SALES           CHICAGO
       40 OPERATIONS     BOSTON
        1 Free Climbing  Swiss Oberland

DISCONNECT;

CONNECT blake/lion;
EXECUTE create_dept ('Alps Climbing','Everest');

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH        DALLAS
       30 SALES           CHICAGO
       40 OPERATIONS     BOSTON
        4 Alps Climbing  Everest

DISCONNECT;

This example shows, each user can see only his/her own data ! This behaviour is completly different from normal Definer Rights, where each user will see the data in Scott's DEPT table. Use AUTHID CURRENT_USER AS to define Invoker Rights. Please not, that Invoker Rights can be granted to ROLES, as this example shows.

Fine Grained Access Control

The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement. A dynamic predicate for a table or view is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface.

For example:

DBMS_RLS.ADD_POLICY ('scott','emp','emp_policy','secusr','emp_sec','select');

Whenever EMP table, under SCOTT schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under SECUSR schema). This returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on whatever session environment variables are available during the function call. These variables usually appear in the form of application contexts.

The server then produces a transient view with the text:

SELECT * FROM scott.emp WHERE P1

Here, P1 is the predicate returned from the EMP_SEC function. The server treats the EMP table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary. If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users also do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer's right.

Example

We need a policy on scott's EMP table, which shows only the rows belonging to the caller of the query. Therefore we have to create the following predicate: (e.g. ename='SCOTT')

connect scott/tiger
select 'ename='''|| sys_context('userenv','session_user') ||''''
from dual;

• Create Security User

CONNECT system/manager
CREATE USER secusr IDENTIFIED by secusr
DEFAULT TABLESPACE users;
GRANT connect,resource,execute_catalog_role TO secusr;

• Create Policy Function

CONNECT secusr/secusr;
CREATE OR REPLACE FUNCTION emp_sec (schema IN varchar2, tab IN varchar2)
  RETURN VARCHAR2 AS
BEGIN
  RETURN 'ename='''|| sys_context('userenv','session_user') ||'''';
END emp_sec;
/

• Enable the Policy Function

EXECUTE dbms_rls.add_policy('scott',
'emp','emp_policy','secusr','emp_sec');
DISCONNECT;

• Test the Policy Function

CONNECT scott/tiger;
SELECT * FROM scott.emp;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL
--------- ---------- --------- --------- --------- ---------
     7788 SCOTT      ANALYST         7521 09-DEC-82     3000

CONNECT sys/manager;
SELECT * FROM scott.emp;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL
--------- ---------- --------- --------- --------- ---------
     7369 SMITH      CLERK           7369 17-DEC-80      800
     7499 ALLEN      SALESMAN        7369 20-FEB-81     1600
     7521 WARD       SALESMAN        7369 22-FEB-81     1250
     7566 JONES      MANAGER         7369 02-APR-81     2975

CONNECT system/manager;
SELECT * FROM scott.emp;

no rows selected

• Drop the Policy Function again

CONNECT secusr/secusr;
execute dbms_rls.drop_policy('scott','emp','emp_policy');

Important Note

The User SCOTT can only see his own rows, the User SYSTEM can see NO rows, but the User SYS can see ALL rows. Policies are not enabled for the SYS User !

Policy Functions without predefined Context

If you want to define your own predicate in the RETURN value, set it to ( ' 1 = 2 ' ) for false
and ( ' 1 = 1 ' ) for true.

....
IF (....) THEN
  RETURN ('1=2');
ELSE
  RETURN ('1=1');
END;

DBMS_RLS.ADD_POLICY

DBMS_RLS.ADD_POLICY (
   object_schema   IN VARCHAR2 := NULL,
   object_name     IN VARCHAR2,
   policy_name     IN VARCHAR2,
   function_schema IN VARCHAR2 := NULL,
   policy_function IN VARCHAR2,
   statement_types IN VARCHAR2 := NULL,
   update_check    IN BOOLEAN  := FALSE,
   enable          IN BOOLEAN  := TRUE);

Application Context (Security Policy)

Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).

To define an application context:

1. Create a PL/SQL package with functions that validate and set the context for your application. You may wish to use an event trigger on login to set the initial context for logged-in users.

2. Use CREATE CONTEXT to specify a unique context name and associate it with the PL/SQL package you created.

3. Reference the application context in a policy function implementing fine-grained access control or create an event trigger on login to set the initial context for a user. For example, you could query a user's employee number and set this as an "employee number" context value.

4. Reference the application context.

Click here for an example, which shows the steps above.

Read Only Databases

The whole database can now be opened READ-ONLY (Before 8i, only Tablespaces). Note that Disk-Sorts in the TEMP tablespaces are not possible, therefore you must set SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE to values big enough to process the sort operation in the memory.

sqlplus /nologin

SQL> connect sys as sysdba;
Enter password:
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area    96394640 bytes
Fixed Size                     64912 bytes
Variable Size               13369344 bytes
Database Buffers           81920000 bytes
Redo Buffers                 1040384 bytes
Database mounted.

SQL> alter database open read only;
Database altered.

SQL> connect scott/tiger
Connected.
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> insert into dept values (50,'DBTEAM','Seftigen');

insert into dept values (50,'DBTEAM','Seftigen')
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TAB'

Multiplexed Archiving up to 5 Destinations

In Oracle8 it was possible to define an alternate archive log destination with LOG_ARCHIVE_DUPLEX_DEST. In Oracle 8i it's possible to have up to 5 alternate destinations. You cannot use LOG_ARCHIVE_DEST_x with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST.

Enter in INIT.ORA (Best if all alternate destinations are on different, fast disks).

log_archive_dest_1 = "location=/u01/db/SOL3/arc1 mandatory reopen=300"
log_archive_dest_2 = "location=/u01/db/SOL3/arc2 mandatory reopen=300"
log_archive_dest_3 = "location=/u01/db/SOL3/arc3 mandatory reopen=300"
log_archive_dest_4 = "location=/u01/db/SOL3/arc4 optional reopen=300"
log_archive_dest_5 = "location=/u01/db/SOL3/arc5 optional reopen=300"
log_archive_min_succeed_dest = 4

sqlplus /nologin
SQL> connect sys as sysdba;
SQL> startup mount;
SQL> archive log list;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system switch logfile;

From now the archive logs will be saved to the specified destinations;

Standby Database

Oracle8i contains an enhanced feature that automatically keeps a standby database synchronized with your production database. This new feature, called the Automated Standby Database (ASD), greatly reduces the amount of manual work database administrators must perform.

The new featues in 8i are:

• Offline Redo-Logs are automatically transfered to the Stand-By database.
• The copied offline Redo-Logs are automatically applied to the Stand-By database.
• Stand-By database may be open "Read-Only".
• More than one Stand-By database supported.
• Stand-By database isn't 100% synchronized with Production database (Online Redolog is missing). 

Block Checking

DB_BLOCK_CHECKING is used to control whether block checking is done for transaction managed blocks. As early detection of corruptions is ueful, and has minimal, if any, performance impact. As the parameter is dynamic, it provides more flexibility than events 10210 and 10211, which it will ultimately replace.

If DB_BLOCK_CHECKSUM is set to TRUE,  a checksum is calculated and stored in the cache header of every data block when writing it to disk. Checksums will be verified when a block is read only if this parameter is TRUE and the last write of the block stored a checksum. Every log block will also be given a checksum before it is written to the current log. Warning:Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead.

We normaly use the following Settings in the INIT.ORA File:

db_block_checking = true
db_block_checksum = false

Other possibilities to check the integrity of the data can be done with:

• ANALYZE TABLE ... VALIDATE STRUCTURE.
• Export to /dev/null.

There exists a Package so called DBMS_REPAIR .... hopefully we never have to use this ....

Using LogMiner to Analyze Online and Archived Redo Logs

LogMiner allows you to read information contained in online and archived redo logs. LogMiner is especially useful for identifying and undoing logical corruption. LogMiner processes redo log files, translating their contents into SQL statements that represent the logical operations performed to the database. The V$LOGMNR_CONTENTS view then lists the reconstructed SQL statements that represent the original operations (SQL_REDO column) and the corresponding SQL statement to undo the operations (SQL_UNDO column). Apply the SQL_UNDO statements to roll back the original changes to the database.

Steps to perform a LogMiner Analyze:

• Create Dictionary File (Data Dictionary is written to a File)

CONNECT sys/manager
execute dbms_logmnr_d.build(
  'sol3_dict_file.ora','/u01/db/SOL3/adm/utl');

• Start Analyze (in the example with two offline redolog files)

BEGIN
dbms_logmnr.add_logfile(
  options => dbms_logmnr.NEW,
  logfilename => '/u01/db/SOL3/arc/SOL3_86.arc');
dbms_logmnr.add_logfile(
  options => dbms_logmnr.ADDFILE,
  logfilename => '/u01/db/SOL3/arc/SOL3_87.arc');
dbms_logmnr.start_logmnr(
  dictfilename => '/u01/db/SOL3/adm/utl/sol3_dict_file.ora');
END;
/

• Show result of LogMiner Analyze

SELECT TO_CHAR(TIMESTAMP,'HH24:MI.SS') "Time",
       USERNAME,
       OPERATION,
       SQL_REDO,
       SQL_UNDO
FROM v$logmnr_contents
WHERE username = 'SCOTT';

• Stop Analyze

EXECUTE dbms_logmnr.end_logmnr();

Event Handling and Event Attributes

System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes. With Oracle, this tracking can be combined with database event notification. You can obtain certain event-specific attributes when a trigger is fired. These attributes can be used as standalone functions.

The following Event Attributs are defined (from Oracle Manual).

System Events with Trigger Examples

Example: Log when database is started

CREATE TABLE event_table (EVENT_TEXT VARCHAR2(255));

CREATE OR REPLACE TRIGGER event_startup
AFTER STARTUP ON DATABASE
BEGIN
 INSERT INTO event_table VALUES
  ('USER: '||USER||' '||SYS.SYSEVENT||' AT '
  ||TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI.SS'));
END;
/
SELECT * FROM event_table;
EVENT_TEXT
-----------------------------------------
User SYS STARTUP at 16.12.1999 15:37.04
USER: SYS STARTUP AT 17.12.1999 11:58.14

CREATE TRIGGER log_errors 
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR (1017)) THEN
    -- Do something for ORA-1017
  ELSE
    -- Do something for the OTHERS errors
  END IF;
END;

Client Events with Trigger Examples

Client events are the events related to user logon/logoff, DML, and DDL operations

Example: On Logon and On Create Trigger

CREATE OR REPLACE TRIGGER Sys.On_Logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO sys.event_table VALUES
  ('USER: '||USER||' '||SYS.SYSEVENT||' AT '
  ||TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI.SS'));
END;
/

CREATE OR REPLACE TRIGGER Scott.On_Create
AFTER CREATE ON SCHEMA
BEGIN
INSERT INTO sys.event_table VALUES
  (sys.dictionary_obj_type||': '||sys.dictionary_obj_name
  ||' created by: '||USER||' at: '
  ||TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI.SS'));
END;
/

SELECT * FROM sys.event_table;

EVENT_TEXT
-------------------------------------------------------
TABLE:  TEST created by: SCOTT at: 19.12.1999 16:29.37
USER:   SCOTT LOGON AT 19.12.1999 16:28.58

Important

If there is an error in a LOGON Trigger, nobody (not even SYS) may connect to the database. Connect with INTERNAL and drop the trigger may help in this situation.

Faster Startup after Instance Crash

DB_BLOCK_MAX_DIRTY_TARGET = n Blocks specifies the number of buffers that can be dirty (modified and different from what is on disk) in the buffer cache. It indirectly specifies a rough limit on the number of blocks that must be read during crash and instance recovery.

Optimizer Statistics and Create Index

Create the Cost based optimizer statistics when you create the index

create index addr_indx on customer(ADDRESS) compute statistics;

Manipulate Optimizer Statistics with DBMS_STATS

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in the data dictionary or in a table created in the user's schema. Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

DBMS_STATS is divided into three main sections:

• Setting or Getting Statistics
• Transferring Statistics
• Gathering Optimizer Statistics

Gathering Optimizer Statistics

The following procedures enable the gathering of certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:

GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS

Gather Table Statistics:

EXEC DBMS_STATS.GATHER_TABLE_STATS
  (ownname=>'SCOTT',tabname=>'EMP',estimate_percent=>60);

Gather Schema Statistics:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS
  (ownname=>'SCOTT',block_sample=>FALSE,estimate_percent=>60,
   method_opt=>'FOR ALL COLUMNS',degree=>5);

Manipulate Optimizer Statistics

You can transfer statistics from the dictionary to a user stat table and from a user stat table to the dictionary. The statistics can be manipulated in the user stat table.

Create the Export Table:

EXEC DBMS_STATS.CREATE_STAT_TABLE
  (ownname=>'SCOTT',stattab=>'SCOTT_STAT');

Retrieve statistics for a particular table and stores them in the user stat table:

EXEC DBMS_STATS.EXPORT_TABLE_STATS
  (ownname=>'SCOTT',tabname=>'EMP',stattab=>'SCOTT_STAT');

Manipulate statistics in the user stat table:

EXEC DBMS_STATS.SET_TABLE_STATS
  (ownname=>'SCOTT',tabname=>'EMP',stattab=>'SCOTT_STAT',
  numrows=>'5000',statown=>'SCOTT');

Now transfer manipulated statistics from the user stat table to the dictionary.

EXEC DBMS_STATS.IMPORT_TABLE_STATS
   (ownname=>'SCOTT',tabname=>'EMP',stattab=>'SCOTT_STAT',statown=>'SCOTT');

Preserve Execution Plans (Outlines)

Plan Stability preserves execution plans in "Stored Outlines".

Advantage

• Plan Stability prevents from affecting the performance characteristics of your applications. (Changes to the optimizer mode settings and changes to parameters affecting the sizes of memory structures such as SORT_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE)
• Plan Stability is most useful when you cannot risk any performance changes in your applications.
• Stored outlines stabilizes the generated execution plan in subsequent Oracle releases.
• Plan Stability also facilitates migration from the rule-based optimizer to the cost-based optimizer when you upgrade to a new version of Oracle.
• You may use different Plans for the Day- and Night Processing.

Stored Outlines will be saved in the OUTLN schema.

Create Outline manually

The following statement creates a stored outline called SALARIES, stored in the category SPECIAL.

CREATE OR REPLACE OUTLINE
  salaries FOR CATEGORY special
  ON SELECT ename, sal FROM emp;

Use Outlines

When this same SELECT statement is subsequently compiled, if the USE_STORED_OUTLINES parameter is set to SPECIAL, Oracle generates the same execution plan as was generated when the outline SALARIES was created.

ALTER SESSION SET USE_STORED_OUTLINES = special;

Deaktivate Outlines

ALTER SESSION SET USE_STORED_OUTLINES = false;

Show stored Outlines

SELECT ol_name,creator FROM outln.ol$;
SELECT * FROM user_outlines;

Rebuild stored Outline

ALTER OUTLINE salaries REBUILD;

OUTLN_PKG Package

The OUTLN_PKG package contains the functional interface for subprograms associated with the management of stored outlines. A stored outline is the stored data that pertains to an execution plan for a given SQL statement. It enables the optimizer to repeatedly recreate execution plans that are equivalent to the plan originally generated along with the outline.The data stored in an outline consists, in part, of a set of hints that are used to achieve plan stability.

Drops all outlines that have not been used since they were created

execute OUTLN_PKG.DROP_UNUSED;

Enable Parallel Automatic Tuning

Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with Decision Support Systems (DSS). You can also implement parallel execution on certain types of OLTP (Online Transaction Processing) and hybrid systems. The optimal setting of the different parameters which are involved for parallel execution are extremly complex. The new initSID.ora parameter PARALLEL_AUTOMATIC_TUNING = TRUE helps to simlify this complex task.

When PARALLEL_AUTOMATIC_TUNING is TRUE, Oracle automatically sets other parameters as shown below. For most systems, you do not need to make further adjustments to have an adequately tuned, fully automated parallel execution environment.

Parameters calculated by PARALLEL_AUTOMATIC_TUNING:

• PARALLEL_ADAPTIVE_MULTI_USER
• PROCESSES
• SESSIONS
• PARALLEL_MAX_SERVERS
• LARGE_POOL_SIZE
• PARALLEL_EXECUTION_MESSAGE_SIZE

Caution !

Be very careful when you set this parameter in OLTP and Hybrid Systems. This Parameter is mainly used for DSS Systems. Read chapter 26 "Tuning Parallel Execution" in the Oracle Tuning Guide !

Locally managed Tablespaces

Typically, tablespaces are "dictionary mapped," which means that such tablespaces rely on SQL dictionary tables to track space utilization. Locally managed tablespaces, on the other hand, use bit maps (instead of SQL dictionary tables) to track used and free space. Therefore there is a smaller overhead for extent allocation and coalesce of free extents is no more necessary. Note that, SMON coalesce adjacient extents in dictionary mapped tablespaces only if PCTINCREASE > 0.

Space Management with locally managed tablespaces

Extents can be allocated UNIFORM (Each extent has a fixed size, typically 1MByte) or AUTOALLOCATE ((Extents with minimal size of 64K). For the SYSTEM tablespace, you can specify EXTENT MANGEMENT LOCAL in the CREATE DATABASE command. If the SYSTEM tablespace is locally managed, other tablespaces in the database can be dictionary-managed but you must create all rollback segments in locally-managed tablespaces.

Normal Tablespace (System Managed)

CREATE TABLESPACE users2
  DATAFILE '/u01/db/SOL3/usr/SOL3_users2.dbf' SIZE 5M REUSE
  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
  INITIAL 1M
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  PERMANENT
  ONLINE;

Temporary Tablespace (UNIFORM Managed)

CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/db/SOL3/tmp/SOL3_temp2.dbf' SIZE 5M REUSE
  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

Note:

• DEFAULT STORAGE is not supported
  ORA-25143: default storage clause is not compatible with allocation policy.
• CREATE DATABASE doesn't support EXTENT MANGEMENT LOCAL yet.
  ORA-00933: SQL command not properly ended.

Read Only Tablespaces

Read Only Tablespaces are not new in 8.1.5, but id you try to switch to an Read-Only Tabelspace on a busy System you no longer get an error, Oracle waits until the ressource becomes free.

alter tablespace users read only;
alter tablespace users read write;

Transportable Tablespaces

You can use transportable tablespaces to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. Moving data via transportable tablespaces can be much faster than performing either an import/export or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

Current Limitations

• The source and target DB must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle DBs, or you can transport tablespaces between NT Oracle DBs. However, you cannot transport a tablespace from a SUN Solaris DB to an NT DB.
• The source and target DB must have the same DB block size.
• The source and target DB must use the same character set.
• You cannot transport a tablespace to a target DB in which a tablespace with
  the same name already exists.
• Currently, transportable tablespaces do not support: snapshot/replication , function-based indexes, Scoped REFs, domain indexes (a new type of index provided by extensible indexing), 8.0-compatible advanced queues with multiple recipients.

Example

Step 1: Pick a Self-contained Set of Tablespaces

You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces.

execute dbms_tts.transport_set_check('USERS',TRUE);

Here, transport_set_check is a PL/SQL routine in the PL/SQL package DBMS_TTS:

PROCEDURE transport_set_check(
  ts_list IN varchar2, incl_constraints IN boolean)

ts_list: List of tablespace names separated by comma
incl_constraints: TRUE: If one would like to take constraints into consideration, FALSE: otherwise.

After invoking this PL/SQL routine, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view will be empty. If the set of tablespaces is not self-contained, this view lists all the violations

select * from transport_set_violations;

Step 2: Generate a Transportable Tablespace Set on Source DB

After identifying the self-contained set of tablespaces you want to transport, generate a transportable set by performing the following tasks:

• Make all tablespaces in the set you are copying read-only.

alter tablespace users read only;

• Invoke the Export utility and specify which tablespaces are in the transportable set. Although the
  Export utility is used, only data dictionary structural information is exported. Hence, this operation is
  even quicker for a large tablespace.

exp userid=sys/manager transport_tablespace=y
tablespaces=users triggers=n constraints=y
grants=y file=trans_users.dmp

• Copy the datafiles to a separate storage space to the target database.

cd /u01/db/SOL3/usr (On Source DB)
ftp rabbit
cd /disk2/db/RAB1/usr
put SOL3_users1.dbf

• If necessary, put the tablespaces in the copied set back into read-write mode as follows:

alter tablespace users read write; (On Source DB)

Step 3: Plug In the Tablespace Set

To plug in a tablespace set, perform the following tasks:

• Go to the target database, rename database file if necessary

mv SOL3_users1.dbf RAB1_users1.dbf

• Plug in the tablespaces and integrate the structural information using the following import statement:

imp userid=sys/manager transport_tablespace=y
datafiles='/u01/db/SOL3/usr/RAB1_users1.dbf'
tablespaces=users file=trans_users.dmp

alter tablespace users read write; (On Target DB)

Notes

When you specify TABLESPACES, the supplied tablespace names are compared to those in the export file. Import returns an error if there is any mismatch. Otherwise, tablespace names are extracted from the export file.

If you do not specify FROMUSER and TOUSER, all database objects (such as tables and indexes) will be created under the same user as in the source database. Those users must already exist in the target database. If not, import will return an error indicating that some required users do not exist in the target database.

You can use FROMUSER and TOUSER to change the owners of objects. For example, if you specify FROMUSER=dcranney,jfee TOUSER=smith, williams, objects in the tablespace set owned by dcranney in the source database will be owned by smith in the target database after the tablespace set is plugged in. Similarly, objects owned by jfee in the source database will be owned by williams in the target database. In this case, the target database does not have to have users dcranney and jfee, but must have users smith and williams.

Partitioning Enhancements

For an in depth discussion with examples on Oracle8 and 8i Partitioning click here.

Online Index Creation and Rebuild

Previously, when creating an index on a table there has always been a DML S-lock on that table during the index build operation, which meant you could not perform DML operations on the base table during the build. Now, with the ever-increasing size of tables and necessity for continuous operations, you can create and rebuild indexes online--meaning you can update base tables at the same time you are building or rebuilding indexes on that table. Note, though, that there are still DML SS-locks, which means you cannot perform other DDL operations during an online index build.

Indexes can now be created and rebuild online, without to lock the corresponding table.

CREATE UNIQUE INDEX pk_cdr ON cdr(bkg_id) ONLINE;

ALTER INDEX pk_cdr REBUILD
  STORAGE (INITIAL 1M NEXT 1M) PCTFREE 0
  TABLESPACE idx
  ONLINE;

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values. Append COMPRESS = i, where i = Number of attributes in the index for Non-Unique indexes and
i = Number of attributes - 1 for Unique indexes.

CREATE INDEX  emp_ename ON emp (ename)
TABLESPACE users
COMPRESS 1;

Function Based Indexes (FBI)

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. You can create a function-based index as either B*-tree or bitmap index.

Using FBI with SQL-Function

• Enable QUERY_REWRITE in the INIT.ORA, so the COST based optimizer can use all kinds of FBIs.

query_rewrite_enabled = true
query_rewrite_integrity = trusted

• Grant query rewrite Privilege

connect system/....
grant query rewrite to scott;

• Create the FBI with Compute Statistics or ....

CREATE INDEX upper_ename_idx ON emp (UPPER(ename)) COMPUTE STATISTICS;

• ... ANALYZE the table

ANALYZE TABLE emp COMPUTE STATISTICS FOR ALL COLUMNS;

• Check the Execution Plan

SQL> set autotrace on explain
SQL> SELECT * FROM emp WHERE UPPER(ename) = 'KING';

Execution Plan
---------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=52874)
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=10 Card=52874)
INDEX (RANGE SCAN) OF 'UPPER_ENAME_IDX' (NON-UNIQUE) (Cost=1 Card=52874)

Note to use a function-based index:

• The table must be analyzed after the index is created.

• The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

Using FBI with SQL-Operation

CREATE INDEX sal_comm_idx ON emp (sal + comm);
SELECT * FROM emp WHERE sal + comm < 4000;

Using FBI with PL/SQL Function

CREATE OR REPLACE FUNCTION sal_com (numSal  IN NUMBER,
                                    numCom  IN NUMBER)
RETURN NUMBER DETERMINISTIC IS
  sal_com  NUMBER;
BEGIN
  sal_com := numSal + numCom;
  RETURN sal_com;
END;
/

CREATE INDEX sal_com_idx ON emp (sal_com(sal,comm)) COMPUTE STATISTICS;
SELECT sal_com(sal,comm) FROM emp WHERE sal_com(sal,comm) < 6000;

DETERMINISTIC Functions

In some cases the optimizer can use a previously calculated value rather than executing a user-written function. This is only safe for functions that behave in a restricted manner. The function must always return the same output return value for any given set of input argument values.

Index only Tables and Secondary Index Support

You can move your existing data into an index-organized table and do all the operations you would perform in an ordinary table. There exists no real "table" in an IOT, all the data are packed in a B*Tree Index. IOT's have NO physical rowid, therefore a secondary index on an index-organized table cannot be based on a physical rowid which is inherently fixed. Instead, a secondary index for an index-organized table is based on what is called the logical rowid (UROWID). A logical rowid has no permanent physical address and can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid. Use IOT when all or nearly all attributes are in the index (Intersection Tables).

Example

In the following example, an IOT table is created which is often used by Web text-search engines.

CREATE TABLE DocIdx (
  Token  VARCHAR2(20),
  DocId  NUMBER,
  Hits   NUMBER,
CONSTRAINT Pk_DocIdx PRIMARY KEY (Token, DocId))
ORGANIZATION INDEX TABLESPACE idx;

Now create the secondary Index (new in 8.1.5)

CREATE INDEX DocHitsId ON DocIdx(Hits);

Now use the logical ROWID (UROWID)

DECLARE
  rid UROWID;
BEGIN
  INSERT INTO DocIdx VALUES ('Or80', 2, 30)
  RETURNING Rowid INTO rid;

  UPDATE DocIdx SET Token='Or81'
   WHERE ROWID = rid;
END;

Drop Column Support

Dropping columns lets you free space in the database by dropping columns you no longer need, or by marking them to be dropped at a future time when the demand on system resources is less.

• Drop a normal column without any Constraints

ALTER TABLE emp DROP COLUMN job;

• Drop a Primary Key:

ALTER TABLE emp DROP COLUMN empno CASCADE CONSTRAINT;

• Drop a Column in a very big Table, when you get Rollback Segments Problems

ALTER TABLE verybig DROP COLUMN col CHECKPOINT 10000;

• Set one or more Colums unused for later Drop

ALTER TABLE emp SET UNUSED COLUMN mgr;
ALTER TABLE emp DROP UNUSED COLUMNS;

Moving Tables to other Tablespaces

The move table clause lets you relocate data of a nonpartitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

ALTER TABLE emp
MOVE TABLESPACE users
STORAGE (INITIAL 64K NEXT 64K) PCTFREE 0;

For an index-organized table rebuilds the index-organized table's primary key index B*-tree. Specify ONLINE that DML operations on the index-organized table are allowed during rebuilding of the table's primary key index B*-tree.

ALTER TABLE iot_tab
MOVE TABLESPACE tab
STORAGE (INITIAL 64K NEXT 64K) PCTFREE 0
ONLINE;

Skip locked rows with SKIP LOCKED

Usually Oracle locks the rows for other session in a SELECT ... FOR UPDATE statement. This behaviour can be desireable or not. Another approach offers the SKIP LOCKED clause. This means, that only unlocked rows will be displayed for all other sessions.

Resource Management

The Database Resource Manager allows the database administrator to have more control over resource management than would normally be possible through operating system resource management alone. Using this facility, the database administrator can:

• Guarantee groups of users a minimum amount of processing resources, regardless of the load or number of users in other groups on the system.
• Distribute available processing resources by allocating percentages of CPU time to different users and applications. For example, in a data warehouse, a higher priority may be given to ROLAP applications than to batch jobs.
• Limit the degree of parallelism that a set of users can use.
• Configure an instance to use a particular plan for allocating resources. A database administrator can dynamically change the plan, for example, from a daytime setup to a nighttime setup, without having to shutdown and restart the instance.

Please consult the Manual Oracle8i Concepts for more Information, this topic is too specific to present here.

Automatic Instance Registration

Database instances register themselves with the listener when started. Prior to this release, information about the instance had to be manually configured in the LISTENER.ORA file. Database instance registration is comprised of two elements:

• Service registration, which provides the listener with instance information, such as database service names and instance names.
• MTS dispatcher registration, which provides dispatcher information to the listener

When an instance is started, initialization parameters are read from the INITSID.ORA. One of these initialization parameters is the service name. By default, an instance background process registers instance information to a listener on the local machine. If a listener is started after the instance, there may be a delay before the instance and dispatchers are registered. The instance will attempt to connect to the listener periodically. Similarly, if a listener gets an incoming request before an instance is registered, the listener may reject the request.

Setup initSID.ora

### Automatic Instance Registration
### -------------------------------
# Service registration, which provides the listener with instance information
# such as database service names and instance names.

instance_name = SOL3
service_names = SOL3,PROD

Setup TNSNAMES.ORA

### NetService Descriptor in TNSNAMES.ORA
### -------------------------------------
# NetService in TNSNAMES.ORA replaces SID Parameter used
# in Oracle7 and Oracle8 Releases. NetService is defined as follows:
# NetService = <service_names from initSID.ora>.<db_domain>

SOL3.WORLD =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT = 1523))
     (CONNECT_DATA = (SERVICE_NAME = SOL3.WORLD))
  )

Load Balancing and Client Failover

Instance registration enables connection load balancing. Connection load balancing balances the number of active connections among various instances and dispatchers for the same service. This enables listeners to make their routing decisions based on how many connections each dispatcher has and on how loaded the nodes that the instances run.

Connection Load Balancing for Parallel Server

Connection load balancing evenly distributes the number of active connections among various instances and dispatchers for the same service. The load of a instance and dispatcher is determined by the number of connections. Connection load balancing is only enabled for an MTS environment.

Example: Connection Load Balancing with Failover for two Listeners in an Oracle 8i Environment

SOL3.WORLD =
  (DESCRIPTION =
     (LOAD_BALANCE = ON)
     (FAILOVER = ON)
     (ADDRESS = (PROTOCOL = TCP)(HOST = 161.72.194.130)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 161.72.194.131)(PORT = 1521))
     (CONNECT_DATA =
        (SERVICE_NAME = SOL3.WORLD)
     )
  )

  New Service Name Concept

Up to Oracle 8, the client was configured with the Oracle System Identifier (SID) of a database instance. This SID was then passed to the listener. The listener would then verify this information and permit or deny a connection. The SID was also used internally by the database as pointer to the System Global Area (SGA). While a SID identified a database instance, it did not identify a database. This limitation caused a database to have no more than one service associated with it.

In Oracle 8.1 multiple instances are supported, using the following new parameters in connect descriptors: SERVICE_NAME is typically the global database name, a name comprised of the database name and domain name, entered during installation or database creation. INSTANCE_NAME is typically the SID entered during installation or database creation. INSTANCE_NAME is optional, representing the name of instance and is used to uniquely identify a specific instance when multiple instances (Parallel Server) share c