Getting Service Name from SID in Oracle DB

select sys_context('userenv','service_name') from dual
select sys_context('userenv','instance_name') from dual

to get all available service_name s: (needs privileged access)

select value from v$parameter where name = 'service_names';


for more info on the sys_context, read here