ORA-01654 Fix?

From: Trail Shredder <trail_at_shred.com>
Date: Wed, 21 Dec 2005 20:02:20 GMT
Message-ID: <gxiqf.54680$lh.8156@tornado.ohiordc.rr.com>

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?

TABLESPACE_NAME BYTES AUTOEXTENSIBLE
============================== =========== ==============
SYSTEM 4292128768 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\SYS1LIVE.ORA
SYSTEM 4242307072 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\SYS2LIVE.ORA
SYSTEM 2097152000 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\SYS3LIVE.ORA
USER_DATA 1048576000 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\USR1LIVE.ORA
ROLLBACK_DATA 3116474368 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\RBS1LIVE.ORA
TEMPORARY_DATA 1048576000 YES
FILE_NAME

---

SYS1:ORAHOME\DATALIVE\TMP1LIVE.ORA 6 ROWS SELECTED
 IN 0.01 SECONDS 600.00 ROWS PER SECOND / 0.00 SECONDS PER ROW Received on Wed Dec 21 2005 - 14:02:20 CST