New in Postgres Plus Advanced Server 9.2

Good News Postgres Plus Advanced Server Beta Version is now availabale, which has all the new feature of PostgreSQL 9.2, and it also has new features which are specific to Advanced Server.

I will cover/explain New features of PostgreSQL 9.2 later as per release Notes. However, the following link covers major features of PostgreSQL 9.2 and can be taken as reference.

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2

In this Blog, I am going to cover only specific features of Advanced Server 9.2 Core. Which are:

1. INSERT APPEN HINT in PPAS 9.2

PPAS 9.2, now supports INSERT append hint. This is a very interesting feature and it is very useful for users who frequently delete records in Bulk and do bulk of INSERTs.
This hint can provide some benefits in INSERTs. This hint makes PPAS not use Free Space Map and Append the rows at the end of relation(table).
Its usage is given below:

INSERT /*+append*/ INTO tab21115 VALUES(1,'abc',sysdate);

2. Procedure Called like Function Call.

PPAS 9.2 now allows calling procedure with following syntax:

SELECT * FROM procedure(arg1,arg2,…);

This type of Procedure call is allowed for Procedures which have OUT/INOUT Params. With this, user can use exec Function.

lets see how it works:

a. Create a Procedure as Given below:

CREATE OR REPLACE PROCEDURE foo_proc(A IN INT, B INOUT INT, C OUT INT)
AS
BEGIN
   b:=a+b;
   c:=b+1;
END;

Till 9.1 and even in 9.2, user can do something like given below:

DECLARE
  d int:=1;
  e int:=2;
  f int;
BEGIN
   foo_proc(d,e,f);
   DBMS_OUTPUT.PUT_LINE('e = '||e);
   DBMS_OUTPUT.PUT_LINE('d = '||d);
   DBMS_OUTPUT.PUT_LINE('f= '||f);
END;
e = 3
d = 1
f= 4

Now in 9.2, user can also do this:

edb=# select * from foo_proc(1,2);
 b | c 
---+---
 3 | 4
(1 row)

In case the user has Procedure and Function with a same name and the user wants to execute procedure using select command, then SELECT command is going to give preference to Function over Procedure as given below:

Function Definition:

CREATE OR REPLACE FUNCTION foo(a INT) RETURN INT
AS
BEGIN
  RETURN 2;
END;

CREATE PROCEDURE foo(A int, B OUT int)
AS
BEGIN
   B:=a+1;
END;

edb=# select * from foo(4);
 foo 
-----
   2

However, user can still use EXEC command to execute Procedure as given below:

edb=# exec foo(4);
 ?column? 
----------
        5
(1 row)

Or if you are using Anonymous function then PERFORM will also work with Procedure as given below:

edb=# DECLARE
edb-#     a int:=4;
edb$#     b int;
edb$# BEGIN
edb$#    PERFORM foo(a,b);
edb$#    DBMS_OUTPUT.PUT_LINE('b = '||b);
edb$# END;
b = 5

EDB-SPL Procedure successfully completed

3. Object Type Support:

PPAS already has Object Type support. However, the new version is coming with a new enhancement in that support. In New Version, user would be able to create Objects with Attributes, Functions, and Procedures. This gives an advantage of reduced coding in terms of defining Object types and maintaining it.

Example is given below:

a. Define a Type:

    CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);

b. Define a Type Body:

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;

c. Define a Table based on Body type:

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);

d. To insert Data, Default constructor can be use as given below:

INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));

e. With the following way, a user can access the data in the table:

SELECT p.id,
       (p.person).first_name,
       p.person.getAge() age
FROM   people p;
 id | first_name | age 
----+------------+-----
  1 | John       |  13
  2 | Jane       |  13
(2 rows)

4. PL/SQL Subtypes:

Subtypes is considered as a TYPE, which is defined on base/user defined types with some constraints, syntax for declaring SUBTYPE in PL/SQL is given below:

SUBTYPE <subtype_name> IS <type_name> [(constraint)] [NOT NULL];

Example is given below:

Example 1:

DECLARE
  SUBTYPE INT2 IS NUMBER;
  v2 INT2 (3,0);
BEGIN
  v2:= 123;
  DBMS_OUTPUT.PUT_LINE ('V2 = '|| v2);
END;

Example 2:

DECLARE
  SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
  verb  v_word  := 'verb';
  noun  v_word  := 'noun';
BEGIN
  noun :='n1';
  DBMS_OUTPUT.PUT_LINE (UPPER(verb));
  DBMS_OUTPUT.PUT_LINE (UPPER(noun));
END;

5. DROP TABLE CASCADE CONSTRAINTS in 9.2:

PPAS 9.2 now supports DROP TABLE CASCADE CONSTRAINTS, this is a very intersting feature and is not available in PostgreSQL 9.2.

Using this command, user would be able to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause and such referential integrity constraints exist, then the database returns an error and does not drop the table.

This is useful, when somebody wants to drop Master table but doesn’t want to drop child table, which has Foreign Key Constraint and is referencing the Master. An example is given below:

CREATE TABLE master(id numeric primary key);
CREATE table child(id numeric references master(id));
insert into master values(1);
insert into child values(1);
edb=# drop table master cascade constraints;
NOTICE:  drop cascades to constraint child_id_fkey on table child
DROP TABLE
edb=# \d child
  Table "enterprisedb.child"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 

As you can see above, I have dropped the Master table, on which child table has referential integrity constaint. However, my Child table still exists. PostgreSQL 9.2, doesn’t have CASCADE CONSTRAINTS. Therefore, user has to first drop the constraints on table which has referential integrity constraints and then drop the referenced table. This process requires user to find all tables which has referential integrity constraints that refer to primary key of master table. This commands helps DBAs/Users not to worry about finding the child tables.

6. Allow throwing Multiple Errors as Warning, while compiling Procedures/Functions using SPL.

This is also a very interesting enhancement found for SPL. Whenever the user wants to compile a function/procedure in SPL, it will show all the error messages in the particular Function/Procedure Body, so that the user doesn’t have to re-visit their code everytime to fix the other issue. An example is given below:

CREATE OR REPLACE FUNCTION foo() RETURN integer
AS
BEGIN
   a:=a=1
;
B:=b + 1;
I am making fool;
END;

Output:
ERROR:  "a" is not a known variable
LINE 4:    a:=a=1
           ^
ERROR:  "b" is not a known variable
LINE 6: B:=b + 1;
        ^
ERROR:  syntax error at or near "I"
LINE 7: I am making fool;
        ^
ERROR:  compilation of SPL function/procedure "foo" failed due to 3 errors

As you can see above, it has given all the mistakes that I made in Function Body, and now I can fix all of them in one go, to reduce the overhead of executing again and again.

This is very useful for Developers/DBAs.

7. DEFAULT for Types declared in Packages:

Now, in PPAS 9.2, user would be able to define DEFAULT value for particular type, as given below:

CREATE OR REPLACE PACKAGE INITPKG_pro_b4_default AS
PROCEDURE show_default_values;
 n1 NUMBER DEFAULT 20;
 c1 VARCHAR2 DEFAULT 'Default';
END;

CREATE OR REPLACE PACKAGE BODY INITPKG_pro_b4_default AS
 PROCEDURE show_default_values IS
  n number;
 BEGIN
  dbms_output.put_line(c1);
  dbms_output.put_line(n1);
  n1 := n1*n1;
  n1 := SQRT(n1);
  dbms_output.put_line(n1);
 END;
END;

edb=# exec INITPKG_pro_b4_default.show_default_values;
Default
20
20.000000000000000

EDB-SPL Procedure successfully completed

8. TABLE Expression support for Nested Tables.

Now, in 9.2, user would be able to use TABLE Expressions for Nested tables. This feature was missing and asked by many Oracle DBAs. Table Expressions allows the user to query a collection in the FROM Clause like a Table.

edb=# CREATE OR REPLACE TYPE comp_typ_str_21189 IS TABLE OF VARCHAR2(100);
CREATE TYPE
edb=# 
edb=# select * from table(comp_typ_str_21189('PPP','QQQ')) ;
 column_value 
--------------
 PPP
 QQQ
(2 rows)

9. INPUT/OUTPUT Functions for NESTED TABLES:

PPAS 9.2 supports user defined input/output functions for NESTED TABLE. This feature was missing in 9.1.

Some examples are given below:

edb=# create or replace type mytab as table of varchar2(90);
CREATE TYPE
edb=# Create or replace function fun return mytab
edb-# as
edb$# begin
edb$#  return mytab('a','b',3);
edb$# end;
CREATE FUNCTION
edb=# select fun;
 fun21168 
----------
 {a,b,3}
(1 row)

edb=# 
edb=# create or replace function fun return mytab
edb-# as
edb$#  nvar mytab;
edb$# begin
edb$#  nvar := mytab();
edb$#  nvar.extend(4);
edb$#  nvar(1) := 'foo';
edb$#  nvar(2) := NULL;
edb$#  nvar(3) := 'deleteme';
edb$#  nvar(4) := 'bar';
edb$#  return nvar;
edb$# end;
CREATE FUNCTION
edb=# --Verify User's is able to see that data
edb=# select fun;
        fun21168         
-------------------------
 {foo,NULL,deleteme,bar}
(1 row)

9. LOG is no more a reserved keyword for functions.
Till PPAS 9.1, LOG was a reserved keyword and users were not allowed to create functions using this Keyword. In 9.2, a user would be able to use this keyword.

edb=# CREATE OR REPLACE FUNCTION log( a int ) return int as
edb$# BEGIN
edb$# dbms_output.put_line('Function LOG is called');
edb$# return a*2;
edb$# END;
CREATE FUNCTION
edb=# SELECT LOG(10);
Function LOG is called
 log 
-----
  20
(1 row)

10. Variables can be named as current_date.

We know that current_date is special function which returns current date of PostgreSQL/PPAS DB. Till PPAS 9.1, users were not allowed to use this reserved name in variables. Now, in 9.2, users would be able to use it. As given below:

edb=# create table t_currentdate(current_date int);
CREATE TABLE
edb=# desc t_currentdate
    Table "enterprisedb.t_currentdate"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 current_date | integer | 

create or replace procedure proc_currentdate(current_date date) is
begin
 dbms_output.put_line(current_date);
end;

edb=# exec proc_currentdate(current_date);
05-OCT-12 00:00:00

EDB-SPL Procedure successfully completed

11. New Data Type STRING.

Since Oracle supports string as data type and this datatype wasn’t available in PPAS 9.1, we have included it in 9.2. String is an alias to VARCHAR Data type. Example is given below:

edb=# CREATE TABLE test_string(col string)
edb-# ;
CREATE TABLE
edb=# insert into test_string values('As an Example');
INSERT 0 1
edb=# select * from test_string;
      col      
---------------
 As an Example
(1 row)

edb=# \d test_string
    Table "enterprisedb.test_string"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 col    | character varying | 

12. NVARCHAR2 data type support in 9.2

PPAS 9.2 allowes users to use NVARCHAR2 as a datatype. In PPAS 9.2, NVARCHAR2 is mapped to VARCHAR data type. This is only for an Oracle user, who always asked question on having this in PPAS.

Example is given below:

edb=# CREATE TABLE test_nvarchar(col NVARCHAR2(10))
edb-# ;
CREATE TABLE
edb=# \d test_nvarchar
     Table "enterprisedb.test_nvarchar"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 col    | character varying(10) | 

13. MultiByte and string delimiter support in EDB*loader.

EDB*Loader in PPAS 9.2, is more efficient, compared to 9.1, in loading data. There were some cases, in which it has been observed that it is difficult to find single character delimiter for data in file as the data in a file has all possible characters. To load those kind of data files, users can now define there own STRING Delimiter. An example is given below:

Data File: data.log:
1$$abc
2$$ccc
3$$dddddddd

Control FIle:
LOAD DATA
INFILE '/tmp/data.log'
BADFILE '/tmp/data.bad'
truncate INTO table edb_loader
FIELDS TERMINATED BY '$$' optionally enclosed by '"'
(id , col )

-bash-4.1$ edbldr userid=enterprisedb/ control=/tmp/edb.ctl log=/tmp/edb.log
EDB*Loader: Copyright (c) 2007-2012, EnterpriseDB Corporation.

Successfully processed (3) records
-bash-4.1$ psql
psql (9.2.0.1)
Type "help" for help.

edb=# select * from edb_loader ;
 id |   col    
----+----------
  1 | abc
  2 | ccc
  3 | dddddddd
(3 rows)

Some Oracle Workarounds in Postgres Plus Advanced Server 9.0

Today, I though to mention some of Oracle workarounds which can be use for Migration of Oracle Database to PPAS 9.0(EnterpriseDB).

1. UTL_RAW.CAST_TO_RAW
This is a Oracle Packaged Function, which can be use to convert VARCHAR2 value into RAW Value. Lets see its example in Oracle:

SQL> select utl_raw.cast_to_raw('TESTING TESTING') from dual;

UTL_RAW.CAST_TO_RAW('TESTINGTESTING')
--------------------------------------------------------------------------------
54455354494E472054455354494E47

Since PPAS 9.0, doesn’t have this package in 9.0, therefore user can use postgresql function decode() for converting of varchar datatype to RAW/bytea data. Following is an example:

edb=# select decode('TESTING TESTING','escape') from dual;
              decode
----------------------------------
 \x54455354494e472054455354494e47
(1 row)

which is a same output.

2. UTL_RAW.CAST_TO_VARCHAR2

Using this packaged function user can convert raw value into a value of data type VARCHAR2 with the same number of data bytes. Following is an example:

SQL> select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw('TESTING TESTING')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CAST_TO_RAW('TESTINGTESTING'))
--------------------------------------------------------------------------------
TESTING TESTING

For this function, PPAS 9.0 is having encode() function which can be use to convert raw/bytea value to VARCHAR/TEXT. Following is an example:

edb=# select encode(decode('TESTING TESTING','escape'),'escape') from dual;
     encode
-----------------
 TESTING TESTING
(1 row)

For more details of above two function, following link can be use:

http://www.enterprisedb.com/docs/en/9.0/pg/functions-binarystring.html

Using above basic function, user can immitate behavior of UTL_RAW Package in PPAS/PostgreSQL.

3. DBMS_OBFUSCATION.DESEncrypt & DBMS_OBFUSCATION.DESDecrypt Procedure
The DESEncrypt/DESDecrypt procedures generates the encrypted/decrypted form of the input data. It uses DES algorithm to encrypt data. Following is an Oracle Example:

DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048);
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST =========');
   dbms_output.put_line('> Raw input                        : ' ||
                 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' ||
               rawtohex(encrypted_raw));
      dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,
               key => raw_key, decrypted_data => decrypted_raw);
      dbms_output.put_line('> Decrypted raw output             : ' ||
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
      dbms_output.put_line('>  ');
      if UTL_RAW.CAST_TO_VARCHAR2(raw_input) =
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

Oracle Output:

========= BEGIN TEST =========
 Raw input      : tigertigertigert
 encrypted hex value     : 5AAB8C0D278AD75CA1968790D00FD75A
 Decrypted raw output     : tigertigertigert

 Raw DES Encyption and Decryption successful

PL/SQL procedure successfully completed.

In PPAS/PostgreSQL, user can use encrypt()/decrypt() function, which is a part of pgcrypto module supplied with PostgreSQL/PPAS. Following is an example in PPAS 9.0:

DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := decode(input_string,'escape');
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := decode(key_string,'escape');
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048);
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -20010);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -20012);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' ||
                 encode(raw_input,'escape'));
   BEGIN
     select encrypt(raw_input,raw_key,'des') into encrypted_raw from dual;
      dbms_output.put_line('> encrypted hex value              : ' ||
               encode(encrypted_raw,'hex'));

     select decrypt(encrypted_raw,raw_key,'des') into decrypted_raw from dual;
      dbms_output.put_line('> Decrypted raw output             : ' ||
                    encode(decrypted_raw,'escape'));
      dbms_output.put_line('>  ');
      if encode(raw_input,'escape') =
                    encode(decrypted_raw,'escape') THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

Output:

========= BEGIN TEST RAW DATA =========
 Raw input                        : tigertigertigert
 encrypted hex value              : 5aab8c0d278ad75ca1968790d00fd75a9931b6116d9d4346
 Decrypted raw output             : tigertigertigert

 Raw DES Encyption and Decryption successful

EDB-SPL Procedure successfully completed

4. UTL_HTTP.REQUEST Function.
Oracle provides UTL_HTTP.RQUEST function, which user can use to access data on the Internet. This function returns 2000 bytes of the data retrieved from the given URL.
Since PPAS/PostgreSQL doesn’t have direct support for this Function, so user can write plperlu function to get same features. Following is a small function/example:

CREATE OR REPLACE FUNCTION view_web_source(text) returns text
as
$$
use LWP::Simple;
my $ua = LWP::UserAgent->new;
$pageURL=$_[0];
$URLsource=get($pageURL);
my $req = HTTP::Request->new(GET => $pageURL);
my $res = $ua->request($req);
if ($res->status_line == "200 OK")
{ return $URLsource; }
 else
{
   return $res->status_line;
}
$$ language plperlu;

CREATE OR REPLACE FUNCTION url_request(url text) returns text
as
$$
select substring(view_web_source($1) from 1 for 2000);
$$ language sql;

Oracle Snapshot:

PPAS Snapshot

Fix of "ORA-29275: partial multibyte character"

This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL.

This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown.

select * from junk_character;'ORA-29275: partial multibyte character'

Oracle Descritption for this error is given below:
ORA-29275:
partial multibyte character
Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.

Which doesn’t give much information.

To find the the column and rows which have those junk/invisible character user can try following trick.

a. Select the data column wise as given below:   select col1 from tablename;   select col2 from tablename;

If you are sure about the columns which has those junk character, then you can do the range data SELECT using ROWNUM to find the Rows which has those Junk Character.

b. select col1,col2 from  rownum between lowerbound and upperbound;

After finding the rownum, user either can update the column value Or delete the rows if those are not important.

To get the data, without junk/invisible characters, user can also use Convert Function.

To fix this issue, I have used the convert Function of oracle to display the result.

CONVERT(COLUMN NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET');

Above function helped me.
Using Above function, I have created a view on top of the Table Data and using that view, I have been able to Migrate the data of Oracle.

Does UPDATE Change ROWID in Oracle?

Yesterday, there was a discussion which was going on OTN (Oracle Forum). I was also part of that discussion. Discussion was about the “Does UPDATE statement change rowid in Oracle?”

As per my comment of “UPDATE does not change rowid”, one of the user has pointed me that UPDATE statement sometime changes the rowid in Oracle and given me following example.

SQL> ed  
Wrote file afiedt.buf  
   
  1  create table moving_rowid (  
  2    col1 number primary key,  
  3    col2 number,  
  4    col3 number,  
  5    col4 varchar2(10)  
  6  )  
  7* organization index  
SQL> /   
 Table created.  
SQL> insert into moving_rowid values( 1, 2, 3, 'foo' );  
1 row created.  
  
SQL> insert into moving_rowid values( 2, 3, 4, 'bar' );  
1 row created.  
    
SQL> select rowid, col1, col2, col3, col4  
  2    from moving_rowid;  
ROWID                  COL1       COL2       COL3 COL4  
---------------- ---------- ---------- ---------- ----------  
*BAEADxsCwQL+             1          2          3 foo  
*BAEADxsCwQP+             2          3          4 bar  
   
SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
   
SQL> commit;  
Commit complete.  
   
SQL> select rowid, col1, col2, col3, col4  
  2    from moving_rowid;  
ROWID                  COL1       COL2       COL3 COL4  
---------------- ---------- ---------- ---------- ----------  
*BAEADxsCwQP+             2          2          3 foo  
*BAEADxsCwQT+             3          3          4 bar  

Above example has made me to think and I have given the following answer on forum with example to show that UPDATE does not change the rowid:

SQL> select rowid, col1,col2,col3,col4 from moving_rowid;  
ROWID    COL1  COL2 COL3 COL4  
AAAFiAAAEAAAAhgAAA  1  2  3 foo  
AAAFiAAAEAAAAhgAAB  2  3  4 bar  
  
SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
  
SQL> commit;  
Commit complete.  
  
SQL> select rowid, col1,col2,col3,col4 from moving_rowid;  
ROWID    COL1  COL2 COL3 COL4  
AAAFiAAAEAAAAhgAAA  2  2  3 foo  
AAAFiAAAEAAAAhgAAB  3  3  4 bar  

No changes in rowid, reason there is no organization has been done as per the index.

Please note: if you change any indexed column and table has an index for organizing data then index will change. This is what happens with partition too.

Now, see the example given below with organization index:

SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
  
SQL> commit;  
Commit complete.  
  
SQL> select rowid,col1,col2,col3,col4 from moving_rowid;  
ROWID   COL1  COL2 COL3  COL4  
*BAEACGwCwQP+  2  2   3   foo  
*BAEACGwCwQT+  3  3  4  bar  
  
Now lets update non-index column:  
SQL> select rowid,col1,col2,col3,col4 from moving_rowid;  
ROWID   COL1  COL2  COL3 COL4  
*BAEACGwCwQP+  2  3  3 foo  
*BAEACGwCwQT+  3  4  4 bar  

I hope above would have clear the behavior. UPDATE does not change the rowid. Users who see change in rowid is actually movement of data pointer. data pointer changes if the organization done on the basis of index (and each update changing the index too) or if row-movement has happened.

Therefore it’s not best practice to do update on the basis of rowid.

Forum link is given below:
http://forums.oracle.com/forums/message.jspa?messageID=9115665#9115665

Understanding Real Application Cluster Processes

With all the Processes of Stand-alone Oracle Database, Real Application Cluster provides some other processes which plays important role. These Processes are:

1. LMON: Globa Enqueue Service Monitor
Responsibilities Assigned:

This Process is responsible for monitoring the entire cluster global enqueues and the resources. It also manages the instances failure and process failures and the associated recovery for Global Cache service and Global Enqueue Service.
Main responsibility of this process is recovery of Global Resources. Services provided by this process is also known as Cluster Group Services.

View:: So, we have one watcher and recovery agent for Global Resources.

2. LMDx: Global Enqueue Service Daemon (Lock Agent)
Responsibility Assigned:

This process controls the access of Global Enqueue and resources. It also handles the Deadlock detection and remote enqueue requests (i.e requests from instances).

View: Hmm, we have one recorder agent and resource information keeper.

3. LMSx: Global Cache Service Processes
Responsibility Assigned:

This is a process which works as Messages handler. RAC provokes up to 10 LMSx processes. It handles the acquisition interrupt and blocking interrupt requests from remote instances. It also keeps the consistency in read version of blocks. It also controls the flow of messages. Overall responsibilities can be described in bullets are given below:
a. Manage the resources requests and cross instance calls for shared resources
b. Listing all invalid lock elements and validating the locks during recovery.
c. Handles the deadlock detection and lock conversion timeouts.

View: So, we have clerk which can keep the records of locks and request for Global resources.

4. LCKx: Broadcaster
Responsibility Assigned:

This mainly manages the requests and Broadcast the information across the instances. Workload of this process automatically get balanced/shared if we have multiple LMSx.

View: This is a Broadcaster. I understand we require it.

4. DIAG: Diagnosability Darmon
Responsibility assigned:

It monitor the health of instances ad keeps the data of instance process failures. Naming Convention followed for this process is ora__NYDB1

View: Hmm Auditor and Nursing Assistant process.

Now lets understand the some important services and who is responsible for it.

1. Global Cache Service:
Responsibility comes under it:

a. Track the location and status of data block i.e its mode,role and access privileges of various data instances.
b. Block transfer between instances and give appropriate lock mode conversion

Who is responsible for it: LMSx and LMD

2. Global Enqueue service:
Responsibility comes under it:

a. Manage and track the status of enqueuing mechanism which includes all non cache fusion intra-instances operations.
b. Perform Concurrency Control on dictionary cache locks, library cache locks and transactions.

Who is responsible: Almost all the RAC specific processes are responsible for it.

So, above the important processes/managers which we get in RAC for Oracle Database Management to provide global services of GCS and GES.