Filipe Miranda (7/2/2009)
Actually both statements are false.
In Oracle the user is not defined as unique in the instance, but unique in the database, as in SQL Server. Oracle's limitation of one database per instance is another issue.
Objects in SQL are created in a schema, not in a database. It looks like they are created in a schema because there's always a default schema for each database user.
The bigger difference is that SQL Server, starting with 2005, finally fixed the issue of confusing user with schema.
An Oracle instance can be thought of as the running database in memory. To say that the uniqueness of a user is in the DB and not in the instance is perhaps misleading, as the instance does not define any
I'm not sure why one DB/instance would considered a limitation, but it should be noted that a database can have many instances ("instances per DB" instead of "DBs per instance"), as when Oracle RAC is used
, so the "1:1" ratio mentioned by an earlier poster is not always correct.
I'm not sure how SS is structured, but in Oracle, every object -- including schemas -- resides wholly within a database. For most intents and purposes, an Oracle user is synonymous with an Oracle schema. "user" = "schema" The difference is in the semantics. This does not appear to be the case for SS, although yes the concept of a schema was much more profound in 2005 than in 2000, as you mention.
A great place for Oracle information is in the documentation. http://tahiti.oracle.com
has all docs for recent versions (no login or account required!). I'd recommend to anyone wanting to look at Oracle from a technical standpoint to start with the Concepts manual. Pay special attention to the SGA (a memory structure in the instance). It's your best friend and worst enemy at the same time.
In 13 years (already?) of Oracleness, the term "instance" is not often used, although it's very important conceptually. The common way to refer to it is to refer to specific (memory) constructs within the instance, such as the SGA, PGA, UGA, and more specifically, the buffer cache, shared pool (how could I forget?), library cache, redo log buffer, etc.