Now, first a quick refresher:
SID - System Identifier uniquely identifying a database instance. Each database instance has an SID - one and only one.
Service Name - An alias to one or more INSTANCES (useful for clustering, failover, without changing end-user configurations) - introduced in Oracle 8.
One SID could have a hundred service names pointing to it if you wanted to...but I'm pretty sure at a minimum it will have one. When you create an instance, Oracle makes you name them both. The 10g Configuration Assistant on Windows has a field named Global Database Name, which maps to Service Name. The SID is
frequently, but not always, the same as the initially specified Global Database Name.
After the creation of a database instance, Oracle tools refer strictly to the SID - after all, that is the name of the instance you'd be configuring. Since Service Name is just an alias, you wouldn't configure the character set or memory options of an alias.
Why do I care what the service name is if I have the SID?
Because knowing it lets me avoid relying on properly configured tnsnames.ora on each client machine. Sometimes, tnsnames.ora files appear to be controlled by a dark magic - the same tnsnames.ora file works fine on one machine, but fails inexplicably on another client machine. Sometimes TNS doesn't work right - and for a Java application that's going to be using JDBC, needing to configure tnsnames.ora is unnecessary work. For the command line tools like SQLPLUS and the import/export tools, you can use the EZCONNECT URL format and avoid TNS completely. There's a catch - EZCONNECT only accepts Service Names.
So, how do you determine the service name? Here's one way...
This assumes you have access to the oracle server. Provided you do, simply use the command:
This will print out various status information, as well as a compact listing of configured service names and which instances they point at. You could also use lsnrctl services for slightly more verbose services output - but depending on how many service names and instances there are, it may be harder to look at.
It's kind of anti-climatic, but that simple command ended the confusion. I thought Service Name was preferred (as it is more flexible than directly specifying the SID), but apparently it isn't always the case.