Holy Water is just plain water, but a holy figure has dipped their hands in the water. Vampires hate this. Holy water is used as a sub weapon in some Castlevania games. It is mainly effective against vampires, and has some other minor uses in the games. Such as burning a fire and making it blue fire.
The Holy Water weapon is used by making impact with the floor. There is in fact two different types since the beginning of the Castlevania series. One is the classic type and the other the modern type.
 Classic Type
Released a green or blue flame in an upward direction, with a lasting time of several seconds. The player made use of the weapon by throwing it upward to come back down in an arc.
 Modern Type
Rather than doing a time consuming arc, this style of Holy Water is simply thrown forward to connect with the ground. Upon collision, it soars forward in a wave at the enemy. As such this weapon takes less skill to use, but to compensate the new style does less damage. This means it cannot damage enemies that are above and if it hits an enemy (before hitting the ground) it sometimes won't turn into a wave.
 Lords of Shadow
In Lords of Shadow, Gabriel will get the Holy Water as a sub-weapon after breaking into the abbey of Vincent Dorin, who was using the holy water to protect himself from the vampires attacking Wygol Village. Gabriel can throw the holy water flask, smashing it off the ground and causing a wave of blue flame to burst forth scorching monsters. Alternatively, it can be used with Light Magic to create a holy aura which surrounds Gabriel for a brief period of time. It is especially effective against vampires and other undead creatures. It is said the Holy Water in Lords of Shadow comes from the tears of the "14 Holy Helpers".