2
Databases

This chapter includes the following sections:

• Schema Migration

• Data Types

• Data Storage Concepts

• Schema Objects

• Data Manipulation Language

Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.

The schema migration topics discussed here include the following:

• Schema Object Similarities

• Schema Object Names

• Table Design Considerations

Schema Object Similarities

There are many similarities between schema objects in Oracle, MS SQL Server, and Sybase Adaptive Server (Sybase). However, some schema objects differ between these databases, as shown in the following table:

Table 2-1 Schema Objects in Oracle and MS SQL Server/Sybase

Oracle	MS SQL Server/Sybase
Database	Database
Schema	Database and database owner (DBO)
Tablespace	Database
User	User
Role	Group/Role
Table	Table
Temporary tables	Temporary tables
Cluster	N/A
Column-level check constraint	Column-level check constraint
Column default	Column default
Unique key	Unique key or identity property for a column
Primary key	Primary key
Foreign key	Foreign key
Index	Non-unique index
PL/SQL Procedure	Transact-SQL (T-SQL) stored procedure
PL/SQL Function	T-SQL stored procedure
Packages	N/A
AFTER triggers	Triggers
BEFORE triggers	Complex rules
Triggers for each row	N/A
Synonyms	N/A
Sequences	Identity property for a column
Snapshot	N/A
View	View

Schema Object Names

Reserved words differ between Oracle, MS SQL Server, and Sybase. Many Oracle reserved words are valid object or column names in MS SQL Server and Sybase. For example, DATE is a reserved word in Oracle, but it is not a reserved word in MS SQL Server and Sybase. Therefore, no column is allowed to have the name DATE in Oracle, but a column can be named DATE in MS SQL Server or Sybase. Use of reserved words as schema object names makes it impossible to use the same names across databases.

You should choose a schema object name that is unique by case and by at least one other characteristic, and ensure that your object name is not a reserved word from either database.

For a list of reserved words in Oracle, see the Oracle8i SQL Reference, Release 2 (8.1.6) (Part Number A76989-01).

Table Design Considerations

This section discusses the many table design issues that you need to consider when converting MS SQL Server or Sybase databases to Oracle. These issues are discussed under the following headings:

• Data Types

• Entity Integrity Constraints

• Referential Integrity Constraints

• Unique Key Constraints

• Check Constraints

Data Types

This section outlines conversion considerations for the following data types:

• DATETIME Data Types

• IMAGE and TEXT Data Types (Binary Large Objects)

• MS SQL Server and Sybase User-Defined Data Types

DATETIME Data Types

The date/time precision in MS SQL Server and Sybase is 1/300th of a second; in Oracle, the precision is one second. All three databases store point-in-time values for DATE and TIME data types. In MS SQL Server and Sybase, the DATETIME data type stores date and time values that are accurate to 1/300th of a second. Oracle uses the DATE data type and stores date and time values that are accurate to one second.

For applications that require finer date/time precision than seconds, the table design must include an INTEGER column with each DATE column. Oracle needs this additional column to store the value of the sequence along with the DATE value, in order to store the sub-second information.

As an alternative, if an MS SQL Server or Sybase application uses the DATETIME column to provide unique IDs instead of point-in-time values, replace the DATETIME column with a SEQUENCE in the Oracle schema definition.

In the following examples, the original design does not allow the DATETIME precision to exceed seconds in the Oracle table. This example assumes that the DATETIME column is used to provide unique IDs. If millisecond precision is not required, the table design outlined in the following example will suffice:

Original Table Design

MS SQL Server/Sybase:

CREATE TABLE example_table
(datetime_column    datetime        not null,
text_column         text            null,
varchar_column      varchar(10)     null)

Oracle:

CREATE TABLE example_table
(datetime_column    date            not null,
text_column         long            null,
varchar_column      varchar2(10)    null)

The following design allows the value of the sequence to be inserted into the integer_column. This allows you to order the rows in the table beyond the allowed precision of one second for DATE data type fields in Oracle. If you include this column in the MS SQL Server or Sybase table, you can keep the same table design for the Oracle database.

Revised Table Design

MS SQL Server/Sybase:

CREATE TABLE example_table
(datetime_column    datetime        not null,
integer_column      int             null,
text_column         text            null,
varchar_column      varchar(10)     null)

Oracle:

CREATE TABLE example_table
(datetime_column    date            not null,
integer_column      number          null,
text_column         long            null,
varchar_column      varchar2(10)    null)

For the MS SQL Server or Sybase database, the value in the integer_column is always NULL. For Oracle, the value for the field integer_column is updated with the next value of the sequence.

Create the sequence by issuing the following command:

CREATE SEQUENCE datetime_seq

Values generated for this sequence start at 1 and are incremented by 1.

Many applications do not use DATETIME values as UNIQUE IDs, but still require the date/time precision to be higher than seconds (for example, the timestamp of a scientific application may have to be expressed in milliseconds, microseconds, nanoseconds, etc.). The precision of the MS SQL Server and Sybase DATETIME data type is 1/300th of a second; the precision of the Oracle DATE data type is 1 second.

The MS SQL Server and Sybase DATETIME data type can be converted to a higher precision in Oracle using one of the following methods:

• The GET_TIME function in the system-defined DBMS_UTILITY package returns the time in 100ths of a second. The MS SQL Server or Sybase DATETIME data type can be converted to Oracle as a combination of DATE column and a NUMBER column, where the NUMBER column holds the time returned by the DBMS_UTILITY.GET_TIME function.

• The following statement is similar to the DBMS_UTILITY.GET_TIME function, and also returns the time in 100ths of a second:

  SQL> SELECT hsecs FROM v$timer;
  

IMAGE and TEXT Data Types (Binary Large Objects)

The physical and logical storage methods for IMAGE and TEXT data differ from Oracle to MS SQL Server and Sybase. In MS SQL Server and Sybase, a pointer to the IMAGE or TEXT data is stored with the rows in the table while the IMAGE or TEXT data is stored separately. This arrangement allows multiple columns of IMAGE or TEXT data per table. In Oracle, IMAGE data may be stored in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBS and CLOBS may or may not be stored in the row depending on their size.

If the MS SQL Server and Sybase TEXT column is such that the data never exceeds 4000 bytes, convert the column to an Oracle VARCHAR2 data type column instead of a CLOB column. An Oracle table can define multiple VARCHAR2 columns. This size of TEXT data is suitable for most applications.

MS SQL Server and Sybase User-Defined Data Types

This MS SQL Server and Sybase T-SQL-specific enhancement to SQL allows users to define and name their own data types to supplement the system data types. A user-defined data type can be used as the data type for any column in the database. Defaults and rules (check constraints) can be bound to these user-defined data types, which are applied automatically to the individual columns of these user-defined data types.

While migrating to Oracle PL/SQL, you must determine the base data type for each user-defined data type, to find the equivalent PL/SQL data type. Note that user-defined data types make the data definition language code and procedural SQL code less portable across different database servers.

Entity Integrity Constraints

You can define a primary key for a table in MS SQL Server or Sybase. Primary keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle provides declarative referential integrity. A primary key can be defined as part of a CREATE TABLE or an ALTER TABLE statement. Oracle internally creates a unique index to enforce the integrity.

Referential Integrity Constraints

You can define a foreign key for a table in MS SQL Server or Sybase. Foreign keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle provides declarative referential integrity. A CREATE TABLE or ALTER TABLE statement can add foreign keys to the table definition. Please refer to Oracle8i Concepts, Release 2 (8.1.6) (Part Number A76965-01), for details of the functionality that is possible for referential integrity constraints.

Unique Key Constraints

You can define a unique key for a table in MS SQL Server or Sybase. Unique keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle defines unique keys as part of CREATE TABLE or ALTER TABLE statements. Oracle internally creates unique indexes to enforce these constraints.

Unique keys map one-to-one from MS SQL Server and Sybase to Oracle.

Check Constraints

Check constraints can be defined in a CREATE TABLE statement or an ALTER TABLE statement in MS SQL Server or Sybase. Multiple check constraints can be defined on a table. A table-level check constraint can reference any column in the constrained table. A column can have only one check constraint. A column-level check constraint can reference only the constrained column. These check constraints support complex regular expressions.

Oracle defines check constraints as part of the CREATE TABLE or ALTER TABLE statements. A check constraint is defined at the TABLE level and not at the COLUMN level. Therefore, it can reference any column in the table. Oracle, however, does not support complex regular expressions.

SQL Server Rule:

create rule phone_rule
as
@phone_number like
"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"

This rule will pass all the phone numbers that resemble the following:
[phone]

This rule will fail all the phone numbers that resemble the following:

650-506-7000
650-GET-HELP

There are a few ways to implement this INTEGRITY constraint in Oracle:

• Simulate the behavior of phone-rule in a check constraint using a combination of SUBSTR, TRANSLATE, and LIKE clauses

• Write a trigger and use PL/SQL

Table-level check constraints from MS SQL Server and Sybase databases map one-to-one with Oracle check constraints. You can implement the column-level check constraints from the MS SQL Server or Sybase database to Oracle table-level check constraints. While converting the regular expressions, convert all simple regular expressions to check constraints in Oracle. MS SQL Server and Sybase check constraints with complex regular expressions can be either reworked as check constraints including a combination of simple regular expressions, or you can write Oracle database triggers to achieve the same functionality.

Data Types

This chapter provides detailed descriptions of the differences in data types used by MS SQL Server, Sybase, and Oracle databases. Specifically, this chapter contains the following information:

• A table showing the base MS SQL Server and Sybase data types available and how they are mapped to Oracle data types

• Recommendations based on the information listed in the table

Data Types Table

Table 2-2 Data Types in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Description	Oracle	Comments
INTEGER	Four-byte integer, 31 bits, and a sign. May be abbreviated as "INT" (this abbreviation was required prior to version 5).	NUMBER(10)	It is possible to place a table constraint on columns of this type (as an option) to force values between -2^31 and2^31. Or, place appropriate constraints such as: STATE_NO between 1 and 50
SMALLINT	Two-byte integer, 15 bits, and a sign.	NUMBER(6)	It is possible to place a table constraint on columns of this type (optionally) to force values between -2^15 and 2^15. Or, place appropriate constraints such as: STATE_NO between 1 and 50
TINYINT	One byte integer, 8 bits and no sign. Holds whole numbers between 0 and 255.	NUMBER(3)	You may add a check constraint of ( x between 0 and 255 ) where x is column name.
REAL	Four-byte, single-precision floating point number. This column has 7-digit precision. The range of values and the actual representation is platform dependent. This can result in incorrect interpretation if data is moved between platforms.	FLOAT	You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this type due to the fact that the Oracle NUMBER type is much more precise and portable than FLOAT.
FLOAT	A floating point number. This column has 15-digit precision.	FLOAT	You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this type due to the fact that the Oracle NUMBER type is much more precise and portable than FLOAT.
BIT	A Boolean 0 or 1 stored as one bit of a byte. Up to 8-bit columns from a table may be stored in a single byte, even if not contiguous. Bit data cannot be NULL.	NUMBER(1)	In Oracle, a bit is stored in a number(1) (or char). In Oracle, it is possible to store bits in a char or varchar field (packed) and supply PL/SQL functions to set / unset / retrieve / query on them.
CHAR(n)	Fixed-length string of exactly n 8-bit characters, blank padded. Synonym for CHARACTER. 0 < n < 256 for MS SQL Server 6.5 and Sybase. 0 < n < 8000 for MS SQL Server 7.0.	CHAR(n)	Pro*C client programs must use mode=ansi to have characters interpreted correctly for string comparison, mode=oracle otherwise.
VARCHAR(n)	Varying-length character string. 0 < n < 256 for MS SQL Server 6.5 and Sybase. 0 < n < 8000 for MS SQL Server 7.0.	VARCHAR2(n)
TEXT	Character string of 8-bit bytes allocated in increments of 2k pages. "TEXT" is stored as a linked-list of 2024-byte pages, blank padded. TEXT columns can hold up to (231-1) characters.	CLOB	The CLOB field can hold up to 4GB.
IMAGE	Binary string of 8-bit bytes. Holds up to (231-1) bytes of binary data.	BLOB	The BLOB field can hold up to 4GB.
BINARY(n)	Fixed length binary string of exactly n 8-bit bytes. 0 < n < 256 for MS SQL Server 6.5 and Sybase. 0 < n < 8000 for MS SQL Server 7.0.	RAW(n)/BLOB
VARBINARY(n)	Varying length binary string of up to n 8-bit bytes. 0 < n < 256 for MS SQL Server 6.5 and Sybase. 0 < n < 8000 for MS SQL Server 7.0.	RAW(n)/BLOB
DATETIME	Date and time are stored as two 4-byte integers. The date portion is represented as a count of the number of days offset from a baseline date (1/1/1900) and is stored in the first integer. Permitted values are legal dates between 1st January, 1753 AD and 31st December, 9999 AD. Permitted values in the time portion are legal times in the range 0 through 25920000. Accuracy is to the nearest 3.33 milliseconds with rounding downward. Columns of type DATETIME have a default value of 1/1/1900.	DATE	The precision of DATE in Oracle and DATETIME in MS SQL Server and Sybase is different. The DATETIME data type has higher precision than the DATE data type. This may have some implications if the DATETIME column is supposed to be UNIQUE. In MS SQL Server and Sybase, the column of type DATETIME can contain UNIQUE values because the DATETIME precision in MS SQL Server and Sybase is to the hundredth of a second. In Oracle, however, these values may not be UNIQUE as the date precision is to the second. You can replace a DATETIME column with two columns, one with data type DATE and another with a sequence, in order to get the UNIQUE combination. It is preferable to store hundredths of seconds in the second column.
SMALL-DATETIME	Date and time stored as two 2-byte integers. Date ranges from 1/1/1900 to 6/6/2079. Time is the count of the number of minutes since midnight.	DATE	With optional check constraint to validate the smaller range.
MONEY	A monetary value represented as an integer portion and a decimal fraction, and stored as two 4-byte integers. Accuracy to the nearest 1/10,000. Data of this type should have a preceding dollar ($) sign when input. In the absence of the "$" sign, MS SQL Server and Sybase create the value as a float.	NUMBER(19,4)	MS SQL Server and Sybase input MONEY data types as a numeric data type with a preceding dollar sign ($) as in the following example, select * from table_x where y > $5.00 You must remove the "$" sign from queries. Oracle is more general and works in international environments where the use of the "$" sign cannot be assumed. Support for other currency symbols and ISO standards through NLS is available in Oracle.
SMALLMONEY	Same as MONEY but constrained to be within a range.	NUMBER(10,4)	Since the range is -214,748.3648 to 214,748.364, NUMBER(10,4) suffices for this field.
TIMESTAMP	TIMESTAMP is defined as VARBINARY(8) with NULL allowed. Every time a row containing a TIMESTAMP column is updated or inserted, the TIMESTAMP column is automatically incremented by the system. A TIMESTAMP column may not be updated by users.	NUMBER	You must place triggers on columns of this type to maintain them. In Oracle you can have multiple triggers of the same type without having to integrate them all into one big trigger. You may want to supply triggers to prevent updates of this column to enforce full compatibility.
SYSNAME	VARCHAR(30) in MS SQL Server 6.5 and Sybase. NVARCHAR(128) in MS SQL Server 7.0.	VARCHAR2(30) and VARCHAR2(128) respectively.

TEXT and IMAGE data types in MS SQL Server and Sybase follow the rules listed below:

• The column of these data types cannot be indexed.

• The column cannot be a primary key.

• The column cannot be used in the GROUP BY, ORDER BY, HAVING, and DISTINCT clauses.

• IMAGE and TEXT data types can be referred to in the WHERE clause with the LIKE construct.

• IMAGE and TEXT data types can also be used with the SUBSTR and LENGTH functions.

Recommendations

In addition to the data types listed in Table 2-2, users can define their own data types in MS SQL Server and Sybase databases. These user-defined data types translate to the base data types that are provided by the server. They do not allow users to store additional types of data, but can be useful in implementing standard data types for an entire application.

Data types can easily be mapped from MS SQL Server and Sybase to Oracle with the equivalent data types listed in the above table. The Migration Workbench converts user-defined data types to their base type. You can defined how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.

Data Storage Concepts

This section provides a detailed description of the conceptual differences in data storage for the MS SQL Server, Sybase, and Oracle databases.

Specifically, it contains the following information:

• A table comparing the data storage concepts of MS SQL Server, Sybase, and Oracle databases

• Recommendations based on the information listed in the table

Data Storage Concepts Table

Table 2-3 Data Storage Concepts in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Database Devices: A database device is mapped to the specified physical disk files.	Datafiles: One or more datafiles are created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace. The combined storage capacity of a the tablespaces in a database is the total storage capacity of the database. Once created, a datafile cannot change in size. This limitation does not exist in Oracle.
Page: Many pages constitute a database device. Each page contains a certain number of bytes.	Data Block: One data block corresponds to a specific number of bytes, of physical database space, on the disk. The size of the data block can be specified when creating the database. A database uses and allocates free database space in Oracle data blocks.
Extent: Eight pages make one extent. 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.
N/A	Segments: A segment is a set of extents allocated for a certain logical structure. The extents of a segment may or may not be contiguous on disk, and may or may not span the datafiles.
Segments (corresponds to Oracle Tablespace): A segment is the name given to one or more database devices. Segment names are used in CREATE TABLE and CREATE INDEX constructs to place these objects on specific database devices. Segments can be extended to include additional devices as and when needed by using the SP_EXTENDSEGMENT system procedure. The following segments are created along with the database: System segment Stores the system tables. Log segment Stores the transaction log. Default segment All other database objects are stored on this segment unless specified otherwise. Segments are subsets of database devices.	Tablespace (corresponds to MS SQL Server and Sybase Segments): A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. A database typically has one system tablespace and one or more user tablespaces. Tablespace Extent: An extent is a specific number of contiguous data blocks within the same tablespace. Tablespace Segments: A segment is a set of extents allocated for a certain logical database object. All the segments assigned to one object must be in the same tablespace. The segments get the extents allocated to them as and when needed. There are four different types of segments as follows: Data segment Each table has a data segment. All of the table's data is stored in the extents of its data segments. The tables in Oracle can be stored as clusters as well. A cluster is a group of two or more tables that are stored together. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment. Index segment Each index has an index segment that stores all of its data. Rollback segment One or more rollback segments are created by the DBA for a database to temporarily store "undo" information. This is the information about all the transactions that are not yet committed. This information is used to generate read-consistent database information during database recovery to rollback uncommitted transactions for users.
	Temporary segment Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.
Log Devices: These are logical devices assigned to store the log. The database device to store the logs can be specified while creating the database.	Redo Log Files: Each database has a set of two or more redo log files. All changes made to the database are recorded in the redo log. Redo log files are critical in protecting a database against failures. Oracle allows mirrored redo log files so that two or more copies of these files can be maintained. This protects the redo log files against failure of the hardware the log file reside on.
Database Devices: A database device contains the database objects. A logical device does not necessarily refer to any particular physical disk or file in the file system. The database and logs are stored on database devices. Each database device must be initialized before being used for database storage. Initialization of the database device initializes the device for storage and registers the device with the server. After initialization, the device can be: Allocated to the free space available to a database Allocated to store specific user objects Used to store the transaction log of a database Labeled as default device to create and alter database objects The SP_HELPDEVICES system procedure displays all the devices that are registered with the server. Use the DROP DEVICE DEVICE_NAME command to drop the device. The system administrator (SA) should restart the server after dropping the device. A device can be labeled as a default device so that the new databases need not specify the device at the time of creation. Use the SP_DISKDEFAULT system procedure to label the device as a default device.	N/A
Dump Devices These are logical devices. A database dump is stored on these devices. The DUMP DATABASE command uses the dump device to dump the database.	N/A
N/A	Control Files: Each database has a control file. This file records the physical structure of the database. It contains the following information: database name names and locations of a database's datafiles and redo log files time stamp of database creation It is possible to have mirrored control files. Each time an instance of an Oracle database is started, its control file is used to identify the database, the physical structure of the data, and the redo log files that must be opened for the database operation to proceed. The control file is also used for recovery if necessary. The control files hold information similar to the master database in MS SQL Server and Sybase.

Recommendations:

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.

Oracle, MS SQL Server, and Sybase all have a way to control the physical placement of a database object. In MS SQL Server and Sybase, you use the ON SEGMENT clause and in Oracle you use the TABLESPACE clause.

An attempt should be made to preserve as much of the storage information as possible when converting from MS SQL Server or Sybase to Oracle. The decisions that were made when defining the storage of the database objects for MS SQL Server or Sybase should also apply for Oracle. Especially important are initial object sizes and physical object placement.

Schema Objects

This section compares the following MS SQL Server, Sybase, and Oracle schema objects:

• Alias

• Database

• Database Link

• Data and Hash Cluster

• Defaults

• Index

• Privilege

• Profile

• Role

• Rule

• Sequence

• Snapshot

• Synonym

• Tables

• Tablespace

• User

• View

Each schema object is compared in separate tables based on create, alter, drop, grant, revoke, and truncate where applicable. Most tables are divided into the following four sections:

• syntax

• description

• permissions

• examples

Each table is followed by a recommendations section that contains important information about conversion implications.

Alias

This section contains the following tables for the schema object Alias:

• Create

• Drop

Create

Table 2-4 Comparison of Creating the Alias Schema Objects in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_addalias login_id usr_nm_inside_db	Syntax: Oracle does not have aliases. Similar functionality is provided by roles. See the Roles section for more information in this regard.
Description: The purpose of an alias is to allow an individual to access the database as another database user without the DBA having to add him or her as a user in the database. The SP_ADDALIAS system procedure creates a row in the SYSALTERNATES table, which keeps all information about aliases. An alias may be set up for any user. This allows more than one user ID to have the same set of privileges as the base user ID.	N/A
Permissions: The SA or DBO can add an alias.	N/A
Example: sp_addalias user1 dbo sp_addalias user2 dbo Both user1 and user2 can act as DBO.	N/A

Recommendations:

A user account can be created for each alias set up on MS SQL Server or Sybase. The same privileges as the base user ID can be granted to this account.

Drop

Table 2-5 Comparison of Dropping the Alias Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_dropalias login_id	Syntax: Oracle does not have aliases. Functionality is provided by roles. See the Roles section for more information in this regard.
Description: The SP_DROPALIAS system procedure allows you to drop an alias.	N/A
Permissions: The SA or DBO can drop an alias.	N/A
Example: sp_dropalias user1	N/A

Recommendations:

Oracle does not have aliases. The MS SQL Server and Sybase alias dropping information is provided for reference.

Database

This section contains the following tables for the schema object Database:

• Create

• Alter

• Drop

Create

Table 2-6 Comparison of Creating the Database Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE DATABASE database_name [ON {DEFAULT | database_ device}[= size] [, database_device [= size]] ...] [LOG ON database_device [= size] [, database_device [= size]]...]	Syntax: CREATE DATABASE database_name [CONTROLFILE REUSE] [LOGFILE [GROUP integer] file_ definition [, [GROUP integer] file_ definition]...] [MAXLOGFILES integer] [MAXLOGMEMBERS] integer] [MAXLOGHISTORY] integer] [DATAFILE file_definition [,file_definition]...] [MAXDATAFILES integer] [MAXINSTANCES integer] [ARCHIVELOG | NOARCHIVELOG] [EXCLUSIVE] [CHARACTER SET charset]
Description: Each server can manage up to 32767 databases. The master database contains all the necessary information about all the other databases created by the users. The model database is used as a template to create other databases. The tempdb is a temporary database that can be used as a working storage by all the users on the server. A database can be assigned as a default database to the user. Note that in Oracle a tablespace can be assigned as a default tablespace to the users. In order to use an object in another database, the object name has to be fully specified in order to reference that object. This can be seen in the following example: database_name.owner_ name.object_name A database is an atomic storage unit for administration, backup, and recovery. CREATE DATABASE creates the specification of the database in the master database (system tables) and creates the database itself as a copy of the model database. A 2M database is created on the default device when the values are not specified.	Description: One server controls one database. One server instance comprises the system global area (SGA) and a few processes. The SGA is used to store data blocks and parsed SQL statements (including PL/SQL blocks) in shared memory. A given database consists of a system tablespace and one or more user-defined tablespaces. Each of the tablespaces may have its own storage, backup, and recovery strategy. The CREATE DATABASE command makes a database ready for initial use. It clears the database files and loads initial database tables required by the RDBMS. Caution: If you use CREATE DATABASE on an existing database, you will destroy the database. The control file stores the physical structure of the database. The file definition takes the following form: 'file' [SIZE integer [K | M] [REUSE]] SIZE is the number of bytes set aside for this file. The suffix K multiplies the value by 1024 and suffix M multiplies it by 1048576. SIZE and REUSE together tell Oracle to reuse the file if it already exists, or create it if it does not exist. SIZE without REUSE creates a file if one does not already exist, but returns an error if a file does exist. Without SIZE, the file must already exist. LOGFILE names the files to be used as the redo log files. Mirrored log files are recommended for full protection against media failure. MAXLOGFILES overrides the LOG_FILES parameter specified in the init.orafile, and defines the maximum number of redo log files that can be created for this database.
Description (continued): The optional ON clause lets you put the database on a specific device. In MS SQL Server and Sybase, the physical devices are initialized using the DISK INIT command. This command attaches a logical device name to physical devices. These logical device names are used in the CREATE DATABASE statement. The LOG ON option creates the transaction log (the SYSLOGS table) on a separate device, and improves performance. If the LOG ON option is omitted, the transaction log is created on the same device as the data tables.	Description (continued): ARCHIVELOG and NOARCHIVELOG define the way redo log files are used when the database is first created. NOARCHIVELOG is the default, meaning that redo log files are reused without saving the contents elsewhere. This provides instance recovery, but does not provide recovery from a media failure. ARCHIVELOG forces redo log files to be archived so you can recover from media failure. ARCHIVELOG also supports instance recovery. DATAFILE names the datafiles used by the database. These files exist in the SYSTEM tablespace. MAXDATAFILES is the maximum number of datafiles that can be created for this database. MAXINSTANCES overrides the INSTANCES parameter in the init.ora file, and sets the maximum number of simultaneous instances that can mount and open this database. The optional REUSE clause tells the server to destroys the contents of the named file and associate this file to the database in the context. EXCLUSIVE is optional and means that all databases are created to allow only one instance that has an exclusive access to the database and allows only one instance of exclusive access to any database created. Use the ALTER DATABASE DISMOUNT and ALTER DATABASE MOUNT PARALLEL commands to allow multiple instances to access the database.
Permissions: The SA can grant permission to use the CREATE DATABASE command. The SA usually retains the CREATE DATABASE permission, and changes the database owner of the database by using the SP_CHANGEDBOWNER system procedure in the new database.	Permissions: You must have the OSDBA role enabled in order to issue this command.
Examples: CREATE DATABASE my_database ON DEFAULT =	Example: CREATE DATABASE my_database LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K, GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K DATAFILE 'test_system' SIZE 10M

Recommendations:

Because of the conceptual differences between the two databases, the best approach is to create the database manually in Oracle. Sizing and backup methods chosen for MS SQL Server and Sybase often apply nearly as well for Oracle. Care should be taken to ensure that sizing and backup information learned by using MS SQL Server or Sybase is passed on to the Oracle database.

MS SQL Server and Sybase applications that use two or more databases on the same server usually translate to one Oracle database with several tablespaces and a different user for each tablespace.

In most cases, the MS SQL Server or Sybase application should be converted so that one server in MS SQL Server or Sybase is converted to one Oracle database instance.

The ability to backup/restore individual databases in MS SQL Server or Sybase is provided by creating one Oracle tablespace for each MS SQL Server or Sybase database. Then DBAs can perform the same backup/restore by tablespace on Oracle that they could by database in MS SQL Server or Sybase.

The ability of MS SQL Server and Sybase to keep logical sets of tables together in databases is accomplished in Oracle by creating tablespaces.

The use of tablespaces in Oracle can provide all the same benefits of multiple databases per server in MS SQL Server and Sybase. However, if there are several completely unrelated databases in the same server in MS SQL Server or Sybase, it may make sense to split them into completely different database instances in Oracle.

Alter

Table 2-7 Comparison of Altering the Database Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: ALTER DATABASE database {ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name] |ADD LOG FILE <filespec> [,...n] |REMOVE FILE logical_file_name |ADD FILEGROUP filegroup_name |REMOVE FILEGROUP filegroup_ name |MODIFY FILE <filespec> |MODIFY FILEGROUP filegroup_ name filegroup_property }	Syntax: ALTER DATABASE [database_name] {ADD LOGFILE [THREAD integer] [GROUP integer] file_definition [,file_ definition]...| ADD LOGFILE MEMBER file [REUSE][, file [REUSE]...] TO {GROUP integer |(file[,file]...) | file} | DROP LOGFILE {GROUP integer|(file[,file]...)|file} | DROP LOGFILE MEMBER file[,file] | RENAME file TO file | NOARCHIVELOG | ARCHIVELOG | MOUNT [EXCLUSIVE | PARALLEL] | OPEN [RESETLOGS | NORESETLOGS] | ENABLE [PUBLIC] THREAD integer | DISABLE THREAD integer | BACKUP CONTROLFILE TO file [REUSE] | DATAFILE file {ONLINE|OFFLINE [DROP]} | CREATE DATAFILE file[,file] [AS file_spec[,file_spec]...] | RENAME GLOBAL_NAME TO database [.domain]| RECOVER recover_clause | SET {DBMAC {ON | OFF} | DBHIGH = string | DBLOW = string}}
Description: ALTER DATABASE is used to add, remove, or modify files or file groups from the database. The database size can also be altered by changing the value of the "database size" configuration variable by using the SP_CONFIGURE system procedure. The reconfigure command should be used to apply the changes.	Description: The log file is assigned to a thread, either explicitly with the THREAD clause or to the thread assigned to the current Oracle instance. Use the THREAD parameter only if you are using Oracle with the parallel server option in parallel mode. The file definition takes the following form: 'file' [SIZE integer [K | M] [REUSE]] SIZE is the number of bytes set aside for this file. The suffix K multiplies the value by 1024 and suffix M multiplies it by 1048576. SIZE and REUSE together tell Oracle to reuse the file if it already exists, or to create it if it does not exist. SIZE without REUSE creates a file if one does not already exist, but returns an error if a file does exist. Without SIZE, the file must already exist. A GROUP is a collection of log files. You can add a GROUP by listing the log files or naming them with an integer. If a mirrored redo log is used, groups of online redo log files can be created. Each member in a GROUP is exactly the same size. The members in the GROUP are multiple copies of the redo log created to protect the log against losing the drive with the log file. ADD LOGFILE MEMBER adds new files to an existing log file group. DROP FILE drops an existing redo log file group. DROP LOGFILE MEMBER drops one or more members of a log file group. RENAME changes the name of the existing database or log file. When you first create the database, it is mounted in exclusive mode, meaning only its creator has access to it. To allow multiple instances, use the MOUNT PARALLEL command on a loosely-coupled cluster configuration. This command is available only for the parallel server version of Oracle. After mounting the database, OPEN it.
The ALTER DATABASE command in MS SQL Server or Sybase can only alter the size of the database. Two megabytes of space is added to the default device of the database, if the size is not specified. The minimum increase you can specify is one megabyte in size. The segments are automatically extended to the additional space. It is important to back up the MASTER database after each use of the ALTER DATABASE command. This ensures that the recovery is easier and safer if the MASTER database is damaged.	RESETLOGS resets the redo logs, cleaning out all the redo log entries when you OPEN the database. Use NORESETLOGS to leave the logs intact when you OPEN the database. You can ENABLE or DISABLE a thread. PUBLIC makes the thread available to any instance not requesting a specific thread. Use DATAFILE to make the datafile ONLINE or OFFLINE. You can CREATE a new datafile to replace a lost or damaged datafile. RENAME GLOBAL_NAME changes the name of the database. Specify the domain to tell Oracle where the database is located on the network. Use RECOVER to recover the database. This command performs media recovery for a lost database.
Permissions: The DBO or SA can use this command. The SA can grant privileges on this command to other users. The DBO or SA must be using the MASTER database to execute this command.	Permissions: The user issuing this command needs the ALTER DATABASE privilege.
Example: To add 3MB to the log device: ALTER DATABASE my_database MODIFY FILE (NAME = logdevice_name SIZE = 3MB) The logdevice_name is the name of the log device specified while creating the database with LOG ON option. To allocate an additional device to the database: ALTER DATABASE database_name ON additional_device_name	Example: ALTER DATABASE ADD LOGFILE GROUP 10 ('log1c', 'log2c') SIZE 3M ALTER DATABASE DROP LOGFILE MEMBER 'LOG3C' ALTER DATABASE DROP LOGFILE GROUP 3 ALTER DATABASE RENAME FILE 'log1a', 'log2a' TO 'log1c', 'log2c'

Recommendations:

Oracle functionality exceeds that of MS SQL Server and Sybase. There should be no conversion implications.

Drop

Table 2-8 Comparison of Dropping the Database Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DROP DATABASE database_name[, database_name ...]	Syntax: Oracle does not have a separate command to drop a database. Caution: If you use the CREATE DATABASE command on an existing database, you will destroy the database.
Description: DROP DATABASE deletes the database and all the objects in it from the server, frees the storage space that had been allocated for it, and deletes the related information from the system tables in the MASTER database. This command will not work if the database is in use. DROP DATABASE does not drop the server. It only drops individual databases. It does not remove devices or clean up operating system files.	N/A
Permissions: The DBO alone is allowed to execute this command. The DBO must be using the MASTER database to execute this command.	N/A
Example: DROP DATABASE my_temp_dbs	N/A

Recommendations:

Oracle does not have a command to drop a database because there will only be one database per instance. The process for removing an Oracle database is the same as the process for removing an MS SQL Server or Sybase server. The CREATE DATABASE command destroys an existing database if it has the same name as the database being created.

If a database is considered equivalent to a tablespace in Oracle, the DROP TABLESPACE command in Oracle is equivalent to the DROP DATABASE command in MS SQL Server or Sybase.

Database Link

This section contains the following tables for the schema object Database Link:

• Create

• Drop

Create

Table 2-9 Comparison of Creating the Database Link Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: SP_ADDLINKED SERVER	Syntax: CREATE [PUBLIC] DATABASE LINK link CONNECT TO user IDENTIFIED BY password USING 'connect_string'
Description: Allows queries against databases accessible via OLE DB data sources.	Description: A database link is a named object that describes a path from one database to another. These objects are used in distributed database environment. PUBLIC links are available to all users except those who have created a private link with the same name. connect_string is the name and the location of the remote database that can be accessed through SQL*Net. Remote tables can be accessed just like the local tables, except that the table name must be suffixed by @link. The DBA can set the maximum number of simultaneous links that can be created.
N/A	Permissions: Any user with the CREATE DATABASE LINK system privilege can create private database links. Any user with the CREATE PUBLIC DATABASE LINK system privilege can create public database links. Also, users must the have CREATE SESSION system privilege on a remote database.
N/A	Examples: CREATE DATABASE LINK sales.hq.acme.com CONNECT TO scott IDENTIFIED BY tiger USING 'D:BOSTON-MFG'

Recommendations:

An MS SQL Server or Sybase server can support one or more databases, and all these databases can be accessed from one another by fully qualifying the object names. In many applications there is a layer that translates the object names to the actual object names with complete reference (along with the server_name, database_name, owner_name). Database links should be created for all the different servers in the Oracle application environment so that the layer mentioned would simply return the object name for Oracle installations.

The MS SQL Server or Sybase system catalogs hold information about the servers known to the local server. These tables can be read and corresponding database links can be created.

In Oracle, database links are used in distributed database environments. A two-phase commit operation is frequently needed in distributed database environments. MS SQL Server and Sybase only have a programmatic two-phase commit, which is very complex and impractical to use. Because of Oracle's straightforward transparent two-phase commit, distributed database applications are more practical in an Oracle environment.

Oracle allows links to other heterogeneous databases via Oracle Gateway technology.

Drop

Table 2-10 Comparison of Dropping the Database Link Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have database links.	Syntax: DROP [PUBLIC] DATABASE LINK link
N/A	Description: This command drops the specified database link from the database.
N/A	Permissions: You can only drop a database link in your own schema. You must have the DROP PUBLIC DATABASE LINK system privilege to drop a PUBLIC database link.
N/A	Examples: DROP DATABASE LINK sales.hq.acme.com

Recommendations:

This command has no effect on the conversion process. Table 2-10 is provided for reference only.

Data and Hash Cluster

This section contains the following tables for the Data and Hash Cluster schema object:

• Create

• Alter

• Drop

Create

Table 2-11 Comparison of Creating the Data and Hash Cluster Schema Objects in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Table clusters and hash clusters are not supported in MS SQL Server or Sybase.	Syntax: CLUSTER [user.]cluster (column data type [, column data type]...) [INITRANS integer] [MAXTRANS integer] [PCTFREE integer] [PCTUSED integer] [SIZE integer[K|M]] [STORAGE storage] [TABLESPACE tablespace] [INDEX|[HASH IS column] HASHKEYS integer]
N/A	Description: Clusters are an optional method of storing tabledata in which one or more tables are physically stored together because they share common columns and are often used together. Clusters require at least one cluster column for each of the tables. These must have the same data type and size, but are not required to have the same name. For the tables in a cluster, rows with the same cluster column values are kept together on disk in the same area, the same logical blocks. Clusters can improve performance when the tables are joined on the cluster columns. Disk access time improves because the related rows are physically stored together. The related columns of the tables in a cluster make up the indexed cluster key. This command creates a cluster of two or more tables. Tables are added to the cluster using CREATE TABLE with the cluster clause. This command commits pending changes to the database. Each distinct value in each cluster column is stored only once, regardless of whether it occurs once or many times in the tables and rows. Tables with LONG columns cannot be clustered. SIZE sets the size in bytes for a logical storage block and should be the average amount of space needed to store all the rows from all the clustered tables that are associated with a single cluster key. If SIZE exceeds the physical block size, Oracle uses the physical block size instead. The cluster is indexed by default. You must create an index on the cluster key before putting any data in the cluster. If you specify the hash cluster, Oracle uses a hash function to store and locate the rows of the tables. HASH IS lets you create your own hash value as a column of the table. Otherwise, Oracle uses an internal hash function based on the columns of the cluster key. HASHKEYS creates the hash cluster and specifies the number of hash values, rounded to the nearest prime number. The minimum value is 2.
N/A	Permissions: You must have the CREATE CLUSTER system privilege to create a cluster in your own schema. You must have the CREATE ANY CLUSTER system privilege to create a cluster in another user's schema.
N/A	Examples: CREATE CLUSTER personnel ( department_number NUMBER) SIZE 512 HASHKEYS 500 STORAGE (INITIAL 100k NEXT 50k PCTINCREASE 10)

Recommendations:

Clusters improve the performance of certain queries, but they can negatively affect the performance of the INSERT and UPDATE operations and other queries.

Use clusters to store the relatively static tables that need to be joined frequently by using a specific key.

Table clusters and hash clusters should be examined as a possible performance improvement, but are not necessary in a conversion from MS SQL Server or Sybase to Oracle.

Use hashing to reduce I/O when locating rows with an equality condition.

Alter

Table 2-12 Comparison of Altering the Data and Hash Cluster Schema Objects in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Table clusters and hash clusters are not supported in MS SQL Server or Sybase.	Syntax: ALTER CLUSTER [user.]cluster {INITRANS integer| MAXTRANS integer| PCTFREE integer| PCTUSED integer| SIZE integer[K|M] | STORAGE storage| ALLOCATE EXTENT[(SIZE integer[K|M]) | (DATAFILE 'filename') | (INSTANCE integer)]}
N/A	Description: The ALTER CLUSTER redefines future storage allocations or allocates an extent for the specified cluster. SIZE determines how many cluster keys are stored in data blocks allocated to the cluster. You can only change the SIZE parameter for an indexed cluster, not for a hash cluster. ALLOCATE EXTENT explicitly allocates a new extent for the cluster. The user can only allocate a new extent for an indexed cluster, not a hash cluster.
N/A	Permissions: User can alter their own clusters. Any user with the ALTER ANY CLUSTER system privilege can alter another user's cluster.
N/A	Examples: ALTER CLUSTER scott.customer SIZE 512 STORAGE (MAXEXTENTS 25)

Recommendations:

Table clusters and hash clusters should be examined as a possible performance improvement, but are not necessary in a conversion from MS SQL Server or Sybase to Oracle.

Drop

Table 2-13 Table 2-13 Comparison of Dropping the Data and Hash Cluster Schema Objects in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Table clusters and hash clusters are not supported in MS SQL Server or Sybase.	Syntax: DROP CLUSTER [user.]cluster [INCLUDING TABLES [CASCADE CONSTRAINTS]]
N/A	Description: This command removes the specified cluster from the database. INCLUDING TABLES drops all tables that belong to the cluster. If the user omits this clause and the cluster still contains tables, Oracle returns an error and does not drop the cluster. CASCADE CONSTRAINTS drops all referential integrity constraints from tables outside the cluster that refer to primary and unique keys in the tables of the cluster. If the user omits this option and such referential integrity constraints exist, Oracle returns an error and does not drop the cluster.
N/A	Permissions: Users can drop their own clusters. Any user with the DROP ANY CLUSTER system privilege can drop another user's cluster.
N/A	Examples: DROP CLUSTER geography INCLUDING TABLES CASCADE CONSTRAINTS

Recommendations:

Table clusters and hash clusters should be examined as a possible performance improvement, but are not necessary in a conversion from MS SQL Server or Sybase to Oracle.

Defaults

This section contains the following tables for the schema object Defaults:

• Create

• Drop

Create

Table 2-14 Comparison of Creating the Default Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE DEFAULT [owner.]default_ name AS value|constant_ expression sp_bindefault default_name, {"table.column" | data type_ name}	Syntax: Defaults are specified as part of the CREATE TABLE or ALTER TABLE statement.
Description: Defaults in MS SQL Server and Sybase are created as separate objects. These defaults are bound to individual columns of the table. The expression used as a default value can be a constant literal or a built-in function that returns a constant value. For example: user_name(), getdate())	Description: You can specify the default value for a column with the DEFAULT constraint in the table creation/alteration statement. The expression used in the DEFAULT constraint can be a constant literal or a built-in function that returns a constant value. For example, SYSDATE.
Permissions: The DBO has the CREATE DEFAULT permission and can transfer it to other users.	N/A
Example: CREATE DEFAULT user_def AS user_id() sp_bindefault user_def , "table1.uid_column"	N/A

Recommendations:

The implementation of defaults in MS SQL Server, Sybase, and Oracle is conceptually very similar.

Defaults in MS SQL Server and Sybase can use built-in functions. These functions have to be parsed and replaced by equivalent Oracle functions. If the equivalent function is not available, you may want to make the column as NULL allowed and update it with the default value from within a trigger.

Drop

Table 2-15 Comparison of Dropping the Default Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DROP DEFAULT [owner.]default_ name [, [owner.]default_name ...]	Syntax: Defaults are specified as part of the CREATE TABLE or ALTER TABLE statement.
Description: A default cannot be dropped if it is currently bound to a column or a user-defined data type. Use SP_UNBINDEFAULT to unbind the default.	N/A
Permissions: Only the default owner can issue this command.	N/A
Example: DROP DEFAULT user_def	N/A

Recommendations:

Replace DROP DEFAULT statements with ALTER TABLE statements.

Index

This section contains the following tables for the schema object Index:

• Create

• Alter

• Drop

Create

Table 2-16 Comparison of Creating the Index Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON [[database.]owner.]table (col_name [, col_name]...) [WITH {FILLFACTOR = x, IGNORE_DUP_KEY, [IGNORE_DUP_ROW|ALLOW_DUP_ ROW]}] ON segment_name	Syntax: CREATE INDEX [user.] index_name ON {[user.]table (col_name[ASC | DESC] [,col_name[ASC | DESC]...) | CLUSTER [user.]cluster} [INITRANS integer] [MAXTRANS integer] [PCTFREE integer] [STORAGE storage] [TABLESPACE tablespace] [NOSORT]
Description: Index names in MS SQL Server and Sybase must be unique for the table. Two tables in MS SQL Server and Sybase can have indexes of the same name. IGNORE_DUP_KEY causes the server to ignore a row that would violate a unique key. It does not give any error message. IGNORE_DUP_ROW causes the server to ignore a row that is a duplicate of an existing row. It does not give any error message. ALLOW_DUP_ROW allows duplicate rows in the table. The FILLFACTOR decides how full each page becomes while creating a new index on existing data. The server must split the pages when they fill up; the FILLFACTOR percentage thus affects performance. FILLFACTOR can be changed globally for all indexes by setting it with SP_CONFIGURE. The FILLFACTOR value should be smaller when the corresponding table is dynamic and is capable of growing. ON segment_name names the segment to which the index is assigned.	Description: Each user schema must have a unique index name for each Oracle database. No two indexes created by the same user can have the same name. Oracle automatically creates unique indexes to enforce unique column constraints. PCTFREE is the percentage of space left free in the index for new entries and the updates. TABLESPACE names the tablespace to which the index is assigned. NOSORT reduces the time to create an index if, and only if, the values in the column being indexed are already in ascending order. Oracle does not balance indexes on its own. This calls for the maintenance of indexes on tables which have very high numbers of INSERTs and DELETEs. The indexes on the dynamic tables need to be dropped and created again from time to time, to ensure the same average response time. A query which can be satisfied from just the index does not need the actual table rows. This occurs when the query selects only columns included in the index key. Cluster: CLUSTER is the cluster key indexed for a cluster. Clusters must have their keys indexed for their associated tables to be accessed. Non-clustered Indexes: All Oracle indexes are non-clustered indexes.
Description (continued): A query which can be satisfied from just the index does not need the actual table rows. This occurs when the query selects only columns included in the index key. Clustered Indexes: In MS SQL Server and Sybase, a clustered index is a b-tree index. The leaf pages contain the data. The index forces the data to be stored in physically sorted order, that is, sequentially. A table can have only one clustered index. A clustered index in MS SQL Server or Sybase is always a UNIQUE index. In most applications, a clustered index is created on the primary key for a table. Non-clustered Indexes: In MS SQL Server and Sybase, a non-clustered index is a b-tree index. The leaf pages contain pointers to the data. The data is stored in a random order. A table can have multiple non-clustered indexes. These indexes can be UNIQUE if specified. A table can have up to 249 non-clustered indexes.
Permissions: The CREATE INDEX permission defaults to the table owner and cannot be transferred.	Permissions: Users can index their own tables. Any valid database user with the INDEX privilege on the table or the CREATE ANY INDEX system privilege can also create an index.
Example: CREATE UNIQUE NONCLUSTERED INDEX c_temp_ix ON table customer (col1, col2) WITH FILLFACTOR = 20 IGNORE_DUP_KEY ON segment index_seg	Example: CREATE INDEX c_temp_ix ON customer(col1 DESC,col2) NOSORT

Recommendations:

Index names in MS SQL Server and Sybase are only required to be unique for each table. In Oracle they must be unique for each user, regardless of the table the index is on. Change the non-unique index names when moving them to Oracle.

Clustered indexes should be replaced by primary keys in Oracle.

UNIQUE non-clustered indexes translate to UNIQUE column constraints.

Oracle never ignores rows being inserted or updated. It either performs the INSERT or UPDATE or gives an error. If MS SQL Server or Sybase indexes were created with IGNORE_DUP_KEY or IGNORE_DUP_ROW, a note should be made that the application needs to change to handle the error.

ALLOW_DUP_ROW functionality is supported in Oracle provided no other constraints are violated.

Alter

Table 2-17 Comparison of Altering the Index Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase have no command comparable to ALTER INDEX.	Syntax: ALTER INDEX [user.]index_name {INITRANS integer | MAXTRANS integer | STORAGE storage}
N/A	Description: The ALTER INDEX command is used to change future storage allocation for data blocks in an index. INITRANS and MAXTRANS change the values of these parameters for the index. See the Tables section for a description of these parameters. STORAGE changes the storage parameters for the index.
N/A	Permissions: The index owner can alter the index. Other users must have the ALTER ANY INDEX system privilege to alter an index.
Example: N/A	Example: ALTER INDEX scott.ix_cust INITRANS 5 STORAGE (NEXT 100K)

Recommendations:

This command has no effect on the conversion process. The information is provided for reference only.

Drop

Table 2-18 Comparison of Dropping the Index Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DROP INDEX [table.] index_name [, [table.] index_name ...]	Syntax: DROP INDEX [user.]index_name
Description: The DROP INDEX command drops the specified index.	Description: The DROP INDEX command drops the specified index. It commits pending changes to the database.
Permissions: By default, the index owner has the DROP INDEX permission which is not transferable.	Permissions: This command can be issued by the owner of the index. A user must have the DROP ANY INDEX system privilege to drop an index from another user's schema.
Example: DROP INDEX test_tabl.test_index	Example: DROP INDEX test_index

Recommendations:

If applications drop multiple indexes with one DROP INDEX command, they need to be converted into multiple DROP INDEX commands in Oracle.

Privilege

This section contains the following tables for the schema object Privilege:

• Grant

• Revoke

Grant

Table 2-19 Comparison of Granting the Privilege Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: System Privileges: GRANT {system_privilege [, system_privilege]...| ALL} TO {user [,user]...| group[,group]... | PUBLIC} Object Privileges : GRANT {object_privilege[, object_ privilege]...| ALL} ON object [(col_list)] TO {user [,user]...| group[,group]... | PUBLIC}	Syntax: System Privileges: GRANT {system_privilege [, system_privilege]...| role[,role]...} TO {user [,user]...| role[,role]...|PUBLIC} [WITH ADMIN OPTION] Object Privileges: GRANT {object_privilege[, object_ privilege]...| ALL[PRIVILEGES]} [(column[,column]...)] ON [user.]object TO {user [,user]...| role[,role]...|PUBLIC} [WITH GRANT OPTION]
Description: System privileges are the privileges granted to create and manage various schema objects. These commands also include the system administrative commands. Object privileges are the privileges granted on the various operations on the schema objects. The column list is applicable only when the object corresponds to a table or a view. SELECT and UPDATE privileges can be column-specific. MS SQL Server and Sybase allow anti-grants. Anti-grants are revoke statements that do not have a corresponding, preceding, explicit grant statement. For example, you can issue the REOVKE SELECT ON SYSLOGINS.PASSWORD FROM PUBLIC command to disallow access to the PASSWORD column in the SYSLOGINS table. This statement need not be preceded by the GRANT SELECT ON SYSLOGINS.PASSWORD TO PUBLIC command. This is frequently used to revoke part of a privilege which was granted previously. It allows system administrators to quickly do things such as "Give everyone EXCEPT Bob access to table X".	Description: System privileges are the privileges granted to create and manage various schema objects themselves. These commands also include the system administrative commands. WITH ADMIN OPTION lets the granted user or role (the grantee) grant the system privilege or role to other grantees. The grantee can also use its option to alter or drop a granted role. Object privileges are the privileges granted on the various operations on the schema objects. The column list is applicable only when the object corresponds to a table or a view. The INSERT and UPDATE privileges can be column-specific. Object privileges include any or all of the following privileges: SELECT, INSERT, DELETE, UPDATE, EXECUTE, ALTER, REFERENCE, and INDEX. WITH GRANT OPTION passes along the right to grant the granted object privileges to another user or role. GRANTs on synonyms become grants on the underlying object that the synonym references. GRANT can be specified to a list of columns when granting the INSERT, REFERENCE, or UPDATE privileges. Oracle only supports additive privileges. This means that the REVOKE statement should have a corresponding GRANT statement. There is no way to REVOKE part of a privilege. You must only GRANT the specific privileges desired.
Permissions: The SA grants system privileges and object owners grant object privileges.	Permissions: To grant a system privilege, the grantor must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege. To grant object privileges, the object must be in the grantor's own schema or the grantor must have been granted the object privileges with the GRANT option.
Examples: GRANT ALL ON items TO PUBLIC GRANT SELECT ON items(column1) TO mary GRANT EXECUTE ON st_proc1 TO mary GRANT CREATE TABLE, CREATE PROCEDURE TO jim	Examples: GRANT ALL ON items TO PUBLIC GRANT SELECT ON items TO mary GRANT EXECUTE ON st_proc1 TO mary GRANT CREATE TABLE, CREATE PROCEDURE TO jim GRANT SELECT, UPDATE ON items TO jim GRANT CREATE SESSION, CREATE VIEW, CREATE SYNONYM TO basic_role WITH ADMIN OPTION; GRANT basic_role TO mary,jim,tom

Recommendations:

In MS SQL Server and Sybase, both grants and revokes are recorded in the system catalogues. When a user attempts an operation against the object, MS SQL Server and Sybase check to see if the user was granted authorization either directly (e.g., "GRANT SELECT ON X TO mary", to public, or via group.

They also check to see if the user was explicitly revoked access from the object at the user, public, or group level. If a permission is revoked, the REVOKE overrides all GRANTs issued. For example, the following statements are executed in MS SQL Server or Sybase:

GRANT SELECT ON X TO public;
REVOKE SELECT ON X FROM bob;

When user "bob" attempts to select on object "X" in the database, MS SQL Server or Sybase sees if bob, public, or the group of which Bob is currently a member, has select on the object (they do) and it is not true that bob, public, or the group of which Bob is currently a member has been revoked select (Bob has). Bob cannot access object X but everyone else can.

Oracle does not allow this kind of granting and revoking because it results in an unmanageable tangle of grants and revokes. Oracle enforces the idea that if a privilege is granted to public, then everyone has the privilege, without exceptions. If it is not accessible to everyone, then it should not be granted to public.

While converting the privileges from MS SQL Server or Sybase to Oracle, all the anti-grants should be resolved before creating the DDL for Oracle. All the privileges should be additive privileges.

Revoke

Table 2-20 Table 2-20 Comparison of Revoking the Privilege Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: System Privileges: REVOKE {ALL | system_privilege} FROM {user [,user]...| group[,group]...|PUBLIC} Object privileges : REVOKE {object_privilege[, object_ privilege]...| ALL} ON object [(col_list)] FROM {user [,user]...| group[,group]... | PUBLIC}	Syntax: System Privileges: REVOKE {system_privilege [, system_privilege]... | role[,role]...} FROM {user [,user]...| role[,role]...|PUBLIC} Object Privileges: REVOKE object_privilege[,object_ privilege]... ON [user.]object FROM {user [,user]...| role[,role]...|PUBLIC} [CASCADE CONSTRAINTS]
Description: MS SQL Server and Sybase allow anti-grants. Anti-grants are REVOKE statements that do not have a corresponding, preceding, explicit grant statement. For example, one can issue the REVOKE SELECT ON SYSLOGINS.PASSWORD FROM PUBLIC command to revoke access to the PASSWORD column of the SYSLOGINS table. This statement need not be preceded with the GRANT SELECT ON SYSLOGINS.PASSWORD TO PUBLIC command. This is frequently used to revoke part of a privilege which was granted previously. It allows system administrators to quickly do things such as "Give everyone EXCEPT Bob access to table X".	Description: The system privileges REVOKE command takes privileges and roles away from users or privileges away from roles. Any system privilege may be revoked. The REVOKE command takes object privileges on a specific object away from a user or role. Oracle supports additive privileges only. This means that the REVOKE statement must have a corresponding GRANT statement. There is no way to revoke part of a privilege. You must only grant the specific privileges desired. The CASCADE CONSTRAINTS statement drops any referential integrity constraints defined by the user or by users granted the role. This applies to a REFERENCES privilege.
Permissions: The SA can revoke the system privileges and the object owners can revoke the object privileges.	Permissions: A user can issue a REVOKE statement provided the user has the corresponding GRANT permissions.
Examples: REVOKE SELECT ON table2 FROM PUBLIC REVOKE SELECT ON table3(password_col) FROM PUBLIC REVOKE CREATE TABLE, CREATE VIEW FROM jim	Examples: REVOKE basic_role FROM mary,jim,tom REVOKE SELECT ON items FROM PUBLIC

Recommendations:

While converting the privileges from MS SQL Server or Sybase to Oracle, all the anti-grants should be resolved before creating the DDL for Oracle.

All the privileges should be additive privileges.

Profile

This section contains the following tables for the schema object Profile:

• Create

• Alter

• Drop

Create

Table 2-21 Comparison of Creating the Profile Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have profiles. MS SQL Server and Sybase use database resources to set the global values for various resources such as the number of connections to the server. The maximum number of connections allowed to the server can be specified in the application environment.	Syntax: CREATE PROFILE profile LIMIT {SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME | LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | COMPOSITE_LIMIT | PRIVATE_SGA} {integer [K|M] |UNLIMITED |DEFAULT}
N/A	Description: The CREATE PROFILE command creates a set of limits on the use of the database resources. When the profile is associated with the user, you can control what the user does by those limits. SESSIONS_PER_USER limits the user to a specified number of concurrent SQL sessions. CPU_PER_SESSION limits the CPU time for a parse, execute, or fetch call in hundredths of seconds. CONNECT_TIME limits THE elapsed time of a session in minutes. IDLE_TIME disconnects a user after this number of minutes; this does not apply when a query is running. LOGICAL_READS_PER_SESSION limits the number of blocks read per session to the specified number. LOGICAL_READS_PER_CALL limits the number of blocks read for parse, execute, or fetch calls. PRIVATE_SGA limits the amount of space the user can allocate in the SGA as private. COMPOSITE_LIMIT limits the total resource cost for a session, in service units, based on a weighted sum of CPU, connect time, logical reads, and private SGA resources. UNLIMITED means there is no limit on a particular resource. DEFAULT picks up the limit from DEFAULT profile, which can be changed with the ALTER PROFILE command.
N/A	Permissions: You must have the CREATE PROFILE system privilege to create a profile.
N/A	Example: CREATE PROFILE clerk LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION unlimited CPU_PER_CALL 6000 LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100 IDLE_TIME 30 CONNECT_TIME 480

Recommendations:

Although profiles are not required in converting from MS SQL Server or Sybase to Oracle, they should be investigated and used wherever possible to aid the DBA in controlling system use.

Alter

Table 2-22 Comparison of Altering the Profile Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have profiles.	Syntax: ALTER PROFILE profile LIMIT {SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME | LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | COMPOSITE_LIMIT integer | UNLIMITED | DEFAULT | PRIVATE_SGA} {integer [K|M]|UNLIMITED| DEFAULT}
N/A	Description: The ALTER PROFILE command allows the user to modify a particular profile setting. See CREATE PROFILE for option information.
N/A	Permissions: You must have the ALTER PROFILE system privilege to alter a profile.
N/A	Example: ALTER PROFILE clerk LIMIT CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000

Recommendations:

Although profiles are not required in converting from MS SQL Server and Sybase to Oracle, they should be investigated and used wherever possible to aid the DBA in controlling system use.

Drop

Table 2-23 Comparison of Dropping the Profile Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have profiles.	Syntax: DROP PROFILE profile [CASCADE]
N/A	Description: The DROP PROFILE command drops thespecified profile from the database. CASCADE de-assigns the profile from any users to whom it is assigned and automatically assigns the DEFAULT profile instead. You must specify this option to drop a profile that is currently assigned to users.
N/A	Permissions: You must have the DROP PROFILE system privilege to remove a profile from a database.
N/A	Example: DROP PROFILE engineer CASCADE

Recommendations:

Although profiles are not required in converting from MS SQL Server and Sybase to Oracle, they should be investigated and used wherever possible to aid the DBA in controlling system use.

Role

This section contains the following tables for the schema object Role:

• Create

• Alter

• Drop

Create

Table 2-24 Comparison of Creating the Role Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_addgroup group_name sp_addrole	Syntax: CREATE ROLE role [NOT IDENTIFIED| IDENTIFIED [BY password|EXTERNALLY]]
Description: The concept of roles exists in MS SQL Server and Sybase. Roles are similar to groups in previous releases. A group is a schema object declared as a group of users. A group called PUBLIC is defined in the MODEL database. Each new database is created with a group called PUBLIC. Every user automatically becomes a member of the group PUBLIC. Create new groups with SP_ADDGROUP and assign a user to a new group with SP_CHANGEGROUP system procedures. A user can be a member of only one group other than PUBLIC at any given time. An individual must register with the database as two different users such as MGR or CLK in order to work in different groups. In this case, one user ID is a member of the MANAGER group, and another is a member of the CLERK group. MS SQL Server and Sybase do not differentiate between the privileges granted to the group and to an individual user. This allows the users to create a view or stored procedure that refers to an object granted to them via their group privilege.	Description: The CREATE ROLE command creates a named role or set of privileges. When the role is granted to the user, all the privileges of that role are granted to the user. Oracle automatically creates several roles. For example, the CONNECT, RESOURCE, and DBA roles provide compatibility with prior Oracle versions. The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles allow the user to use the import and export utilities. Create the role using the CREATE ROLE statement and then grant privileges to the role using GRANT statements. Use roles to group and easily assign privileges to many users. Roles can be granted to other roles to define a hierarchy of permissions. A user may be assigned to multiple roles and can switch between roles. The SET ROLE command can be used to enable a particular role. Once enabled, all the privileges associated with the role are enabled. This is useful to simulate real-life roles, such as manager, clerk, etc. When the user wants to access something that the role allows, enable the role using the SET ROLE command. Oracle gives a different treatment to the privileges granted to a role as opposed to directly granted to a user.
Permissions: The DBO can execute this command.	Permissions: You must have the CREATE ROLE system privilege to create a role.
Examples: sp_addgroup teller	Examples: CREATE ROLE teller IDENTIFIED BY cashflow

Recommendations:

Oracle roles and MS SQL Server or Sybase groups/roles can all be granted privileges. This concept is similar in MS SQL Server, Sybase, and Oracle because it is used mainly to give a set of privileges to a set of users.

In MS SQL Server and Sybase, you make all the users members of one group and grant a set of privileges to the group. In Oracle, you create a role with a set of privileges and then grant this role to a number of users.

To replicate the functionality of groups, create a role for each MS SQL Server or Sybase group and the privileges granted to each group are granted to the corresponding role. The roles are then assigned to each user. Each Oracle user would be assigned to the following two roles only:

CONNECT and the user's corresponding MS SQL Server or Sybase group.

If the MS SQL Server or Sybase application uses groups to grant the privileges and the privileges are required to create views and stored procedures, you must grant the privileges in the Oracle application to individual users as well as to roles. This is necessary because Oracle does not allow the user to build objects that refer to the objects which the user was given access to through roles.

Since many roles can be assigned to a person, it may be advisable to investigate the richer functionality of Oracle roles to see if more logical groupings of privileges are possible. Furthermore, since roles may be assigned other roles, you may find it more convenient to create a hierarchy of roles suitable for each application.

Alter

Table 2-25 Comparison of Altering the Role Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_changegroup new_group_name user_name_inside_db	Syntax: ALTER ROLE role {NOT IDENTIFIED | IDENTIFIED [BY password | EXTERNALLY]} GRANT role[,role]... TO {user [,user]...| role[,role]...|PUBLIC} REVOKE role[,role]... FROM {user [,user]| role[,role]|PUBLIC} The SET ROLE command can be used to switch between roles.
Description: This command changes the named user from the current group to the new group.	Description: The ALTER ROLE command lets the user modify the password for the roles already created with CREATE ROLE. The GRANT/REVOKE PRIVILEGE statements are used to change assignments of roles.
Permissions: The DBO can change the group of other database users. The users cannot do this themselves.	Permissions: You must be granted either ROLE with ADMIN OPTIONS or have the ALTER ANY ROLE system privilege to alter a role.
Examples: sp_changegroup new_grp user1 sp_changegroup "public" user1 This removes the user user1 from the existing group without assigning user1 to any other group.	Examples: ALTER ROLE teller IDENTIFIED BY letter

Recommendations:

Oracle roles and MS SQL Server or Sybase groups can both be granted privileges. Each MS SQL Server or Sybase user can only belong to one group, but each Oracle user can have many roles. This concept is similar in MS SQL Server, Sybase, and Oracle databases because it is used mainly to give a set of privileges to a set of users. In MS SQL Server and Sybase, you make all the users members of one group and give a set of privileges to the group. In Oracle, you create a role with a set of privileges and then grant this role to a group of users.

To replicate the functionality of groups, create a role for each MS SQL Server or Sybase group and the privileges granted to each group are granted to the corresponding role. The roles are then assigned to each user. Each Oracle user would be assigned the following two roles only: CONNECT and the user's corresponding MS SQL Server or Sybase group.

In Oracle, the SET ROLE command can be used to switch between roles.

If the MS SQL Server or Sybase application uses groups to grant the privileges and the privileges are required to create views and stored procedures, you must grant the privileges in the Oracle application to individual users as well as to roles.

Drop

Table 2-26 Comparison of Dropping the Role Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_dropgroup group_name sp_droprole role_name	Syntax: DROP ROLE [user.]role
Description: The sp_dropgroup command drops the specified group. The group cannot be dropped if it has users attached to it as members.	Description: The DROP ROLE command drops the specified role. It commits pending changes to the database.
Permissions: The DBO can change the group of other database users. The users cannot do this themselves.	Permissions: The user must have been granted the role with the ADMIN option or have the DROP ANY ROLE system privilege to use this command.
Example: sp_dropgroup accountant	Example: DROP ROLE accountant

Recommendations:

Oracle functionality directly matches or exceeds that of MS SQL Server and Sybase. There should be no conversion implications.

Rule

This section contains the following tables for the schema object Rule:

• Create

• Drop

Create

Table 2-27 Comparison of Creating the Rule Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE RULE [owner.]rule_name AS condition_expression sp_bindrule rule_name, {"table.column"|data type_name} [,future_only]	Syntax: Oracle allows check constraints in tables for simple business rules. Triggers may be used for more complex rules.
Description: CREATE RULE creates data integrity constraints in the database. SP_BINDRULE binds the rule to the columns. Rules in MS SQL Server and Sybase are a special kind of stored procedure. Rules can define complex data integrity constraints. A rule is bound to the database column. A rule can be bound to a particular column of a table or it can be bound to a user-defined data type. Domains are implemented using rules. The MS SQL Server or Sybase rule can refer to one or more columns in the table because they are implemented in a manner similar to the stored procedures.	N/A
Permissions: The DBO has the CREATE RULE permission by default, and can transfer it to the other users.	N/A
Examples: CREATE RULE col1_rule AS @col1_var > 100	N/A

Recommendations:

Oracle allows check constraints in tables for simple business rules. Triggers may be used for more complex rules.

The LIKE clause in MS SQL Server and Sybase rules can accept wildcard characters and ranges of values while the Oracle LIKE clause accepts only wildcard characters. If the rule has ranges of values (it uses regular expressions), it can be translated using a combination of SUBSTR and TRANSLATE in an Oracle check constraint.

MS SQL Server and Sybase integrity constraints can be implemented as check constraints in Oracle. See the Tables section for information about check in table constraints.

The column reference in the MS SQL Server or Sybase rule definition is not a column name. A rule is bound to the database column. The @var_name should be parsed out and replaced by this column name if you convert the RULEs to check constraint.

Drop

Table 2-28 Comparison of Dropping the Rule Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: sp_unbindrule rule_name, {"table.column"|data type_name} [,future_only] DROP RULE [owner.]rule_name [,[owner.]rule_name ...]	Syntax: Oracle can DROP/DISABLE a check constraint by using the ALTER TABLE command.
Description: SP_UNBINDRULE detaches the rule from the columns and must be used before DROP RULE can be implemented. DROP RULE drops a rule from the database.	N/A
Permissions: The rule owner has the DROP RULE permission by default. This privilege is not transferable.	N/A
Examples: DROP RULE col1_rule	N/A

Recommendations:

Sequences are very useful and are very efficient. You should replace the equivalent code in MS SQL Server or Sybase that generates unique IDs with references to sequences.

Sequence

This sections contains the following tables for the schema object Sequence:

• Create

• Alter

• Drop

Create

Table 2-29 Comparison of Creating the Sequence Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Sequences are not implemented in MS SQL Server or Sybase.	Syntax: CREATE SEQUENCE [user.]sequence [INCREMENT BY integer] [START WITH integer] [MAXVALUE integer | NOMAXVALUE] [MINVALUE integer | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE integer | NOCACHE] [ORDER | NOORDER]
Description: The user has to build the logic to create sequence numbers whenever necessary. This is not a trivial task in multi-user applications. The common way to implement this is with a table of sequence names and last values. Whenever a number is needed, the application must lock a row in the table, SELECT the current value, UPDATE it, and unlock the row. The problem with this approach is that it causes applications to wait while another user is getting the next value. This single-threads the application and limits the performance to the speed of this operation.	Description: A sequence generates a serial list of unique numbers. Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. Oracle implements sequences internally so that blocking does not occur. The default value for INCREMENT BY is 1. A positive value causes ascending increments, a negative value causes decrements. START WITH is the number to start the sequence. START WITH defaults to MAXVALUE for descending sequence and MINVALUE for ascending sequences. CYCLE restarts a sequence when MINVALUE or MAXVALUE is reached. CACHE allows a pre-allocated set of sequence numbers to be kept in the memory. ORDER guarantees that the sequence numbers are assigned to the instances requesting them in the order the requests are received.
N/A	Permissions: To create a sequence in your own schema, you must have the CREATE SEQUENCE privilege. To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.
N/A	Example: CREATE SEQUENCE eseq INCREMENT BY 10

Alter

Table 2-30 Comparison of Altering the Sequence Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Sequences are not implemented in MS SQL Server or Sybase.	Syntax: ALTER SEQUENCE [user.]sequence {INCREMENT BY integer | {MAXVALUE integer | NOMAXVALUE} | {MINVALUE integer | NOMINVALUE} | {CYCLE | NOCYCLE} | {CACHE integer | NOCACHE} | {ORDER | NOORDER} |
N/A	Description: Use ALTER SEQUENCE to alter the sequence definition.
N/A	Permissions: If the sequence is in your own schema, you can alter the sequence. Otherwise you must have the ALTER privilege on the sequence or the ALTER ANY SEQUENCE system privilege.
N/A	Examples: ALTER SEQUENCE eseq CYCLE CACHE 5

Recommendations:

ALTER SEQUENCE does not allow you to set the next value a sequence will generate. To set the next value of a sequence, either change the increment, select the next value, and change the increment back, or drop and recreate the sequence.

Drop

Table 2-31 Comparison of Dropping the Sequence Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Sequences are not implemented in MS SQL Server or Sybase.	Syntax: DROP SEQUENCE [user.]sequence
N/A	Description: This command drops the specified sequence from the database.
N/A	Permissions: If the sequence is in your own schema you can drop the sequence. Otherwise, you must have the DROP ANY SEQUENCE system privilege.
N/A	Example: DROP SEQUENCE elly.eseq

Recommendations:

This command does not affect database conversion. The information is provided for reference only.

Snapshot

This section contains the following table for the schema object Snapshot:

• Create

Create

Table 2-32 Comparison of Creating the Snapshot Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not support snapshots.	Syntax: Refer to Oracle8i Replication Management API Reference, Release 2 (8.1.6) (Part Number: A76958-01) for information about DBMS_REPCAT.CREATE_SNAPSHOT.
Replication in MS SQL Server and Sybase is not as mature as replication in Oracle.	Description: Snapshots provide an automatic method for table replication. There are two types of snapshots: read-only and updateable. A snapshot log is a table associated with the master table of a snapshot that tracks changes to the master table. You can have only one log per master table. A simple snapshot selects data from a single master table using a simple query. A complex snapshot selects data using a GROUP BY, CONNECT BY, subquery, join, or set operation in the query.
N/A	Permissions: User with the CREATE SNAPSHOT privilege can create snapshots in their own shema. A user with the CREATE ANY SNAPSHOT privilege can create snapshots in any user's schema.
N/A	Examples:

Recommendations:

While converting a distributed database application from MS SQL Server or Sybase to Oracle, you should look for situations requiring constant availability of remote information and handle them using table snapshots. Refer to Oracle8i Replication Management API Reference, Release 2 (8.1.6) (Part Number: A76958-01) for more information about the DBMS_REPCAT package.

Synonym

This section contains the following tables for the schema object Synonym:

• Create

• Drop

Create

Table 2-33 Comparison of Creating the Synonym Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not support synonyms. Views that do SELECT * FROM are good candidates for conversion to synonyms.	Syntax: CREATE [PUBLIC] SYNONYM [user.]synonym FOR [user.]object [@database_link]
N/A	Description: A synonym is an alias for object names. A synonym is not an object itself, but a direct reference to an object. Synonyms are used to mask the real name and owner of an object, provide public access to an object, provide location transparency for the objects of a remote database, and simplify the SQL statements for database users. PUBLIC makes the synonym available to all users. Without PUBLIC, other users must prefix the synonym with the owner name. Synonyms can be created on tables, views, stored procedures, and other synonyms. @database_link links to a remote database. The synonym refers to an object in the remote database as specified by the database link.
N/A	Permissions: You must have the CREATE SYNONYM privilege to create a private synonym in your own schema. You must have the CREATE ANY SYNONYM privilege to create a private synonym in another user's schema. You must have the CREATE PUBLIC SYNONYM privilege to create a PUBLIC synonym.
N/A	Examples: CREATE PUBLIC SYNONYM our_custs FOR customer CREATE SYNONYM custs FOR customer

Recommendations:

This command does not affect database conversion. The information is provided for reference only.

Oracle uses synonyms to build location transparency for objects in distributed database applications.

Drop

Table 2-34 Comparison of Dropping the Synonym Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not support synonyms.	Syntax: DROP [PUBLIC] SYNONYM [schema.]synonym
N/A	Description: This command removes the specified synonym from the database.
N/A	Permissions: To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege. To drop a public synonym, either the synonym must be in your own schema or you must have the DROP ANY PUBLIC SYNONYM system privilege.
N/A	Examples: DROP SYNONYM our_custs

Recommendations:

This command does not affect database conversion. The information is provided for reference only.

Tables

This section contains the following tables for the schema object Tables:

• Create

• Alter

• Drop

• Truncate

Create

Table 2-35 Comparison of Creating the Table Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE TABLE [[database.]user.]table (column data type [NOT NULL|NULL] [,column data type [NOT NULL|NULL]]...) [ON segment_name]	Syntax: CREATE TABLE [user.]table [table_constraint] ({column data type [DEFAULT clause] [column_constraint] | table_constraint} [,{column data type [DEFAULT clause] [column_constraint] | table_constraint}]...) [CLUSTER cluster(column [,column]...) | [[INITRANS integer] [MAXTRANS integer] [PCTFREE integer] [PCTUSED integer] [STORAGE storage] [TABLESPACE tablespace]] [ENABLE enable | DISABLE disable] [AS query]
Description: MS SQL Server 6.5 and Sybase are case-sensitive for object names by default. If the user is not specified, user defaults to the user issuing this command. MS SQL Server 6.5 and Sybase default to NOT NULL for columns. (Table Description is continued after the Column Constraint and Table Constraint sections below.)	Description: Oracle is case insensitive for object names. Oracle is case insensitive for object names. If the user is not specified, it defaults to the user issuing this command. Oracle defaults to NULL for columns. DEFAULT specifies a value to be assigned to the column if a row is inserted without a value for this column. The value can be a simple literal or the result of an expression. The expression, however, cannot include a reference to a column, to LEVEL, or to ROWNUM. The AS clause creates the rows of the new table through the returned query rows. The columns and types of the query must match those defined in the CREATE TABLE statement. (Table Description is continued after the Column Constraint and Table Constraint sections below.)
Column Constraints Syntax: See the Rule section.	Column Constraints Syntax: [CONSTRAINT constraint] {[NOT] NULL | {UNIQUE | PRIMARY KEY}| REFERENCES [user.]table [ (column) ] [ON DELETE CASCADE] | CHECK (condition)} [[USING INDEX[PCTFREE integer | INITRANS integer | MAXTRANS integer | TABLESPACE tablespace | STORAGE storage]] [EXCEPTIONS INTO [user.] table] | DISABLE]
Column Constraints Description: See the Rule section.	Column Constraints Description: CONSTRAINT is an optional name assigned to this constraint. NULL permits NULL values and is the default. The NOT NULL clause specifies that every row must have a value for this column. UNIQUE forces column values to be unique. You can use only one primary key on a table. An index enforces the unique or primary key, and the USING INDEX clause and its options specify the storage characteristics of that index. REFERENCES identifies this column as a foreign key from [user.]table [(column)]. If you omit the column, this implies that the name in the user.table defaults to the primary key in this table. ON DELETE CASCADE maintains referential integrity automatically by removing foreign key rows in the dependent tables if you remove the primary key row in this table. CHECK assures that the values for this column pass a condition such as the following: Amt number(12,2) CHECK (Amt >= 0) This condition may be any valid expression that tests TRUE or FALSE. It can contain functions, any columns from this table, and literals. EXCEPTIONS INTO specifies a table into which Oracle puts rows that violate an enabled integrity constraint. This table must be local and must exist before you use this option. DISABLE disables the integrity constraint when it is created. You can enable it later with the ENABLE clause in CREATE or ALTER TABLE.
Table Constraints Syntax: See the Rule section.	Table Constraints Syntax: [CONSTRAINT constraint] {[NOT] NULL | {UNIQUE|PRIMARY KEY} (column[,column]...) | FOREIGN KEY (column[,column]...) REFERENCES [user.]table [(column[,column]...) [ON DELETE CASCADE] | CHECK (condition)} [[USING INDEX[PCTFREE integer | INITRANS integer | MAXTRANS integer | TABLESPACE tablespace | STORAGE storage]] [EXCEPTIONS INTO [user.] table] | DISABLE]
Table Constraints Description: See the Rule section.	Table Constraints Description: Table constraints are similar to column constraints, except that you can reference multiple columns with a single constraint. For example, a table constraint can declare three columns as PRIMARY KEY.
Table Description (continued): The ON segment_name clause places the table on a specific segment. This logical device must already be assigned to the database with either CREATE DATABASE or ALTER DATABASE constructs. The table is created on the default segment if the ON segment_name clause is not specified.	Table Description (continued): CLUSTER includes this table in the named cluster. INITRANS specifies the initial number of transactions that can update a data block concurrently. The default value is 1. Every transaction takes space in the data block itself until the transaction is completed. MAXTRANS sets the maximum number of transactions that can update a data block concurrently. Use TABLESPACE to specify the name of the tablespace on which this table is created. Whenever Oracle inserts a row into a table, it first checks how much space is available in the current data block. If the size of the row leaves less than PCTFREE percent in the block, it puts the row in a newly allocated block instead. The default value for PCTFREE is 10. PCTUSED defaults to 40. This is the minimum percentage of available space in a block that will allow the insertion of new rows in this data block. The other space available is used for updating the existing rows. The STORAGE clause has various sub-clauses as follows: STORAGE ( [INITIAL integer] [NEXT integer] [PCTINCREASE integer] [MINEXTENTS integer] [MAXEXTENTS integer]) INITIAL allocates the first extent of space to the object. NEXT is the size of the extent allocated after the initial extent has been filled. PCTINCREASE controls the rate of growth of extents beyond the second. If this is set to 0, every additional extent will be of the same size as the second extent, specified by NEXT.
	Table Description (continued): MINEXTENTS defaults to 1, meaning that when the object is created, only one initial extent is allocated. A number larger than 1 creates that many total extents, and all of these are allocated to the object when the object is created. MAXEXTENTS sets the total number of extents that can be allocated. The default value is 99. The ENABLE and DISABLE clauses enable and disable constraints.
Permissions: The DBO has the CREATE TABLE permission by default and can transfer it to other users.	Permissions: You must have the CREATE TABLE privilege to create a table in your own schema. You must have the CREATE ANY TABLE privilege to create a table in another user's schema.
Examples: CREATE TABLE test_tbl (col1 int NOT NULL, col2 char(10) NULL) CREATE TABLE table3 (col1 my_type NOT NULL, col2 varchar(100) NULL)	Examples: CREATE TABLE test_tb1 (col1 NUMBER(6) NOT NULL PRIMARY KEY, col2 VARCHAR2(10) DEFAULT "ab") TABLESPACE tabs_1 STORAGE ( INITIAL 400K NEXT 400K MAXEXTENTS 5 PCTINCREASE 0)

Recommendations:

The conceptual definition of tables is the same in MS SQL Server, Sybase, and Oracle.

Reserved Words
Be aware that table names and column names in MS SQL Server and Sybase can be reserved words in Oracle.

Defaults
Convert MS SQL Server and Sybase defaults to be created as part of the table creation in Oracle.

NULL/Not NULL
CREATE TABLE should always specify the NULL/NOT NULL constraint as the default is the opposite in MS SQL Server, Sybase, and Oracle.

Row-Migration
Row migration occurs when a row is updated and increases in size until it no longer fits in the block at which time it must be moved to another block. Migration may also occur when rows grow in size and PCTFREE is not set correctly.

Row-Chaining
Row-chaining can occur when one row exceeds the size of one data block and has to be stored as a chain of data blocks. This affects all types of operations on this table. Oracle users can set the block size for their database to avoid such situations.

If MS SQL Server or Sybase tables have large record sizes, it may be necessary to increase the block size in Oracle to avoid row chaining problems.

Unique Keys
Define unique keys for columns defined in MS SQL Server and Sybase as unique non-clustered index.

Storage
The storage specifications for index must be added manually.

Data Types
MS SQL Server and Sybase data types should be translated to equivalent Oracle data types. See the Data Types section of this chapter for a table of equivalent data types.

Alter

Table 2-36 Comparison of Altering the Table Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: ALTER TABLE [[database.]owner.]table ADD column data type NULL [[, column data type NULL]...]	Syntax: ALTER TABLE [user.]table {ADD ({column data type [DEFAULT clause] [column_constraint] | table_constraint} [,{column data type [DEFAULT clause] [column_constraint] | table_constraint}]...) | MODIFY ({column data type [DEFAULT clause] [column_constraint] | table_constraint} [,{column data type [DEFAULT clause] [column_constraint] | table_constraint}]...) | DROP {PRIMARY KEY| UNIQUE(column[,column]...) | CONSTRAINT constraint_name} [CASCADE] | PCTFREE integer | PCTUSED integer | INITRANS integer | MAXTRANS integer | STORAGE storage | ALLOCATE EXTENT [({SIZE integer[K|M] | DATAFILE file | INSTANCE integer})] } [ENABLE enable|DISABLE disable]
Description: This command can be used to add columns at the end of the table. Columns of type BIT cannot be added to an existing table.	Description: ADD allows the user to add a column with a column constraint and a default value to the end of an existing table, or to add a table constraint to the table's definition. Table constraints and column constraints follow the same format in CREATE TABLE. MODIFY changes an existing column. The changes include: A change in column type or decrease in the size provided every row contains NULL value for this column. A NOT NULL column may be added to a table with no rows. An existing column can be modified to NOT NULL if it has a non-NULL value in every row. Increasing the length of a NOT NULL column without specifying NULL leaves it as NOT NULL. Views that reference a table with SELECT FROM will not work after the column is added to the table unless they are dropped and re-created. An existing column can be modified to have a DEFAULT or a column constraint. DROP allows the user to drop a column constraint or a table constraint. ALLOCATE EXTENT lets the user explicitly allocate a new extent. ENABLE and DISABLE, enable and disable constraints.
Permissions: The table owner and DBO can use the ALTER TABLE command. The DBO must impersonate the table owner with the SETUSER command before using the ALTER TABLE command.	Permissions: The table owner can alter the table. A user must have the ALTER privilege on the table or the ALTER ANY TABLE system privilege to alter a table.
Example: ALTER TABLE students ADD extra_curr_act varchar(255) NULL	Examples: ALTER TABLE students ADD (extra_curr_act varchar(255)) PCTFREE 30 PCTUSED 60 ALTER TABLE items ADD(CONSTRAINT c1 CHECK (unit_price < total_price))

Recommendations:

Oracle functionality exceeds that of MS SQL Server and Sybase. There should be no conversion implications.

Drop

Table 2-37 Comparison of Dropping the Table Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DROP TABLE [[database.]owner.]table [, [[database.]owner.]table ...]	Syntax: DROP TABLE [user.]table [CASCADE CONSTRAINTS]
Description: The DROP TABLE command removes the table definition and all of its data, indexes, triggers, and permissions from the database.	Description: The DROP TABLE command drops the table and commits pending changes to the database. All indexes, triggers, and grants associated with the table are dropped. Objects depending on the dropped table are marked invalid and cease to work. CASCADE CONSTRAINTS drops all referential integrity constraints referring to keys in the dropped table.
Permissions: The table owner and DBO can use the DROP TABLE command. The DBO must impersonate the table owner with the SETUSER command before using the DROP TABLE command.	Permissions: Only the table owner can drop a table. A user must have the DROP ANY TABLE system privilege to drop a table.
Example: DROP TABLE test_tab1	Examples: DROP TABLE test_tab1 CASCADE CONSTRAINTS

Recommendations:

Convert MS SQL Server and Sybase applications that drop multiple tables with one DROP TABLE command into multiple DROP TABLE commands in Oracle.

Truncate

Table 2-38 Comparison of Truncating the Table Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: TRUNCATE TABLE [[database.]owner.]table	Syntax: TRUNCATE TABLE [user.]table [DROP | REUSE STORAGE]
Description: TRUNCATE TABLE removes all rows from the table. This statement is not responded to by a DELETE TRIGGER. This statement is not logged and therefore cannot activate the trigger.	Description: TRUNCATE removes all rows from the table. DROP STORAGE deallocates space from the deleted rows. REUSE STORAGE leaves the space allocated for the new rows in the table. The TRUNCATE command is faster than a DELETE command as it does not generate the rollback information, does not fire any delete triggers, and does not record any information in the snapshot log. In addition, TRUNCATE does not invalidate the objects depending on the deleted rows or the privileges on the table. You cannot roll back the TRUNCATE statement.
Permissions: Only the table owner can issue this command.	Permissions: The table owner can truncate the table. A user must have the DELETE ANY TABLE system privilege to truncate a table.
Example: TRUNCATE TABLE test_tab1	Example: TRUNCATE TABLE test_tab1

Recommendations:

Oracle functionality directly matches or exceeds that of MS SQL Server and Sybase. There should be no conversion implications.

Tablespace

This section contains the following tables for the schema object Tablespace:

• Create

• Alter

• Drop

Create

Table 2-39 Comparison of Creating the Tablespace Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have tablespaces. See the Data Storage Concepts section of this chapter for more information.	Syntax: CREATE TABLESPACE tablespace DATAFILE file_definition [, file_ definition]... [DEFAULT STORAGE storage] [ONLINE | OFFLINE]
N/A	Description: DEFAULT STORAGE defines the default storage for all objects created in this tablespace. The ONLINE clause, which is the default, indicates that the tablespace becomes available as soon as it is created. The OFFLINE clause prevents access to the tablespace until the ALTER TABLESPACE clause changes it to ONLINE.
N/A	Permissions: Only the DBA can create the tablespaces. You must have the CREATE TABLESPACE system privilege to create a tablespace.
N/A	Example: CREATE TABLESPACE rb_segs DATAFILE 'datafile_1' SIZE 50M DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2MAXEXTENTS 50 PCTINCREASE 0) OFFLINE

Recommendations:

Tablespaces have some features in common with MS SQL Server and Sybase "databases" and some features in common with MS SQL Server and Sybase "segments".

As part of the conversion process, the structure of the Oracle database must be determined. It should be based on how databases and devices are used in MS SQL Server and Sybase.

Tablespaces and Databases
Oracle tablespaces and MS SQL Server and Sybase databases are similar in the following respects:

• Provide backup features

• Can be brought off-line independently of other parts of the server

• Provide a method for grouping related tables together

Tablespaces and Segments
Oracle tablespaces and MS SQL Server and Sybase segments both provide the control over the physical location of the database objects.

However, Oracle provides features that MS SQL Server and Sybase do not, such as default storage information and usage quotas.

Alter

Table 2-40 Comparison of Altering the Tablespace Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have tablespaces. See the Data Storage Concepts section of this chapter for more information.	Syntax: ALTER TABLESPACE tablespace {ADD DATAFILE file_definition [,file_ definition] | RENAME DATAFILE file [,file]... TO file [,file] | DEFAULT STORAGE storage | ONLINE | OFFLINE [NORMAL | TEMPORARY|IMMEDIATE]| {BEGIN | END} BACKUP}
N/A	Description: ADD DATAFILE adds a file or series of files to the tablespace. The database file names and sizes of these files are specified in file_definition. RENAME DATAFILE ...TO changes the name of an existing tablespace file. The tablespace should be offline while the renaming takes place RENAME DATAFILE ...TO does not actually rename the files, but only associates their new names with this tablespace. DEFAULT STORAGE defines the default storage for all future objects created in this tablespace. ONLINE brings the tablespace on-line, and the OFFLINE clause takes the tablespace off-line, either without waiting for its users to logoff (IMMEDIATE), or after the users have logged off (NORMAL). BEGIN BACKUP assures that all database files in this tablespace are backed up the next time you do a system backup. You can execute the BEGIN BACKUP clause at any time. The END BACKUP clause indicates that the system backup is finished. If the tablespace is on-line, any system backup must also back up archive redo logs. If the tablespace is off-line, system backup of archive redo logs is unnecessary.
N/A	Permissions: You must have the ALTER TABLESPACE system privilege to perform any of this command's operations. If you have to MANAGE TABLESPACE system privilege, you can only take the tablespace online or offline and begin or end a backup.
N/A	Example: ALTER TABLESPACE rb_segs ADD DATAFILE 'extra_file' SIZE 1M

Drop

Table 2-41 Comparison of Dropping the Tablespace Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have tablespaces. See the Data Storage Concepts section of this chapter for more information.	Syntax: DROP TABLESPACE tablespace [INCLUDING CONTENTS]
N/A	Description: This command drops the specified tablespace. INCLUDING CONTENTS allows you to drop a tablespace that contains data. Without this option, you can only drop empty tablespaces. Tablespace should be offline in order to execute this command successfully
N/A	Permissions: You must have the DROP TABLESPACE system privilege to drop a tablespace.
N/A	Example: DROP TABLESPACE test_tbspc

User

This section contains the following tables for the schema object User:

• Create

• Alter

• Drop

Create

Table 2-42 Comparison of Creating the User Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: This is a two-step process. First the user is given access to the server, and then to the specific database within that server. User is given access to server: sp_addlogin "login_id" [, password ] [, default_database ] [, default_language ] User is given access to the database. The user is considered to be a member of the PUBLIC group. sp_adduser login_id [,usr_nm_inside_db ] [,group_name]	Syntax: CREATE USER user IDENTIFIED {BY password | EXTERNALLY} [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA {integer [K | M] | UNLIMITED} ON tablespace] [PROFILE profile]
Description: These commands give the user access to the server and then to the specific database within that server. 1. The default password is NULL and the default database is the MASTER database. The Password is not encrypted and is stored in the SYSLOGINS table. It is accessible to the SA. 2. The user is registered as the user of a database with the SP_ADDUSER procedure. The user remains a member of the PUBLIC group even if made a member of another group.	Description: This command creates a user account that lets the user log into the database with a certain set of privileges and storage settings. When first created, the user has no privileges. You must GRANT roles and privileges to the user. The privilege to CREATE SESSION is the minimal privilege required for any user in order to log on. EXTERNALLY specifies that access to the database is verified through the operating system security. DEFAULT TABLESPACE is the tablespace in which the user creates objects. TEMPORARY TABLESPACE stores temporary objects created for the user's operations. You can put the QUOTA clause on either the default or temporary tablespaces to limit the amount of space, in bytes, that a user can allocate. PROFILE assigns a named profile to the user to limit usage of database resources. Oracle assigns the DEFAULT profile to the user if the profile is not specified while creating the user.
Permissions: 1. The SA can add the server login. 2. The DBO can add a user to the database.	Permissions: You must have the CREATE USER system privilege to create a user.
Example: 1. sp_addlogin "whales", whales_pass, pubs 2. sp_adduser whales name_in_db group	Example: CREATE USER whales IDENTIFIED BY whales_pass

Recommendations:

MS SQL Server, Sybase, and Oracle treat the individual users in a very similar way, and the conversion is straightforward.

The user SA in MS SQL Server and Sybase is roughly equivalent to the user SYSTEM in Oracle. The user who is the DBO in MS SQL Server or Sybase can be converted to have DBA privileges in Oracle.

Alter

Table 2-43 Comparison of Altering the User Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not support ALTER USER. However, the user can make the following comparable changes: The password can be changed using the SP_PASSWORD system procedure after the user account is set up. The default database can be changed using the SP_DEFAULTDB system procedure. The default language can be changed using the DP_DEFAULTLANGUAGE system procedure.	Syntax: ALTER USER user {IDENTIFIED {BY password | EXTERNALLY}| DEFAULT TABLESPACE tablespace| TEMPORARY TABLESPACE tablespace| QUOTA {integer [K | M] | UNLIMITED} ON tablespace| PROFILE profile| DEFAULT ROLE {role[,role]... | ALL [EXCEPT role[,role]] | NONE}}
N/A	Description: This command can be used to change a user's password or the DEFAULT or TEMPORARY tablespace. ALTER USER can also change the quota, the resource profile, or the default role.
N/A	Permissions: You must have to ALTER USER system privilege to alter the characteristics of a database user. However, you can change your own password without this privilege.
N/A	Examples: ALTER USER scott IDENTIFIED BY lion DEFAULT TABLESPACE tstest

Recommendations:

There should be no conversion implications.

Drop

Table 2-44 Comparison of Dropping the User Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: Drop the user account from the server: sp_droplogin login_id Drop the user from the database: sp_dropuser usr_nm_inside_db	Syntax: DROP USER user [CASCADE]
Description: These two commands are used to drop the user from the server and the database. The DBO cannot be dropped using the SP_DROPUSER command.	Description: This command drops the specified user. It commits pending changes to the database. CASCADE drops all the objects in the user's schema before dropping the user, and you must specify CASCADE if the user has any objects in the schema.
Permissions: The SA can drop the user account from the server. The DBO can drop the user from the database.	Permissions: The user must have the DROP USER system privilege to drop a user.
Example: Drop the user from the database: sp_dropuser bradley Drop the user account from the server: sp_droplogin bradley	Example: DROP USER bradley CASCADE

Recommendations:

Oracle functionality is similar to that of MS SQL Server and Sybase. There should be no conversion implications.

View

This section contains the following tables for the schema object View:

• Create

• Alter

• Drop

Create

Table 2-45 Comparison of Creating the View Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: CREATE VIEW [owner.]view_name [(column_name [, column_name] ...)] AS select_statement	Syntax: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [user.]view_name [(alias[,alias]...) ] AS select_statement [WITH CHECK OPTION [CONSTRAINT constraint]]
Description: INSERTs and UPDATEs are allowed on a view provided only one of the base tables is undergoing change. View cannot be created on temporary table. You cannot create a trigger or build an index on a view. You cannot update a view with the following clauses in the select_statement: ORDER BY, COMPUTE, DISTINCT, and SELECT INTO. The select_statement can refer to one or more tables and other views. If a view definition statement includes aggregate functions (GROUP BY), and the query on the view does not include the aggregate columns, the GROUP BY is ignored. If a view is defined with an outer join, and query includes a condition on a column from the inner table of the outer join, all rows from the inner table are returned. Rows that do not meet the condition, display NULL values in the respective columns of that row. This result is not the same as it would be when two tables are joined with an outer join.	Description: You can use VIEW to UPDATE and DELETE rows if the view is based on a single table and its query does not contain the GROUP BY clause, the DISTINCT clause, group functions, or references to the pseudo-column ROWNUM. You can update views containing other pseudo-columns or expressions, provided they are not referenced in the statement UPDATE. INSERT rows using VIEW if the view is based on a single table and if its query does not contain the GROUP BY clause, the DISTINCT clause, group functions, or references to any pseudo-columns, or any expressions. If the underlying table is altered to add more columns after the creation of the view, views that reference the table withSELECT*FROM must be dropped and recreated to see the new columns. You cannot create a trigger or build an index on a view. OR REPLACE recreates a view if it already exists. The FORCE option creates the view regardless of whether the tables to which the view refers exist or whether the user has privileges on them. The user still cannot execute the view but can create it. The NOFORCE option creates the view only if the base tables exist and the user has privileges on them.
	Description: (continued) If alias is specified, the view uses the alias as the name of the corresponding column in that query. If an alias is not specified, the view inherits the column name from the query. In the latter case, each column in a query must have a unique name. AS select_statement identifies the columns of the tables and other views that are to appear in this view. The WHERE clause in the select_statement determines which rows are to be retrieved. The WITH CHECK OPTION clause restricts inserts and updates performed through the view. This prevents them from creating rows that the view cannot itself select, based on the WHERE clause of the CREATE VIEW statement. This option may be used in a view that is based on another view. However, if the underlying view also has a WITH CHECK OPTION clause, it is ignored. CONSTRAINT is an optional name given to CHECK OPTION.
Permissions: The DBO has the CREATE VIEW permission by default and can transfer it to other users.	Permissions: You must have the CREATE VIEW system privilege to create a view in your own schema. You must have the CREATE ANY VIEW system privilege to create a view in another user's schema. FORCE/NOFORCE clauses dictate whether a user needs privileges on the base tables.
Examples: CREATE VIEW sales_staff AS select empno, ename, deptno from emp where deptno = 10	Example: CREATE OR REPLACE VIEW sales_staff AS select empno, ename, deptno from emp where deptno = 10 WITH CHECK OPTION CONSTRAINT sales_staff_cnst

Recommendations:

View text may include some MS SQL Server and Sybase-specific SQL constructs which must be converted manually. Also, the MS SQL Server and Sybase views that use GROUP BY and aggregates need one or more view equivalents to get the same results in Oracle.

Also see the INSERT, SELECT, UPDATE, and DELETE statements in the Data Manipulation Language section of this chapter for more information in this regard.

Alter

Table 2-46 Comparison of Altering the View Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: MS SQL Server and Sybase do not have a command comparable to ALTER VIEW.	Syntax: ALTER VIEW [user.]view COMPILE
N/A	Description: This command is used to explicitly recompile a view that is invalid. Explicit recompilation allows you to locate recompilation errors prior to runtime. You may want to explicitly recompile a view after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it. Note that this command does not change the definition of an existing view. To redefine a view you must use the CREATE VIEW command with the OR REPLACE option.
N/A	Permissions: You can issue this command on your own view. You must have the ALTER ANY TABLE system privilege to issue this command on the view of another user's schema.
N/A	Examples: ALTER VIEW user1.test_view COMPILE

Recommendations:

ALTER VIEW has no effect on database conversion. This information is provided for reference only.

Drop

Table 2-47 Comparison of Dropping the View Schema Object in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DROP VIEW [owner.]view_name [[,owner.]view_name ...]	Syntax: DROP VIEW [user.]view_name
Description: This command drops the specified view from the database.	Description: This command drops the specified view and commits pending changes to the database. Views and synonyms built on dropped view are marked invalid and cease to work.
Permissions: Only the view owner can issue this command.	Permissions: The owner of the view can drop it. You must have the DROP ANY VIEW system privilege to drop a view from the database.
Examples: DROP VIEW test_view	Examples: DROP VIEW test_view

Recommendations:

MS SQL Server and Sybase applications that drop multiple views with one DROP VIEW command must have those commands converted to multiple

DROP VIEW commands in Oracle.

Data Manipulation Language

This section uses tables to compare the syntax and description of Data Manipulation Language (DML) elements in the MS SQL Server, Sybase, and Oracle databases. Each table is followed by a recommendations section based on the information in the tables. The following topics are presented in this section:

• Connecting to the Database

• SELECT Statement

• SELECT with GROUP BY Statement

• INSERT Statement

• UPDATE Statement

• DELETE Statement

• Operators
  • Comparison Operators

  • Arithmetic Operatorss

  • String Operators

  • Set Operators

  • Bit Operators

• Built-In Functions
  • Character Functions

  • Date Functions

  • Mathematical Functions

• Locking Concepts and Data Concurrency Issues
  • Locking

  • Row-Level Versus Page-Level Locking

  • Read Consistency

• Logical Transaction Handling

Connecting to the Database

The statement illustrated in the following table connects a user to a database.

Table 2-50 Connecting to the Database in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: USE database_name	Syntax: CONNECT user_name/password SET role
Description: A default database is assigned to each user. This database is made current when the user logs on to the server. A user executes the USE DATABASE_NAME command to switch to another database.

Recommendations:

This concept of connecting to a database is conceptually different in the MS SQL Server, Sybase, and Oracle databases. An MS SQL Server or Sybase user can log on to the server and switch to another database residing on the server, provided the user has privileges to access that database. An Oracle Server controls only one database, so here the concept of a user switching databases on a server does not exist. Instead, in Oracle a user executes the SET ROLE command to change roles or re-issues a CONNECT command using a different user_name.

SELECT Statement

The statement in the following table retrieves rows from one or more tables or views.

Table 2-51 SELECT Statement in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: SELECT [ALL | DISTINCT] {select_ list} [INTO [owner.]table] [FROM [owner.]{table | view}[alias] [HOLDLOCK] [,[owner.]{table | view }[alias] [HOLDLOCK]]...] [WHERE condition] [GROUP BY [ALL] aggregate_free_ expression [, aggregate_free_ expression]...] [HAVING search_condition] [UNION [ALL] SELECT...] [ORDER BY {[[owner.]{table | view }.]column | select_list_ number | expression} [ASC | DESC] [,{[[owner.]{table | view }.]column | select_list_number | expression} [ASC | DESC]...] [COMPUTE row_aggregate(column) [,row_aggregate(column)...] [BY column [, column...]]] [FOR BROWSE] The individual element in the select list is as follows: [alias = ] {* | [owner.]{table | view}.* | SELECT ... | {[owner.]table.column | constant_ literal | expression} [alias]}	Syntax: SELECT [ALL | DISTINCT] {select_list} FROM [user.]{table | view } [@dblink] [alias] [, [user.] {table | view3} [@dblink] [alias]... [WHERE condition] [CONNECT BY condition [START WITH condition]] [GROUP BY aggregate_free_ expression [,aggregate_free_ expression]...] [HAVING search_ condition] [ {UNION [ALL] | INTERSECT | MINUS} SELECT ...] [ORDER BY {expression | position} [ASC | DESC]...] [FOR UPDATE [OF [[user.]{table | view}.]column [,[[user.]{table | view}.]column... ] [noWAIT] ] The individual element in the select list is as follows: { * | [owner.]{table | view | snapshot | synonym}.* | {[owner.]table.column | constant_literal | expression } alias]}
Description: DISTINCT eliminates the duplicate rows. The INTO clause and the items that follow it in the command syntax are optional, because MS SQL Server and Sybase allow SELECT statements without FROM clauses as can be seen in the following example: SELECT getdate() SELECT...INTO allows you to insert the results of the SELECT statement into a table. SELECT_LIST can contain a SELECT statement in the place of a column specification as follows: SELECT d.empno, d.deptname, empname = (SELECT ename FROM emp WHERE enum = d.empno) FROM dept d WHERE deptid = 10 The above example also shows the format for the column alias. ALIAS = selected_column COMPUTE attaches computed values at the end of the query. These are called row_aggregates. Outer joins are implemented as follows: WHERE tab1.col1 *= tab2.col1; where all values from TAB1 will be returned even if TAB2 does not have a match or WHERE tab1.col1 =* tab2.col1; where all values from TAB2 will be returned even if TAB1 does not have a match. If a GROUP BY clause is used, all non-aggregate select columns are needed. FOR BROWSE keywords are used to get into browse mode. This mode supports the ability to perform updates while viewing data in an OLTP environment. It is used in front-end applications using DB-Library and a host programming language. Data consistency is maintained using the TIMESTAMP field in a multi-user environment. The selected rows are not locked; other users can view the same rows during the transaction. A user can update a row if the TIMESTAMP for the row is unchanged since the time of selection.	Description: DISTINCT eliminates the duplicate rows. The INSERT INTO <table> SELECT FROM.... construct allows you to insert the results of the SELECT statement into a table. COLUMN ALIAS is defined by putting the alias directly after the selected COLUMN. If you use TABLE ALIAS, the TABLE must always be referenced using the ALIAS. You can also retrieve data from SYNONYMS. EXPRESSION could be a column name, a literal, a mathematical computation, a function, several functions combined, or one of several PSEUDO-COLUMNS. Outer joins are implemented as follows: WHERE tab1.col1 = tab2.col1 (+); Where all values from TAB1 will be returned even if TAB2 does not have a match or WHERE tab1.col1 (+) = tab2.col1; where all values from TAB2 will be returned even if TAB1 does not have a match. If a GROUP BY clause is used, all non-aggregate select columns must be in a GROUP BY clause. The FOR UPDATE clause locks the rows selected by the query. Other users cannot lock these row until you end the transaction. This clause is not a direct equivalent of the FOR BROWSE mode in MS SQL Server and Sybase.

SELECT Statements without FROM Clauses:

MS SQL Server and Sybase support SELECT statements that do not have a FROM clause. This can be seen in the following example

SELECT getdate()

Oracle does not support SELECTs without FROM clauses. However, Oracle provides the DUAL table which always contains one row. Use the DUAL table to convert constructs such as the one above.

Translate the above query to:

SELECT sysdate FROM dual;

SELECT INTO Statement:

The MS SQL Server and Sybase SELECT INTO statement can insert rows into a table. This construct, which is part SELECT and part INSERT, is not supported by ANSI. Replace these statements with INSERT...SELECT statements in Oracle.

If the MS SQL Server or Sybase construct is similar to the following:

SELECT col1, col2, col3 
INTO target_table
FROM source_table
WHERE where_clause

you should convert it to the following for Oracle:

INSERT into target_table
SELECT col1, col2, col3
FROM source_table
WHERE where_clause

Subqueries in Place of Columns:

In MS SQL Server and Sybase, a SELECT statement may appear anywhere that a column specification appears. Oracle does not support this non-ANSI extension to ANSI SQL. Change the subquery in the SELECT list either by using a DECODE statement or by dividing the query into two different queries.

Use the following SALES table as a basis for the examples below:

Year	Qty	Amount
1993	1	1.3
1993	2	1.4
1993	3	3
1993	4	2.3

MS SQL Server/Sybase:

If you want to select the year, q1 amount, q2 amount, q3 amount, and q4 as a row, MS SQL Server and Sybase accept the following query:

SELECT distinct year,
 q1 = (SELECT amt FROM sales 
       WHERE qtr=1 AND year = s.year),
 q2 = (SELECT amt FROM sales 
       WHERE qtr=2 AND year = s.year),
 q3 = (SELECT amt FROM sales 
       WHERE qtr=3 AND year = s.year), 
 q4 = (SELECT amt FROM sales
       WHERE qtr=4 AND year = s.year)
FROM sales s

In Oracle:

In this example, replace the SELECT statements with DECODE so that the query functions as normal. The DECODE function is much faster than MS SQL Server and Sybase subqueries. Translate the above query to the following for Oracle:

SELECT year, 
DECODE( qtr, 1, amt, 0 ) q1,
DECODE( qtr, 2, amt, 0 ) q2,
DECODE( qtr, 3, amt, 0 ) q3,
DECODE( qtr, 4, amt, 0 ) q4
FROM sales s;

If you cannot convert your query using the above method, create views and base the query on the views rather than on the original tables.

For example, consider the following query in MS SQL Server and Sybase:

SELECT name,
sumlength = (SELECT sum(length) FROM syscolumns WHERE id = t.id),
count_indexes = (SELECT count(*) FROM sysindexes WHERE id = t.id)
FROM sysobjects t

This query returns the sum of the lengths of the columns of a table and the number of indexes on that table. This is best handled in Oracle by using some views.

Convert this to the following in Oracle:

CREATE view V1 ( sumlength, oid ) as 
SELECT sum(length), id FROM syscolumns
GROUP BY  id

CREATE view V2 ( count_indexes, oid ) AS 
SELECT count(*), id FROM sysindexes
GROUP BY  id

SELECT name, sumlength, count_indexes 
FROM sysobjects t, v1, v2
WHERE t.id = v1.oid
AND t.id = v2.oid

Comparing Subqueries to Subqueries:

MS SQL Server and Sybase also allow a SELECT statement in the WHERE clause. For example, consider the following statement from MS SQL Server or Sybase:

SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 100
  AND(SELECT security_code 
       FROM employee_security 
       WHERE empno = emp.empno) = 
      (SELECT security_code 
       FROM security_master 
       WHERE sec_level = dept.sec_level)

Convert this to the ANSI-standard statement below for Oracle:

SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 100
  AND EXISTS (SELECT security_code
              FROM employee_security es
              WHERE es.empno = emp.empno
                AND es.security_code =
                    (SELECT security_code 
                     FROM security_master 
                     WHERE sec_level =
                           dept.sec_level));

Column Aliases:

Convert column aliases from the following MS SQL Server or Sybase syntax:

SELECT employees=col1 FROM tab1

to the following Oracle syntax:

SELECT col1 employees FROM tab1

Note: MS SQL Server and Sybase also support Oracle-style column aliases.

Table Aliases:

Remove table aliases (also known as correlation names) unless they are used everywhere.

Compute:

Replace the COMPUTE clause with another SELECT. Attach the two sets of results using the UNION clause.

Outer JOIN Syntax:

Convert the outer JOIN syntax from the MS SQL Server or Sybase syntax to the Oracle syntax.

In addition to these, there are many implications due to the differences in the implementation of the special clauses such as GROUP BY, functions, joins. These are discussed later in this chapter.

SELECT with GROUP BY Statement

Table 2-52 SELECT with GROUP BY Statement in Oracle and MS SQL Server/Sybase

MS SQL Server/Server	Oracle
Syntax: See the SELECT Statement section.	Syntax: See the SELECT Statement section.
Description: Non-aggregate SELECT columns must be in a GROUP BY clause.	Description: All non-aggregate SELECT columns must be in a GROUP BY clause.

INSERT Statement

The statements illustrated in the following table add one or more rows to the table or view.

Table 2-53 INSERT Statement in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: INSERT [INTO] [[database.]owner.] {table | view}[(column [, column]...)]{VALUES (expression [,expression]...) | query}	Syntax: INSERT INTO [user.]{table | view}[@dblink][(column [, column]...)]{VALUES (expression [, expression]...) | query...};
Description: INTO is optional. Inserts are allowed in a view provided only one of the base tables is undergoing change.	Description: INTO is required. Inserts can only be done on single table views.

Recommendations:

INSERT statements in MS SQL Server and Sybase must be changed to include an INTO clause if it is not specified in the original statement.

The values supplied in the VALUES clause in either database may contain functions. The MS SQL Server-specific functions must be replaced with the equivalent Oracle constructs.

Note: Oracle lets you create functions that directly match most MS SQL Server and Sybase functions.

Convert inserts that are inserting into multi-table views in MS SQL Server and Sybase to insert directly into the underlying tables in Oracle.

UPDATE Statement

The statement illustrated in the following table updates the data in a table or the data in a table referenced by a view.

Table 2-54

MS SQL Server	Oracle
Syntax: UPDATE [[database.]owner.] {table | view} SET [[[database.]owner.] {table. | view.}] column = expression | NULL | (select_statement) [, column = expression | NULL | (select_statement)]... [FROM [[database.]owner.]table | view [, [[database.]owner.]table | view]... [WHERE condition]	Syntax: UPDATE [user.]{table | view} [@dblink] SET [[ user.] {table. | view.}] { column = expression | NULL | (select_ statement) [, column = expression | NULL | (select_statement)...] | (column [, column]...) = (select_ statement)} [WHERE {condition | CURRENT OF cursor}]
Description: The FROM clause is used to get the data from one or more tables into the table that is being updated or to qualify the rows that are being updated. Updates through multi-table views can modify only columns in one of the underlying tables.	Description: A single subquery may be used to update a set of columns. This subquery must select the same number of columns (with compatible data types) as are used in the list of columns in the SET clause. The CURRENT OF cursor clause causes the UPDATE statement to effect only the single row currently in the cursor as a result of the last FETCH. The cursor SELECT statement must have included in the FOR UPDATE clause. Updates can only be done on single table views.

Recommendations:

There are two ways to convert UPDATE statements with FROM clauses as indicated below.

Method 1 - Convert UPDATE statements with FROM clauses:

Use the subquery in the SET clause if columns are being updated to values coming from a different table.

Convert the following in MS SQL Server or Sybase:

update titles
SET pub_id = publishers.pub_id
FROM titles, publishers
WHERE titles.title LIKE 'C%' 
AND publishers.pub_name = 'new age'

to the following in Oracle:

UPDATE titles

SET pub_id = 
( SELECT a.pub_id
  FROM publishers a
  WHERE publishers.pub_name = 'new age'
)
WHERE titles.title like 'C%'

Method 2 - Convert UPDATE statements with FROM clauses:

Use the subquery in the WHERE clause for all other UPDATE...FROM statements.

Convert the following in MS SQL Server or Sybase:

UPDATE shipping_parts
SET qty = 0
FROM shipping_parts sp, suppliers s
WHERE sp.supplier_num = s.supplier_num
  AND s.location = "USA"

to the following in Oracle:

UPDATE shipping_parts
SET qty = 0
WHERE supplier_num IN (
SELECT supplier_num 
FROM suppliers WHERE location = 'USA')

DELETE Statement

The statement illustrated in the following table removes rows from tables and rows from tables referenced in views.

Table 2-55 DELETE Statement in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
Syntax: DELETE [FROM] [[database.]owner.]{table | view} [FROM [[database.]owner.]{table | view} [, [[database.]owner.]{table | view}]...] [WHERE where_clause]	Syntax: DELETE [FROM] [user.]{table | view} [@dblink] [alias] [WHERE where_clause]
Description: The first FROM in DELETE FROM is optional. The second FROM clause is an MS SQL Server or Sybase extension that allows the user to make deletions based on the data in other tables. A subquery in the WHERE clause serves the same purpose. Deletes can only be performed through single table views.	Description: FROM is optional. ALIAS can be specified for the table name as a correlation name, which can be used in the condition. Deletes can only be performed through single table views

Remove Second FROM Clause:

Remove the second FROM clause from the DELETE statements.

Convert the following MS SQL Server or Sybase query:

DELETE 
FROM sales
FROM sales, titles
WHERE sales.title_id = titles.title_id 
AND titles.type = 'business'

to the following in Oracle:

DELETE 
FROM sales
WHERE title_id in
( SELECT title_id 
   FROM titles
   WHERE type = 'business' 
)

Remove the second FROM even if the WHERE contains a multi-column JOIN.

Convert the following MS SQL Server or Sybase query:

DELETE 
FROM sales
FROM sales, table_x
WHERE sales.a = table_x.a
   AND sales.b = table_x.b
   AND table_x.c = 'd'

to the following in Oracle:

DELETE 
FROM sales
WHERE ( a, b ) in
 ( SELECT a, b 
   FROM table_x
   WHERE c = 'd' )

Operators

Comparison Operators

The following table compares the operators used in the MS SQL Server, Sybase, and Oracle databases. Comparison operators are used in WHERE clauses and COLUMN check constraints/rules to compare values

Table 2-56 Comparison Operators in Oracle and MS SQL Server/Sybase

Operator	Same in All Three Databases	MS SQL Server/Sybase Only	Oracle Only
Equal to	=
Not equal to	!= <>		^=
Less than	<
Greater than	>
Less than or equal to	<=	!>
Greater than or equal to	>=	!<
Greater than or equal to x and less than or equal to y	BETWEEN x AND y
Less than x or greater than y	NOT BETWEEN x AND y
Pattern Matches a followed by 0 or more characters a followed by exactly 1 character a followed by any character between x and z a followed by any character except those between x and z a followed by %	LIKE 'a%' LIKE 'a_'	LIKE'a[x-z]' LIKE'a[^x-z]'	LIKE 'a\%' ESCAPE '\'
Does not match pattern	NOT LIKE
No value exists	IS NULL
A value exists	IS NOT NULL
At least one row returned by query	EXISTS (query)
No rows returned by query	NOT EXISTS (query)
Equal to a member of set	IN =ANY		= SOME
Not equal to a member of set	NOT IN != ANY <> ANY		!= SOME <> SOME
Less than a member of set	< ANY		< SOME
Greater than a member of set	> ANY		> SOME
Less than or equal to a member of set	<= ANY	!> ANY	<= SOME
Greater than or equal to a member of set	>= ANY	!< ANY	>= SOME
Equal to every member of set	=ALL
Not equal to every member of set	!= ALL <> ALL
Less than every member of set	< ALL
Greater than every member of set	> ALL
Less than or equal to every member of set	<= ALL	!> ALL
Greater than or equal to every member of set	>= ALL	!< ALL

Recommendations:

1. Convert all !< and !> to >= and <=

Convert the following in MS SQL Server or Sybase:

WHERE col1 !< 100

to this for Oracle:

WHERE col1 >= 100

2. Convert like comparisons which use [ ] and [^]

SELECT title
FROM titles
WHERE title like "[A-F]%"

Method 1 - Eliminating use of [ ]:

Use this method with the SUBSTR () function if possible.

SELECT title
from titles
where substr (titles,1,1) in
      ('A', 'B', 'C', 'D', 'E', 'F')

Method 2 - Eliminating use of [ ]:

The second method uses the % construct.

SELECT title
FROM titles
WHERE (title like 'A%' 
   OR  title like 'B%'
   OR  title like 'C%'
   OR  title like 'D%'
   OR  title like 'E%'
   OR  title like 'F%')

3. Change NULL constructs:

The following table shows that in Oracle, NULL is never equal to NULL. Change the all = NULL constructs to IS NULL to retain the same functionality.

Table 2-57 Changing NULL Constructs

NULL Construct	MS SQL Server/Sybase	Oracle
where col1 = NULL	depends on the data	FALSE
where col1 != NULL	depends on the data	TRUE
where col1 IS NULL	depends on the data	depends on the data
where col1 IS NOT NULL	depends on the data	depends on the data
where NULL = NULL	TRUE	FALSE

If you have the following in MS SQL Server or Sybase:

WHERE col1 = NULL

Convert it as follows for Oracle:

WHERE col1 IS NULL

Arithmetic Operators

Table 2-58 Arithmetic Operators in Oracle and MS SQL Server/Sybase

Operator	Same in All Three Databases	MS SQL Server/Sybase Only	Oracle Only
Add	+
Subtract	-
Multiply	*
Divide	/
Modulo	v	%	mod(x, y)

Recommendations:

Replace any Modulo functions in MS SQL Server or Sybase with the mod() function in Oracle.

String Operators

Table 2-59 String Operators in Oracle and MS SQL Server/Sybase

Operator	Same in All Three Databases	MS SQL Server/Sybase Only	Oracle Only
Concatenate	s	+	||
Identify Literal	'this is a string'	"this is also a string"

Recommendations:

Replace all addition of strings with the || construct.

Replace all double quotes string identifiers with single quote identifiers.

In MS SQL Server and Sybase, an empty string ('') is interpreted as a single space in INSERT or assignment statements on VARCHAR data. In concatenating VARCHAR, CHAR, or TEXT data, the empty string is interpreted as a single space. The empty string is never evaluated as NULL. You must bear this in mind when converting the application.

Set Operators

Table 2-60 Set Operators in Oracle and MS SQL Server/Sybase

Operator	Same in All Three Databases	MS SQL Server/Sybase Only	Oracle Only
Distinct row from either query	UNION
All rows from both queries	UNION ALL
All distinct rows in both queries	d		INTERSECT
All distinct rows in the first query but not in the second query	d		MINUS

Bit Operators

Table 2-61 Bit Operators in Oracle and MS SQL Server/Sybase

Operator	Same in All Three Databases	MS SQL Server/Sybase Only	Oracle Only
bit and		&
bit or		|
bit exclusive or		^
bit not		~

Recommendations:

Oracle enables you to write your own procedures to perform bitwise operations.

If you have the following MS SQL Server or Sybase construct:

X | Y :(Bitwise OR)

You could write a procedure called dbms_bits.or (x,y) and convert the above construct to the following in Oracle:

dbms_bits.or(x,y)

Built-In Functions

Character Functions

Table 2-62 Character Functions in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle	Description
ascii(char)	ascii(char)	Returns the ASCII equivalent of the character.
char(integer_expression)	chr(integer_expression)	Converts the decimal code for an ASCII character to the corresponding character.
charindex( specified_exp, char_string)	instr(specified_exp, char_string, 1, 1)	Returns the position where the specified_exp first occurs in the char_string.
convert( data type, expression, [format])	to_char, to_number, to_date, to_label, chartorowid, rowtochar, hextochar, chartohex	Converts one data type to another using the optional format. The majority of the functionality can be matched. Refer to Oracle8i SQL Reference, Release 2 (8.1.6) (Part Number A76989-01) for more information.
datalength ( expression )	g	Computes the length allocated to an expression, giving the result in bytes.
difference(character_exp, character_exp)	d	Returns the numeric difference of the SOUNDEX values of the two strings.
isnull(variable, new_value)	nvl(variable, new_value)	If the value of the variable is NULL, the new_value is returned.
lower(char_exp)	lower(char_exp)	Converts uppercase characters to lowercase characters.
ltrim(char_exp)	ltrim(char_exp)	Truncates trailing spaces from the left end of char_exp.
patindex(pattern,	column_name)	Returns the position of the pattern in the column value. The pattern can have wild characters. This function also works on TEXT and BINARY data types.
replicate(char_exp, n)	rpad(char_exp, length(char_exp)*n, '')	Produces a string with char_exp repeated n times.
reverse( char_string)		Reverses the given char_string.
right(char_exp, n)	substr(char_exp, (length(char_exp)	Returns the part of the string starting at the position given by n from the right and extending up to the end of the string.
rtrim(char_exp)	rtrim(char_exp)	Truncates the trailing spaces from the right end of char_exp.
soundex(exp)	soundex(exp)	Returns phonetically similar expressions to the specified exp.
space(int_exp)	rpad(' ', int_exp-1, '')	Generates a string with int_exp spaces.
str(float_exp, length)	to_char(float_exp)stuff(char_exp, start, length, replace_str)substr(char_exp, 1, start) ||replace_str ||substr(char_exp, start+length)	Replaces a substring within char_exp with replace_str.
substring(char_exp, start, length) Works on IMAGE and TEXT data types	substr(char_exp, start, length) Does not work with LONG and LONG_RAW data types	Replaces a substring within char_exp with replace_str.
textptr(column_name)	d	Returns a pointer as a varbinary(16) data type for a named IMAGE or TEXT column.
textvalid("column_name", text_pointer)	h	Returns 1 if the specified text_pointer is valid for the specified column_name. The column must be of type TEXT or IMAGE.
upper(char_exp)	upper(char_exp)	Converts lowercase characters to uppercase characters.

Miscellaneous Functions

Table 2-63 Comparison Operators in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle	Description
datalength ( expression )	lengthb	Computes the length allocated to an expression, giving the result in bytes.
isnull(variable, new_value)	nvl(variable, new_value)	If the value of the variable is NULL, the new_value is returned.

Recommendations:

Note: The above functions tables list all the MS SQL Server and Sybase character manipulation functions. They do not list all the Oracle functions. There are many more Oracle character manipulation functions that you can use.

Defining Functions in Oracle:

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and function of MS SQL Server and Sybase functions.

Date Functions

Table 2-64 Date Functions in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle	Description
dateadd(dd, int_ exp,datetime_var)	date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of days to the date contained in datetime_var.
dateadd(mm, int_ exp,datetime_var)	add_months(date, int_exp) or date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of months to the date contained in datetime_var.
dateadd(yy, int_ exp,datetime_var)	date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of years to the date contained in datetime_var.
datediff(dd, datetime1,datetime2)	date2-date1	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of days.
datediff(mm, datetime1,datetime2)	months_between ( date2, date1)	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of months.
datediff(yy, datetime1,datetime2)	(date2-date1) /365.254	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of years.
datename (datepart, date)	to_char(date, format)	Returns the specified part of the date as an integer. The MS SQL Server and Sybase DATETIME has a higher precision than Oracle DATE. For this reason, it is not always possible to find an equivalent format string in Oracle to match the datepart in MS SQL Server or Sybase. See the Data Types section of this chapter for more information about conversion of the DATETIME data type.
datepart(datepart, date)	to_char(date, format)	Returns the specified part of the date as a character string (name). The MS SQL Server and Sybase DATETIME has a higher precision than Oracle DATE'. For this reason, it is not always possible to find an equivalent format string in Oracle to match the datepart in MS SQL Server or Sybase.
getdate()	sysdate	Returns the system date.

Recommendations:

The above table lists all the MS SQL Server and Sybase date manipulation functions. It does not list all the Oracle date functions. There are many more Oracle date manipulation functions that you can use.

It is recommended that you convert most date manipulation functions to "+" or "-" in Oracle.

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and functionality of all MS SQL Server and Sybase functions. This is a useful feature, where users can call a PL/SQL function from a SQL statement's SELECT LIST, WHERE clause, ORDER BY clause, and HAVING clause. With the parallel query option, Oracle executes the PL/SQL function in parallel with the SQL statement. Hence, users create parallel logic.

Mathematical Functions

Table 2-65 Mathematical Functions in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase	Oracle
abs(n)	abs(n)
acos(n)	acos(n)
asin(n)
atan(n)	atan(n)
atn2(n,m)
ceiling(n)	ceil(n)
cos(n)	cos(n)
cot(n)
degrees(n)
exp(n)	exp(n)
floor(n)	floor(n)
log(n)	ln(n)
log10(n)	log(base,number)
pi()
power(m,n)	power(m,n)
radians(n)
rand(n)
round(n[,m])	round(n[,m])
sign(n)	sign(n)
sin(n)	sin(n)
sqrt(n)	sqrt(n)
tan(n)	tan(n)

Recommendations:

The above table lists all the MS SQL Server and Sybase number manipulation functions. It does not list all the Oracle mathematical functions. There are many more Oracle number manipulation functions that you can use.

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and functionality of all MS SQL Server and Sybase functions. This is the most flexible approach. Users can write their own functions and execute them seamlessly from a SQL statement.

Oracle functions listed in the table work in SQL as well as PL/SQL.

Locking Concepts and Data Concurrency Issues

Locking

Locking serves as a control mechanism for concurrency. Locking is a necessity in a multi-user environment because more than one user at a time may be working with the same data.

Table 2-66 Locking in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase

Oracle

MS SQL Server and Sybase locking is fully automatic and does not require intervention by users. MS SQL Server and Sybase apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place. For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, MS SQL Server and Sybase reads block the modifications to the data. Update locks: Update locks are held at the page level. They are placed during the initial stages of an update operation when the pages are being read. Update locks can co-exist with shared locks. If the pages are changed later, the update locks are escalated to exclusive locks. Intent locks: MS SQL Server and Sybase locking is fully automatic and does not require intervention by users. MS SQL Server and Sybase apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place. For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, MS SQL Server and Sybase reads block the modifications to the data. Extent locks: Extent locks lock a group of eight database pages while they are being allocated or freed. These locks are held during a CREATE or DROP statement, or during an INSERT that requires new data or index pages. A list of active locks for the current server can be seen with SP_LOCK system procedure.

Oracle locking is fully automatic and does not require intervention by users. Oracle features the following categories of locks: Data locks (DML locks) to protect data.The "table locks" lock the entire table and "row locks" lock individual rows. Dictionary locks (DDL locks) to protect the structure of objects. Internal locks to protect internal structures, such as files. DML operations can acquire data locks at two different levels; one for specific rows and one for entire tables. Row-level locks: An exclusive lock is acquired for an individual row on behalf of a transaction when the row is modified by a DML statement. If a transaction obtains a row level lock, it also acquires a table (dictionary) lock for the corresponding table. This prevents conflicting DDL (DROP TABLE, ALTER TABLE) operations that would override data modifications in an on-going transaction. Table-level data locks can be held in any of the following modes: Row share table lock (RW): This indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. This prevents other transactions from obtaining exclusive write access to the same table by using the LOCK TABLE table IN EXCLUSIVE MODE statement. Apart from that, all the queries, inserts, deletes, and updates are allowed in that table. Row exclusive table lock (RX): This generally indicates that the transaction holding the lock has made one or more updates to the rows in the table. Queries, inserts, deletes, updates are allowed in that table. Share lock (SL): Share row exclusive lock(SRX) Exclusive lock (X): The dynamic performance table V$LOCK keeps the information about locks.

Recommendations:

In MS SQL Server and Sybase, SELECT statements obtain shared locks on pages/rows. This prevents other statements from obtaining an exclusive lock on those pages/rows. All statements that update the data need an exclusive lock. This means that the SELECT statement in MS SQL Server or Sybase blocks the UPDATE statements as long as the transaction that includes the SELECT statement does not commit or rollback. This also means that two transactions are physically serialized whenever one transaction selects the data and the other transaction wants to change the data first and then select the data again. In Oracle, however, SELECT statements do not block UPDATE statements, since the rollback segments are used to store the changed data before it is updated in the actual tables. Also, the reader of the data is never blocked in Oracle. This allows Oracle transactions to be executed simultaneously.

If MS SQL Server or Sybase logical transactions are automatically translated to Oracle logical transactions, the transactions explained above that execute properly in MS SQL Server and Sybase as they are serialized will cause a deadlock in Oracle. These transactions should be identified and serialized to avoid the deadlock. These transactions are serialized in MS SQL Server and Sybase as INSERT, UPDATE, and DELETE statements block other statements.

Row-Level Versus Page-Level Locking

Table 2-67 Row-Level Versus Page-Level Locking in Oracle and MS SQL Server/Sybase

MS SQL Server/Sybase

Oracle

MS SQL Server 6.5 and Sybase do not have a row-level locking feature. MS SQL Server 6.5 and Sybase apply a page-level lock, which effectively locks all rows on the page, whenever any row in the page is being updated. This is an exclusive lock whenever the data is being changed by DML statements. MS SQL Server 7.0 implements a form of row-level locking. MS SQL Server 7.0 escalates locks at row level to page level automatically. SELECT statements are blocked by exclusive locks that lock an entire page.

Oracle has a row-locking feature. Only one row is locked when a DML statement is changing the row.

Recommendations:

No changes are required to take advantage of the row-level locking feature of Oracle.

Read Consistency

Table 2-68 Read Consistency in Oracle and MS SQL Server/Sybase

MS SQL Server

Oracle

MS SQL Server and Sybase provide the HOLDLOCK function for transaction-level read consistency. Specifying a SELECT with HOLDLOCK puts a shared lock on the data. More than one user can execute a SELECT with HOLDLOCK at the same time without blocking each other. When one of the users tries to update the selected data, HOLDLOCK blocks the update until the other users commit, rollback, or attempt an update and a deadlock occurs. This means that HOLDLOCK prevents other transactions from updating the same data until the current transaction is in effect.

Read consistency as supported by Oracle does the following: Ensures that the set of data seen by a statement is consistent at a single point-in-time and does not change during statement execution Ensures that reads of database data do not wait for other reads or writes of the same data Ensures that writes of database data do not wait for reads of the same data Ensures that writes wait for other writes only if they attempt to update identical rows in concurrent transactions To provide read consistency, Oracle creates a read-consistent set of data when a table is being read and simultaneously updated. Read consistency functions as follows: 1. When an update occurs, the original datavalues changed by the update are recorde in rollback segments. 2. While the update remains part of an uncommitted transaction, any user that reads the modified data views the original data values. Only statements that start afteranother user's transaction is committed reflect the changes made by the transaction. You can specify that a transaction be read only using the following command: SET TRANSACTION READ ONLY

Logical Transaction Handling

Table 2-69

MS SQL Server/Sybase

Oracle

After completion, any statement not within a transaction is automatically committed.A statement can be a batch containing multiple T-SQL statements that are sent to the server as one stream. The changes to the database are automatically committed after the batch executes. A ROLLBACK TRAN statement subsequently executed has no effect. In MS SQL Server and Sybase, transactions are not implicit. Start logical transaction with a BEGIN TRANSACTION clause. Logical transactions can be committed or rolled back as follows. BEGIN TRANSACTION [transaction_name] Use COMMIT TRAN to commit the transaction to the database. You have the option to specify the transaction name. Use ROLLBACK TRAN to roll back the transaction. You can set savepoints to roll back to a certain point in the logical transaction using the following command: SAVE TRANSACTION savepoint_name Roll back to the specified SAVEPOINT with the following command: ROLLBACK TRAN <savepoint_name> MS SQL Server and Sybase allow you to nest BEGIN TRAN/COMMIT TRAN statements. When nested, the outermost pair of transactions creates and commits the transaction. The inner pairs keep track of the nesting levels. A ROLLBACK command in the nested transactions rolls back to the outermost BEGIN TRAN level, if it does not include the name of the SAVEPOINT. Most MS SQL Server and Sybase applications require two-phase commit, even on a single server. To see if the server is prepared to commit the transaction, use PREPARE TRAN in two-phase commit applications. Completed transactions are written to the database device at CHECKPOINT. A CHECKPOINT writes all dirty pages to the disk devices since the last CHECKPOINT. Calls to remote procedures are executed independently of any transaction in which they are included.

Statements are not automatically committed to the database. The COMMIT WORK statement is required to commit the pending changes to the database. Oracle transactions are implicit. This means that the logical transaction starts as soon as data changes in the database. COMMIT WORK commits the pending changes to the database. ROLLBACK undoes all the transactions after the last COMMIT WORK statement. Savepoints can be set in transactions with the following command: SET SAVEPOINT savepoint_name The following command rolls back to the specified SAVEPOINT: ROLLBACK <savepoint_name> Two-phase commit is automatic and transparent in Oracle. Two-phase commit operations are needed only for transactions which modify data on two or more databases. When a CHECKPOINT occurs, the completed transactions are written to the database device. A CHECKPOINT writes all dirty pages to the disk devices that have been modified since last checkpoint

Recommendations:

Transactions are not implicit in MS SQL Server and Sybase. Therefore, applications expect that every statement they issue is automatically committed it is executed.

Oracle transactions are always implicit, which means that individual statements are not committed automatically. When converting an MS SQL Server or Sybase application to an Oracle application, care needs to be taken to determine what constitutes a transaction in that application. In general, a COMMIT work statement needs to be issued after every "batch" of statements, single statement, or stored procedure call to replicate the behavior of MS SQL Server or Sybase for the application.

In MS SQL Server and Sybase, transactions may also be explicitly begun by a client application by issuing a BEGIN TRAN statement during the conversion process.