You can't map everything on a one to one relationship when comparing Oracle and SQL Server. There are some similarities between both products and then there are concepts that could pass as two concepts within the other product. Let's start out with the basic stuff (Columns, Tables, ...) and continue up until we reach the instance.
Oracle Database | SQL Server
Column | Column
Table | Table
*) Schema | Schema
*) User | Schema
*) User | Database User
*) User | SQL Login
Data Dictionary | INFORMATION_SCHEMA
Tablespace | Filegroup
Oracle Database | SQL Server Database
Oracle Instance | SQL Server Instance
Reference: [Microsoft SQL Server 2008 - Practical SQL Server 2008 for Oracle Professionals] ()
Database and Instances
An Oracle instance will normally contain one database.
The exception being when an Oracle instance has been configured to run a container database (CDB), which will contain multiple pluggable databases (PDBs).
Reference: 17 Introduction to the Multitenant Architecture (Oracle | Docs | Database Concepts)
A SQL Server instance normally contains multiple databases.
A newly created SQL Server instance will start off with the basic four system databases (master, model, msdb, tempdb).
The database at the Oracle level is the container for multiple schemas. The same is valid for the SQL Server. However, there is a difference *) between the Oracle schema and the SQL Server schema.
... in SQL Server
In SQL Server you can create a schema like this:
/****** Object: Schema [DBT] Script Date: 12.12.2018 09:42:26 ******/
CREATE SCHEMA [DBT] AUTHORIZATION TheOwner
Reference: CREATE SCHEMA (Transact-SQL) (Microsoft | Docs | SQL)
Any newly created object (table, view) can be linked to a specific schema, otherwise it is linked/stored inside the default schema
dbo, unless the database user creating the new object has a differente default schema.
they told me that they're going to use something like DBO.REPORT_XXX to name tables
In this case
dbo. is the default schema for any object created in SQL Server. The
dbo schema doesn't have to be explicitly created as it exists as soon as you create a database. You could create your own schemas and create tables within that schema. Here an example using the previously crated
----------- schema.tablename -------
CREATE TABLE [DBT].[Application](
[ID] [int] NOT NULL,
[APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
... in Oracle Database
In Oracle the schema is not a stand-alone concept/object. An Oracle schema is linked to an Oracle user. When you create an Oracle user a schema with the same name is created for that user. The
CREATE statement for an Oracle user doesn't give a hint that the schema will be created:
CREATE USER ORACLEUSER IDENTIFIED BY YourPasswordHere1084
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
When you create objects (tables, views) you create them inside a specific schema. There is no default schema.
CREATE TABLE ORACLEUSER.ORACLUSERTABLE
So in summary the Oracle user has a schema that has the same name as the user. The user is synonymous with the schema. Hence the *) pretty weird comparison in the table at the top.
- The Oracle user (password/permissions) can be compared to the combination of SQL Server login (password) and the SQL Server database user (permissions).
- The Oracle schema can be compared to the SQL Server schema.
- The Oracle schema cannot exist without an Oracle user.
- The SQL Server schema can exist without a corresponding user, but requires to be linked to a database user.
- A SQL Server table can be created in the default schema
- An Oracle table has to be created in a specified schema (e.g.
Reference: Difference between database vs user vs schema (DBA Stack Exchange)
Answering Your Question
but, can I achieve something like SQL Server schemas on Oracle?
Yes, you can. You create an Oracle user with password and a schema is automatically created. And yes, you could create a
dbo user/schema in Oracle.