Sql-server – Whats the term in Oracle, when I’m speaking about SCHEMAS on SQL Server

oracle-11g-r2sql-server-2008-r2

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

Best Answer

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.

Related Solutions

How to sync instance A to instance B without losing any of the data already on instance B

The default, built-in, Oracle answer would probably be Oracle Streams.

The stream can propagate information within a database or from one database to another.

A possible alternative would be to create materialized views of A on B. You may have to union the data from the materialized views with the existing tables or create views that do the union. The materialized views could be refreshed daily.

The options already listed may also be good fits depending on the requirements. You should consider them all.

Update: As your comments indicate that you would like to replicate packages as well, you could integrate those into your change management process so that when changes are made to A they are also made to B. You would need to use a different schema since you want to preserve the originals.

On the other hand, the requirement for moving lobs as well makes things more difficult. If you want to use just one technology to handle all of your requirements you will probably have to go with datapump. B could be setup to pull the tables with blobs and the packages from A into a separate schema.

You may still want to consider creating some views to union the data from the existing tables in B with the transported tables from A.

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.