Introduction
Recently I
had a training session on developing and administrating an Oracle 12g database and,
since my background is mostly on SQL Server 2000 and above, I had some problem
in the beginning understating the new concepts. Adults, unlike kids, don’t have
the ease to lean a new language, natural or computer, without using
associations. So… what concepts of SQL Server map with the Oracle database? I
haven’t found any that article that explain this, at least on a rookie
point-of-view.
The major version
of these database, SQL Server and Oracle, as you may know, are probably the
most common databases used on big companies, and it is also very common that
large companies have both of this versions installed. In my company, where this
situation happens, we have different different DBA teams to handle the developer’s
requests. So they couldn't provide assistance on this. I didn’t understand, until the end of the training session, why it
took so long and why it was so complicated, to create a database in Oracle…
How the article is organized
I will
present an overview of the major parts of an installation of both of these
systems, with a bit of explanation and then a map between those two. You’ll see
that a concept has a totally different mapping between them and you should be
careful when addressing a team that is specialized a database system. I’ll go
to the level of the table as, from my point of view, the concepts beyond that, are
straight forward and or they map “exactly” the same or they don’t exists in one
of them.
SQL Server
After you made
a Microsoft SQL Server installation on a server, in the simplest scenario you
hand up with an instance attached. Then you make a login in the instance and
create a database. A database has, at least one schema, default is usually dbo
and some data files where the data is stored. You then create a user attached
with some login and some default schema and then, finally, you create your
table. You can create multiple instances on the same server.
Oracle
Equally,
after you install an Oracle Database installation on a server, on the simplest
scenario, you have a database where you data is going to be saved. For the sake
of simplicity, you then create an instance so you can connect to the database.
You then create a user to connect to the system and, if that user creates at
least on object, it has a schema.
Concept mapping
| SQL Server | Oracle |
| Database | Schema |
| Login/User | User |
| Data files | Database |
| Instance | Database/Instance |
First is
the centerpiece of the system. I consider, in SQL Server, the database as the most
important concept of the system. Is where you hold the data, is where you most
likely setup up isolation and most of the times where can map, on a 1 to 1 relation,
with your application. In Oracle this maps with a schema, but the concept of
schema only exists if a user has created at least on object.
SQL Server also
has a concept of a login, which can map to different database users. Oracle doesn’t
have this as a separate entity, and the user is used to identify an external entity
to the database.
Therefore,
a User/Schema in Oracle actually aggregates the concepts of Database Login and
User in SQL Server. The schema, logical aggregation in SQL Server, doesn’t map
directly in Oracle.
In Oracle
the database are the physical files that support the system, this includes the
data files, control files and redo log files, much the same way as data files in
SQL Server do, both data and transaction log.
Instance, in
SQL Server, is the highest level of separation that you can have, also, when you
connect to a SQL Server you identify an instance. In Oracle, those two concepts
are separated. The highest degree of isolation is at database level but the connection
is handled by the instance.
Conclusion
Both
systems uses familiar database concepts but with different meanings. So be
aware of your terminology when addressing a data base administrator.
As I
mentioned before, I am not an expert in Oracle and the mapping that I've
presented was collected in the training sessions. If you find any error please
comment this article and I gladly correct any mistake.
Thank you
