Re: ORA-01654 Fix?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 21 Dec 2005 21:45:35 +0100
Message-ID: <28fjq15sogecjj2rgkrojllocnvn6jlv32@4ax.com>

On Wed, 21 Dec 2005 20:02:20 GMT, "Trail Shredder" <trail_at_shred.com> wrote:

>I am being told to create another tablespace for an error that I have been
>getting (ORA-01654: unable to extend index SYSADM.OPS_TRANS_IDX1 by 138254
>in tablespace SYSTEM ORA-27039: create file failed, file size limit reached
>OSD-02067: illegal option specified ORA-27039: create fil).
>
>I am trying to understand this.....
>
>Based on the info below, do I have a tablespace that is autoextending to 3
>datafiles?
>
>would my SQL look like this? alter tablespace system add datafile
>'sys1:\orahome\datalive\sys4live.ora size 2000000000 autoextend on;
>
>Why is it that the 3rd datafile is only 2G and the 1st and 2nd are 4G?
>Would if be more beneficial to increase the size of the 3rd datafile?

You have been told to *add another datafile* to a tablespace. Which is quite something different.
However, based on the data you provide, and your previous posts, you would better
- create an extra tablespace for all data that doesn't belong to SYS or SYSTEM
- move that data to the new tablespace with alter table <table_name> move <new_tablespace>

- export the database
- delete the current database
- create a new database with a properly size SYSTEM tablespace
- import the database
- and above all

GET OUT OF THIS MESS (because that what it is) ASAP.

You are on your way to end in a shredder yourself.

Based on the info below
you have
1 tablespace SYSTEM consisting of 3 datafiles, those datafiles are all on autoextend. Now, that is a true mess. 2 Your sql wouldn't look like that because it would make the mess only bigger.
It also wouldn't look like that because you don't specify a MAXSIZE for that datafile and the default maxsize is 32 G.

3 Obviously the files have different maxsizes, which can be verified by adding MAXBYTES and MAXBLOCKS to your select. It would be more benificial to get out of this mess asap, but if your O/S doesn't have a 2 G datafile limit, you could make the 3rd datafile grow, provided you like to live in messes, that is.

--
Sybrand Bakker, Senior Oracle DBA

Received on Wed Dec 21 2005 - 14:45:35 CST