By: Daniel Calbimonte | Comments (6) | Related: > SQL Server and Oracle Comparison
Problem
There are several terms that are similar in SQL Server and Oracle. However, there are other terms that are different and require some clarification. This tip was written to clarify terms in Oracle and SQL Server for users who need to work in both worlds.
Solution
What are the SQL extensions in SQL Server and Oracle?
| Oracle |
SQL Server |
|---|---|
| PL-SQL (Procedural Language - Structured Query Language). | T-SQL (Transaction Structure Query Language). |
What does package mean?
| Oracle |
SQL Server |
|---|---|
| In Oracle, a package is a group of different PL-SQL subprograms and items. It is used in PL-SQL. | In SQL Server, a package is a group of tasks used by SQL Server Integration Services. It is used to automate tasks, integrate objects and can be created visually or using programming languages. |
What database files are used?
| Oracle |
SQL Server |
|---|---|
| In Oracle you can have tablespaces that have the logical storage. Physically, the data is stored in data files. There are also other files used called control files used to operate the databases. You also have the Redo log to register database activities. | In SQL Server the primary Datafiles have the mdf extensions and are used to store data. There are also secondary datafiles (with ndf extensions also used to store Database information). You also have the Log files with ldf extensions to store the database logs. The datafiles are grouped in Filegroups. |
What is an instance?
| Oracle |
SQL Server |
|---|---|
| An instance is a set of memory structures used to administer data files. You can have several instances in one server. In Oracle an instance and a database are closely related. One database has at least one instance. | An instance is a service that runs as an operating system service. It handles the system and user databases. You can have several instances in one server. In SQL Server, an instance has many system databases and can have one or multiple user databases. |
Is there a list of system tables?
| Oracle |
SQL Server |
|---|---|
| Yes. You can find a list here. | In SQL Server, most of the information is stored in views (we call the views catalog views). You can find a list here. |
What is a data dictionary?
| Oracle |
SQL Server |
|---|---|
| The data dictionary is a set of read-only tables that store information about the database. | In SQL Server, the term data dictionary is used differently. The database objects can be retrieved using the system stored procedures and a data dictionary is an explanation of the tables, columns, etc. |
What are the system databases?
| Oracle |
SQL Server |
|---|---|
| In Oracle the instance and the database are closely related, there are not system databases like in SQL Server. |
In SQL Server each instance has the
system databases which includes: |
What are the dynamic performance tables?
| Oracle |
SQL Server |
|---|---|
| In Oracle, the dynamic performance tables are read only views used to identify performance problems in the database. The prefix used is V$ . | In SQL Server the name of these views (and functions in the Microsoft case) are Dynamic Management views and functions. The prefix used is sys.dm_. Beware that the Dynamic Management views include other views that are not used for performance purposes. |
Where are changes in a database registered?
| Oracle |
SQL Server |
|---|---|
|
In Oracle it is uses the Redo Log . It consists of 2 or more files used to stored database changes as they occur. The Redo Log is used to restore the information at a specified moment. |
In SQL Server the changes are stored in the transaction log file. It has the same purpose as the Redo Log. |
How can I store the information temporarily?
| Oracle |
SQL Server |
|---|---|
|
In Oracle, it uses a tempfile. You can create a temporary table space used to store temporary data. |
In SQL Server, the data is stored in tempdb which is a system database used for this purpose. |
Is there a database service for the Cloud?
| Oracle |
SQL Server |
|---|---|
|
Yes, there are several companies that offer Services in the cloud, but Oracle also offers services. For more information, review this link. Oracle is offering not only databases, but other services in the cloud like ERPs, CRMs, etc. For more information about these services, refer to this link. |
Yes, there are several companies that offer Services in the cloud, but Microsoft also offers these services. The cloud in the Microsoft world are in the Microsoft Azure Portal. Azure is the key word for the Microsoft Cloud Services. You can have Linux, UNIX, Microsoft Machines in Azure. |
What tools are used for business intelligence?
| Oracle |
SQL Server |
|---|---|
|
There are several tools used for BI. In Oracle they are called the OBIEE (Oracle Business Intelligence Enterprise Edition) products:
|
Yes, there are several BI tools:
|
Are there command line tools to access databases?
| Oracle |
SQL Server |
|---|---|
|
Yes. You can use SQL*Plus. |
Yes. You can use sqlcmd or PowerShell. |
Next Steps
For more information about SQL Server and Oracle, refer to these links:
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
