Date storage comparison of SQL server2000 and oracle9i

Date storage comparison of SQL server and oracle

Microsoft SQL Server	Oracle
Database Devices: A database device is mapped to the specified physical disk files.	Data Files: One or more data files are created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the data files in a tablespace is the total storage capacity of the tablespace. The combined storage capacity of a  tablespace in a database is the total storage capacity of the database. Once created, a data file 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 data files.
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 Microsoft SQL Server 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.
	Tablespace Segments (Cont): 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 data files 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 Microsoft SQL Server.

 

Comments:

There is little difference in storage structure of the two databases. Physical storage structure differs as there are tablespaces in oracle to group related logical structures together but segments are used in SQL server. In oracle, each database has a control file but no control files are found in SQL server.

References:

 

 

1. Oracle (2007).Oracle® Database SQL Developer Supplementary Information for Microsoft SQL Server Migrations Release 1.2 Part Number E10379-01 from http://download.oracle.com/docs/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm#BABFDFBI 

2 .Abbey,M. Corney,M. & Abramson,I.(2002). Oracle9i A beginners’s Guide

U.S.A: McGraw-hillW

3. Waymire, R.,& Sawtell,R.(2001). Sams Teach Yourself Microsoft Sql Server2000 in 21 Days

U.S.A: Sams

 

 

 

 

 

 

 

 

Advertisement

~ by sikanderjeet on April 23, 2009.