Oracle 8 new features

Paritioning of tables and indexes

slave processes:

LGWR_IO_SLAVES = n (0 - n)

DBWR_IO_SLAVES = n (0 - n)

ARCH_IO_SLAVES = n (0 - n)

Off-line Redologfiles duplicate

MAXDATAFILES and DB_FILES

MAXLOGMEMBERS and MAX LOG FILES

Dynamic size of the control files: CONTROL_FILE_RECORD_KEEP_TIME = n

-- -------------------------------------------------------------------------------

Multiple data block Buffer pool: keep and recycle

CREATE TABLE keep_db_pool

(test NUMBER NOT NULL)

STORAGE (BUFFER_POOL keep INITIAL 10K NEXT 10K)

TABLESPACE tab;

CREATE TABLE keep_db_pool

(test NUMBER NOT NULL)

STORAGE (BUFFER_POOL recycle INITIAL 10K NEXT 10K)

TABLESPACE tab;

db_block_buffers = 7000

buffer_pool_keep = (buffers: 1500, lru_latches: 1)

buffer_pool_recycle = (buffers: 1000, lru_latches: 1)

-----------------------------------------------------------------------

Large pool for MTS session DATA and RMAN

large_pool_size = n bytes/K/M (0K - 2GB)

large_pool_min_alloc = n bytes/K/M (16K - 64M)

------------------------------------------------------------------------

Tablespace MINIMUM EXTENT

------------------------------------------------------------------------

NOLOGGING write (no Redo)

ALTER TABLESPACE temp_load NOLOGGING;

ALTER INDEX idx_big REBUILD NOLOGGING PARALLEL (DEGREE 4)

STORAGE (INITIAL 128M, NEXT 16M)

TABLESPACE idx;

CREATE TABLE emp_temp AS SELECT * FROM emp NOLOGGING;

-----------------------------------------------------------------------

New data types (BLOB, CLOB, BFILE)

DBMS_LOB

CREATE TABLE book (

title VARCHAR2(40),

author VARCHAR2(40),

text CLOB,

author_pic BLOB)

LOB(text,author_pic) STORE AS(

STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)

CHUNK 50

PCTVERSION 30

NOCACHE LOGGING

INDEX (STORAGE (INITIAL 1M NEXT 1M))

);

CREATE TABLE word_processing_docs (

doc_name VARCHAR2(100),

doc_content BFILE);

CREATE OR REPLACE DIRECTORY wp_docs

AS '/data/docs/wp_docs';

GRANT READ ON DIRECTORY wp_docs TO ppb;

-- ----------------------------------------------------------------------

Index only Tables (index Organized Tables) IOT's

CREATE TABLE my_intersection (

id1 NUMBER(15) NOT NULL,

id2 NUMBER(15) NOT NULL,

job VARCHAR2(500) NULL,

CONSTRAINT pk_my_intersection

PRIMARY KEY (id1,id2))

ORGANIZATION INDEX

STORAGE (INITIAL 1M NEXT 250K PCTINCREASE 0)

TABLESPACE idx

INCLUDING job

OVERFLOW TABLESPACE tab;

-- ---------------------------------------------------------------------------

Reverse Key Indexes

CREATE INDEX deptno_ename ON emp (deptno, ename) REVERSE;

-- ----------------------------------------------------------------------------

Inline (Immediate) Views

SELECT e.emp_count, d.dept_count

FROM (SELECT COUNT(*) emp_count FROM emp) e,

(SELECT COUNT(*) dept_count FROM dept) d;

-- ----------------------------------------------------------------------------

DML Return of values

set serveroutput on;

DECLARE

local_rowid ROWID;

BEGIN

INSERT INTO dept (deptno,dname,loc)

VALUES (99,'Akadia AG','Seftigen')

RETURNING ROWID INTO local_rowid;

dbms_output.put_line('local_rowid: ' || ROWIDTOCHAR(local_rowid));

END;

-- ----------------------------------------------------------------------------

Index Fast Full Scans (FFS) /*+ Index_FFS (table index) */

SELECT /*+ Index_FFS(grosse_tabelle my_index) */ COUNT(*)

FROM grosse_tabelle;

-- -----------------------------------------------------------------------------

New ROWID format

select rowid,ename from emp where deptno = 10;

OOOOOOFFFBBBBBBSSS

O = Data object Nummer

F = File Nummer

B = Block Nummer

S = Slot Nummer im Block

-- ---------------------------------------------------------------------------------

Index Rename

alter index PRICE_INDEX rename to PRICE_INDEX1;

-- -----------------------------------------------------------------------------------

CONNECT as SYS vs. CONNECT as INTERNAL

O7_DICTIONARY_ACCESSIBILITY = TRUE

NT: orapwd80 file=D:\Orant\Database\pwdDOR1.ora password=manager entries=5

UNIX: orapwd file=$ORACLE_HOME/dbs/orapwd password=manager entries=5

GRANT SYSDBA to scott;

CONNECT scott/tiger; /* Normal Connect */

CONNECT scott/tiger as SYSDBA; /* Privileged Connect for SHUTDOWN etc /*

-- -------------------------------------------------------------------------------------

Remote CONNECT as SYS

REMOTE_LOGIN_PASSWORD_FILE = EXCLUSIVE

v$pwfile_users

SVRMGR> connect sys/manager@RAB1 as sysdba;

Connected.

SVRMGR> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

------------------------------ ----- -----

INTERNAL TRUE TRUE

SYS TRUE TRUE

SCOTT TRUE FALSE

-- ------------------------------------------------------------------------------------

Oracle Password Verification Functions:

Deferred Constraints

alter table emp

add constraint fk_deptno

foreign key(deptno) references dept (deptno)

deferrable initially immediate;

alter table emp

add constraint fk_deptno

foreign key(deptno) references dept (deptno)

deferrable initially deferred;

alter table emp

add constraint fk_deptno

foreign key(deptno) references dept (deptno)

not deferrable initially immediate;

alter table emp

add constraint fk_deptno

foreign key(deptno) references dept (deptno)

not deferrable initially deferred;

ERROR at line 3:

ORA-02447: cannot defer a constraint that is not deferrable

-- ----------------------------------------------------------------------------------

Cascade Update failed:

Normally constraints is not deferrable initially immediate (Oracle-7)

SQL> update dept set deptno = 21

where deptno = 20;

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated

child record found

Now we change Constraint to "deferrable initially immediate" (Oracle -8)

SQL> set constraint fk_deptno deferred;

SQL> update dept set deptno = 21 where deptno = 20;

Database is inconsistent now.

SQL> update emp set deptno = 21 where deptno = 20;

SQL> commit;

SQL> set constraint fk_deptno immediate;

-- -----------------------------------------------------------------------------------------

ALTER TABLE MODIFY CONSTRAINT (Oracle 8.1.5)

With Oracle 8i it's possible to modify the state of an existing constraint. For example change from INITIALLY DEFERRED to INITIALLY IMMEDIATE.

ALTER TABLE emp MODIFY CONSTRAINT emp_foreign_key INITIALLY IMMEDIATE;

-- --------------------------------------------------------------------------------------------

ON DELETE SET NULL (Oracle 8.1.5)

determines how Oracle automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table

CASCADE specifies that Oracle removes dependent foreign key values.

SET NULL specifies that Oracle converts dependent foreign key values to NULL.

alter table emp

add constraint fk_deptno

foreign key(deptno) references dept (deptno)

on delete set NULL;

-- -----------------------------------------------------------------------------------------------

Deferred PRIMARY KEYs and UNIQUE CONSTRAINTs

DEFERRED PRIMARY KEY clause:

ALTER TABLE emp ADD (

CONSTRAINT pk_emp

PRIMARY KEY (empno)

DEFERRABLE INITIALLY DEFERRED

USING INDEX

TABLESPACE idx

STORAGE

(INITIAL 100K

NEXT 100K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 2)

)

/

select c.constraint_name,

i.search_condition,

c.table_name,

c.column_name,

i.r_constraint_name,

i.status

from user_cons_columns c,

user_constraints i

where i.table_name = c.table_name

and i.constraint_name = c.constraint_name

and c.table_name = 'EMP';

Constraints of Tabelle: EMP

Constraint Column

Name Text Name

------------------------ ------------------------- ---------------

NN_EMPNO EMPNO IS NOT NULL EMPNO

PK_EMP EMPNO

select i.table_owner,

i.index_name,

c.table_name,

c.column_name,

decode(i.uniqueness,'UNIQUE','YES','NONUNIQUE','NO','???') uni,

i.tablespace_name

from user_ind_columns c, user_indexes i

where i.table_name = c.table_name

and i.index_name = c.index_name

and i.table_name like upper('EMP');

Indexes of Tabelle: EMP

Table Index Column Uniq-

Owner Name Name ness Tablespace

---------- ------------------- ------------------- ----- ----------

SCOTT PK_EMP EMPNO NO IDX

ALTER TABLE EMP DISABLE CONSTRAINT pk_emp;

-- ------------------------------------------------------------------------------

Enforced (Enable Novalidate) Constraints

constrainst status:Enabled,Disabled,Enable Novalidate

ALTER TABLE emp DISABLE CONSTRAINT check_salary;

ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT check_salary;

-- -------------------------------------------------------------------------------

Disable Validate (Oracle 8.1.5)

ALTER TABLE emp DISABLE VALIDATE CONSTRAINT emp_primary_key CASCADE;

insert into emp (empno,deptno) values (7369,10);

ORA-25128: No insert/update/delete on table with

constraint (SCOTT.EMP_PRIMARY_KEY) disabled and validated

ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT emp_primary_key;

-- --------------------------------------------------------------------------------

Parallel SELECTs

parallel_min_server

parallel_max_servers

parallel_server_idle_time

parallel_min_percent

for SELECTs:

ALTER TABLE emp PARALLEL DEGREE (5);

ALTER TABLE emp NOPARALLEL;

SELECT /*+ PARALLEL (emp,5) */ *

FROM emp;

-- ---------------------------------------------------------------

Parallele DMLs (INSERT, DELETE, UPDATE)

ALTER SESSION ENABLE PARALLEL DML;

CREATE TABLE emp_new AS

SELECT * FROM emp

WHERE 1 = 2;

INSERT /*+ PARALLEL (emp_new,5) */

INTO emp_new

SELECT /*+ PARALLEL (emp,2) */ *

FROM emp;

COMMIT;

ALTER SESSION DISABLE PARALLEL DML;

-- ---------------------------------------------------------------

Bulk Operation using the Oracle-8 DBMS_SQL Package

DECLARE

vCursor INTEGER := DBMS_SQL.Open_Cursor;

vCount INTEGER;

vStatement VARCHAR2(2000);

vEmps DBMS_SQL.Varchar2_Table;

BEGIN

vStatement := 'UPDATE emp SET sal = sal + 1' ||

' WHERE ename = :ThisEname';

vEmps(1) := 'KING';

vEmps(2) := 'MILLER';

vEmps(3) := 'HUBER';

vEmps(4) := 'SCOTT';

vEmps(5) := 'ADAMS';

BEGIN

DBMS_SQL.Parse(vCursor,vStatement, DBMS_SQL.Native);

DBMS_SQL.Bind_Array(vCursor,'ThisEname',vEmps);

vCount := DBMS_SQL.Execute(vCursor);

DBMS_SQL.Close_Cursor(vCursor);

DBMS_OUTPUT.PUT_LINE('vCount = ' || TO_CHAR(vCount));

END;

END;

-- ------------------------------------------------------------------------------

Basic Object Types:

Simple Types,

Composite Object Types

Self Referencing Types

CREATE TYPE paycheck_type AS OBJECT (

check_number NUMBER(20),

payer VARCHAR2(40),

payee VARCHAR2(40),

date_issued DATE,

payment_authorization VARCHAR2(10),

payer_account_number VARCHAR2(20),

routing_number VARCHAR2(20),

payment_amount NUMBER(10));

DROP TABLE paycheck_tab;

CREATE TABLE paycheck_tab (

empid VARCHAR2(10) NOT NULL,

emp_check paycheck_type,

CONSTRAINT pk_paycheck_rel

PRIMARY KEY (empid));

To insert data into an object column, the user must refer to the object type that defines the column. This reference is called a constructor. The constructor is used for insertion of data into object tables as well. The use of insert statements on a relational and object table with use of constructors is listed in the following code block

INSERT INTO paycheck_tab

VALUES (39283,

paycheck_type (4596854,'Acme','HANSON',

TO_DATE('14.07.2001','DD.MM.YYYY'),'YES','59439585','00584857479',2016));

SELECT p.emp_check.check_number

FROM paycheck_tab p

WHERE empid = 39283;

DROP TABLE paycheck_obj;

CREATE TABLE paycheck_obj

OF paycheck_type;

INSERT INTO paycheck_obj

VALUES (

paycheck_type (4596854,'Acme','HANSON',

TO_DATE('14.07.2001','DD.MM.YYYY'),'YES','59439585','00584857479',2016));

SELECT check_number FROM paycheck_obj

WHERE routing_number = '00584857479';

-- --------------------------------------------------------------

Object type with member functions:

DROP TYPE payroll_type;

CREATE TYPE payroll_type AS OBJECT (

check_number NUMBER(20),

payer VARCHAR2(40),

payee VARCHAR2(40),

date_issued DATE,

payment_authorization VARCHAR2(10),

payer_account_number VARCHAR2(20),

routing_number VARCHAR2(20),

payment_amount NUMBER(10),

MEMBER FUNCTION adjust_amount (p_number IN NUMBER,

p_amount IN NUMBER) RETURN NUMBER,

PRAGMA RESTRICT_REFERENCES (adjust_amount, WNDS));

DROP TYPE BODY payroll_type;

CREATE TYPE BODY payroll_type AS MEMBER FUNCTION adjust_amount

(p_number IN NUMBER,

p_amount IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_number - p_amount;

END;

END;

-- Object Table

DROP TABLE payroll_obj;

CREATE TABLE payroll_obj

OF payroll_type;

INSERT INTO payroll_obj

VALUES (

payroll_type (4596854,'Acme','HANSON',

TO_DATE('14.07.2001','DD.MM.YYYY'),'YES','59439585','00584857479',2016));

The applications can then refer to the methods for an object type using the same dot notation used to reference the attributes. The following PL/SQL statement demonstrates the use of a method from the PAYROLL type to update the data in an attribute. The value( ) operation in the following PL/SQL block is designed to return all attribute values stored in an object so that those values may be placed into a variable declared to be the same type as the object table.

set serveroutput on;

DECLARE

my_payroll payroll_type;

my_pay_amount NUMBER(10);

BEGIN

--

SELECT VALUE(p)

INTO my_payroll

FROM payroll_obj p

WHERE check_number = 4596854;

--

DBMS_OUTPUT.PUT_LINE('my_payroll.payer = ' || my_payroll.payment_amount);

--

UPDATE payroll_obj

SET payment_amount = my_payroll.adjust_amount(my_payroll.check_number,200)

WHERE check_number = my_payroll.check_number;

--

SELECT VALUE(p)

INTO my_payroll

FROM payroll_obj p

WHERE check_number = 4596854;

--

DBMS_OUTPUT.PUT_LINE('my_payroll.payer = ' || my_payroll.payment_amount);

--

END;

-- ---------------------------------------------------------------------------------

Reference Types

So far, all object types discussed have been simple types containing attributes declared with all scalar datatypes. Oracle8 also allows the definition of two new classes of datatypes, called reference types and collection types. These two datatypes are designed to allow object types to have referential integrity on a one-to-one and one-to-many level. However, reference and collection types extend the concept of referential integrity to a new level. In relational databases, foreign keys provide referential integrity between columns of the same datatype containing the same data. Reference datatypes are used to provide a one-to-one relationship between a row of an object table or object type column of a relational table and another object table or object type column of a relational table. Collection types are designed to store a grouping or collection of like-structured elements, to provide a one-to-many relationship in the same situation. This section will explain reference and collection types in further detail and provide syntax and examples for the creation of each.

-- Create Object Type

DROP TYPE paycheck_type;

CREATE TYPE paycheck_type AS OBJECT (

check_number NUMBER(20),

payer VARCHAR2(40),

payee VARCHAR2(40),

date_issued DATE,

payment_authorization VARCHAR2(10),

payer_account_number VARCHAR2(20),

routing_number VARCHAR2(20),

payment_amount NUMBER(10));

-- Create Oject table

DROP TABLE paycheck_obj;

CREATE TABLE paycheck_obj

OF paycheck_type;

INSERT INTO paycheck_obj

VALUES (

paycheck_type (4596854,'Acme','HANSON',

TO_DATE('14.07.2001','DD.MM.YYYY'),'YES','59439585','00584857479',2016));

Developers can use the reference type to define a "foreign key relationship" between two objects. The reference type can reference all columns in the table for a particular row—it is a pointer to a particular object within an object table. The definition of a reference type should include a definition of scope—the table to which the reference type will refer. A special keyword called REF should be used for defining the reference type. The definition of the reference type is demonstrated with the following code block:

CREATE TYPE debit_type AS OBJECT (

acc_wd_num NUMBER(10),

debit REF paycheck_type);

-- Create Object Table

CREATE TABLE debit_obj

OF debit_type

(SCOPE FOR (debit) IS paycheck_obj);

INSERT INTO debit_obj

SELECT 1, REF(pc)

FROM paycheck_obj pc

WHERE pc.check_number = 4596854;

Selecting the reference type itself using the ref( ) operation produces not the data in the object, but the pointer used to refer to the object. The user can use the pointer value created as the reference type and execute a special function called deref( ) to produce the actual values from the pointer to those values.

SELECT acc_wd_num,DEREF(d.debit)

FROM debit_obj d

WHERE d.debit.check_number = 4596854;

-- ---------------------------------------------------------------

Collection Types (VARRAY)

CREATE TYPE phone_type AS OBJECT (

location VARCHAR2(20),

phone_num VARCHAR2(10));

-- VARRAY Type

CREATE TYPE phone_array AS VARRAY(10)

OF phone_type;

-- Object Type of VARARY

CREATE TYPE staff_type AS OBJECT (

empid VARCHAR2(10),

name VARCHAR2(40),

phone phone_array);

-- Object Table via VARARY

CREATE TABLE staff

OF staff_type;

-- Data Inserted

INSERT INTO staff VALUES

('1234','Zahn',phone_array(phone_type('Seftigen','3450240')));

INSERT INTO staff VALUES

('1235','Steiner',phone_array(phone_type('Linden','4857664')));

-- --------------------------------------------------------------------------------

Collection Types (Nested Tables)

The other collection type is the nested table. A nested table is exactly that—a table within a table. The nested table architecture is exceptionally suited for applications that in Oracle7 have parent/child tables with referential integrity.

--

-- Nested Tables

--

CREATE TYPE phone_type AS OBJECT (

location VARCHAR2(20),

phone_num VARCHAR2(10));

-- Object Type

CREATE TYPE phone_tab AS TABLE

OF phone_type;

-- Nested Table

CREATE TABLE staff (

empid VARCHAR2(10),

name VARCHAR2(40),

phone_details phone_tab)

NESTED TABLE phone_details STORE AS phone_details_tab;

-- Data Inserted

INSERT INTO staff VALUES

('1234','Zahn',phone_tab(phone_type('Seftigen','3450240')));

INSERT INTO staff VALUES

('1235','Steiner',phone_tab(phone_type('Linden','4857664')));

Nested tables and variable-length arrays share many similarities, but also have many differences. The differences between the nested table and a variable-length array can be identified as follows. The VARRAY works well for storing like units of information. These units ideally have few attributes, all of which may be populated and to which a specific order can be placed. Consider the example given of employee annual paycheck information. Each element in the array corresponds directly to the pay week for the year, in which order is important. With the presence of a limit attribute, the VARRAY also stores only a limited number of elements, while the nested table may store an unlimited number of objects, just as the base object table can. Another key difference between VARRAY and nested table data is indexing. An object column composed of VARRAY types cannot be indexed, while a nested table can be indexed. Storage for VARRAY data is also integrated with the storage segments of the data in the object table, up to a certain size. Data in nested tables is stored in another segment.

-- -------------------------------------------------------------------------------

Object Views and Instead of Trigger

CREATE TABLE employee (

empid VARCHAR2(10) NOT NULL,

lastname VARCHAR2(30),

firstname VARCHAR2(30),

salary NUMBER(15,4),

CONSTRAINT pk_employee

PRIMARY KEY (empid));

CREATE TABLE address (

empid VARCHAR2(10) NOT NULL,

street1 VARCHAR2(30),

street2 VARCHAR2(30),

city VARCHAR2(30),

state_prov VARCHAR2(30),

postcode VARCHAR2(30),

CONSTRAINT pk_address

PRIMARY KEY (empid));

CREATE TYPE address_type AS OBJECT (

street1 VARCHAR2(30),

street2 VARCHAR2(30),

city VARCHAR2(30),

state_prov VARCHAR2(30),

postcode VARCHAR2(30));

/

CREATE TYPE employee_type AS OBJECT (

empid VARCHAR2(10),

lastname VARCHAR2(30),

address address_type);

-- Object View and Object Types

CREATE OR REPLACE VIEW employee_obj_view OF employee_type

WITH OBJECT OID (empid) AS

SELECT E.empid,E.lastname,

address_type(A.street1,A.street2,A.city,A.state_prov,A.postcode)

FROM employee E, address A

WHERE E.empid = A.empid;

-- Instead of Trigger

CREATE OR REPLACE TRIGGER empl_trigger INSTEAD OF

INSERT ON employee_obj_view FOR EACH ROW

BEGIN

INSERT INTO employee (empid,lastname)

VALUES (:NEW.empid,:NEW.lastname);

INSERT INTO address (empid,street1,street2,city,state_prov,postcode)

VALUES (:NEW.empid,

:NEW.address.street1,

:NEW.address.street2,

:NEW.address.city,

:NEW.address.state_prov,

:NEW.address.postcode);

END;

-- Data inserted

INSERT INTO employee_obj_view VALUES

(49384,'MANFRAN',address_type('506 Pudding Street',

'Apt. Q','Moan','WY','70506'));

-- ------------------------------------------------------------------------------

Advanced Queuing By DBMS_QUEUE

DBMS_AQ.ENQUEUE

DBMS_AQ.DEQUEUE

AQ_TM_PROCESSES = 1

AQ_USER_ROLE

AQ_ADMINISTRAOR_ROLE

cf: DBMS_PIPE, DBMS_ALERT

-- --------------------------------------------------------------------------

Recovery Manager RMAN

CONTROL_FILE_RECORD KEEP_TIME

catrman.sql