sql server 2000 and Oracle 10.1 - it's not the size of the database that matters, it's how you use it

Home -> Community -> Mailing Lists -> Oracle-L -> sql server 2000 and Oracle 10.1 - it's not the size of the database that matters, it's how you use it

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 10 Jun 2004 16:23:51 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FC27@irvmbxw02>

Whilst writing up a document contrasting SQL Server object storage versus Oracle object storage, I was suprised to find out that

according to documentation, the maximum size of a SQL Server database (1 EB) is orders of magnitude greater than the maximum size of an Oracle database (8 PB for a 32 KB blocksize database if I read the documentation correctly); also a SQL serve datafile can be up to 32 TB and an Oracle datafile is limited to 128 GB (in a 32 KB blocksize database). And since there can be up to 32,767 databases per SQL Server instance, a SQL Server instance can have 32,767 EB = 32 ZB of data?
The maximum size of an Oracle database hasn't changed from version 9.2

Does anyone know any different?

(N.B. SQL Server 2000 terminology:
instance = full installation: the program binaries, the processes (Windows services) and registry entries, and the physical data files and log files for the databases. The set of physical datafiles and log files for all databases of an instance would roughly correspond to an Oracle database.
one instance = several databases.
one database = several filegroups and transaction logs. one filegroup = several data files.)

Here are some documentation extracts.

>From SQL Server 2000 Online books:

  Object                            Maximum sizes/numbers
Database size                        1,048,516 TB (= 1 EB)
Databases per instance of SQL Server    32,767
Filegroups per database                    256
Files per database                      32,767
File size (data)                            32 TB
File size (log)                             32 TB
Instances per computer                      16
Objects in a database            2,147,483,647

Oracle 10.1 documentation

----++++++++++++++++++++++++++ UNIX ++++++++++++++++++++++++++----

http://download-west.oracle.com/docs/html/B10812_01/appendix_f.htm#sthre f837
<http://download-west.oracle.com/docs/html/B10812_01/appendix_f.htm#sthr ef837>
Oracle(r) Database Administrator's Reference 10g Release 1 (10.1) for UNIX Systems: AIX-Based Systems, hp HP-UX PA-RISC (64-bit), hp Tru64 UNIX, Linux x86, and Solaris Operating System (SPARC)
Part No. B10812-01
Appendix F Database Limits

Table F-1 CREATE CONTROLFILE and CREATE DATABASE Parameters

Parameter       Default    Maximum Value 
MAXLOGFILES       16             255 
MAXLOGMEMBERS      2               5 
MAXLOGHISTORY    100           65534 
MAXDATAFILES      30           65534 
MAXINSTANCES       1              63

...

Table F-2 File Size Limits

Data Files

Operating System         Maximum Size 
     Any                4,194,303 multiplied by the value
                        of the DB_BLOCK_SIZE parameter 

============================================

Import/Export files and SQL*Loader files

Operating System               Maximum Size 
Tru64 UNIX                       16 TB

AIX, HP-UX, Linux, Solaris: 2,147,483,647 bytes 32-bit with 32-bit files

AIX, HP-UX, Linux, Solaris: Unlimited 32-bit with 64-bit files

AIX, HP-UX, Linux, Solaris: Unlimited 64-bit

Control files
Operating System Maximum Size Solaris, HP-UX, Linux 20000 database blocks

AIX                     10000 database blocks 
Tru64 UNIX              19200 database blocks 



----++++++++++++++++++++++++++ Microsoft Windows
++++++++++++++++++++++++++----

http://download-west.oracle.com/docs/cd/B13789_01/win.101/b10113/specs.h tm#i1005859
<http://download-west.oracle.com/docs/cd/B13789_01/win.101/b10113/specs. htm#i1005859>
Oracle(r) Database Platform Guide
10g Release 1 (10.1) for Windows
Part Number B10113-01
Chapter 13 Oracle Database Specifications for Windows

Table 13-3 Block Size Guidelines

Type                                   Size
Maximum block size            16,384 bytes or 16 kilobytes (KB)
Minimum block size                 2 kilobytes (KB)
Maximum blocks for each file   4,194,304 blocks
Maximum possible file size        64 Gigabytes (GB)

with 16 K sized blocks

Table 13-4 Maximum Number of Files for Each Database Block Size Number of Files

 2 KB        20,000
 4 KB        40,000
 8 KB        65,536
16 KB        65,536


Table 13-5 Maximum File Sizes
Type                                    Size
Maximum file size for a FAT file         4 GB
Maximum file size in NTFS               16 Exabytes (EB)
Maximum database size             65,536 * 64 GB equals approximately 4
Petabytes (PB)
Maximum control file size           20,000 blocks 


----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jun 10 2004 - 18:20:54 CDT