Prev: FRM-40508: Oracle error: unable to insert record
Next: Instant Client ODBC
From: Trail Shredder on 21 Dec 2005 15:02
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


From: bdbafh on 21 Dec 2005 15:33
The reason that "you are being told to create another tablespace" is
that user segments should not be stored in the system tablespace.

Your real problems far exceed the error that you post.

Lets just assume for the duration of this thread that you're treating
the Oracle database as a toy black box and that you're a developer and
not familiar with administering an Oracle database. You haven't read
the Concepts Manual for the release that you're working with and
certainly have not read the Administration Guide or "2 Day DBA"
guides.

Someone (possibly you) created an application schema named "SYSADM" and
failed to assign its default tablespace to something other than
"SYSTEM. THAT is the root cause of this particular issue (the real root
cause being a lack of training, expertise, knowledge regarding
administering Oracle databases).

Judging by the database name (poor use of OFA exhibited in the datafile
names) this is not a single user database residing on your laptop and
the data inside this database may have some importance. Before you do
anything - including reading any further in this post - back that
sucker up. Seriously.

Your real solution starts with creating a tablespace and assign that to
be the default tablespace for the app schemas that reside in this
database. that will prevent new segments from being created in the
system tablespace. It will not move any existing segments out of that
tablespace, nor will it prevent those existing segments from growning
(adding extents).

The second part of this solution includes altering tables to have their
segments stored in the new app-specific tablespace, followed by
rebuilding their indexes into either the same tablespace - or possibly
even into an additional tablespace, if you choose to do so.

Since you have not posted the version and edition of the oracle
database server software that you have installed - I cannot futher
recommend any specific actions regarding what type of tablespace you
should create (dictionary or locally managed, uniform extent or auto
allocate extent management) nor if you should attempt online
reorganization of such segments (depends upon edition of Oracle).

you really do need to do some serious RTFMing or sub this out to
someone who has.

It is possible on certain version of oracle on certain operations for
autoextending datafiles to hit certain sizes whereby the tablespace is
corrupted. If that tablespace is the system tablespace, that database
would be in very very very very serious trouble.

good luck.

-bdbafh

From: Sybrand Bakker on 21 Dec 2005 15:45
On Wed, 21 Dec 2005 20:02:20 GMT, "Trail Shredder" <trail(a)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
From: Mark D Powell on 21 Dec 2005 16:32
Sybrand is correct in that you will probably have to re-create the
database to resize the SYSTEM tablespace in order to reclaim the file
space.

I consider your problem just another example of why extendable
datafiles are not a good idea. I can monitor file space utilization in
a couple of minutes per database per week. Fixing a mess like this
often takes midnight and weekend maintenance windows. I work enough of
those anyway just to apply modifications for production enhancements.

IMHO -- Mark D Powell --

From: Trail Shredder on 21 Dec 2005 16:43
I need to explain. This is an MRP system that runs on the front end of the
database. It was developed about 7 years ago and the company closed its
doors about 4 years ago and we have been left with it. We are currently
shopping for a new MRP system and I am hoping to get this one limping along
for about 6-8 months till we get another system in place. I understand now
that it was poorly written and their DBA expertise was nill! It has been an
expensive learning curve! I will also be doing some DBA learning this time
around. Sorry for the lack of DBA intelligence.

"Trail Shredder" <trail(a)shred.com> wrote in message
news:gxiqf.54680$lh.8156(a)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
>
>


 |  Next  |  Last
Pages: 1 2
Prev: FRM-40508: Oracle error: unable to insert record
Next: Instant Client ODBC