1

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

  • I could find some answers here and by creating a new user, I could create a table with a "schema". I just don't think this is usefull. I would like to create something as an object inside the user. – Racer SQL Dec 11 '18 at 19:36
  • For Oracle: USER === SCHEMA. I don't understand MS-SQL things. Can you add some information about "how you use Schemas in MS-SQL". – Michael Kutz Dec 11 '18 at 19:56
3

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.

  • SQL Server schemas are also linked to users. If an AUTHORIZATION clause is omitted, the authorization (owner) defaults to the user creating the schema. SELECT name, USER_NAME(principal_id) AS schema_owner FROM sys.schemas; – Dan Guzman Dec 12 '18 at 13:04
  • I stand corrected. Thanks for pointing that out. It wasn't scripted out when I was previously checking the SQL Server schema. I re-read the documentation and can verify that your statement is absolute correct. I'll amend that to my answer. – hot2use Dec 12 '18 at 13:10
  • "An Oracle Instance will contain one database" is no longer true since Oracle 12. One instance can contain multiple pluggable databases – a_horse_with_no_name Dec 12 '18 at 13:28
  • If I look at Oracle Database Architecture and 13 Oracle Database Instance then pluggable databases aren't mentioned in the general context of the concepts of Oracle databases and instances. Only in the context of multitennant architecture are CDB (container database) are PDB (pluggable databases) mentioned. I will add a reference. I stand corrected again. – hot2use Dec 12 '18 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.