SQL Server 2005 : SQL Server is a relational database management system (RDBMS) developed by Microsoft thats designed specifically for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft with some additional features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.
Oracle 9i :
Oracle is a relational database management system (RDBMS) from Oracle Corporation, which runs on more than 80 platforms. Oracle was ntroduced in the late 1970s, it was one of the first database product to run on a variety of platforms from micro to mainframe. The Oracle database is Oracle's flagship product, and version 11g was introduced in 2007.
The "i" and "g" Versions :
The Oracle version with "i" reflects the database support for Internet with its built-in java Virtual machine. And the version with "g" emphasis for grid computing which enables clusters of low-cost, industry standard servers to be treated as a single unit.
Comparision SQL Server and Oracle
This section contains information comparing both the Database technologies used in MIGSORA project to migrate data, Microsoft SQL Server and Oracle database. It includes the following sections:
Schema Migration
Data Types
Data Storage Concepts
Data Manipulation Language
Schema Migration:
The schema contains the definition of database -specific objects like tables, views, indexes, users, stored procedures etc.
The schema migration topics discussed here include the following:
Object Similarities
Both RDBMS Database SQL Server and Oracle have many similarities and dis-similarities.
Comparision of both database in reference to Schema is given below in table chart.
Table Schema Objects in Oracle and Microsoft SQL Server
Oracle
Microsoft SQL Server
Database
Database
Schema
Database and database owner (DBO)
Tablespace
Database
User
User
Role
Group/Role
Table
Table
Temporary tables
Temporary tables
Cluster
Cluster
Primary Key
Primary Key
Foreign Key
Foreign Key
PL/SQL Procedure
Transact-SQL (T-SQL) stored procedure
Packages
NA
Synonyms
NA
Snapshot
NA
View
View
â- Schema Object Names
Object Names
Every RDBMS have reserved word which a user can't use while assigning a name to database objects.
Same both SQL Server and Oracle have reserved words but they differ in both the databases. Many
Oracle reserved words are valid in Microsoft SQL Server. For Example Date is the reserved word in Oracle
But it can be use as a column name in SQL Server as it was not a reserved word in case of Microsoft SQL Server.
Hence no column was allowed to have DATE as a column name but in SQL a column can be named as a DATE.
Using of reserved word as a schema name makes it impossible to use the same name across the database.
One should choose a schema object name that is unique by at least one characteristics, and need to make it sure that
The name used for object was not a reserved word for that database.
â- Table Design Considerations
This particular section discusses many table creation issues which one should think about while
Converting SQL Server database to Oracle 9i database. Issues discussed are as follows :
â- Data Types
DATETIME Data Types
Precision of Datetime datatype in SQL Server is 1/300th of a second whereas Oracle don't have any Datetime datatype rather Oracle have Timestamp which has a precision of 1/100000000th of a second. Apart from Timestamp datatype Oracle also have Date data type that stores date and time values accurate to one second.
â- Entity Integrity Constraints
Entity Integrity Constraints
In case of Microsoft SQL Server you can define a Primary key through CREATE Table statement
or a ALTER TABLE statement. Same you can create a Primary key in Oracle with CREATE Table or ALTER TABLE statement, but Oracle provides a declarative referential integrity by automatically creating a Unique index to enforce the integrity.
Data Types
This section provides a table showing the base data types used by Microsoft SQL Server and Oracle database available and how they are mapped to each other.
Table Datatypes in Oracle and Microsoft SQL Server.
SQL Server
Oracle
INTEGER
NUMBER(10)
SMALLINT
NUMBER(6)
TINYINT
NUMBER(3)
REAL
FLOAT
FLOAT
FLOAT
BIT
NUMBER(1)
CHAR(n)
CHAR(n)
VARCHAR(n)
VARCHAR2(n)
TEXT
CLOB
IMAGE
BLOB
BINARY(n)
RAW(n)/BLOB
VARBINARY(n)
RAW(n)/BLOB
DATETIME
DATE
MONEY
NUMBER(19,4)
SMALLMONEY
NUMBER(10,4)
TIMESTAMP
NUMBER
In addition to the data types provided in above table chart users can also define their own data type as per the
there requirement by using predefined base datatypes. You can map datatypes from Microsoft SQL Server to Oracle with equivalent data types listed in above table.
Data Storage Concepts
This section defines the detailed description of Datastorage concepts in both SQL Server and Oracle Database.
Microsoft SQL Server
Oracle
Database Devices:
A database device is mapped to the specified
physical disk files.
Datafiles:
All physical data of all logical structures in a tablespace are stored by creating a datafiles in Oracle. The size of all datafiles is the size of tablespace. The size of tablespace is the total storage capacity of database. Once created a size of datafile cannot be changed.
Page:
Many pages make up a database device. Each
page contains a certain number of bytes.
Data Block:
One data block corresponds to a definite
number of bytes, of database
space, on the disk. The size of the data
block can be specified when creating the
database
Extent:
Each extent consists of eight pages. Space is
allocated to all the databases in increments of
one extent at a time.
Extent:
An extent is a specific number of
contiguous data blocks, obtained in a single
allocation.
Log Devices:
Log devices are the logical area assigned to store the log
of database. The database object to store the log can be specified while creating the database.
Redo Log Files:
Each database in oracle have a set of atleast two or more redo log files. The number of redo log files to be created can be specified while creating database. All changes made to database were recorded in redo log files.
NA
Control Files:
Each database has a control file. It is one of the important file of oracle database. This file
records the physical structure of the
database. It contains the following
information:
â- database name
â- names and locations of a database's
datafiles
â- names and locations of redo log files
â- time stamp of database creation
The conceptual differences in the storage structures do not affect the conversion
process directly. However, the physical storage structures need to be in place before
conversion of the database begins.
Methods:
Objective for migration process can be achieved through four steps. The first step is to capture a snapshot of the SQL Server 2005 database. In this step meta-data of SQL Server 2005 would be gathered and stored in repository for migration. The captured repository model would be then converted to an Oracle model. This model represents what the Oracle database would look like. The converted model is then fired as a SQL script, which when run against an Oracle database, will generate all the migrated users, tables, views and other relevant objects. The last step is to migrate or move the data, which can be done using scripts by utilizing the data move tools like Bulk Copy Program (BCP) and Oracle's SQL*Loader.
