The Talmud speaks of several rabbis who successfully appealed to G-d to revive a deceased colleague or some other individual. As I recall, one revived rabbi asked to be return to the world of the dead. I can't place the names or incidents, however, and I'm having trouble searching the issue without getting lots of info about the rabbis' view on how Hashem will revive the dead in the Messianic Era. Can anyone help?
Rabbi Chanina bar Chama brought back one of Antoninus' servants to life (Avodah Zara 10b):
Rabbi Haninah bar Hama thereupon went out but found that the man had been slain. Thought he, “How shall I act now? Shall I call and say that the man is dead?—but one should not bring a sad report; shall I leave him and walk away?—that would be slighting the king.”
So he prayed for mercy for the man, and he was restored to life. He then sent him in. Said Antoninus: “I am well aware that the least one among you can bring the dead to life, still when I call let no one be found with you.”
here is one from Megillah 7b (via dafyomi.co.il)
(a) (Rava): A person is obligated to become drunk on Purim until he cannot distinguish 'cursed is Haman' from 'blessed is Mordechai.' (b) Rabah and R. Zeira ate their Purim Seudah together; Rabah got drunk and slaughtered R. Zeira. The next day he prayed, and restored him to life. 1. The next year, Rabah invited R. Zeira to eat with him again. R. Zeira declined, because miracles do not always happen.
The Gemara you are looking for is in Bava Kamma 117:b. Rav Yochanon revived Rav Kahana after having caused his death and according to the second Pshat of Rashi, Rav Kahana told him he prefers to remain dead then to experience death again.