Tuesday 13 January 2009

Blocking sessions-locks in Oracle

Did you ever wanted to quickly find which sessions are blocking each other in Oracle?

In this post I will introduce an SQL script, which would quickly tell you if there is blocking between the sessions and which would also show you what SQL these sessions are using.

To see the script working, first create a dummy table and insert some test data.




SQL> create table t (a char(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;


Then select some rows from the dummy table for update.



SQL> select * from t where a='z' for update;

A
-
z



In second session try to update the rows which you have selected above. Due to locks this will block! ACID and serialization kicks in.


SQL> update t set a='x' where a='z';


It will just hung!

To see what is blocking, run this query in a third session as SYSDBA.


SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/


BLOCKING_STATUS
--------------------------------------------------------------------------------
15-JAN-2009 07:41:27 User hero@world.com ( SID= 144 ) with the statemen
t: select * from t where a='z' for update is blocking the SQL statement on hero@world.com ( SID=147 ) blocked SQL -> update t set a='x' where a='z'




Monday 12 January 2009

Google Chart APIs

Now this is cool!

How about if you could just pass 2-3 numbers to a URL and get back a very nice looking SVG chart on a web page for FREE and on your website?

I found out that Google now give an API where you pass few numbers in a URL and it just Charts them for your.

Try for yourself here:

http://code.google.com/apis/chart/

I taught I blog about this, as I can see uses of it in real time monitoring. They claim that you can call their API up to 250K times a day for free.

Here is a nice idea. A day has 86400 seconds, hmmm how about monitoring the load of your server with it? :-)

Create a database using OSDM (Oracle SQL Developer Modeling)

In this post I show you how easy it is to create a database using Oracle SQL Developer Data Modeling.


Suppose your database requirements are as simple as follows:

  • You want an event management database system. Not a calendar, but something where you can write events into.
  • You want to be able to invite friends to attend to these events.
  • You also want your friends/users to choose the types of events they like most.



Data model it with OSDM


Using OSDM you can quickly draw the data model with foreign keys and primary keys for your database design requirements above. Is not difficult to use just filling in dialog boxes about the properties of the tables.


(Click to enlarge)



OSDM will not only draw you the model, but will also give you the ability to extract the DDL and then run it on something like Oracle Apex's SQL command prompt. How great is that! I just hope that Oracle WILL give this tool free with Oracle SQL developer in later releases.



Extract the DDL from the Model

In OSDM use File -> Export -> DDL File to extract the SQL statements from the data model.

Using the simple OSDM you can extract the DDL from the above model and you get a nice SQL file with the CREATE TABLE and ALTER TABLE ... CONSTRAINT ... commands which will look like this:


-- Generated by Oracle SQL Developer Data Modeling Version: 1.5.1 Build: 518
-- at: 2009-01-12 21:56:49
-- for: Oracle Database 10g
-- Oracle Database 10g



CREATE TABLE EVENTS
(
EVENT_ID NUMBER NOT NULL ,
EVENT_DESCRIPTION CLOB ,
EVENT_DATE DATE ,
EVENT_TYPE_ID NUMBER NOT NULL
) LOGGING
;



ALTER TABLE EVENTS
ADD CONSTRAINT EVENTS_PK PRIMARY KEY ( EVENT_ID ) ;


CREATE TABLE USERS
(
USER_ID NUMBER NOT NULL ,
USER_EMAIL VARCHAR2 ,
USER_NAME VARCHAR2
) LOGGING
;



ALTER TABLE USERS
ADD CONSTRAINT USERS_PK PRIMARY KEY ( USER_ID ) ;


CREATE TABLE ATTEND
(
USER_ID NUMBER NOT NULL ,
EVENT_ID NUMBER NOT NULL ,
ATTEND_DATE DATE
) LOGGING
;




CREATE TABLE EVENT_TYPES
(
EVENT_TYPE_ID NUMBER NOT NULL ,
EVENT_TYPE_DESCRIPTION VARCHAR2 NOT NULL
) LOGGING
;



ALTER TABLE EVENT_TYPES
ADD CONSTRAINT EVENT_TYPES_PK PRIMARY KEY ( EVENT_TYPE_ID ) ;


CREATE TABLE USER_EVENT_PREFERENCES
(
USER_ID NUMBER NOT NULL ,
EVENT_TYPE_ID NUMBER NOT NULL
) LOGGING
;





ALTER TABLE EVENTS
ADD CONSTRAINT EVENTS_EVENT_TYPES_FK FOREIGN KEY
(
EVENT_TYPE_ID
)
REFERENCES EVENT_TYPES
(
EVENT_TYPE_ID
)
NOT DEFERRABLE
;


ALTER TABLE ATTEND
ADD CONSTRAINT ATTEND_USERS_FK FOREIGN KEY
(
USER_ID
)
REFERENCES USERS
(
USER_ID
)
NOT DEFERRABLE
;


ALTER TABLE ATTEND
ADD CONSTRAINT ATTEND_EVENTS_FK FOREIGN KEY
(
EVENT_ID
)
REFERENCES EVENTS
(
EVENT_ID
)
NOT DEFERRABLE
;


ALTER TABLE USER_EVENT_PREFERENCES
ADD CONSTRAINT USER_EVENT_PREFERENCES_USERS_FK FOREIGN KEY
(
USER_ID
)
REFERENCES USERS
(
USER_ID
)
NOT DEFERRABLE
;


ALTER TABLE USER_EVENT_PREFERENCES
ADD CONSTRAINT USER_EVENT_PREFERENCES_EVENT_TYPES_FK FOREIGN KEY
(
EVENT_TYPE_ID
)
REFERENCES EVENT_TYPES
(
EVENT_TYPE_ID
)
NOT DEFERRABLE
;



-- Oracle SQL Developer Modeling Summary Report:
--
-- CREATE TABLE 5
-- CREATE INDEX 0
-- ALTER TABLE 5
-- CREATE VIEW 0
-- CREATE PROCEDURE 0
-- CREATE TRIGGER 0
-- CREATE STRUCTURED TYPE 0
-- CREATE COLLECTION TYPE 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE SNAPSHOT 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- ERRORS 0
-- WARNINGS 0



To get Oracle SQL Developer Data Modeling go to Oracle's Website here

Thursday 8 January 2009

Oracle Apex and Ajax

In this post I will try to explain how you can create an Ajax Search Page, in just few steps and very little code on your Apex pages.

I have already created my Ajax Search Page, see here http://apex.oracle.com/pls/otn/f?p=38209:fishbase, by reading instructions from an example in a book called Pro Oracle Application Express book by John Edward Scott and Scott Spendolini . A very good all-round concepts book on Oracle Apex Development.


You will need 2 pages to create the Ajax Search Page.



1. Create a new blank page in your application with a search query

You need to create a new blank page in your application and you need to use the Printer Friendly page template as it has to be minimal looking with no headers, footers or tabs. This page will also have a Report region where you will write the SQL query which the Ajax Search Page will use to search your chosen table on your chosen column. In my case this page was page7.

The SQL I used for searching the "fish" table in my application is like below

select f_name_gb "English", f_name_gr "Greek", f_latin "Latin", f_name_tr "Turkish" from fish
where
(
instr(upper(f_name_gb), upper(nvl(:P7_SEARCH, f_name_gb))) > 0
or
instr(upper(f_name_gr), upper(nvl(:P7_SEARCH, f_name_gr))) > 0
or
instr(upper(f_name_tr), upper(nvl(:P7_SEARCH, f_name_tr))) > 0
or
instr(upper(f_latin), upper(nvl(:P7_SEARCH, f_latin))) > 0
)




2. Modify the Printer Friendly Page Template you used in step 1 above

In page7 above, you will also need to change the Body section of the Printer Friendly template to use the code below, that is the template code around the tag #BOX_BODY# , should be modified like below.


<ajax:BOX_B0DY>
<div id="BOX_BODY">
#BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</div>
</ajax:BOX_B0DY></td>


Now you can run page7 and it will display all records from your table without applying the search.

Now the most important bit is the fact that we will call and display page7 with Ajax into a region in another page. That is why the use of the Printer Friendly template on page7. Because page7 will display via a <div> tag in page1. One more thing, is that page1 which calls the AjaxReport page7, must have a Search Box Text item for the search field where users will type. You must create this text field in page1. This text field is your Search Box, it is where users will type and search.



3. Do an Ajax call on the page you have just created above from another page.

When users enter text in the Search filed in page1, a call is made to the Ajax page page7 from page1 via the JavaScript function on page1, see below. The search is performed and the SQL is executed. The Javascript you will add to the Header of the calling page1 performs the Ajax call and retrieves the report from page7 and places it in the <div> tag which is in a region of page1. You will see what the region with the <div> contains in later steps.

Just include your page number in lines 5 and 6 below. As you see because my Ajax page number is 7, it has 7 and P7_SEARCH included in these lines. Modify this to reflect your page number.

<script type="text/javascript">
<!--

function AjaxReportRefresh(pThis){
var l_val = pThis.value;
var get = new htmldb_Get(null,$x('pFlowId').value,null,7);
get.add('P7_SEARCH',l_val)
gReturn = get.get(null,'<ajax:BOX_BODY>','</ajax:BOX_BODY>');
get = null;
$x('AjaxReport').innerHTML = gReturn;
return;
}
//-->
</script>


4. Create a new HTML region on page1 call it AjaxReport

Next, I created a new HTML region on page1 and called it AjaxReport. This is where the results from page7 will be displayed. The source of this region is the <div> tag :


<div id="AjaxReport"><br /></div>


5. Last Step, call the Javascript function created in step 3

Everytime something is entered into the search field P1_SEARCH on page1 the JavaScript function of step 3 will be called. To do that enter the code below to the HTML Form Element Attributes section for P1_SEARCH (search box Text field item) page item on page1.


onkeyup=" AjaxReportRefresh (this)"


You are ready to test the page, type some letters and see how the page responds by filtering records from your table as you type the letter. Enjoy!

Monday 5 January 2009

Installing Oracle Apex on Oracle 10g R2 with EPG is not supported

Apex with Oracle EPG (Embedded PL/SQL Gateway) is not supported for any version below version Oracle 11g. See here, http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/otn_install.htm#BABFICIA





The Note in section "3.3.5 Configure the Embedded PL/SQL Gateway" says it all.

If you are going to use Oracle Apex on 10g, you will have to use an HTTP Server, like Apache or Oracle's version of Apache.

Do not try to configure EPG in an earlier release than Oracle 11g, you will be frustrated as this is not supported. Oracle Apex with a seperate HTTP server, that is NO EPG, is possible and supported from Oracle 9.2.0.3 or later releases.

Oracle Apex is a rapid application development environment using Oracle XMLDB. Oracle Apex comes free and you get it in Oracle 11g by default. Apex is, I think, Oracle's answer to hosted development and Cloud Computing.

I don't know for what reason one would do install Apex with EPG in Oracle 10g. The purpose of this post was to tell you that this is NOT possible.

My Oracle 10g R2 dev installation didn't have extra database applications and it was not ready for any kind Apex configuration not even for the supported configuration which uses an HTTP server.

I had to manually configure the Oracle 10gR2 installation on my dev box to be able to use Apex. I had to install Oracle Text, JVM and the Oracle XML database and do other pre-installation tasks such as change SGA component sizes, see below for details of what you have to do.

Oracle Apex and Oracle 10g database requirements

http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/pre_require.htm#BABFCFBC


Steps for manually installing the extra database components needed for Apex to an existing database installation are below.


1. install Oracle Text

You will have to create the DRSYS tablespace first and then run the following script as SYSDBA

SQL> @$ORACLE_HOME/ctx/admin/catctx.sql ctxsys DRSYS TEMP NOLOCK


2. Install Oracle JVM (for releases before 10g R1 (10.1) )

SQL>@$ORACLE_HOME/javavm/install/initjvm.sql


3. Install Oracle XML Database

As XMLDB is not installed, (this is not a DB for XML LOBs) you will have to manually install it after creating the XDB tablespace.

SQL>@$ORACLE_HOME/ctx/admin/catqm.sql change_on_install XDB TEMP

and then

SQL>@$ORACLE_HOME/ctx/admin/catxdbj.sql

4. Install Apex

SQL>@apexins password APEX_TS APEX_TS TEMP /i/

If you succeed you will get:


Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the FLOWS_030000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex