Concept mapping between SQL Server and Oracle

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 

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