Re: Problem with tablespace file size limit
From: sybrandb <sybrandb_at_gmail.com>
Date: Thu, 20 Aug 2009 02:56:03 -0700 (PDT)
Message-ID: <ef4738d9-641e-4cb6-816f-3d217dae7f00_at_d34g2000vbm.googlegroups.com>
On 20 aug, 11:23, jstuglik <jakub.stug..._at_gmail.com> wrote:
> Hi.
> I have a problem with tablespace file size limit. I use Oracle 11g
> Standard Edition One on Fedora 8 Linux (x86_64).
> The linux file system is ext3 with 4096 bytes blocks (which gives 2TiB
> max file size). User oracle (on which the RDBMS runs) has no limits
> set for a max file size (unlimited). The tablespace has also unlimited
> extends set and the block size is set to 8192 bytes. Nevertheless I
> encountered a problem with extending tablespace file size past 32GB.
> The exact error was:
>
> ORA-01654: unable to extend index XXX.SYS_C0011327 by 8192 in
> tablespace XXX
>
> The index is a primary key index on one of the tables. The tablespace
> datafile size is now 34359730176 bytes, hence extending it by another
> 8192 bytes would give exactly 32GB of data.
> Just to be sure I also checked if there is a possibility of creating
> file sized more than 32GB on user oracle and it succeeded (I used cat /
> dev/zero > aa).
> The query from user_tablespaces shows that tablespace max size is
> 2147483645 with 8192 block size so it is way more than 32GB. It is
> also standard SFT.
> Oh, before you ask, there is plenty of free space on disk.
>
> Do you have any ideas?
> Please help me ASAP because now the system is running only because I
> dropped one of the less important indices but it will fill up again in
> short time.
Sybrand Bakker
Senior Oracle DBA Received on Thu Aug 20 2009 - 04:56:03 CDT
Date: Thu, 20 Aug 2009 02:56:03 -0700 (PDT)
Message-ID: <ef4738d9-641e-4cb6-816f-3d217dae7f00_at_d34g2000vbm.googlegroups.com>
On 20 aug, 11:23, jstuglik <jakub.stug..._at_gmail.com> wrote:
> Hi.
> I have a problem with tablespace file size limit. I use Oracle 11g
> Standard Edition One on Fedora 8 Linux (x86_64).
> The linux file system is ext3 with 4096 bytes blocks (which gives 2TiB
> max file size). User oracle (on which the RDBMS runs) has no limits
> set for a max file size (unlimited). The tablespace has also unlimited
> extends set and the block size is set to 8192 bytes. Nevertheless I
> encountered a problem with extending tablespace file size past 32GB.
> The exact error was:
>
> ORA-01654: unable to extend index XXX.SYS_C0011327 by 8192 in
> tablespace XXX
>
> The index is a primary key index on one of the tables. The tablespace
> datafile size is now 34359730176 bytes, hence extending it by another
> 8192 bytes would give exactly 32GB of data.
> Just to be sure I also checked if there is a possibility of creating
> file sized more than 32GB on user oracle and it succeeded (I used cat /
> dev/zero > aa).
> The query from user_tablespaces shows that tablespace max size is
> 2147483645 with 8192 block size so it is way more than 32GB. It is
> also standard SFT.
> Oh, before you ask, there is plenty of free space on disk.
>
> Do you have any ideas?
> Please help me ASAP because now the system is running only because I
> dropped one of the less important indices but it will fill up again in
> short time.
The maximum size of a smallfile tablespace datafile at 8k blocksize is
32G, as per the Oracle Reference Manual.
When sizing datafiles one of the aspects is recoverability. Personally
I wouldn't extend it beyond 32G, just because a restore would likely
take too long.
Of course you can add another datafile
(alter tablespace add datafile '....' would do that trick)
so your question is a bit strange, as well as labeling it ASAP, as
this is a forum of volunteers, so no one is obliged to answer.
Sybrand Bakker
Senior Oracle DBA Received on Thu Aug 20 2009 - 04:56:03 CDT