An Oracle instance can run only one database, and schemas are widely used to logically group database objects in Oracle.
A Microsoft SQL Server instance can run multiple databases, and although SQL Server also supports schemas, an Oracle schema is usually migrated to a SQL Server database, not to a schema.
Oracle Schema to SQL Server Database Migration:
In Oracle, each schema usually contains logically independent objects, often used by different applications, so their migration to separate databases in SQL Server gives the following benefits:
This approach also has a disadvantage: if there are cross-schema object references, you have to modify your queries from schema.object to database..object syntax:
Oracle:
-- Reference a table in schema SALES in Oracle SELECT * FROM sales.products;
SQL Server:
-- Reference a table in database SALES in SQL Server SELECT * FROM sales..products;
In Oracle, CREATE USER statement in used to create a database user and associated schema. Optionally, schema data can be stored in a separate tablespace.
Oracle:
-- Firstly create a tablespace to store the user data CREATE TABLESPACE sales_data DATAFILE 'sales_data.dbf' SIZE 1M AUTOEXTEND ON; -- Create a user and assign the default tablespace CREATE USER sales IDENTIFIED BY password DEFAULT TABLESPACE sales_data QUOTA UNLIMITED ON sales_data;
When a table is created in the sales schema, it is stored in the sales_data tablespace by default.
Oracle:
-- Table is stored in sales_data tablespace by default CREATE TABLE sales.regions ( id NUMBER(10), name VARCHAR2(90) );
You may also have a separate tablespace to store indexes and LOB data.
Oracle:
-- Create a tablespace to store indexes CREATE TABLESPACE sales_idx DATAFILE 'sales_idx.dbf' SIZE 1M AUTOEXTEND ON; -- Allow SALES user to store data in SALES_IDX tablespace ALTER USER sales QUOTA UNLIMITED on sales_idx; -- Create an index and store it in sales_idx tablespace CREATE UNIQUE INDEX sales.regions_id_idx ON sales.regions (id) TABLESPACE sales_idx;
In SQL Server, you can use CREATE DATABASE statement to create a database and assign PRIMARY filegroup to store the database data.
A filegroup in SQL Server is similar to tablespaces in Oracle, it is a logical storage for table and index data that can contain one or multiple OS files.
SQL Server:
-- Create database and define the default PRIMARY filegroup to store the data CREATE DATABASE sales ON PRIMARY (NAME = sales_data, FILENAME = 'C:\MSSQLData\sales_data.ndf', SIZE = 3MB);
When a table is created in the sales database, it is stored in the PRIMARY filegroup (sales_data) by default.
SQL Server:
-- Table is stored in PRIMARY filegroup by default CREATE TABLE sales..regions ( id INT, name VARCHAR(90) );
In SQL Server, you may also have a separate filegroup to store indexes and LOB data.
SQL Server:
-- Add a filegroup to the database ALTER DATABASE sales ADD FILEGROUP sales_idx; -- Add a OS file to the filegroup ALTER DATABASE sales ADD FILE (NAME = sales_idx, FILENAME = 'C:\MSSQLData\sales_idx.mdf', SIZE = 3MB) TO FILEGROUP sales_idx; -- Create an index and store it in sales_idx filegroup CREATE UNIQUE INDEX regions_id_idx ON sales..regions (id) ON sales_idx;
Oracle 11g R2 SQL Language Reference
Microsoft SQL Server 2012 - Books Online
Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012