COMPARISON BETWEEN ORACLE9I AND SQL SERVER2000

Introduction

In this article, I compare SQL Server 2000 with Oracle 9i Database regarding price, performance, platforms supported, SQL dialects and products limits.

Platform comparison

SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.
In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

Hardware requirements

To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware:

Hardware	Requirements
Processor	Pentium 166 MHz or higher
Memory	32 MB RAM (minimum for Desktop Engine), 64 MB RAM (minimum for all other editions), 128 MB RAM or more recommended
Hard disk space	270 MB (full installation), 250 MB (typical), 95 MB (minimum), Desktop Engine: 44 MB Analysis Services: 50 MB minimum and 130 MB typical English Query: 80 MB

Oracle 9i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

To install Oracle 9i under the Intel or compatible platforms, you should have the following hardware:

Hardware	Requirements
Processor	Pentium 166 MHz or higher
Memory	RAM: 128 MB (256 MB recommended) Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB
Hard disk space	140 MB on the System Drive plus 4.5 GB for the Oracle Home Drive (FAT) or 2.8 GB for the Oracle Home Drive (NTFS)

To install Oracle 9i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware:

Hardware	Requirements
Memory	A minimum of 512 MB RAM
Swap Space	A minimum of 2 x RAM or 400 MB, whichever is greater
Hard disk space	4.5 GB

 

Software requirements

SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE) and requires the following software:

Operating System	Enterprise Edition	Standard Edition	Personal Edition	Developer Edition	Desktop Engine	SQL Server CE
Windows CE	No	No	No	No	No	Yes
Windows 9x	No	No	Yes	No	Yes	No
Windows NT 4.0 Workstation with Service Pack 5	No	No	Yes	Yes	Yes	No
Windows NT 4.0 Server with Service Pack 5	Yes	Yes	Yes	Yes	Yes	No
Windows NT 4.0 Server Enterprise Edition with Service Pack 5	Yes	Yes	Yes	Yes	Yes	No
Windows 2000 Professional	No	No	Yes	Yes	Yes	No
Windows 2000 Server	Yes	Yes	Yes	Yes	Yes	No
Windows 2000 Advanced Server	Yes	Yes	Yes	Yes	Yes	No
Windows 2000 DataCenter	Yes	Yes	Yes	Yes	Yes	No
Windows XP Professional	No	No	Yes	Yes	Yes	No

Oracle 9i Database comes in three editions: Enterprise, Standard and Personal and requires the following software:

Platform	Operating System Version	Required Patches
Windows-based	Windows NT 4.0	Service Pack 5
Windows-based	Windows 2000	Service Pack 1
Windows-based	Windows XP	Not Necessary
AIX-Based	AIX 4.3.3	Maintenance Level 09 and IY24568, IY25282, IY27614, IY30151
AIX-Based	AIX 5.1	AIX 5L release 5.1 ML01+ (IY22854), IY26778, IY28766, IY28949, IY29965, IY30150
Compaq Tru64 UNIX	Tru64 5.1	5.1 patchkit 4
Compaq Tru64 UNIX	Tru64 5.1A	5.1A patchkit 1
HP-UX	HP-UX version 11.0 (64-bit)	Sept. 2001 Quality Pack, PHCO_23792, PHCO_24148, PHKL_24268, PHKL_24729, PHKL_ 25475, PHKL_25525, PHNE_24715, PHSS_23670, PHSS_24301, PHSS_24303, PHSS_24627, PHSS_22868
Linux	SuSE Linux Enterprise Server 7 (or SLES-7) with kernel 2.4.7, and glibc 2.2.2	Not Necessary
Sun Solaris	Solaris 32-Bit 2.6 (5.6), 7 (5.7) or 8 (5.8)	Not Necessary
Sun Solaris	Solaris 64-Bit 8 (5.8)	Update 5

 

Performance comparison

It is very difficult to make the performance comparison between SQL Server 2000 and Oracle 9i Database. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database’s provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms.

TPC tests

The Transaction Processing Performance Council (TPC.Org) is independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy.

The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.

The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the TPC-C test (OLTP test).

Note. Because most organizations really do not run very large databases, so the key points on which SQL Server 2000 won the TPC-C benchmarks do not really matter to the vast majority of companies.

Price comparison

One of the main Microsoft SQL Server 2000 advantage in comparison with Oracle 9i Database is that SQL Server is cheaper. Other SQL Server advantage is that Microsoft includes the Online analytical processing (OLAP) and Data Mining as standard features in SQL Server 2000 Enterprise Edition. So, you can save up to four times with SQL Server 2000 Enterprise Edition if you use OLAP and Data Mining.

Compare pricing for SQL Server 2000 Standard Edition and Oracle9i Standard Edition:

Number of CPUs	Oracle9i Standard Edition	SQL Server 2000 Standard Edition
1	$15,000	$4,999
2	$30,000	$9,998
4	$60,000	$19,996
8	$120,000	$39,992
16	$240,000	$79,984
32	$480,000	$159,968

Compare pricing for SQL Server 2000 Enterprise Edition (which include OLAP and Data Mining) and Oracle9i Enterprise Edition with OLAP and/or Data Mining:

Number of CPUs	Oracle9i Enterprise Edition	Oracle9i Enterprise Edition with OLAP or Data Mining	Oracle9i Enterprise Edition With OLAP and Data Mining	SQL Server 2000 Enterprise Edition
1	$40,000	$60,000	$80,000	$19,999
2	$80,000	$120,000	$160,000	$39,998
4	$160,000	$240,000	$320,000	$79,996
8	$320,000	$480,000	$640,000	$159,992
16	$640,000	$960,000	$1,280,000	$319,984
32	$1,280,000	$1,920,000	$2,560,000	$639,968

Note. This is not a full price comparison between SQL Server 2000 and Oracle 9i Database. It is only a brief comparison. You can have any discounts and the prices can be increased or decreased in the future. See Microsoft and Oracle to get more information about the price of their products.

Features comparison

Both SQL Server 2000 and Oracle 9i Database support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article I want to make the brief comparison of the Transact-SQL with PL/SQL and show some SQL Server 2000 and Oracle 9i Database limits.

T-SQL vs PL/SQL

The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by Oracle 9i Database is called PL/SQL. PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL:

Feature	PL/SQL	T-SQL
Indexes	B-Tree indexes, Bitmap indexes, Partitioned indexes, Function-based indexes, Domain indexes	B-Tree indexes
Tables	Relational tables, Object tables, Temporary tables, Partitioned tables, External tables, Index organized tables	Relational tables, Temporary tables
Triggers	BEFORE triggers, AFTER triggers, INSTEAD OF triggers, Database Event triggers	AFTER triggers, INSTEAD OF triggers
Procedures	PL/SQL statements, Java methods, third-generation language (3GL) routines	T-SQL statements
Arrays	Supported	Not Supported

 

SQL Server 2000 and Oracle 9i limits

Here you can find some SQL Server 2000 and Oracle 9i Database limits:

Feature	SQL Server 2000	Oracle 9i Database
database name length	128	8
column name length	128	30
index name length	128	30
table name length	128	30
view name length	128	30
stored procedure name length	128	30
max columns per index	16	32
max char() size	8000	2000
max varchar() size	8000	4000
max columns per table	1024	1000
max table row length	8036	255000
max query size	16777216	16777216
recursive subqueries	40	64
constant string size in SELECT	16777207	4000
constant string size in WHERE	8000	4000

 

Conclusion

It is not true that SQL Server 2000 is better than Oracle 9i or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database’s provider. But SQL Server 2000 has some advantages in comparison with Oracle 9i and vice versa.

The SQL Server 2000 advantages:

• SQL Server 2000 is cheaper to buy than Oracle 9i Database.
• SQL Server 2000 holds the top TPC-C performance and price/performance results.
• SQL Server 2000 is generally accepted as easier to install, use and manage.

The Oracle 9i Database advantages:

• Oracle 9i Database supports all known platforms, not only the Windows-based platforms.
• PL/SQL is more powerful language than T-SQL.
• More fine-tuning to the configuration can be done via start-up parameters.

 References

1. SQL Server 2000 Books Online

2. Oracle documentation

3. Oracle and SQL Server 2000 Price Comparison

4. Why is SQL Server better/worse than Oracle?

5. Top Ten TPC-C by Performance Version 5 Results

6. Top Ten TPC-C by Price/Performance Version 5 Results

STORAGE STRUCTURE OF ORACLE

 

Segment Types

 Objects in an Oracle database such as tables, indexes, clusters, sequences, etc., are comprised of segments.  There are several different types of segments.

 Table:  Data is stored in tables.  When a table is created with the CREATE TABLE command, a table segment is allocated to the new object.

·         Table segments do not store table rows in any particular order.

·         Table segments do not store data that is clustered or partitioned.

·         The DBA has almost no control over the location of rows in a table.

·         The segment belongs to a single tablespace.

 

Table Partition:  If a table has high concurrent usage, that is simultaneous access by many different system users as would be the case for a SALES_ORDER table in an online-transaction processing environment, you will be concerned with scalability and availability of information as the DBA.  This may lead you to create a table that is partitioned into more than one table partition segment.

• A partitioned table has a separate segment for each partition.
• Each partition may reside in a different tablespace.
• Each partition may have different storage parameters.
• The Oracle Enterprise Edition must have the partitioning option installed in order to create a partitioned table.

 Cluster:  Rows in a cluster segment are stored based on key value columns.  Clustering is sometimes used where two tables are related in a strong-weak entity relationship.

• A cluster may contain rows from two or more tables.
• All of the tables in a cluster belong to the same segment and have the same storage parameters.
• Clustered table rows can be accessed by either a hashing algorithm or by indexing.

 

Index:  When an index is created as part of the CREATE TABLE or CREATE INDEX command, an index segment is created. 

• Tables may have more than one index, and each index has its own segment.
• Each index segment has a single purpose – to speed up the process of locating rows in a table or cluster.

 Index-Organized Table:  This special type of table has data stored within the index based on primary key values.  All data is retrievable directly from the index structure (a tree structure).

 Index Partition:  Just as a table can be partitioned, so can an index.  The purpose of using a partitioned index is to minimize contention for the I/O path by spreading index input-output across more than one I/O path.

• Each partition can be in a different tablespace.
• The partitioning option of Oracle Enterprise Edition must be installed.

 Undo:  An undo segment is used to store “before images” of data or index blocks prior to changes being made during transaction processing.  This allows a rollback using the before image information.

 Temporary:  Temporary segments are created when commands such as CREATE INDEX, SELECT DISTINCT, GROUP BY, and ORDER BY clauses cause Oracle to perform memory sorts of information.  Often sort actions require more memory than is available.  When this occurs, intermediate results of sort actions are written to disk so that the sort operation can continue – this allows information to swap in and out of memory by writing/reading to/from disk.  Temporary segments store intermediate sort results.

 LOB:  Large objects can be stored as one or more columns in a table.  Large objects (LOBs) include images, separate text documents, video, sound files, etc. 

• These LOBs are not stored in the table – they are stored as separate segment objects.
• The table with the column actually has a “pointer” value stored in the column that points to the location of the LOB.

 Nested Table:  A column in one table may consist of another table definition.  The inner table is called a “nested table” and is stored as a separate segment.  This would be done for a SALES_ORDER table that has the SALES_DETAILS (order line rows) stored as a nested table. 

 Bootstrap Segment:  This is a special cache segment created by the sql.bsq script that runs when a database is created. 

• It stores initial data dictionary cache information when a database is opened.
• This segment cannot be queried or updated and requires no DBA maintenance.

 Storage Clauses/Parameters

 When database objects are created, the object always has a set of storage parameters.  This figure shows three ways that an object can obtain storage clause parameters. 

 

Tablespaces have space managed depending on the type of tablespace:

• Locally Managed Tablespaces – use bitmaps to track used and free space – Locally managed is the default for non-SYSTEM permanent tablespaces when the type of extent management is not specified at the time a tablespace is created.
  • Tablespace extents for Locally Managed are either (1) Uniform specified with the UNIFORM clause or (2) variable extent sizes determined by the system with the AUTOALLOCATE clause. 
    • Uniform:
      • Specify an extent size or use the 1MB default size.
      • Each extent contains at least 5 database blocks.
    • System Managed:
      • Oracle determines optimal size of additional extents with a minimum extent size of 64KB.
      • With SEGMENT SPACE MANAGEMENT AUTO, the minimum extent size is 1MB if the Database Block size is 16K or larger.
• Dictionary Managed Tablespaces – data dictionary tables track space utilization.

 Facts about storage parameters:

• Segment storage parameters can override the tablespace level defaults with the exception of two parameters.  You cannot override the MINIMUM EXTENT or UNIFORM SIZE tablespace parameters.
• If you do not specify segment storage parameters, then a segment will inherit the tablespace default parameters.
• If tablespace default storage parameters are not set, the Oracle server system default parameters are used. 
• Locally managed tablespaces cannot have the storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS specified; however, these parameters can be specified at the segment level. 
• When storage parameters of a segment are modified, the modification only applies to extents that are allocated after the modification takes place.

 Extents

 Extents are allocated in chunks that are not necessarily uniform in size, but the space allocated is contiguous on the disk drive as is shown in this figure.  When a database object such as a table grows, additional disk space is allocated to its segment of the tablespace in the form of an extent.

 Example of a CREATE TABLE Command

• This shows the creation of a table named Orders in the Data01 tablespace.
• Data01 is locally managed, but here the storage parameters specified override the storage parameters for the tablespace.

 CREATE TABLE Orders (
    Order_Id        NUMBER (3) PRIMARY KEY,
    Order_Ddate     DATE DEFAULT (SYSDATE),
    Ship_Date       DATE,
    Client          VARCHAR(3) NOT NULL,
    Amount_Due      NUMBER(10,2),
    Amount_Paid     NUMBER(10,2))
PCTFREE 5 PCTUSED 65
STORAGE (

    INITIAL 1M

    NEXT 48K

    PCTINCREASE 5

    MINEXTENTS 1

    MAXEXTENTS UNLIMITED)
TABLESPACE Data01;

 

Database Block

 The Database Block or simply Data Block, as you have learned, is the smallest size unit for input/output from/to disk in an Oracle database.

• A data block may be equal to an operating system block in terms of size, or may be larger in size, and should be a multiple of the operating system block.
• The DB_BLOCK_SIZE parameter sets the size of a database’s standard blocks at the time that a Database is created.  However, Oracle actually supports the creation of databases that have more than one block size.  This is primarily done when you need to specify tablespaces with different block sizes in order to maximize I/O performance. 

 

• You’ve already learned that a database can have up to four nonstandard block sizes specified. 
• Block sizes must be sized as a power of two between 2K and 32K in size, e.g., 2K, 4K, 8K, 16K, or 32K. 
• A sub cache of the Database Buffer Cache is configured by Oracle for each nonstandard block size. 

 

Standard Block Size:  The DB_CACHE_SIZE parameter replaces the previous DB_BLOCK_BUFFERS parameter from Oracle 8i and previous RDBMS software releases.  This new parameter specifies the size of the Database Buffer Cache.  The minimum size for DB_CACHE_SIZE must be specified as follows:

• One granule where a granule is a unit of contiguous virtual memory allocation in RAM. 
• If the total System Global Area (SGA) based on SGA_MAX_SIZE is less than 128MB, then a granule is 4MB.
• If the total SGA is greater than 128MB, then a granule is 16MB. 
• The default value for DB_CACHE_SIZE is 48MB. 

 Nonstandard Block Size:  If a DBA wishes to specify one or more nonstandard block sizes, the parameter following parameters are set.  If the operating system only supports block sizes up to a specified limit, for example, 16MB, then do not try to set block sizes greater than the operating system limit.  Additionally, do not use these parameters to set standard block sizes.  For example, if the standard block size is 4KB, do not use the DB_4K_CACHE_SIZE parameter.

• DB_2K_CACHE_SIZE   — parameter for 2KB nonstandard block sizes.
• DB_4K_CACHE_SIZE   — parameter for 4KB nonstandard block sizes.
• DB_8K_CACHE_SIZE   — parameter for 8KB nonstandard block sizes.
• DB_16K_CACHE_SIZE   — parameter for 16KB nonstandard block sizes.
• DB_32K_CACHE_SIZE   — parameter for 32KB nonstandard block sizes.

 

• Temporary tablespaces must be the standard block size.  This also applies to permanent tablespaces that have been specified as default temporary tablespaces for system users.

 

Data Block Contents

 

This figure shows the components of a data block.  This is the structure regardless of the type of segment to which the block belongs.

Block header – contains common and variable components including the block address, segment type, and transaction slot information. 

The block header also includes the table directory and row directory. 

On average, the fixed and variable portions of block overhead total 84 to 107 bytes.

• Table Directory – used to track the tables to which row data in the block belongs.
  • Data from more than one table may be in a single block if the data are clustered.
  • The Table Directory is only used if the data are clustered.
• Row Directory – used to track which rows from a table are in this block. 
  • The Row Directory includes for each row or row fragment in the row data area.
  • When space is allocated in the Row Directory to store information about a row, this space is not reclaimed upon deletion of a row, but is reclaimed when new rows are inserted into the block.
  • A block can be empty of rows, but if it once contained rows, then data will be allocated in the Row Directory (2 bytes per row) for each row that ever existed in the block.
• Transaction Slots are space that is used when transactions are in progress that will modify rows in the block. 
• The block header grows from top down.
• Data space (Row Data) – stores row data that is inserted from the bottom up.

 

Free space in the middle of a block can be allocated to either the header or data space, and is contiguous when the block is first allocated.

• Free space is allocated to allow variable character and numeric data to expand and contract as data values in existing rows are modified. 
• New rows are also inserted into free space. 
• Free space may fragment as rows in the block are modified or deleted. 
• SMON coalesces this free space periodically.

 Manual Data Block Free Space Management — Database Block Space Utilization Parameters

 Manual data block management requires a DBA to specify how block space is used and when a block is available for new row insertions.  This is the default method for data block management for dictionary managed tablespace objects (another reason for using locally managed tablespaces with UNIFORM extents).  Database block space utilization parameters are used to control space allocation for data and index segments. 

 The INITTRANS parameter specifies the initial number of transaction slots created when a database block is initially allocated to either a data or index segment.  These slots store information about the transactions that are making changes to the block at a given point in time.  The amount of space allocated for a transaction slot is 23 bytes. If you set INITRANS to 2, then there are 46 bytes (2 * 23) pre-allocated in the header, etc.  These slots are in the database block header.

 

The INITTRANS parameter specifies a minimum level of concurrent access.  The default is 1 for a data segment and 2 for an index segment.  If a DBA specifies INITTRANS at 4, for example, this means that 4 transactions can be concurrently making modifications to the database block.  Also, setting this to a figure that is larger than the default can eliminate the processing overhead that occurs whenever additional transaction slots have to be allocated to a block’s header when the number of concurrent transactions exceeds the INITTRANS parameter.

 

The MAXTRANS parameter specifies the maximum number of concurrent transactions that can modify rows in a database block.  Surprisingly, the default maximum is 255.  This value is quite large.  This parameter is set to guarantee that there is sufficient space in the block to store data or index data.

 

The PCTFREE and PCTUSED Parameters

 

You, as the DBA, must decide how much Free Space is needed for data blocks in manual management of data blocks. 

 

You set the free space with the PCTFREE and PCTUSED parameters at the time that you create an object like a Table or Index. 

 

PCTFREE:  The PCTFREE parameter is used at the time an object is created to set the percentage of usable block space to be reserved during row insertion for possible later updates to rows in the block. 

PCTUSED:  The parameter PCTUSED is used to set the level at which a block can again be considered by Oracle for insertion of new rows.  It is like a low water mark whereas PCTFREE is a high water mark.

 

Both PCTFREE and PCTUSED are calculated as percentages of the available data space – Oracle deducts the space allocated to the block header from the total block size when computing these parameters.

 

Free lists:  When a segment is created, it is created with a Free List that is used to track the blocks allocated to the segment that are available for row insertions. 

 

Automatic Segment Space Management

 

Free space can be managed either automatically or manually.  Automatic segment space management was introduced with Oracle 9i.  It simplifies the management of the PCTUSED, FREELISTS, and FREELIST GROUPS parameters and generally provides better space utilization where objects may vary considerably in terms of row size.  This can also yield improved concurrent access handling for row insertions.  A restriction is that you cannot use this approach if a tablespace will contain LOBs.

 

Using the Data Dictionary to Manage Storage

 

Periodically you will need to obtain information from the data dictionary about storage parameter settings.  The following views are useful.

• DBA_EXTENTS – information on space allocation for segments.
• DBA_SEGMENTS – stores information on segments.
• DBA_TABLESPACES – a row is added when a tablespace is created. 
• DBA_DATA_FILES – a row is added for each datafile in the database.
• DBA_FREE_SPACE – shows the space in each datafile that is free.

 

  

REFERENCES

http://www.akadia.com/services/sqlsrv_data_structure.html

 

 

 

Datatypes usedin Oracle9i and SQL Server2005

Datatypes Used in Oracle 9i

 

 

Character Strings

• CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).

• NCHAR (size) – A fixed-sized field of characters, where the character set is determined by its definition. So, the maximum size is 2000 bytes per row or 2000 characters. This handles multibyte character sets.

• VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters.

• NVARCHAR2 (size) – A variable-sized field of characters, where the character set is determined by its definition. The maximum size is 4000 bytes per row or 4000 characters. This handles multibyte character sets.

Note: The VARCHAR2 datatype is the successor of VARCHAR. So it is recommended that you use VARCHAR2 as a variable-sized array of characters.

• LONG – A variable-sized field of characters. The maximum size of this field is 2GB.

Number

• NUMBER (precision, scale) – A variable-sized number, where the precision is between 1 and 38 and size is between -84 and 127. A NUMBER datatype with only one parameter is NUMBER (precision), where the parameter specifies the precision of the number. A NUMBER datatype with no parameters is set to its maximum size.

Date and Time

• DATE – A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT.

• TIMESTAMP (precision) – A variable-sized value ranging from 7 to 11 bytes, that is used to represent a date/time value. It includes both date and time. The precision parameter determines how many numbers are in the fractional part of SECOND field. The precision of the SECOND field within the TIMESTAMP value may have a value ranging from 0 to 9 with a default precision of 6.

• TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’.

• TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.

Intervals

• INTERVAL DAY (day_precision) TO SECOND (second_precision) – A fixed-sized 11 byte value that represents a period of time. It includes days, hours, minutes and seconds.

• INTERVAL YEAR (year_precision) TO MONTH – A fixed-sized 5 byte value that represents a period of time. It includes years and months.

Binaries

• RAW (size) – A variable-sized field of raw binary data. The maximum size for this datatype is 2000 bytes.

• LONG RAW – A variable-sized field of raw binary data. The maximum size for this datatype is 2 GB.

• BLOB – The Binary Large Object is a field that holds unstructured binary data. The maximum size for this datatype is 4 GB.

• CLOB – The Character Large Object is a field that holds single byte character data. The maximum size for this datatype is 4 GB.

• NCLOB – The National Character Large Object is a field that holds either single byte of multibyte character data dependent on the national character set. The maximum size for this datatype is 4 GB.

• BFILE – An external binary file. The maximum size for this file is 4 GB. The size is also limited by the operating system.

Rows

• ROWID – A datatype that contains binary data that is used to identify a row.

 

 

Each ROWID is:

•  
  • 6 bytes for normal indexes on non-partitioned tables, local indexes on partitioned tables and row pointers for chained/migrated rows.
  • 10 bytes for global indexes on partitioned tables.

• UROWID – The Universal ROWID is the datatype used to store both logical and physical ROWID values as well as foreign tables accessed through a gateway.

Alternatives for ANSI Standard Datatypes

Instead of using ANSI standard datatypes, you can use Oracle defined datatypes. View the table below to see the Oracle datatype alternative for ANSI standard datatypes.

ANSI Standard	Oracle Datatype
CHARACTER and CHAR	CHAR
CHARACTER VARYING and CHAR VARYING	VARCHAR2
NUMERIC, DECIMAL, DEC, INTEGER, INT and SMALLINT	NUMBER
FLOAT, REAL, DOUBLE PRECISION	FLOAT

Datatypes Used in SQL Server2005

Exact numerics

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

 

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079

 

Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

 

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

 

Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

 

Other Data Types

• sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
• timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
• uniqueidentifier: Stores a globally unique identifier (GUID).
• xml: Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
• cursor: A reference to a cursor.
• table: Stores a result set for later processing.

 

References:-

1.       http://www.devarticles.com/c/a/Oracle/Datatypes-Used-in-Oracle-9i/1/

2.      

3.       http://pstcc11.pstcc.edu/~rbarber/hpc2800/handouts/datatypes.htm

4.       http://www.teratrax.com/sql_guide/data_types/sql_server_data_types.html    (sql server datatypes)

5.       http://msdn.microsoft.com/en-us/library/aa258271.aspx  (sql server)

 

 

 

 

 

OVERVIEW OF PRIMARY COMPONENTS OF ORACLE 9i

The Oracle architecture includes a number of primary components, which are discussed below:

• Oracle server: There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle database.

• Oracle instance: An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

More information on SGA and background processes:-

1. The System Global Area (SGA) is made up of the database buffer cache, the shared pool, and the log buffer cache.

2. The database buffer cache is where data is stored in memory to improve access time for the data.

3. The shared pool holds the library cache, the data dictionary cache, and control structures.

4. The log buffer cache stores the redo log entries prior to them being written to disk.

5. An instance consists of the SGA and a set of background processes.

6. There are five mandatory background processes:

➤ DBWn processes (also known as Database Writer) writes out the dirty, or changed, buffers from the database buffer cache to disk.

➤ LGWR (Log Writer) writes redo log entries from log buffers to the redo log files.

➤ PMON (Process Monitor) cleans up failed processes by rolling back uncompleted transactions and releasing any locks and resources held by failed processes.

➤ SMON (System Monitor) cleans up the temporary segments, performs instance

recovery, and coalesces contiguous chunks of free space in dictionary managed data files.

➤ CKPT (Checkpoint) signals Database Writer at checkpoint time and updates all the data files and control files with the current checkpoint information.

7. Other optional processes include ARCHn, RECO, Dnnn, Snnn, SNPn, and QMNn.

8. The most important optional process to be aware of for the exam is ARCn, or Archiver. Archiver copies redo log files to an archive destination when the database is running in ARCHIVELOG mode.

9. To start up an Oracle instance, you can use the following STARTUP commands: STARTUP NOMOUNT, STARTUP MOUNT, STARTUP OPEN, or STARTUP RESTRICT.

10. To shut down an Oracle instance, you can use the following SHUTDOWN commands: SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, SHUTDOWN TRANSACTIONAL, or SHUTDOWN ABORT.

• Oracle database: An Oracle database consists of operating system files, also known as database files that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance. An Oracle Database is the collection of three file types.

Ø  Data files: - Data files make up the physical side of tablespaces. Each tablespace has one or more data file. Data files can belong to exactly one tablespace.

Ø  Control files: - Control files track database structures and ensure that data files are synchronized via the log sequence number.

Ø  Redo log files: - Redo log files contain changes made to the data to enable recovery of data in case of failure.

 

 

• Other key files structures: The oracle server also uses other files that are not part of the database:-

Ø  Parameter file:-The Parameter files contain static initialization parameters to tell Oracle how the database and memory structures should be configured.

Ø  Password file:- The Password file authenticate users privileged to start up and shut down an Oracle instance.

Ø  Archived redo log files: – Archived redo log files are offline copies of the online redo log files that may be necessary to recover from media failures.  

 

• User and server processes: The user and server processes are the primary processes involved when a SQL statement is executed; however, other processes may help the server complete the processing of the SQL statement.

• Other processes: Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on.

References:-1.http://www.informit.com/content/images/0789732653/downloads/0789732653_CramSheet.pdf

2.http://www.scribd.com/doc/257810/Oracle-9i-DBA-Fundamentals-I-Vol-1-Student-Guide

 

 

Release History of Sql Server and Oracle.

 

_{<span style=”text-decoration:}

underline;”>

Database creation in SQL Server and Oracle

   Database Creation in SQL Server

Probably before using a database,we must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, in the same way, there are also different ways to create a database.

To create a new database in SQL Server Management Studio

1.      Microsoft SQL Server 2008 -> SQL Server Management Studio

2.      On the Connect to Server dialog box, make the correct selections and provide the appropriate information, then click Connect

3.      In the Object Explorer, expand the server name followed by the Databases node

4.         Right-click Databases and click New Database…
 

5. The Name text box, type MotorVehicleAdministration. Now this is our new database.

 The Location of a Database

By default, when we create a new database, Microsoft SQL Server assumed that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder. If we the New Database dialog box of the SQL Server Management Studio, if we specify the name of the database and click OK, the interpreter automatically creates a new file, and appends the .MDF extension to the file: this is the (main) primary data file of our database.

 

 The Primary Size of a Database

When originally creating a database, we may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. If you use the New Database dialog box, after specifying the name of the database and click OK, the interpreter automatically specifies that the database would primarily use 2MB. This is enough for a starting database. Of course, we can either change this default later on or we can increase it when necessary.

If we want to specify a size different from the default, if we are using the New Database to create your database, in the Database Files section and under the Initial Size column, change the size as we wish.

 

Database Creation with Code

 

Creating a Database Using the SQL Query Analyzer

 

1.     To open the code editor, in the Object Explorer, right-click the name of the server and click New Query

2.     In the empty window, type:

CREATE DATABASE RealEstate1;
  GO

3.     To execute the statement, press F5

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName factor is the name that the new database will carry. Although SQL is not case-sensitive, we should make it a habit to be aware of the cases you use to name your objects. Every statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, we can omit the semi-colon.

 

CREATING A DATABASE WITH ORACLE:-

 

 

There are two ways that you can create a database in Oracle 9i.

 

1.    using oracle database configuration Assistant

2.    Manually using create database command

Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 9i’s latest features. Simply read the instruction and follow the prompts.

 The other way to create a database is to do it manually.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory as shown below:

(Create a new folder on any drive where you want to keep your database(c drive in this case) and within that folder create 4 folders named as bdump, cdump, udump, create.)

Create a parameter file keeping in mind the needs of the database to be created as shown below:-

db_name=sikander

instance_name=sikander

db_block_size=4096

db_cache_size=50m

shared_pool_size=500000

control_files=’c:\sikander\create\control.ctl’

background_dump_dest=’c:\sikander\bdump’

user_dump_dest=’c:\sikander\udump’

core_dump_dest=’c:\sikander\cdump’

undo_management=auto

undo_tablespace=undotbs1

compatible=9.2.0.1.0

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename.

SID is created using command

Oradim  -new –sid sikander –intpwd sikander

 

 

Now we are about to create the database. When creating a new database, write the code into an sql file so that you have a copy of the database features as well as the ability to fix any mistakes in the create database command

create database sikander

logfile

group1(‘c:\sikander\create\redo1.log’)size  20m,

group2(‘c:\sikander\create\redo2.log)size  20m

maxlogfiles  5

maxlogmembers  1

maxloghistory  1

maxdatafiles  100

maxinstances   1

datafile’c:\sikander\create\system.dbf’size  400m

undo tablespace undotbs1

datafile’c:\sikander\create\undo1.dbf’size  50m

default temporary tablespace temp

tempfile’c:\sikander\create\temp1.dbf’size  100m

 

Now set the oracle sid using command:

>set oracle_sid=sikander

and then sqlplusw

Now startup pfile using:

>startup pfile=’c:\sikander\sikander.ora’ nomount

Now create spfile from pfile using

>create spfile from pfile =’c:\sikander\sikander.ora’

 

 REFERENCES:-

http://www.functionx.com/sqlserver/Lesson02.

 

System Requirements for Oracle9i and SQL Server 2008

System Requirements for SQL Server 2008 Standard(32-bit)

System Requirements for Oracle 9i Enterprise edition and for some other versions

 

 

 

 

For additional information, visit Oracle MetaLink at:
https://metalink.oracle.com

The data belongs to these sites but with some changes.
http://technet.microsoft.com/en-us/library/ms143506.aspx
http://download.oracle.com/docs/cd/B19306_01/install.102/b14316/reqs.htm