Kill Session in Oracle

Retrieve session identifiers and session serial number (which uniquely identifies a session's objects):

select sid, serial# from v$session where username = 'USER'

kill the session:

alter system kill session 'sid,serial#'

Disconnect the session:

alter system disconnect session 'sid,serial#' post_transaction;

alter system disconnect session 'sid,serial#' immediate;

Read more »

Collection and Record In Oracle

A collection is an ordered group of elements, all of the same type. 

In a collection, the internal components are always of the same data type, and are called elements. We can access each element by its unique subscript. e.g. Lists and arrays.

A record is a group of elements, which can be of different types. 

In a record, the internal components can be of different data types, and are called fields. We can access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL has 3 collection types as below:

• Index-by tables, also known as associative arrays,  are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
•  Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. We can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although we can change the number of elements at runtime). They use sequential numbers as subscripts. We can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created
Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block
Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level
Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level

Read more »

DENSE_RANK in Oracle/PL-SQL

DENSE_RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• The ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
  
  Examples:
  1.  Query to return the dense_rank for a $50000 salary(Single Column DENSE_RANK)

  
  SELECT DENSE_RANK(50000) WITHIN GROUP
  (ORDER BY salary DESC NULLS LAST) SAL_RANK
  FROM employees;
  

  
  

  2.  Query to return the dense_rank for an employee with a salary of $50,000 

  and a commission of 10%$(Multiple Column DENSE_RANK)

  
  SELECT DENSE_RANK(10,50000) WITHIN GROUP
  (ORDER BY commission_pct, salary) SAL_RANK
  FROM employees;

  
  

  3. Query to rank the employees in department '60' based on their salaries. Identical salary values receive the same rank. However, no rank values are skipped. 

  SELECT department_id, last_name, salary,

         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) DENSE_RANK

    FROM employees 

    WHERE department_id = 60

    ORDER BY DENSE_RANK, last_name;

  
  

  
  

Read more »

RANK In Oracle/ PL-SQL

RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• Ties are assigned the same rank, with the next ranking(s) skipped. So, if we have 3 items at rank 2, the next rank listed would be ranked 5.

Examples:

1.  Query to return the rank for a $50000 salary(Single Column RANK)

SELECT RANK(50000) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM employees;

2.  Query to return the rank for an employee with a salary of $50,000 and a commission of 10%$(Multiple Column RANK)

SELECT RANK(.10,50000) WITHIN GROUP
(ORDER BY commission_pct, salary) RANK
FROM employees;

3.  Query to find the employee with the nth highest salary

SELECT *
FROM (
  SELECT employee_id, last_name, salary,
  RANK() OVER (ORDER BY salary DESC) EMPRANK
  FROM employees)
WHERE emprank = n;

4. Query to rank the employees  in department 60 based on their salaries. 
Identical salary values receive the same rank and cause nonconsecutive ranks.

SELECT department_id, last_name, salary,

    RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK

  FROM employees WHERE department_id = 60

  ORDER BY RANK, last_name;

Read more »

Bulk Collect - Save Exceptions

The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and still continue processing.

BULK COLLECT construct is used to work with batches of data rather than single record at a time. Whenever we have to deal with large amount of data, bulk collect provides considerable performance improvement.

Declare 
cursor cur_emp 
is 
select * from Emp; 
  
type array is table of c%rowtype;
l_data array; 
dml_errors EXCEPTION; 
PRAGMA exception_init(dml_errors, -24381); 
l_errors number; 
l_errno number; 
l_msg varchar2(4000); 
l_idx number;

Begin 
open cur_emp;
loop 
 fetch cur_emp bulk collect into l_data limit 100;
begin forall i in 1 .. l_data.count SAVE EXCEPTIONS 
 insert into t2 values l_data(i);
exit when cur_emp%notfound;
end loop;
close cur_emp;

Exception
when DML_ERRORS 
then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;

DBMS_OUTPUT.PUT_LINE(‘Error #’ || i || ‘ occurred during ‘||‘iteration #’ || l_idx);

DBMS_OUTPUT.PUT_LINE(‘Error message is ‘ || l_msg);

end loop;
end;
/

Read more »

Oracle Regular Expression

Regular expressions specify patterns to search for in string data using standardized syntax conventions. A regular expression can specify complex patterns of character sequences. For example, the following regular expression:

a(b|c)d

searches for the pattern: 'a', followed by either 'b' or 'c', then followed by 'd'. This regular expression matches both 'abd' and 'acd'.

Oracle Database 11g offers five regular expression functions as below:

1. REGEXP_LIKE
2. REGEXP_SUBSTR
3. REGEXP_REPLACE
4. REGEXP_INSTR
5. REGEXP_COUNT

REGEXP_LIKE(source, regexp, modes) :

This function searches a character column for a pattern. 

source parameter - is the string or column the regex should be matched against. 

regexp parameter - is a string with the regular expression. 

modes parameter -  is optional. It sets the matching modes.

• In SQL, can be used in the WHERE and HAVING clauses of a SELECT statement to return rows matching the regular expression specified.  

          Example:

         SELECT * FROM emp 
     WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

     FIRST_NAME           LAST_NAME
     -------------------- -------------------------
     Steven               King
     Steven               Markle
     Stephen              Stiles

• In PL/SQL script, it returns a Boolean value. It can be used in Check Conditions.

       Example:

      IF REGEXP_LIKE('subject', 'regexp') 

      THEN 

          /* Match */ 

      ELSE 

          /* No match */ 

      END IF;

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) :

This function returns the actual substring matching the regular expression pattern specified. If the match attempt fails, NULL is returned. 

position parameter - specifies the character position in the source string at which the match attempt should start. The first character has position 1. 

occurrence parameter - specifies which match to get. Set it to 1 to get the first match. If you specify a higher number, Oracle will continue to attempt to match the regex starting at the end of the previous match, until it found as many matches as you specified. The last match is then returned. If there are fewer matches, NULL is returned. 

Example:

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,')"REGEXPR_SUBSTR"
  FROM DUAL;
REGEXPR_SUBSTR
-----------------
, Redwood Shores,

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) 

This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern specified.

Example:

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM emp;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes) 

This function searches a string for a given occurrence of a regular expression pattern. If we specify, which occurrence we want to find and the start position to search from, this function returns an integer indicating the position in the string where the match is found.

Example:

The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"
  FROM DUAL;
REGEXP_INSTR
------------
          37

REGEXP_COUNT(source, regexp, position, modes) 

This function returns the number of times the regex can be matched in the source string. It returns zero if the regex finds no matches at all. This function is only available in Oracle 11g and later.

Example:

SELECT REGEXP_COUNT(first_name, 'S', 1) FROM emp;

Read more »

Oracle Advanced Queuing - Troubleshooting

Debugging can be done by the following steps:

1. Check if messages are being propagated at all or the propagation is slow

• queue-to-dblink: The propagation delivers messages or events from the source queue to all subscribing queues at the destination database identified by the dblink. A single propagation schedule is used to propagate messages to all subscribing queues. Hence any changes made to this schedule will affect message delivery to all the subscribing queues. 
• queue-to-queue: This propagation mode delivers messages or events from the source queue to a specific destination queue identified on the database link. This allows the user to have fine-grained control on the propagation schedule for message delivery. This new propagation mode also supports transparent failover when propagating to a destination Oracle RAC system. With queue-to-queue propagation, you are no longer required to re-point a database link if the owner instance of the queue fails on Oracle RAC. This mode supports multiple propagations to the same target database if the target queues are different.

select TOTAL_NUMBER 
from DBA_QUEUE_SCHEDULES 
where QNAME=’<source_queue_name>’;


If TOTAL_NUMBER is increasing, then propagation is most likely functioning, although it may be slow.

2. Check if the database link to the destination database has been set up properly. 

3. Check Message State and Destination. Find the queue table for a given queue

select QUEUE_TABLE 
from DBA_QUEUES 
where NAME = &queue_name;

4. Check for messages in the source queue with

select count (*) 

from AQ$<source_queue_table>  

where q_name = 'source_queue_name';

5. Check for messages in the destination queue.

select count (*) 
from AQ$<destination_queue_table>  
where q_name = 'destination_queue_name';

6. Check to see who is using job queue processes.

7. Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.

8. Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.

9. In case of Oracle Real Application Clusters (Oracle RAC), this queue table and queue pair must exist for each Oracle RAC node in the system. They are used for communication between job queue processes and are automatically created.

10. Check that the consumer attempting to dequeue a message from the destination queue is a recipient of the propagated messages.

11. Turn on propagation tracing at the highest level using event 24040, level 10.

12. Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

Read more »

Oracle Advanced Queue - Technical Concepts

Create a USER with Administrator Role:

CONNECT / AS SYSDBA

CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users

GRANT connect TO aq_admin;

GRANT create type TO aq_admin;

GRANT aq_administrator_role TO aq_admin;

ALTER USER aq_admin QUOTA UNLIMITED ON users;

Create a USER with USER role:

CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;

GRANT connect TO aq_user;

GRANT aq_user_role TO aq_user;

Define Payload

The format or structure of a message is called the payload. While creating a queue, we need to tell Oracle the Payload structure.

CONNECT aq_admin/aq_admin

CREATE OR REPLACE TYPE event_msg_type AS OBJECT (

  Header_ID NUMBER,

  Line_ID   NUMBER,

  Current_status VARCHAR2(50),

);

/

GRANT EXECUTE ON event_msg_type TO aq_user;

Create Queue Table 

Queues are implemented using a queue table which can hold multiple queues with the same payload type. 

GRANT EXECUTE ON event_msg_type TO aq_user;

EXECUTE DBMS_AQADM.create_queue_table 

( queue_table         =>  'aq_admin.event_queue_tab', 

  queue_payload_type  =>  'aq_admin.event_msg_type'

);

Create Queue

EXECUTE DBMS_AQADM.create_queue

(queue_name   =>  'aq_admin.event_queue',

 queue_table  =>  'aq_admin.event_queue_tab'

);

Start Queue

EXECUTE DBMS_AQADM.start_queue 

(queue_name         => 'aq_admin.event_queue',

 enqueue            => TRUE

);

Grant Privilege to AQ_USER

CONNECT aq_admin/aq_admin

EXECUTE DBMS_AQADM.grant_queue_privilege 

(  privilege     =>     'ALL', 

   queue_name    =>     'aq_admin.event_queue', 

   grantee       =>     'aq_user', 

   grant_option  =>      TRUE

);

Enqueue Message

Messages can be written to the queue using the DBMS_AQ.ENQUEUE procedure.

CONNECT aq_user/aq_user

DECLARE

  l_enqueue_options     DBMS_AQ.enqueue_options_t;

  l_message_properties  DBMS_AQ.message_properties_t;

  l_message_handle      RAW(16);

  l_event_msg           AQ_ADMIN.event_msg_type;

BEGIN

  l_event_msg := AQ_ADMIN.event_msg_type(1,1,'Entered');

  DBMS_AQ.enqueue(queue_name          => 'aq_admin.event_queue',        

                  enqueue_options     => l_enqueue_options,     

                  message_properties  => l_message_properties,   

                  payload             => l_event_msg,             

                  msgid               => l_message_handle);

  COMMIT;

END;

/

Dequeue Message

Messages can be read from the queue using the DBMS_AQ.DEQUEUE procedure.

CONNECT aq_user/aq_user

SET SERVEROUTPUT ON

DECLARE

  l_dequeue_options     DBMS_AQ.dequeue_options_t;

  l_message_properties  DBMS_AQ.message_properties_t;

  l_message_handle      RAW(16);

  l_event_msg           AQ_ADMIN.event_msg_type;

BEGIN

  DBMS_AQ.dequeue(queue_name          => 'aq_admin.event_queue',

                  dequeue_options     => l_dequeue_options,

                  message_properties  => l_message_properties,

                  payload             => l_event_msg,

                  msgid               => l_message_handle);

  DBMS_OUTPUT.put_line ('Event Name  : ' ||l_event_msg.name);

  DBMS_OUTPUT.put_line ('Header ID   : ' ||l_event_msg.Header_id);

  DBMS_OUTPUT.put_line ('Line ID     : ' ||l_event_msg.line_id);

  DBMS_OUTPUT.put_line ('Status     : ' ||l_event_msg.status);

  COMMIT;

END;

/

Read more »

Oracle Advanced Queuing - Understanding

Advanced Queuing (AQ) is a flexible message exchange mechanism so that the web based business applications can communicate with each other. One producer application enqueues one or more messages into one queue. Each message is dequeued and processed by one of the consumers application. A message stays in the queue until a consumer dequeues it or the message expires.

Administration and access privileges for advanced queuing are controled using two roles:

• AQ_ADMINISTRATOR_ROLE - Allows creation and administration of queuing infrastructure.
• AQ_USER_ROLE - Allows access to queues for enqueue and dequeue operations.

Advanced Queuing sends and receives messages in two ways:

Point-to-Point : 

A point-to-point message is aimed at a specific target i.e single-consumer queue. 

Senders and receivers decide on a common queue in which to exchange messages. 

Each message is consumed by only one receiver.  

Publish-Subscribe: 

A publish-subscribe message can be consumed by multiple receivers.

Publish-subscribe messaging has a wide dissemination mode--broadcast--and a more narrowly aimed mode--multicast, also called point-to-multipoint.

Read more »

Bulk Collect - NO_DATA_FOUND Exception Handling

When we use BULK COLLECT, if a query does not fetch any records, it does not throw NO_DATA_FOUND exception.  So, we need to check whether the collection variable has any elements or not. 

Example:

1. SQL> declare
2.  type emp_tab is table of emp%rowtype;
3.  t_emp emp_tab;
4.  begin
5.  select * bulk collect into t_emp from emp;
6.  IF t_emp.count = 0 THEN
7.   dbms_output.put_line(‘Bulk Collect: No Records in the Table’);
8.  ELSE
9.   dbms_output.put_line(t_emp.count)
10.  END IF;
11.  end;
12.  / 

Read more »