6

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?

dobri
  • 57
  • 1
  • 4

3 Answers3

7

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

While @Gordon Linoff 's answer is pretty much perfect explanation of the idea behind schemas in both databases, to answer your practical problem:

You create a schema as a user:

CREATE USER Accounting IDENTIFIED BY some_password;

And then create objects in that schema with:

CREATE TABLE Accounting.Payments
(
  ID NUMBER(19),
  Amount NUMBER(19,4)
)
marmarta
  • 838
  • 5
  • 20
1

A historical note that helps explain some of the confusion: Up to MS SQL Server 2000, Schema meant nearly the same thing as in Oracle. And MS SQL Server was based on Sybase SQL Server, which was also perhaps more similar to Oracle. Then by 2005, MS took away the binding between user and schema, though a default schema name like dbo is based on a user name.

Good answer is in the DBA Stack Forum: What is the difference between an Oracle and Microsoft schema?

charles ross
  • 570
  • 4
  • 15