Oracle equivalent of SQL Server SCHEMA

Question

I have a task to migrate a SQL Server database to Oracle.

There are several different SCHEMAs in our SQL Server database and I have to create something similar in Oracle. In SQL Server SCHEMA is like a namespace in C#. But as far as could understand from the Oracle docs, Schema in Oracle has quite a different meaning from that in SQL Server.

To be more specific, I have the following SQL statement in SQL Server:

CREATE SCHEMA Accounting

CREATE TABLE [Accounting].[Payments]
(
    ID BIGINT,
    Amount MONEY
)

So what is the equivalent of this script in Oracle?

Answer 1

The Oracle equivalent of a SQL Server schema is, well, a schema. An Oracle server talks to a single database and that is the "real" difference between the two: A SQL Server instance can connect to multiple databases whereas an Oracle instance connects directly to only one. Because of this, SQL Server uses a three-part naming convention within a server and Oracle only has a two-part naming convention.

In both, schema objects are generally speaking the unit of security -- they are convenient for assigning permissions to groups of objects. The two databases differ on some very important points. In Oracle, a schema is essentially synonymous with a user. SQL Server was once organized this way, but now a schema is separate from users (allowing objects to be moved between schemas for instance).

In SQL Server, permissions do not have to be at the schema level, although that is often convenient for organizational purposes. For instance, you might have underlying tables that users have no direct access to in one schema. You can have another schema with views and the schema has permissions to access the tables. Then, a new view added to the schema automatically has the "right" permissions.