Why did the Prophet (peace be upon him) not change the name of the great Companion, Al-Sakran ibn 'Amr (may Allah be pleased with him), even though the name carries meanings of intoxication and mental disturbance?

There is no recorded statement from scholars regarding what you asked about. Changing a name with an ugly meaning is recommended, but not obligatory. This is indicated by what Al-Bukhari narrated from Sa'id ibn Al-Musayyab, that his grandfather, Hazn, came to the Prophet, peace and blessings be upon him, who asked him his name. He said, "Hazn." The Prophet said, "Rather, you are Sahl (easy)." Hazn replied, "I will not change a name my father gave me." Ibn Al-Musayyab said, "And hardship (al-ḥazūnah) remained among us thereafter." Al-Tabari holds that the Prophet's changing of names was by way of choice, not prohibition, and that names are for distinguishing. He bases this on the Prophet's acceptance of Hazn's refusal to change his name; for if it had been sinful, the Prophet would have compelled him to change it.