I would recommend reading historian D. C.A. Hillman's book,The Chemical Muse: Drug Use and the Roots of Western Civilization, in which he provides numerous primary sources and historical accounts of physicians doing things like prescribing opium, sedatives, and narcotics.

His historical hypothesis (albeit a far-fetched one) is that the free and open use of drugs in the ancient Greek and Roman societies constituted an integral element of a free society, and may have favorably developed the course of Western civilization.

It is well established that opium poppies were cultivated in the Roman Empire. Two main points of contention, to my knowledge, are the rate of development in the poppies' usage and how understood the effects of opium were.

The passing but consistent mention of poppies in classical writing suggests that they had a rootedness in ancient culture and were not an alien idea to readers in the Roman Empire. Livy recalls in his History of Rome a scene in which the King of Rome's son, Sextus Tarquinius, has successfully infiltrated the highest ranks of the nearby city of Gabii. He sends a message to his father asking what he should do. In response, his father walks around his garden beheading poppies. This is evidence of their cultivation on the Italian peninsula around 500 years before the Roman Empire arose. At this time, they had at least culinary and ornamental uses.

It is likely that the use of the opium poppy as a soporific and as an analgesic in Europe was imminent. However, knowledge of these effects and others seems more centred in Ancient Greece, where it remained precarious. It is possible, based on citations in Dioscorides' De Materia Medica, that opium's possible effects generated a legacy of debate, caution, and uncertainty in ancient society.

By the time the Roman Empire was established, opium's soporific effects were without a doubt known and exploited in Rome. Its cultivation, harvest, and preparation were all well-documented. One Roman Emperor, Marcus Aurelius, was famously addicted to opium. The exact reasons as to why are debated. Some believe Marcus Aurelius used opium to overcome stress; Thomas W. Africa puts forward the argument that opium was an obvious form of escapism for the aloof Stoic, such that "Drugs incapacitated Marcus less than they insulated his natural reserve and fired his philosophic insight to the fever pitch of vision".

This all suggests a total absence of taboo surrounding opium and opium use, at least amongst powerful Romans and doctors (and indeed, the case of Marcus Aurelius indicates that there was a symbiotic dialogue about the effects of opium between the two camps). The aforementioned paper, importantly, quotes Galen, and suggests that ideas about addiction and withdrawal were not yet formal medical concepts. I do not know how great the connection between the historic stigmatization (more specifically, illegality) of drugs and the understanding of their less desirable effects is, but I think it is a thought worth bearing in mind: that some Romans would have used opium to excessive or socially undesirable extents (such as suicide) without controversy.

For other substances, Celsus' De Medicina is the greatest contemporary authority on various Roman remedies.

One interesting medicine was silphium. I read about this a long time ago - but I believe it was an contraceptive native to north Africa, specifically Libya. When the Roman Empire expanded to Libya, harvesting and trading silphium became so prolific that the plant went extinct. The Wikipedia page shows the sheer value and abundance of this particular substance in ancient Rome and is absolutely worth reading.

Ultimately, I believe drugs were relatively common in the Roman Empire with the fundamental two being opium and alcohol.

There is a not-widely-accepted theory that the ritual drink (kykeon) used in the Eleusinian Mysteries acted as a psychedelic, possibly made using rye infected with ergot (a precursor to LSD) or with DMT (which occurs in small amounts in some Mediterranean plants). I don't know of any Classical text that refers to the use of hallucinogens for recreation or otherwise outside of a religious context.

Followup question: were drugs used by the famous prophetic oracles?

No,one has mentioned the use of marijuana. I became curious about this after watching HBO's Rome which I thought was more or less historically accurate. In it some of the aristocracy were smoking marijuana from a pipe. Does anyone know if the ancients smoked marijuana?