Whats the term in Oracle, when I'm speaking about SCHEMAS on SQL Server

Question

Here, we have our applications developed both for oracle and SQL Server.

Devs are trying to fix their own way to be organized, using VW on views, PR on procedures and etc.

they told me that they're going to use something like DBO.REPORT_XXX to name tables, and I said "why don't you guys use Schemas"?

but, can I achieve something like sql server schemas on oracle?

I'm new with oracle and everything is different (like schema on oracle is the name of the database, that's a user)...

Answer 1

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).

Schemas

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:

USE [StackExchange]
GO

/****** Object:  Schema [DBT]    Script Date: 12.12.2018 09:42:26 ******/
CREATE SCHEMA [DBT] AUTHORIZATION TheOwner
GO

^{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 DBT schema:

USE [StackExchange]
GO
----------- 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
 PROFILE "DEFAULT"
 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
(
  Identity  INTEGER
);

Summary

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 dbo. (e.g. dbo.UserTable)
• An Oracle table has to be created in a specified schema (e.g. ORACLEUSER.ORACLUESRTABLE)

^{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.