Sam describes it as "a devil's trap for angels" but it differs because angels can still use some of their powers while trapped within the flames. Sam and Dean keep bottles of the oil in the truck of the Impala, each of which was presumably supplied by Castiel.
It is also discovered that Holy Oil is one of the components needed in order to see hellhounds. Dean scorches two pairs of glasses in Holy Oil fire. 
Holy Fire is capable of hurting, though not killing demons. Castiel throws Meg onto a ring of Holy Fire to escape and Sam douses Abaddon in Holy Oil and sets her on fire. Both are not killed, but Abaddon is left in so much pain she abandons her meatsuit and flees.