ER modeling Tools now on the Web!

I was looking at Google Apps Gallery and came across this ER modeling tool which is very cool and I decided to blog about it.


The tool is called GAE SQL Designer and is done by Jason W. Miller. It is an ER modeling tool on the web! All you need is a browser and you start modeling databases, with no installs and no extra software. You can access it directly here http://gaesql.appspot.com/

Excellent, I liked the idea very much! One thing that is missing is the ability to generate SQL from your data models. There is another less intuitive google app for that here: http://code.google.com/p/wwwsqldesigner.

Apart from that, I think Jason's GAE SQL Designer will be useful in quick prototyping and learning. Well done Jason!

Oracle SQL Developer for Ubuntu

Here is a nice post about a packaging for Oracle SQL Developer on Ubuntu.

http://blog.kizoom.com/2009/02/oracle-sqldeveloper-for-ubuntu-version.html

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'



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