It seems <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property> is not mandatory in hibernate.cfg.xml file. But what is the real purpose of this property..? I need a real time example..

This simply accommodates for differences between database implementations.

What type of example are you looking for? You can download the source code for various dialect implementations, or read the JavaDocs.

What is the fascination with the dialect? It really is one of the most minor aspects of learing Hibernate.