Re: Problem with tablespace file size limit
Date: Fri, 21 Aug 2009 07:07:20 -0700 (PDT)
On Aug 20, 5:23 am, jstuglik <jakub.stug..._at_gmail.com> wrote:
> 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.
Why not use a bigfile tablespace if you don't want to add another datafile?
It is a kind of peculiar idea to be thinking you want to not consider adding a second datafile to an existing tablespace.
My systems now are using ASM along with bigfile tablespaces in an 11g linux 64 bit environment and it is pretty smooth sailing. You may want to look at ASM eventually and get away from a cooked file system approach. Received on Fri Aug 21 2009 - 09:07:20 CDT