Oracle's Parallel Server option allows multiple instances running on different nodes to access the same database (see Figure 27.1).
Figure 27.1 : Oracle Parallel Server allows multiple instances to access the same database.
Only one instance has access to the database in single-instance mode (also known as exclusive mode). Multiple instances accessing the same database provide the following advantages:
| Use the INIT.ORA file for different instances |
By keeping different INIT.ORA files for each Oracle instance in an OPS environment, you can optimize each instance's performance depending on its desired workload. |
Oracle uses global locks to preserve data integrity and consistency while allowing concurrent read/write access to the same data files. To enable simultaneous access to the data files and to implement global locks, Oracle Parallel Server requires the following functionality at the hardware level:
Oracle Parallel Server is available on several platforms where multinode systems are available from hardware vendors. Because the database in an Oracle Parallel Server environment is accessed by all instances running on all nodes, all data files, control files, and online redo log files need to be accessible to all nodes.
| Instances share files in an OPS environment |
Instances in an Oracle Parallel Server environment share all the data files and control files. However, only one instance can write to a set of online redo logs assigned to it at startup. In addition, each instance has its own initialization parameter file. |
Various vendors have implemented this feature differently. These approaches can be divided into two categories:
Figure 27.2 : Disks in a shared-disk system are connected to nodes via a high-speed interconnect.
Figure 27.3 : Shared-nothing systems don't have a common resource.
To coordinate concurrent access to the data files and to synchronize the data in the memory of all nodes, OPS uses the Integrated Distributed Lock Manager (IDLM). IDLM requires the high-speed interconnect between nodes for messaging traffic generated for communication among nodes for synchronization.
Oracle Parallel Server also requires the following special software components:
As mentioned earlier, the use of Oracle Parallel Server option increases system availability (because of the increased redundancy) and provides more system resources to interact with the database.
| OPS offers increased system availability |
Increased system availability in an OPS environment stems mainly from the fact that if one instance of the database is down, chances are that other instances accessing the same database will make it available for users. |
The increased system resources can be used to increase the throughput. You can do the following by using the additional CPU and memory available on the additional nodes:
Additional system resources (to access the same database) comes with an additional cost of synchronization among the concurrent activities. Some increased resources are used to meet the overhead of the synchronization. Thus, the net amount of additional system resources available for application processing is as follows:
additional system resources available for processing = resources added - resources to synchronize concurrent access
Therefore, OPS is advantageous as long as the cost of synchronization remains low. If the cost of synchronization becomes comparable to the resources added, the use of OPS isn't justified.
The resources consumed for synchronization depends on Oracle's internal architecture. A significant part of this cost, however, depends on the nature of the workload and the application architecture. OPS has been found suitable in the following situations:
Oracle8 is an object-relational database because it allows user-defined object types. Oracle8 lets you define an object's attribute and the methods associated with it. For example, consider a part to be an object; its attribute can then be defined as part number, description, stock location, current inventory, and so on. Finding the location of a part, increasing the inventory on arrival of the part from the supplier, decreasing the inventory when the part is consumed, and so on are the methods you can associate with a part.
| Security and privileges of objects |
Object types are owned by the user who creates them and follow the Oracle's standard security and privilege mechanism. A user must be granted the EXECUTE privilege on the object types by the owner before he or she can use an object type defined by another user. |
If you're involved with designing applications with a relational database, you might wonder why an object type should be used. Consider a large company, where each division and location has its own installed computer systems and information systems division. Division A needs to define a part number field for its application and decides that it should be a 12-digit number. Also consider that Division B independently defines the part number field to be VARCHAR2(12). Obviously, there's a lack of communication between the two groups. This type of inconsistency can cause problems while transferring the data between the applications. This problem won't arise if the corporate DBA defines a part_type object and all the divisions use the already-defined part_type objects in their applications. Thus, defining a part type promotes standards and uniformity. The standard method to manipulate the part_type object can also be defined centrally and reused by the individual divisions. The use of object types has the following advantages:
An object type has a specification and a body. The specifications consist of the object's attributes and the declaration of intended methods to be used with the objects. The body consists of PL/SQL code for the methods associated with the object.
Consider the case of a research organization that needs to define an author object type. You could define an author object type as follows:
create or replace type author_type as object ( Last_name varchar2(20), First_Name varchar2(20), author_title varchar(20));
When it's defined, you can see the attributes of an object datatype by using SQL*Plus's DESC command:
SQL> desc author_type Name Null? Type ------------------------------- -------- ---- LAST_NAME VARCHAR2(20) FIRST_NAME VARCHAR2(20) AUTHOR_TITLE VARCHAR2(20)
The preceding CREATE command creates an author_type datatype, which can further be referenced anywhere you would refer a standard datatype such as a number, CHAR, or VARCHAR2. For example, the following CREATE datatype command creates a paper_type datatype and uses the preceding author_type datatype definition:
 | The predefined author_type datatype |
 | The AUTHOR_TYPE datatype as part of the new object type definition |
Note the AUTHOR_TYPE datatype listed under the describe output's type column. When defined, the datatype can be used in an application. The following example creates a table by using the paper_type datatype:
| Altering and dropping object types |
When it's defined, you can drop a user object type by using the DROP TYPE command. You can't change an object type's existing attributes, but you can add new member subprogram specifications. |
| The defined datatype |
Similarly, you can use paper_type in a PL/SQL program:
|
| The defined datatype |
You need to write a statement similar to the following in order to insert a row into the table paper_sales:
insert into paper_sales values (123456789,999999,
paper_type(1111,'Research Paper',
author_type('Last','First','Professor'),'Oracle8',100),10);
To reference an element in a datatype, you have to prefix it with the datatype name. For example, to select the paper# paper name from the paper_sales table, you would write a query similar to the following:
SQL> select sales_order#,x.paper.paper#,x.paper.name
from paper_sales x ;
SALES_ORDER# PAPER.PAPER# PAPER.NAME
------------ ------------ ------------------------------
123456789 1111 Research Paper
The following query selects the author's last and first name and title:
select sales_order#,x.paper.paper#,x.paper.name,
x.paper.author.last_name,
x.paper.author.first_name,
x.paper.author.author_title
from paper_sales x;
SALES_ORDER# PAPER.PAPER# PAPER.NAME PAPER.AUTHOR.LAST_NA
------------ ------------ ------------ --------------------
PAPER.AUTHOR.FIRST_N PAPER.AUTHOR.AUTHOR_
-------------------- --------------------
123456789 1111 Research Paper Last
First Professor
An object table is a database table that's defined with an object type only-it doesn't use any relational columns. You can nest one object table inside another via an object type. Nested tables are useful in a master-detail relationship. The following code creates an object type employee_type and then uses it to create an object table employee_list:
create or replace type employee_type as object(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2));
create or replace type employee_list
as table of employee_type ;
An object table is shown as the list of object datatype in the DESC command's output:
|
| The object table |
The following SQL command creates the nested table dept by using the already created employee_list object table:
| Using nested tables |
Nested tables are a convenient way to store tables with master-detail relationship. In this example, information about all the employees in a department is contained within the department table itself. |
create table dept (
dept_no number(3),
dept_name varchar(30),
employees employee_list)
nested table employees store as employees_list_table;
SQL> desc dept
Name Null? Type
------------------------------- -------- ----
DEPT_NO NUMBER(3)
DEPT_NAME VARCHAR2(30)
EMPLOYEES EMPLOYEE_LIST
Use the following command to insert rows into nested table:
insert into dept values ( 20,'Sales', employee_list( employee_type (7369,'SMITH','CLERK','17-DEC-80',800,NULL), employee_type (7566,'JONES','MANAGER','2-APR-81',2975,NULL)));
To query contents of a nested table, you need to use the THE function:
select e_list.ename from THE (select employees from dept where dept_no=20) e_list;
A method is a PL/SQL procedure or function associated with the object. Oracle8 supports the following types of methods:
Oracle automatically creates a constructor method for an object so that you can perform DML functions on the object. By default, the object's constructor method has the same name as the object itself, and all the object's attributes are available as parameters of the constructor method. The following example shows how constructor methods are used to insert data in the paper_sales table:
| Constructor methods |
A member method is a stored procedure associated with an object type. Before you can define a member method, you need to associate it with the object as its attribute. You can do this in the CREATE TYPE statement or by using an ALTER TYPE statement:
create or replace type paper_type as object ( paper# number(10), name varchar2(30), author author_type, subject varchar2(20), price number(5), stock_qty number(5), member function STOCK_VALUE return number, PRAGMA RESTRICT_REFERENCES( stock_value , WNDS, WNPS));
The member function definition can also have input parameters (although none are shown here) and it needs to specify the type of single return value.
PRAGMA RESTRICT_REFERENCES is a compiler directive that specifies what type of operations the function can perform. You need to specify this directive to prevent it from doing unwanted modifications to the object. Four options are available for PRAGMA RESTRICT_REFERENCES:
| WNDS | Write no database state |
| WNPS | Write no package state |
| RNDS | Read no database state |
| RNPS | Read no package state |
As soon as it's defined, the methods associated with an object are shown when you describe the object:
SQL> desc paper_type Name Null? Type ------------------------------- -------- ---- PAPER# NUMBER(10) NAME VARCHAR2(30) AUTHOR AUTHOR_TYPE SUBJECT VARCHAR2(20) PRICE NUMBER(5) STOCK_QTY NUMBER(5) METHOD ------ MEMBER FUNCTION STOCK_VALUE RETURNS NUMBER
After specifying the method as the object type attribute, the next step is to define the body of the PL/SQL procedure for the intended function. The following PL/SQL code calculates the STOCK_VALUE:
create or replace type body paper_type as
member function stock_value
return number is
begin
return self.price * self.stock_qty;
end stock_value;
end ;
/
Type body created.
When defined, the methods can be called like a PL/SQL program.
However, each occurrence of an object has its own state. Because
methods are used to manipulate an object's particular state, the
methods need to reference a particular object occurrence. Listing
27.1 demonstrates how to use methods associated with an object.
Note the usage of the STOCK_VALUE member method associated
with the object PAPER_TYPE in lines 10 through 14.
LISTING 27.1 Using the MEMBER method
01: DECLARE
02: paper_type1 paper_type := paper_type(1,'Paper_1',
author_type('Last','First','Professor'),'Oracle8',100,10);
03: paper_type2 paper_type := paper_type(2,'Paper_2',
author_type('Last','First','Professor'),'Oracle8',200,20);
04: paper_type3 paper_type := paper_type(3,'Paper_3',
author_type('Last','First','Professor'),'Oracle8',300,30);
05: begin
06: dbms_output.put_line('Value for paper_type1 is:'||to_char(paper_type1.stock_value));
07: dbms_output.put_line('Value for paper_type2 is:'||to_char(paper_type2.stock_value));
08: dbms_output.put_line('Value for paper_type3 is:'||to_char(paper_type3.stock_value));
09: end ;
10: /
11: Value for paper_type1 is:1000
12: Value for paper_type2 is:4000
13: Value for paper_type3 is:9000
14: PL/SQL procedure successfully completed.
An object type's MAP method doesn't have any input parameters. It returns a single scalar value, which can be used to compare object datatypes in SQL statements having predicates for equality, between and in, order by, group by, distinct, unique, and so on. Listing 27.2 shows how to define the MAP method for the paper_type object.
| The MAP and ORDER methods |
The ORDER method is similar to the MAP method, with this difference: It can take one input argument. The MAP method, on the other hand, doesn't take any input argument. |
LISTING 27.2 Using the MAP method
01: create or replace type paper_type as object (
02: paper# number(10),
03: name varchar2(30),
04: author author_type,
05: subject varchar2(20),
06: price number(5),
07: stock_qty number(5),
08: map member function return_paper# return number,
09: member function STOCK_VALUE return number,
10: PRAGMA RESTRICT_REFERENCES( stock_value , WNDS, WNPS))
11: /
12:
13: Type created.
14:
15: SQL> desc paper_type
16: Name Null? Type
17: ------------------------------- -------- ----
18: PAPER# NUMBER(10)
19: NAME VARCHAR2(30)
20: AUTHOR AUTHOR_TYPE
21: SUBJECT VARCHAR2(20)
22: PRICE NUMBER(5)
23: STOCK_QTY NUMBER(5)
24:
25: METHOD
26: ------
27: MAP MEMBER FUNCTION RETURN_PAPER# RETURNS NUMBER
28:
29: METHOD
30: ------
31: MEMBER FUNCTION STOCK_VALUE RETURNS NUMBER
Note the use of the MAP keyword in front of the MEMBER FUNCTION keyword (line 8) to define the MAP method. When defined, the MAP method can be used to order objects of that type. Here, return_paper# can be used to order the paper_type object selected from the tables papers.
Listing 27.3 explains the usage of the MAP method to use the ORDER clause in the SQL statement.
| Using the SELF keyword |
The use of SELF keyword is optional here. It references the object's current state. It's useful if you need to pass the method's output as an argument to another method or procedure. |
LISTING 27.3 Using an ORDER clause in the
MAP method
01: create or replace type body paper_type as
02: member function stock_value
03: return number is
04: begin
05: return self.price * self.stock_qty;
06: end stock_value;
07: map member function return_paper# return number is
08: begin
09: return self.paper#;
10: end return_paper#;
11: end ;
12:
13: create table papers of paper_type;
14: insert into papers values (paper_type(1,'Paper_1',
author_type('Last','First','Professor'),'Oracle8',100,10));
15: insert into papers values (paper_type(6,'Paper_6',
author_type('Last','First','Professor'),'Oracle8',600,60));
16: insert into papers values (paper_type(8,'Paper_8',
author_type('Last','First','Professor'),'Oracle8',800,80));
17: insert into papers values (paper_type(9,'Paper_9',
author_type('Last','First','Professor'),'Oracle8',900,90));
18: insert into papers values (paper_type(4,'Paper_4',
author_type('Last','First','Professor'),'Oracle8',400,40));
19:
20: SQL> select * from papers order by 1 ;
21:
22: PAPER# NAME
23: ---------- ------------------------------
24: AUTHOR(LAST_NAME, FIRST_NAME, AUTHOR_TITLE)
25: -------------------------------------------------------
26: SUBJECT PRICE STOCK_QTY
27: ---------------------- ---------- ----------
28: 1 Paper_1
29: AUTHOR_TYPE('Last', 'First', 'Professor')
30: Oracle8 100 10
31:
32: 1 Paper_1
33: AUTHOR_TYPE('Last', 'First', 'Professor')
34: Oracle8 100 10
35:
36: 4 Paper_4
37: AUTHOR_TYPE('Last', 'First', 'Professor')
38: Oracle8 400 40
39:
40: 6 Paper_6
41: AUTHOR_TYPE('Last', 'First', 'Professor')
42: Oracle8 600 60
43:
44: 8 Paper_8
45:
46: AUTHOR_TYPE('Last', 'First', 'Professor')
47: Oracle8 800 80
48:
49: 9 Paper_9
50: AUTHOR_TYPE('Last', 'First', 'Professor')
51: Oracle8 900 90
52:
53:
54: 6 rows selected.
A view that allows you to define the object types via a relational table's underlying structure is known as an object view. Assume that you have an existing database table whose structure is as follows:
SQL> describe paper Name Null? Type ------------------------------- -------- ---- PAPER# NUMBER(10) NAME VARCHAR2(30) SUBJECT VARCHAR2(20) PRICE NUMBER(5) STOCK_QTY NUMBER(5)
You can create an object named paper_type_v based on this table's column:
create type paper_type_v as object ( paper# number, name varchar(30),subject varchar2(20), price number, stock_qty number);
Use the following command to create an object view by using the object paper_type_v:
create or replace view paper_ov of paper_type_v with object oid (paper#) as select paper#, name, subject, price, stock_qty from paper ;
You can either insert data into paper table by using the object view paper_type_v, or you can update the paper table directly:
insert into paper_ov values
(paper_type_v(1,'Paper One','Using Oracle',9.99,10));
Because you can create the abstract datatype to insert data into the table via the object view, you can associate methods to manipulate the data in the table and thus standardize the application code. You can also use the INSTEAD OF trigger to update data in an object view.
Whether it's a war or a bridge construction project, we humans have successfully adopted the "divide-and-conquer" strategy to manage things that become too large in volume and size. Oracle8's partition option is meant for a similar intent-for very large database objects. In a VLDB (very large database) environment, some objects tend to become very large, and it becomes increasingly difficult to manage them as a single entity. As the object gets bigger, the administrative tasks of restoring and backing up, loading data, building indexes, and so on become almost impossible; failure at any point forces you to repeat the operation. Imagine the ease of handling twenty 5GB pieces independently instead of handling a single 100GB table. Dividing the table into multiple partitions will provide the following advantages:
| Partitions are transparent to the end user |
When created, the underlying partitions of a table or an index are totally transparent to the application and users. However, users can use a partition in an SQL query's FROM clause instead of the table. |
Oracle8 allows you to partition only tables and the indexes. Other objects-clusters, nested tables, index-organized tables, snapshots, and snapshot logs-can't be partitioned. You also can't partition a table that's part of a cluster or one that contains a column of long, LOBs, or long raw datatypes or object datatypes. You can partition an index as long as it's not a cluster index or defined on a clustered table. You can partition a bitmap index only if it's a global bitmap index on a partitioned or a non-partitioned table. You can't create local bitmap indexes on individual partitions of a partitioned table.
A partitioned table can have a local or global partitioned or non-partitioned index. A non-partitioned table can contain a partitioned index. As shown in Figure 27.4, an index that refers to rows in a specific partition is known as the "local index." On the other hand, a "global index" refers to the rows from all partitions of a table. A global index can be partitioned.
Figure 27.4 : Partitions of a global index contain rows from any table partition.
You can partition a table while creating it. Logical attributes such as column names, datatypes, and constraints of all the partitions of an object must be the same; physical attributes (such as storage parameters) for all an object's partitions could be different for each partition, however. The SQL command in Listing 27.4 creates a partitioned table with these characteristics.
| Different partitions of an object can be placed in different tablespaces |
You can specify the storage parameters of each and every partition of a table and an index in the partition's storage clause, which can include tablespace, PCTFREE, PCTUSED, initial extent, next extent, and so on. |
LISTING 27.4 Creating a partitioned table
01: create table warehouse (
02: w_id number,
03: w_ytd number(12),
04: w_tax number(4),
05: w_name varchar2(10),
06: w_street_1 varchar2(20),
07: w_street_2 varchar2(20),
08: w_city varchar2(20),
09: w_state char(2),
10: w_zip char(9)
11: )
12: partition by range (w_id)
13: (
14: partition ware_P1 values less than (50)
15: tablespace TS_ware_P1
16: pctfree 95 pctused 4
17: storage (initial 2m next 1m minextents 12 pctincrease 0)
18: ,
19: partition ware_P2 values less than (100)
20: tablespace TS_ware_P2
21: pctfree 95 pctused 4
22: storage (initial 2m next 1m minextents 12 pctincrease 0)
23: ,
24: partition ware_P3 values less than (150)
25: tablespace TS_ware_P3
26: pctfree 95 pctused 4
27: storage (initial 2m next 1m minextents 12 pctincrease 0)
28: ,
29: partition ware_P4 values less than (200)
30: tablespace TS_ware_P4
31: pctfree 95 pctused 4
32: storage (initial 2m next 1m minextents 12 pctincrease 0)
33: )
34: initrans 4
35: pctfree 95 pctused 4
36: storage (initial 2m next 1m pctincrease 0);
The command in Listing 27.4 creates a warehouse table with four partitions. The first partition contains rows with warehouses numbered 1 to 50, the second partition contains rows for warehouses 51 through 100, and so on. This scheme of partitioning is called "range partitioned."
| Using ALTER to manage partitions |
The ALTER TABLE and ALTER INDEX commands allow you to add, delete, move, modify, rename, split, and truncate partitions as and when needed. |
You can use the command in Listing 27.5 to create local indexes
(line 4 of Listing 27.4) on each partition of this table.
LISTING 27.5 Creating a partitioned index
01: create unique index tpc.iwarehouse on tpc.warehouse(w_id)
02: pctfree 1 initrans 4
03: nologging
04: local
05: (partition iware_P1
06: tablespace TS_widx_P1
07: storage (initial 200K next 20K pctincrease 0),
08: partition iware_P2
09: tablespace TS_widx_P2
10: storage (initial 200K next 20K pctincrease 0),
11: partition iware_P3
12: tablespace TS_widx_P3
13: storage (initial 200K next 20K pctincrease 0),
14: partition iware_P4
15: tablespace TS_widx_P4
16: storage (initial 200K next 20K pctincrease 0));
Oracle's advance queuing is an interprocess messaging mechanism. Messaging between processes is normally synchronous in a two- or three-tier application environment. In this type of mechanism, the client and the server follow a real-time handshake mechanism where the client sends a request to the server and then waits for acknowledgment from the server. The server responds to the client and in turn waits for its acknowledgment.
| Queuing technology used in critical systems |
Queuing technology is used to develop/implement high-end transaction processing solutions in banking, trading, and reservation systems, as well as other critical applications. |
In each case, the sender of the message waits for the acknowledgment before proceeding with the next step. In certain types of processing this is a must; however, in many situations it may be acceptable for the sender to send the message and proceed without waiting for the acknowledgment. A queue mechanism can be used to exchange messages under such situations (see Figure 27.5).
Figure 27.5 : A queue acts as intermediate message storage between sender and receiver.
Oracle Advance Queuing's operating mechanism can be compared to that of a mailing service; its components are very similar to that of real-life mailing services. Oracle's Advance Queuing feature consists of queues (similar to mail boxes), messages composed of the control information and the data to be sent (the envelopes containing the letter and address information), enqueue and dequeue operations (dropping the letter in the mail box and retrieving the mail designated for you), and agents (the customers using the mail services).
Oracle creates two additional roles for advance queue operations:
Before users can use or administer the advance queuing features, they need to be granted these roles. The following command appoints the user aq_admin as the queue administrator:
$sqlplus sys/password
SQL> grant AQ_ADMINISTRATOR_ROLE to aq_admin with admin option ;
SQL> execute dbms_aqadm.grant_type_access('aq_admin');
Note that the grant_type_access procedure is used to grant necessary privileges for the administrative operations involving a multiple-consumer queue. After these commands are executed, the user aq_admin can then assign privileges so that another user can utilize the Advance Queue option. User aq_admin can also use the Advance Queue feature with this role. We will use this user for the sample commands.
Figure 27.6 shows the sequence of operations necessary for using the advance queues.
Figure 27.6 : Perform this sequence of operations to use the advance queues.
The DBMS_AQADM package's create_queue_table procedure is used to create a queue table (see Listing 27.6).
LISTING 27.6 Creating an object to be used as a
message
| Creates event_object_t object datatype to be used as message in advance queue |
The parameters for the create_queue_table procedure are as follows:
The command in Listing 27.6 creates sample_queue_table, which stores the user-defined datatype object event_object_t, which needs to be defined before queue creation. Users can find information about the user-owned queue from the data dictionary table USER_QUEUE_TABLES:
SQL> select * from user_queue_tables ; QUEUE_TABLE TYPE ------------------------------ ------- OBJECT_TYPE ----------------------------------------------------------- SORT_ORDER RECIPIEN MESSAGE_GROUP ---------------------- -------- ------------- USER_COMMENT -------------------------------------------------- SAMPLE_QUEUE_TABLE OBJECT AQ_ADMIN.EVENT_OBJECT_T PRIORITY, ENQUEUE_TIME SINGLE NONE sample queue table
| Need to see all queue tables? |
The DBA_QUEUE_TABLE dictionary view contains information about all the queue tables in the database. |
You can use the dbms_aqadm.drop_queue_table procedure to drop a queue table.
When the queue table is created, users can create the desired
queue by using the dbms_aqadm package's create_queue
procedure (see Listing 27.7).
LISTING 27.7 The create_queue procedure
01: begin
02: dbms_aqadm.create_queue
03: (
04: queue_name => 'SAMPLE_QUEUE',
05: queue_table => 'SAMPLE_QUEUE_TABLE',
06: comment => 'sample queue'
07: );
08: end ;
09: /
This procedure takes the following input parameters:
The data dictionary view USER_QUEUES has the following columns. You can choose either all the columns or select columns from this view to receive information about the queues you defined.
| Need to see all defined queues? |
The DBA_QUEUES view contains information about all queues defined in the database. |
SQL> desc user_queues Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) QUEUE_TABLE NOT NULL VARCHAR2(30) QID NOT NULL NUMBER QUEUE_TYPE VARCHAR2(15) MAX_RETRIES NUMBER RETRY_DELAY NUMBER ENQUEUE_ENABLED VARCHAR2(7) DEQUEUE_ENABLED VARCHAR2(7) RETENTION VARCHAR2(40) USER_COMMENT VARCHAR2(50)
Use the dbms_aqadm.drop_queue procedure to drop a queue.
After creating the queue, the queue administrator must start the queue before it can be used to store and retrieve messages. Use the dbms_aqadm.start_queue procedure to start the queue:
begin
dbms_aqadm.start_queue
(
queue_name => 'SAMPLE_QUEUE'
);
end;
/
The USER_QUEUES view's ENQUEUE_ENABLED and ENQUEUE_DISABLED columns show the start and stop status of user queues.
You can use the dbms_aqadm.stop_queue procedure to stop a started queue.
The DBMS_AQ package has enqueue and dequeue procedures used for storing and retrieving messages through a queue. The sender process agent sends the message by using the following parameters of the enqueue procedure:
| Input parameters for dequeue procedure |
The dequeue procedure of the DBMS_AQ package takes input parameters similar to the one used for enqueue procedure, except that it uses dequeue_options instead of enqueue_options. Dequeue options, however, aren't a mandatory parameter. |
Listing 27.8 shows how to send and receive messages by using advanced
queuing.
LISTING 27.8 How to use queues for sending and receiving
messages
01: file enqueu.sql
02: *****************************************************
03: declare
04: enqueue_options dbms_aq.enqueue_options_t;
05: message_properties dbms_aq.message_properties_t;
06: message_handle RAW(16);
07: message aq_admin.event_object_t;
08:
09: begin
10: dbms_aq.enqueue(
11: queue_name => 'sample_queue',
12: enqueue_options => enqueue_options,
13: message_properties => message_properties,
14: payload => message,
15: msgid => message_handle);
16:
17: commit;
18: end;
19: /
20: *******************************************************
21: file dequeu.sql
22: *******************************************************
23: declare
24: dequeue_options dbms_aq.dequeue_options_t;
25: message_properties dbms_aq.message_properties_t;
26: message_handle RAW(16);
27: message event_object_t;
28: begin
29:
30: dbms_aq.dequeue(
31: queue_name => 'sample_queue',
32: dequeue_options => dequeue_options,
33: message_properties => message_properties,
34: payload => message,
35: msgid => message_handle);
36:
37: dbms_output.enable;
38: dbms_output.put_line
39: ('Message retrieved from the queue>>>: '
40: || message.event_call || ' '||
41: message.num_arg||' ' ||
42: message.arguments);
43: commit;
44: end;
45: /
46: *******************************************************
47:
48: SQL> @enqueu.sql
49:
50: PL/SQL procedure successfully completed.
51:
52: SQL> @dequeu.sql
53: Message retrieved from the queue>>>: Sample Message 1 First sample message
54:
55: PL/SQL procedure successfully completed.
56:
57: SQL>
Many of the procedures available in this package have been discussed, and their usage should be obvious to you by now. The procedures not mentioned until now are as follows:
This package has procedures used for enqueue and dequeue operations. Please refer to the enqueue and dequeue example given earlier in this chapter for more details.
An RDBMS allows you to store and retrieve data in tabular form. This works very well for most business information that's in the form of numbers and a character string of smaller length. With the proliferation of computer technology and the popularity of personal computers, however, the soft copy of textual documents such as memos, letters, news item, bulletins, electronic mails, HTML files, résumés, reports, and so on make up a very important part of the business and need efficient and effective handling. Oracle8's ConText option allows you to store and manipulate these textual documents in the following ways:
| ConText is a separately priced cartridge |
The ConText option is released as separate cartridge with Oracle8. This cartridge not only has powerful test/document storage retrieval capabilities, but also has powerful linguistic capabilities. |
Table 27.1 shows the datatypes available.
| VARCHAR2 | Can store only 4,000 characters. Character strings functions can be used to manipulate the data. |
| LONG | Can store up to 4GB of data. Doesn't allow string functions to be used directly. Stored text can be manipulated via ConText option. |
| LONG RAW | Can store structured text documents. Doesn't allow string functions to be used directly. Stored text can be manipulated via the ConText option. |
Figure 27.7 depicts these two methods storing text in the picture form.
Figure 27.7 : The Oracle8 database ConText option stores documents internally and externally.
You can view the Oracle ConText option as a set of procedures and PL/SQL programs. Once it's installed and configured, the ConText option provides application (front-end) developers with a set of functions and procedures; these things allow developers to retrieve and manipulate the text data stored in an Oracle database.
| Text-enabling the database server |
Before you can execute a text query, you need to text-enable the database instance by setting the initialization parameter TEXT_ENABLE to TRUE or by using the ALTER SESSION set text_enable=true; command. |
Enable Oracle Server to process text documents
The query examples use the table white_papers. The code
in Listing 27.9 was used to set up the sample session.
LISTING 27.9 Preparing a table for ConText queries
| Creates the table |
| Loads document file in sample table with ctxload context loader (document files located in directory from where context loader is being invoked) |
 | Contents of the loader control file white_paper.ld |
 | Document files located in the directory from where the context loader is being invoked |
 | Creates policy for white_paper table |
 | Creates text index for white_paper table's already-defined policy |
| Start a context server with DDL personality before defining a policy |
To define a policy, you must have a context server with DDL personality running. You can check the status of the available context servers by using the ctxctl utility's STATUS command. |
After the operations in Listing 27.9 are complete, you can perform the text queries on the designated column of a given table. Now use the table white_paper to learn the use of ConText queries.
The following query looks for white papers that contain the word Manager:
select paper#,name,author from white_papers where contains (white_papers.papers,'Manager') > 0 ;
This query uses a simple method known as the one-step query. Because one-step queries are simpler to write, they're preferred for interactive use. They are slower, however, and the intermediate results aren't available for use. Oracle also offers two-step and in-memory query methods for advanced queries with improved performance; these query types are discussed later in this chapter.
The CONTAINS function lets you use several operators to combine multiple search words and conditions. Table 27.2 lists the important operators available.
| Selects the rows that contain both search conditions specified with AND | |
| Selects the rows that contain either search condition specified | |
| Selects a record if the score of the first record minus the score of the second record exceeds the threshold | |
| Selects a record if the sum of search scores exceed the threshold | |
| Near; returns the score based on proximity of the specified words | |
| Stem; selects the rows that have stem expansions of the specified word | |
| Soundex; returns a record that contains words sounding similar to the specified word | |
| Fuzzy; performs a fuzzy match (expands the word to include words spelled similarly) | |
| Threshold; selects all records having scores exceeding the threshold | |
| Weight; assigns different weights to the scores of the searches | |
| Max; selects specified number of highest scoring records | |
| Wildcard; performs a multiple-character wildcard search | |
| Performs a single-character wildcard search | |
| Grouping; specifies the order in which search criteria is evaluated | |
| Escape; allows you to include a reserved word in a query by including it within braces | |
| Searches for all words defined as synonyms of the specified word | |
| Searches for all words defined as related to the specified word | |
| Searches for words defined as broader terms for the specified word |
By using the CONTAINS function in a one-step query, you can perform the following text type queries:
| Scores returned by the CONTAINS function |
The CONTAINS function of Oracle's ConText option returns an integer that you can use to compare the results of multiple hits from the same CONTAINS function or from multiple CONTAINS functions. |
select paper#,name,author from white_papers where contains (white_papers.papers,'customer and packets') > 0;
select name,author from white_papers where
contains (white_papers.papers,'{dog and cat}') > 0;
select name,author from white_papers where contains (white_papers.papers,'zip not dog') > 0;
SQL > select name,author,score(0) from white_papers where contains (white_papers.papers,'dog near zip') > 0 SQL> / NAME AUTHOR SCORE0 -------------------- -------------------- ---------- IIII IIII 97 JJJJ JJJJ 93 KKKK KKKK 73
SQL> select name,author,score(0) from white_papers where contains (white_papers.papers,'dog near zip') > 75 SQL> / NAME AUTHOR SCORE0 -------------------- -------------------- ---------- IIII IIII 97 JJJJ JJJJ 93 select Name, Author from WHITE_PAPERS where CONTAINS (WHITE_PAPERS,'backup > 40') > 0;
SQL> select name,author,score(0) from white_papers where contains (white_papers.papers,'dog near zip:1') > 0;
SQL> select name,author,score(0) from white_papers where contains (white_papers.papers,'dog near zip:10') > 0;
select name,author,score(0) from white_papers where contains (white_papers.papers,'$cache') > 0;
SQL >select name,author,score(0) from white_papers where contains (white_papers.papers,'cac%') > 0;
By looking at the examples given in the previous sections, the CONTAINS function's simple syntax should be clear to you by now. The CONTAINS function's complete syntax is as follows:
| Specifies table name and column name to be queried |
| Specifies text to be searched and conditions |
| Labels score generated by CONTAINS; has a default value of 0 in queries with single CONTAINS function and requires a specified label in a query with multiple CONTAINS functions |
| Specifies policy to be used for columns having multiple policies defined |
The Oracle ConText option processes queries in two steps:
| Text tables require primary keys |
Any table that will be used for text searches by the Oracle ConText option must have a primary key defined before a policy and a text index can be created on it. |
If you try to create a text policy for a table that doesn't have a primary key, errors similar to the following are displayed:
SQL> execute ctx_ddl.create_policy
('PAPER2_policy','white_paper2.papers');
begin ctx_ddl.create_policy
('PAPER2_policy','white_paper2.papers'); end;
*
ERROR at line 1:
ORA-20000: ConText error:
DRG-10503: textkey must be specified -- table has no primary key
ORA-06512: at "CTXSYS.DRUE", line 180
ORA-06512: at "CTXSYS.CTX_DDL", line 1329
ORA-06512: at line 1
In a one-step query, the intermediate table isn't preserved by Oracle and you can't reuse its results. In a two-step query, the user creates the intermediate query before the query can be run. Use the following command to create the intermediate table:
create table query_temp( textkey varchar2(64), score number, conid number );
After creating the intermediate query table, you can proceed to execute the query:
execute ctx_query.contains('WH_PPR_POLICY','dog','QUERY_TEMP');
select a.score, a.conid, b.paper#, b.author, b.name
from query_temp a, white_papers b
where a.textkey = b.paper#;
The preceding example shows how an intermediate table is required
to execute a two-step query. In-memory queries use a cursor in
memory rather than in the database table to store the intermediate
result (see Listing 27.10).
LISTING 27.10 An in-memory query
| Defines the query search condition |
| Defines the cursor |
| Enables the output |
| Opens the cursor for intermediate results |
| Prints the column titles |
| Fetches the results in the cursor |
 | Prints the results to output |
 | Closes the cursor |
Setting up and managing the ConText option can be divided into the following tasks:
When the ConText option is installed, it automatically creates
CTXSYS and CTXDEMO users. The installation process
also creates CTXADMIN, CTXAPP, and CTXUSR
roles. Table 27.2 lists the functions and privileges of these
users and roles.
| CTXSYS | Owns the ConText data dictionary; is given the CTXADMIN and DBA roles; can start and stop the context server processes |
| CTXDEMO | Owns the sample ConText database objects; is given CTXAPP role |
| CTXADMIN | The most privileged context role; can perform all ConText administration tasks |
| CTXAPP | Can manage the ConText data dictionary, set up linguistics services, and perform text queries |
| CTXUSR | Should be given to a normal ConText user who needs to perform ConText queries |
You can create additional ConText option users and assign them the appropriate roles. See Chapter 9 "Creating and Managing User Accounts," for more information.
Context servers are the background processes dedicated to performing context-related operations. These processes must be started before you can perform any text operations. A context server can load data in the database, create text index, and perform text queries. You can assign specific tasks to be performed by each context server by specifying the personality mask while starting the context server. The ctxctl context control utility lets you manage the context servers interactively.
The following help session shows how start and stop context servers query their status by using the ctxctl utility:
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
*** ConText Option Servers Control ***
Servers on que_sun1.
Type help for a list of commands.
command> help
The following commands are available:
help [command] - commands information
status - show running servers
start n [ling | query | ddl | dml |
load]... - start n servers
stop [pid]... | all - stop server processes
quit - terminate ctxctl
exit - terminate ctxctl
command> start 1 query ddl dml
Enter ConText Option administrator password for V804><password>
Waiting for servers to start up..........
command> status
+-------+-------+-------+-------+------+-------+
| PID | LING. | QUERY | DDL | DML | LOAD |
+-------+-------+-------+-------+------+-------+
| 12981 | | X | X | X | |
+=======+=======+=======+=======+======+=======+
| Total | 0 | 1 | 1 | 1 | 0 |
+-------+-------+-------+-------+------+-------+
command> stop 12981
You can also use ctxsrv to start and stop the context servers from the command line:
ctxsrv -user ctxsys/password -personality QDML
| Personality and personality masks |
A context server process can perform any of the functions defined here. The type of functions performed by the server gives the server its personality. A personality mask is used to define the server's personality while starting the server. To change the personality of an existing context server, use the PL/SQL procedure execute ctx_adm.change_mask ('DRSRV_9999', 'QDML');, which allows an existing server, DRSRV_9999, to perform query, DDL, DML, and linguistics functions. |
You can divide the functions performed by context servers into five categories:
ctxsrv -user ctxsys/password -personality D
ctxsrv -user ctxsys/password -personality M
ctxsrv -user ctxsys/password -personality Q
ctxsrv -user ctxsys/password -personality L
| Linguistics queries |
The Oracle ConText option supports linguistics queries on text tables. These queries let you search documents with a search criteria containing gists and themes. A detailed discussion on setting up tables for these queries and creating these queries is beyond the scope of this book, and it's recommended that you refer to suitable Oracle documentation for this. |
The CTX_ALL_SERVERS view gives the status information of all started context servers. The CTX_SERVERS view provides the status information for the active context servers.
Context servers are responsible for executing the text commands received from all context-user client processes. Oracle has implemented a queuing mechanism to allow the processing of requests received from multiple clients by few servers.
| Viewing context queue information |
You can use the CTX_ALL_DML_QUEUE,CTX_ALL_DML_SUM, and CTX_ALL_QUEUE data dictionary views to see the status of various context queues. CTX_USER_DML_QUEUE,CTX_USER_DML_SUM, and CTX_USER_QUEUE data dictionary views can be used to see user-level activity. Oracle also offers the CTX_SVC and CTX_ADM packages, which you can use to view queue information and administer queues. |
Client processes put their processing requests in a queue known as the Text Request Queue. This queue is internally subdivided into multiple queues, with one queue for each function. Context servers regularly scan each request queue according to their personality and execute the desired operations. The context server informs the client process when the processing is finished. The Text Request Queue is used for query, DML, DDL, and linguistics operations only. The load operation isn't queued; instead, the server with the loader personality detects and processes the newly placed files as described in the preceding section.
The context loader is an easy-to-load utility that allows you
to load formatted and text data into an Oracle table's LONG
or LONG RAW columns. Listing 27.11 shows a simple usage
of the context loader-loading the data in the database.
LISTING 27.11 Using the context loader to load data
01: ctxload -user ctxdemo/ctxdemo -name white_papers
-file white_paper.ld -separate
02: -log $HOME/vlunawat/ctxload.log
03:
04: $ more white_paper.ld
05: <textstart:PAPER#=1,NAME='AAAA',AUTHOR='AAAAA'>
06: doc1.rtf
07: <textend>
08: <textstart:PAPER#=2,NAME='BBBB',AUTHOR='BBBB'>
09: doc2.rtf
10: <textend>
11: <textstart:PAPER#=3,NAME='CCCC',AUTHOR='CCCC'>
12: doc3.doc
13: <textend>
14: <textstart:PAPER#=8,NAME='HHHH',AUTHOR='HHHH'>
15: doc8.doc
16: <textend>
17: <textstart:PAPER#=8,NAME='HHHH',AUTHOR='HHHH'>
18: doc8.doc
19: <textend>
| Specifies the name of theloader log file |
On line 1, -user specifies the username and the password, -name specifies the name of the database table in which data is loaded, and -file specifies the name of the loader control file. The contents of the loader control file used in the chapter's example are shown here.
Also on line 1: -separate specifies that the contents of the load file contain a pointer to a file holding the document and that each document is to be loaded in one row of the table.
To store the documents in OS files rather than in database tables, follow along with these prompts:
SQL> create table docs (doc# number primary key,
author varchar2(20), paper long);
Table created.
SQL> execute ctx_ddl.set_attribute
('PATH','/home/oracle/ctxdemo');
PL/SQL procedure successfully completed.
SQL> execute ctx_ddl.create_preference
('COMMON_DIR','comment','OSFILE');
PL/SQL procedure successfully completed.
SQL> insert into docs values
(1,'ABC','/home/oracle/ctxdemo/doc2.doc');
1 row created.
SQL> insert into docs values
(2,'BCD','/home/oracle/ctxdemo/doc1.rtf');
1 row created.
SQL> commit ;
Commit complete.
SQL> exec ctx_ddl.create_policy('DOC_POLICY','DOCS.PAPER');
PL/SQL procedure successfully completed.
SQL> execute ctx_ddl.create_index('DOC_POLICY');
PL/SQL procedure successfully completed.
© Copyright, Macmillan Computer Publishing. All rights reserved.