The Water of Oblivion is the most dangerous thing in the Land of Oz.
The fountain and its water were placed centuries ago by Glinda, to deal with the wicked king who ruled Oz at that time. Once he was tricked into drinking from it, he forgot his wickedness, and grew wise.
Since then it has remained in the courtyard to deal with enemies. When the Nomes, Whimsies, Phanfasms, and Growleywogs tunneled under the Land of Oz, hoping to invade, they came up near the fountain. Ozma had used her magic belt to fill the tunnel with dust so they would all become thirsty, and when they emerged they all drank and forgot themselves. Roquat, leading the invasion, forgot his name and would come to be known as Ruggedo. (The Emerald City of Oz)
Ruggedo became wicked again, slowly regaining some of his memories, and finally Ozma had him exposed again, hoping this time to keep him in Oz this time so that he could grow wise rather than wicked. (The Magic of Oz)
However, the fountain's effects wore off Ruggedo again, and he came to hate his life in Oz. Over his life he would be exposed more times, but would always recover his memories. (Kabumpo in Oz, etc.)
In other adaptations
In Alexander Volkov's series, the analogue of the Water is called "Soporific Water". It used to come once a month from an underground spring, after the spring was accidentally destroyed, it was remade into a well. The water puts a person into an enchanted sleep, virtually indistinguishable from death. The duration of the sleep is in proportion to the amount of water drunk. Even the vapors of the water can cause the effect (this can be slowed down by physical contact with diamonds).
After waking up, the amnesia is even more complete than in Baum's books; the person's mind is that of a newborn baby. However, the memories can be restored over the course of a few days. Also, false memories could be implanted, which was used to reeducate a large amount of parasites at one point, as well as at least one outright criminal. The water lost its qualities after one day, in a well stoppered bottle, it maintained limited effects for at least three days. Then, the effects includes no memory loss, and only a few hours' sleep. Interestingly, even in that case, the sleep was also shown to cure at least one type of drug addiction.
Lost in Oz
The Winged Monkeys throw "water balloons of oblivion", which cause some short-term memory erasure. They attempted to hit Dorothy and her friends with them, to erase their memories of their first adventure, but they missed. ("Lost in Oz")
In creating the Water of Oblivion, L. Frank Baum drew, perhaps unwittingly, on a rich store of folklore and legend. The idea of a drink that purges memories can be found in Norse and Hindu and other world mythologies. In the Western tradition it is most familiar in the water of Lethe in Greek mythology: the souls of the recently dead drink from the River Lethe to forget their old lives.
Baum is perhaps unusual in the stress he places on the moral influence of the Water of Oblivion. In his works, the drink not only wipes the memory but leaves the drinkers "as innocent as babes." (Amnesia caused by psychological stress has the same moral dimension in Aunt Jane's Nieces on Vacation.)