Monday, 27 December 2010

Creating a database link to access objects on a remote database

1. service name in tnsname
2. create a database link on the local database
3.connect using this database link to remote database

Service Name
To create a database link that points to a remote database you should have proper service name entry in local computer's tnsnames.ora file

Adding service name details in the tnsnames file to access remote database
(if this is already existing ignore it)

ex: tns entry to connect to a remote database running on testserver using service name 'test'

test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testserver)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

create database link on the local database (the database from which you are trying to access another database)

Syntax:

create database link link_name connect to user_name identified by password using service_name

link_name = this is the name for the database link
username = username on the remote database. this username will be used to connect to remote             database
password = password for the user specified
service_name = this is the service name that is in the local database's tnsnames.ora file.

Ex:

create database link 'testlink' connect to hr identified by hr using 'test'


Accessing objects on remote

remoteobjetct_name@dblink_name

select * from employees@testlink;

Saturday, 18 December 2010

XXX is not a valid responsibility for the current user. Please contact your System Administrator" – oracle apps 11i



This issue happens when you end date a responsibility and give a new responsibility to a user. Once user tries to log in “XXX is not a valid responsibility for the current user. Please contact your System Administrator”. This is because of cache at server, oracle does this to speed up the process.

Clearing this cache will make the user to log in with new responsibility.

Clearing the cache

Steps
go to > Functional Administrator responsibility
Click on Core services tab (the tab at the top right)
Click on Caching Framework Tab (blue tab under main tab)
Click on Global configuration
Click on Clear All Cache
A warning message related to performance will come , say yes

Screen shots

After selecting functional administrator responsibility




2


3



4


5


6


Tuesday, 21 September 2010

ORA-01653 : unable to extend table

in oracle apps 11i receivables When entering receipt and saving oracle gave this error

ORA-01653 : unable to extend table
AR.AR_CASH_RECEIPT_HISTORY_ALL BY 1024 in tablespace ARD

check for extend management is automatic or manual

dba_data_files

select autoextensible from dba_data_files where tablespace_name='ARD'
it will show yes or no



The table was created with size 400M and extend allocation was not automatic so i manually extended 100MB.

ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
AUTOEXTEND ON MAXSIZE 500M;


ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
AUTOEXTEND off;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','ARD') FROM DUAL;

As this is user managed extend management actually what should have done was either resize or add new datafile.

here is how


ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
RESIZE 500M;

ALTER DATABASE
ADD DATAFILE '/u01/proddata/prod/ard2.dbf' SIZE 400M
AUTOEXTEND OFF;

Tuesday, 20 July 2010

Adding a service to start at booting on fedora

chkconfig --list

lists all the services starting at booting with different levels



to add a service to start at booting


chkconfig --add [service_name]

ex:

chkconfig -add sshd



to add different levels(0,1,2,3,4,5,6)



chkconfig --level [levels] [service_name] [service_option>]


level: the runlevel where this service has to start (if there are more than one level enter different level without any seperator)

service options : on|off|reset|resetpriorities


ex:

chkconfig --level 2 sshd on

chkconfig --level 235 sshd on

so to add sshd to start up at booting in 235 levels

chkconfig --add sshd
chkconfig --level 235 sshd on

Wednesday, 14 July 2010

Oracle 11i E-Business suite installation set up for Linux

Tested on
Red Hat Enterprise Linux AS release 4 kernel 2.6.9-5.EL
Oracle Enterprise Linux AS release 4 (october update 6) kernel 2.6.9-67.0.0.0.1.EL

Required Packages
ompat-db-4.1.25-9
compat-gcc-32-3.2.3-47.3
compat-gcc-32-c++-3.2.3-47.3
compat-oracle-rhel4-1.0-5 (from oracle patch 4198954)
compat-libcwait-2.0-2 (from oracle patch 4198954)
compat-libgcc-296-2.96-132.7.2
compat-libstdc++-296-2.96-132.7.2
compat-libstdc++-33-3.2.3-47.3
xorg-x11-deprecated-libs-devel-6.8.1-23.EL
xorg-x11-deprecated-libs-6.8.1-23.EL
The following required component must be installed manually:
openmotif21-2.1.30-x

Domain Name Resolver Parameters
add these parameters in the /etc/resolv.con file
options attempts:5
options timeout:15

Verify that the /etc/hosts file is formatted as follows
127.0.0.1 localhost.localdomain
ip_addrss node_name.domain_name
let these loopback entry be there if you want to add a new for your server do it as follows
etc: 192.168.1.7 oracle11i.mylearn.com
now host file will look like
127.0.0.1 localhost.localdomain
192.168.1.7 oracle11i.mylearn.com

to set up ip address for the network card go to Application>system Settings>Network

Verify that the /etc/sysconfig/network file is formatted as follows
HOSTNAME=node_name.domain_name

if the /etc/sysconfig/networking/profiles/default/network file exist, remove it
now restart the system

Edit /etc/sysctl.conf file to make the following changes

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 10000 65000

run /sbin/sysctl -p so that these vales will get effected

to see newly set up kernel values
/sbin/sysctl.conf -a | grep 'kernel_param'
/sbin/sysctl.con -a | grep 'sem'

Edit /etc/serurity/limits.conf file
soft nofile 4096
hard nofile 65535

verify that host name setting is correct.
hostname
this command should return a fully quallified hostname
ex:
hostname
oracle11i.mylearn.com

create dba group
groupadd dba
create oinstall group
groupadd oinstall
create user for 11i
useradd -g oinstall -G dba oracle
to see the user details
finger oracle
create software location
mkdir -p /u01/oracle

change owner of the folder if the location is created by root
chown -r oracle:oinstall /u01/oracle

open a terminal session as root and disable access control for installation
xhost +

change user to oracle
su – oracle
run an exterm session
xterm
go to staging area > starthere/Disk1/rapidwiz/
run
./rapidwizard.sh

Monday, 28 June 2010

Adding a new supplier type – oracle apps 11i


supplier type is coming from a value set that's defined in oracle purchasing look up codes so before you can use any particular supplier type it has to be defined in the values set. To add a new type follow the steps below

Purchasing > Set Up > Purchasing > Look up codes
search for VENDOR TYPE
Add whatever new type you want to add

Tuesday, 8 June 2010

Configuring ssh on Linux


Tested on fedora 13

First check ssh service is running

to check the status of ssh

service sshd status
or
/etc/init.d/ssh status

if it is stopped you can not use ssh

to start this service issue the follwoing command

service sshd start
or
/etc/init.d/sshd start

if you are starting it for the first time you will see these messages on the screen else you will see the last one only, if it starts

Generating SSH2 RSA host key: [ OK ]
Generating SSH1 RSA host key: [ OK ]
Generating SSH2 DSA host key: [ OK ]
Starting sshd: [ OK ]

now you can use ssh

to connect to another host use

ssh username@hostname




Tuesday, 1 June 2010

Fixing approval history updation error using retry errored workflow items

Requisitions approval history was not updated properly after concerned person has approved requisitions. If you go and check action history the action history was missing latest approval. When investigated found out that workflow finished with an error in “ UPDATE_ACTION_HISTORY_APPROVE ”

To find out the errored items in a workflow follow the steps below

Log on to Oracle Application Manager
select Workflow Manager from navigate to list box(situated top right corner) and Click go
go work items under workflow metrics and click on show button to see the graph
click on error block on the graph
this will take to you to a a page with different work item types
click the relevant work items
this will take you to a page with different work item activity stage
click the relevant work activity stage
this will take to a new page with all the activities
select the activity you are concerned with
click launch workflow monitor this will open a new page with
check the item status it will be showing (“Error”)
Retrying errored work items
Here you have three buttons abort,retry,launch workflow monitor
Clikc on retry to retry the item again
this will ask for confirmation once confimed this will be run again
if it run successfully the status of that item should be changed to active

Retrying errored work items in the backend

this select satatment will give you all the errored items, the query is a modified version of query from oracle provided script wfretry.sql

SELECT
ias.item_type
,IAS.item_key
,pa.instance_label
,pa.process_name activity
,ias.activity_result_code
,COUNT(*)
FROM
wf_item_activity_statuses ias
,wf_process_activities pa
WHERE 1=1
AND ias.item_type LIKE :item_type
AND ias.process_activity = pa.instance_id
AND ias.activity_status = 'ERROR'
GROUP BY ias.item_type, IAS.item_key, pa.instance_label, pa.process_name, ias.activity_result_code
ORDER BY pa.instance_label;

Run this procedure to execute the 'retry' action

Wf_Engine.HandleError(itemtype ,itemkey',activity,command ',result );
note :when instance lable was given procedure was giving an error so we changed this to process_name

ex:
BEGIN
Wf_Engine.HandleError(itemtype => 'REQAPPRV',itemkey => '8784-32803',activity => 'RESPONSE_APPROVE',command => 'RETRY',result => NULL );
COMMIT;
END;

Table related to workflow
wf_item_activity_statuses  - work flow items with different action and status
wf_items                             - work flow items

Sunday, 11 April 2010

Accessing windows 7 files from xp (sharing files from windows 7 to xp)

On windows 7 make sure computers are on the same group
Go to network and sharing centre
Click on Change advanced sharing settings (its on the left pane)

Under File and Printer Sharing
Turn on File and Printer Sharing
Under password protected sharing (for security)
Turn on Password protected sharing
if you select the other option, people can access without giving a user name and password, otherwise you have to provide user name and password)

Friday, 2 April 2010

Apex 3.2 Installation

Install http server (it’s can be found on 10g companion cd)

Dwnload and unzip

E:\oracle\apps\apex

Change your working directory to apex.

E:> cd \oracle\apps\apex

Create a table space called apex for oracle apex

Checking the shared_pool_size of the Target Database

SHOW PARAMETER PFILE;

SHOW PARAMETER SHARED_POOL_SIZE

ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;

SHUTDOWN

STARTUP

Oracle XML DB Requirement, Oracle Text Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you

are using a preconfigured database created either during an installation or by

Database Configuration Assistant (DBCA), Oracle XML DB is already installed and

Configured.

Select comp_name “component” from dba_registry;

PL/SQL Web Toolkit

Oracle Application Express requires the PL/SQL Web Toolkit version 10.1.2.0.6 or

later.

Checking for PL/SQL Web Toolkit

Select owa_util.get_version from dual;

if current version is below this , install as follow

Installing the PL/SQL Web Toolkit 10.1.2.0.6

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

1) Stop all applications using the database

2) Navigate to the directory apex/owa

3) Connect via SQL*Plus as the database user SYS

4) Run the PL/SQL Web Toolkit installation script by issuing: @owainst.sql

5) Review the output displayed from owainst.sql and ensure no errors were encountered.

6) Confirm the PL/SQL Web Toolkit version is 10.1.2.0.6 or greater by connecting to the database

select owa_util.get_version from dual;

7) Run the utlrp.sql script from the Oracle Database home to recompile all invalid PL/SQL

packages

To run utlrp.sql, issue: @?/rdbms/admin/utlrp.sql

Start SQL*Plus and connect to the database where Oracle Application Express is

installed as SYS specifying the SYSDBA role. For example:

Now we are going to run apex installation script

If you are not on apex location yet change directory to apex

E:> cd \oracle\apps\apex

Connect as sys and run the script as follows

@apexins apex apex TEMP /i/

tablespace_apex is the name of the tablespace for the Oracle

Application Express application user.

tablespace_files is the name of the tablespace for the Oracle

Application Express files user.

tablespace_temp is the name of the temporary tablespace.

images is the virtual directory for Oracle Application Express images. To

support future Oracle Application Express upgrades, define the virtual

image directory as /i/.

Change the Password for the ADMIN Account

1. Change your working directory to the apex directory where you unzipped the

installation software.

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

3. Run apxchpwd.sql. For example:

@apxchpwd

When prompted enter a password for the ADMIN account.

Configure Oracle HTTP Server Distributed with Oracle Database Oracle Application Server 10g

Unlocking the APEX_PUBLIC_USER Account

The APEX_PUBLIC_USER account is locked at the end of a new installation of Oracle

Application Express. You must unlock this account before configuring the database

access descriptor (DAD) in a new installation.

Connect as sysdba

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password

Where new_password is the new password you are setting for APEX_PUBLIC_

USER. You will use this password when creating the DAD in the sections that

follow.

Copy the Images Directory

Copy E:\oracle\apps\apex\images e:\oracle\apps\http_server\apache\

Editing the dads.conf File If this is a new installation of Oracle Application Express, you

must edit the dads.conf file. The dads.conf file contains the information about the

DAD to access Oracle Application Express.

Go to the dads file location

http_home\apache\mod_plsql

open dads.conf with any text editor

replace ORACLE_HTTPSERVER_HOME, host, port,

service_name, and apex_public_user_password with values appropriate

for your environment.

Alias /i/ "ORACLE_HTTPSERVER_HOME/Apache/images/"

AddType text/xml xbl

AddType text/x-component htc

Order deny,allow

PlsqlDocumentPath docs

AllowOverride None

PlsqlDocumentProcedure wwv_flow_file_mgr.process_download

PlsqlDatabaseConnectString host:port:service_name ServiceNameFormat

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlAuthenticationMode Basic

SetHandler pls_handler

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDefaultPage apex

PlsqlDatabasePassword apex_public_user_password

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Allow from all

Locate the line containing PlsqlNLSLanguage set as follows

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

Ex:

## edited by yoonus on 7 Feb 2009

Alias /i/ "/orasoft/http_server/Apache/images/"

AddType text/xml xbl

AddType text/x-component htc

Order deny,allow

PlsqlDocumentPath docs

AllowOverride None

PlsqlDocumentProcedure wwv_flow_file_mgr.process_downloadd

PlsqlDatabaseConnectString localhost:1521:orcl ServiceNameFormat

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlAuthenticationMode Basic

SetHandler pls_handler

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDefaultPage apex

PlsqlDatabasePassword jameela

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Allow from all

After setting up save this configuration, stop http server and restart

http://hostname:7777/pls/apex

defualt port for httpserver if you changed it give the port accordingly

Friday, 19 March 2010

Using Oracle Profile

First of all alter the system or edit the parameter file for this entry
Alter system set resource_limit = true
After that create a profile with the required options
Assign these profiles to the users whoever you want to restrict

Create profile test_profile
Limit session_per_user 2
idle_time 2 ;

Assign this profile to users

Alter user scott
Profile test_profile;

oracle 10g on OEL and RHL

Swap space configuration
Between 513 MB and 2048 MB 1.5 times the size of RAM Between 2049 MB and 8192 MB Equal to the size of RAM

check swap space
# grep SwapTotal /proc/meminfo
check free memory
free

Disk Space Requirements
Between 150 and 200 MB of disk space the /tmp directory
df -k /tmp (this will give in kb if you want in larger size give -h)
Maximum space requirement for enterprise installation 1.5 GB

Checking the Software Requirements
Operating system Requirements
Red Hat Enterprise Linux 5.0

Determining distribution and version of Linux
# cat /etc/issue
For installation purpose edit the following file to make the release as 4 Even though oracle 10g is certified on 5 oracle is recommending changing this to 4 to pass the os checking (oracle release notes for 10g on linux)
On Oracle Enterprise Linux 5 and Red Hat Enterprise Linux 5, the installation passes the operating system prerequisite checks if you change each 5 to 4 in the /etc/redhat-release file. Ensure that you replace the original values in the /etc/redhat-release file after the Oracle installation is complete.
Make a back up first C p /etc/redhat-release /etc/redhat-release.bck
Now edit and change version number to 4

Kernel Requirements 2.6.18
check if the required kernel is installed:
uname -r

Package Requirements

Oracle Enterprise Linux 5.0, and Red Hat Enterprise Linux 5.0 The following packages (or later versions) must be installed:
binutils-2.17.50.0.6-2.el5
compat-libstdc++-33-3.2.3-61
elfutils-libelf-0.125-3.el5
elfutils-libelf-devel-0.125
gcc-4.1.1-52 gcc-c++-4.1.1-52
glibc-2.5-12 glibc-common-2.5-12
glibc-devel-2.5-12
glibc-headers-2.5-12
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.1-52
libstdc++-4.1.1
libstdc++-devel-4.1.1-52.e15
make-3.81-1.1
sysstat-7.0.0
unixODBC-2.2.11
unixODBC-devel-2.2.11
libXp-1*
in the list of packages that oracle has listed ” libXp-“ is not there but when after we start installation if this package is not there the following error will be thrown
(check this package if it is not there when you run ./runInstaller you will get a “java.lang.UnsatisfiedLinkError: /tmp/OraInstall2005-08-30_10-54-49PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory”

Check if the required package is installed using following command
rpm -q package_name ex: rpm –q unixODBC

Installing package
rpm -ivh packagename
rpm –ivh package_name
ex : rpm – ivh sysstat-7.0.0

Removing package
rpm -e package_name
ex : rpm – e sysstat-7.0.0

# hostname

The output of this command should be similar to the following:
Rac1.localdomain.com(this is my hostname)
Verify that the domain name has not been set dynamically by using the domainname command as follows:

# domainname
This command should not return any results.
Verify that the hosts file contains the fully qualified host name by using the following command:

# cat /etc/hosts grep `eval hostname`
The output of this command should contain an entry for the fully qualified host name and for localhost.
For example:

192.168.1.101 rac1.localdomain.com
rac1 127.0.0.1 localhost.localdomain
localhost If the hosts file does not contain the fully qualified host name, then open the file(/etc/hosts) and make the required changes in it.
You can set up hostname using GUI go to system – administration - network

Creating the Oracle Inventory and DBA Group
groupadd oinstall
groupadd dba

Creating the Oracle Software Owner User
# useradd -g oinstall -G dba oracle
The -g option specifies the primary group, which must be the Oracle Inventory group, for example oinstall
The -G option specifies the secondary groups, which must include the OSDBA group and if required, the OSOPER group. For example, dba or dba,oper
Set the password of the oracle user:
# passwd oracle
Verifying that the User nobody Exists 1.
To determine whether the user exists, enter the following command:
# id nobody
If the nobody user does not exist, then enter the following command to create it: useradd nobody
Configuring Kernel Parameters
Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following: if any of the current values are larger than the recommended value, then specify the larger value.
kernel.shmall = 2097152
kernel.shmmax = 2147483648

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
to get the effect of new kernel setting run
/sbin/sysctl -p
Setting Shell Limits for the oracle User

Add the following lines in the /etc/security/limits.conf file:
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
If you are not logged in as the oracle user, then switch user to oracle:
$ su - oracle
To determine the default shell for the oracle user, enter the following command:
$ echo $SHELL
Open the oracle user's shell startup file in any text editor:
Bourne shell (sh), Bash shell (bash), or Korn shell (ksh):
$ vi .bash_profile
Enter or edit the following line, specifying a value of 022 for the default file mode creation mask:
umask 022

If the ORACLE_SID, ORACLE_HOME, or ORACLE_BASE environment variable is set in the file, then remove the corresponding lines from the file. Save the file, and exit from the editor. To run the shell startup script, enter one of the following commands:
* Bash shell on Red Hat Enterprise Linux:
$ . ./.bash_profile

To verify that the environment has been set correctly, enter the following commands: $ umask $ env more
Verify that the umask command displays a value of 22, 022, or 0022

Create directories for oracle software and datafiles
Mkdir –P /u01/app/oracle
If oracle user doesn’t not have privilege change to root and do as follwing
Su - root
mkdir P /u01/app/oracle
Change folder owner to oracle
chown –r oracle:oinstall /u01/app/oracle
set folder priviliges
chmod –r 770 /u01/app/oracle

Data files
mkdir –P /u02/oradata
chown –r oracle:oinstall /u02/oradata
Chmod –r 770 /u02/oradata
now we have to start a xterminal
as root run
xhost +
change user to oracle
su - oracle
open an x terminal by running
xterm
copy the database installation to disk
change privilege so that oracle can execute .runInstall
ex:
location you copied
/var/stage/10g
Change owner if its not under oracle
chown –r oracle:oinstall /var/stage/10g
chmod –r 770 /var/stage/10g
Now change directory to stagin area and run
. runInstall

error encountered when tried to start the listener

Linux Error: 29 Illegal Seek

http://forums.oracle.com/forums/thread.jspa?threadID=520405

Include in /etc/hosts file
127.0.0.1 localhost.localdomain localhost




Wednesday, 17 March 2010

Type english get Malayalam

http://t13n.googlecode.com/svn/trunk/blet/docs/help_ml.html

Monday, 15 March 2010

Lost Updates

Lost updates happen in multi user environment quite normally if the application is not well built. It happens when you have multiple users and some of them are trying to update same data around same time.

User’s action from query to update: - Data is retrieved to user's memory, make the necessary changes and puts the data back to the database.

Data is read from the database to the users’ machine around the same time, session one makes required changes and saves, by this time the other session also does some changes and saves. If there is no checking the second session will overwrite the first session's changes.

Let’s take an application to explain

This is an application which will be used by users for booking for programmes. The current situation is that only one seat is available but two users are querying the system for booking at the same time.

User_1 queries the system and gets the status of booking “open” only for one person

User_2 queries the system and gets the status of booking “open” only for one person

User_1 books so the status changed to “booked” (no room for new booking)

User_2 still sees the status as “open” on his screen

User_2 books and so the status changes to “booked” and gets saved

Ex:

Don’t forget to set serveroutput on !

--create table for programme booking

CREATE TABLE prg_bookings(envent_id NUMBER,no_booking integer,book_limit integer) ;

--insert a programme with id 1001

INSERT INTO prg_bookings values(1001,9,10);

commit;

CREATE OR REPLACE PROCEDURE PRINT (text VARCHAR2)

IS

BEGIN

DBMS_OUTPUT.put_line (text);

END PRINT;

Run session 1 and session 2 in two different sessions, run session 1 first and then session 2 in less than 10 seconds. In examples I have used dbms lock so that both sessions will see the same data.

Session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 ;

END IF;

IF (SQL%ROWCOUNT) = 1

THEN

PRINT (SQL%ROWCOUNT || ' i have booked');

Commit;

END IF;

END;

Session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 ;

END IF;

IF (SQL%ROWCOUNT) = 1

THEN

PRINT (SQL%ROWCOUNT || ' i have booked');

Commit;

END IF;

END;

Here the booking done by session 1 will be lost. This issue is tracked in two ways

  1. Pessimistic Locking

Pessimistic locking is the mechanism where at the time of user’s intention to update any row, lock that particular row with select ....for update.

ex:

SELECT

no_booking

,book_limit

FROM prg_bookings

WHERE

envent_id = 1001

FOR UPDATE;

  1. Optimistic Locking :- is done in the following ways

Column compare

Hash compare

Timestamp compare

Version compare

Ora_rowscn(new in 10g)

Use this update statement to reset no of booking column
UPDATE prg_bookings SET no_booking = 9 WHERE envent_id = 1001;
commit;



Column compare

Session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 and no_booking = v_total_booking ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;
END;

Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 and no_booking = v_total_booking ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Hash compare (this technique is used by oracle apex)

Timestamp compare

--let's add a column for time stamp comaparison

ALTER TABLE prg_bookings

ADD tim TIMESTAMP;

UPDATE prg_bookings SET no_booking = 9,tim=localtimestamp

WHERE envent_id = 1001;

commit;

session 1
--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_tim_at_qry TIMESTAMP;

BEGIN

SELECT

no_booking

,book_limit

,tim

INTO

v_total_booking

,v_booking_limit

,v_tim_at_qry

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,tim = localtimestamp

WHERE envent_id = 1001 and tim = v_tim_at_qry ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. Timestamp has been changed,tim stamp at query was ' || v_tim_at_qry );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;

session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_tim_at_qry TIMESTAMP;

BEGIN

SELECT

no_booking

,book_limit

,tim

INTO

v_total_booking

,v_booking_limit

,v_tim_at_qry

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,tim = localtimestamp

WHERE envent_id = 1001 and tim = v_tim_at_qry ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Version compare
-let us add another column for version compare

ALTER TABLE prg_bookings DROP COLUMN tim;

ALTER TABLE prg_bookings

ADD ver INTEGER;

UPDATE prg_bookings SET no_booking = 9,ver=0 WHERE envent_id = 1001 ;

commit;

session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_ver PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

,ver

INTO

v_total_booking

,v_booking_limit

,v_ver

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,ver = ver+1

WHERE envent_id = 1001 and ver = v_ver ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. version changed,version no at query was ' || v_ver );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;

Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_ver PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

,ver

INTO

v_total_booking

,v_booking_limit

,v_ver

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,ver = ver+1

WHERE envent_id = 1001 and ver = v_ver ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Ora_rowscn(new in 10g)

--now lets recreate the table to enable row level scn

drop table prg_bookings;

CREATE TABLE prg_bookings(envent_id NUMBER,no_booking integer,book_limit integer) rowdependencies;

INSERT INTO prg_bookings values(1001,9,10);

commit;

Session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_scn_at_query NUMBER;

BEGIN

SELECT

no_booking

,book_limit

,ORA_ROWSCN

INTO

v_total_booking

,v_booking_limit

,v_scn_at_query

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

WHERE envent_id = 1001 and ORA_ROWSCN= v_scn_at_query ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. Scn changed, Sct at query was ' || v_scn_at_query );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;
Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_scn_at_query NUMBER;

BEGIN

SELECT

no_booking

,book_limit

,ORA_ROWSCN

INTO

v_total_booking

,v_booking_limit

,v_scn_at_query

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

WHERE envent_id = 1001 and ORA_ROWSCN= v_scn_at_query;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;