Re: 2G limit on tablespaces

While most UNIX implementations have inherited a 2GB file size limit, you can still create ORACLE tablespaces of greater size by using any one of the following strategies:

1. Filesystem Files

Create your tablespace using multiple UNIX file system files. For example, to create a tablespace of approximately 10GB in size in a database which has a 4K database page size, you could hypothetically do the following:

CREATE TABLESPACE bigts DATAFILE

'/u001/.../data/bigts1.ora' size 2097148K,
'/u002/.../data/bigts2.ora' size 2097148K,
'/u003/.../data/bigts3.ora' size 2097148K,
'/u004/.../data/bigts4.ora' size 2097148K,
'/u005/.../data/bigts5.ora' size 2097148K
    DEFAULT STORAGE ...
    ONLINE; Note that 2097148K is 4K short of 2GB (2097152K). When creating a tablespace, ORACLE automatically allocates the size you specify PLUS the size of one additional database page for each file in the specification (at least in does under Solaris). This addition datafile space is not available for allocation to extents and appears to be used for datafile space management functions.

2. Raw Devices

Create your tablespace using one or more raw devices. Using the same example above with 2GB raw devices, you could hypothetically do the following:

CREATE TABLESPACE bigts DATAFILE

'/dev/rdsk/c10t0d0s7' size 2097148K,
'/dev/rdsk/c10t0d1s7' size 2097148K,
'/dev/rdsk/c10t0d2s7' size 2097148K,
'/dev/rdsk/c10t0d3s7' size 2097148K,
'/dev/rdsk/c10t0d4s7' size 2097148K
    DEFAULT STORAGE ...
    ONLINE; Note that the one page datafile size addon behavior also applies to raw devices. You can allocate raw devices in sizes greater than 2GB; however, be warned of the following situation we bumped into. On ORACLE Server versions 7.1.4 and before running on Solaris, a datafile whose total allocation is greater than 2GB will disable asynchronous IO (I'm not sure if it is only on the file or instance wide) and a maximum total allocation size of 4GB is supported. If you intend to create a tablespace with raw devices whose total allocation (don't forget the one page addon) is greater than 2GB, it would be highly advisable to check with ORACLE regarding your platform's addressing limits and the asynchronous IO issue.

We have deployed many tablespaces in the 2GB to 18GB size range using the raw device strategy without problems (other than those noted above).

Hope this helps,

Doug

--dc Received on Wed Jun 21 1995 - 00:00:00 CEST