Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: error

Re: error

From: DA Morgan <>
Date: Tue, 14 Mar 2006 08:51:57 -0800
Message-ID: <>

Shredder wrote:

> "Sybrand Bakker" <> wrote in message 

>> On Mon, 13 Mar 2006 19:30:32 GMT, "Brian Houghtby"
>> <> wrote:
>>> "Andrew Hardy" <junkmail@[]> wrote in message
>>> news:dv4esd$eqn$
>>>> Brian Houghtby wrote:
>>>>> I am trying to run a procedure that was written for us by our MRP
>>>>> vendor
>>>>> who is no longer in business. I am getting the following error. This
>>>>> is
>>>>> an Oracle8i database running on Netware 5.1.
>>>>> begin
>>>>> ops_archive_all;
>>>>> end;
>>>>> ORA-01653: unable to extend table SYSADM.IC_TRANSACT_ARCHIVE by 177512
>>>>> in
>>>>> tablespace SYSTEM
>>>>> ORA-27039: create file failed, file size limit reached
>>>>> OSD-02067: illegal option specified
>>>>> ORA-27039: create file failed, file size limit reached
>>>>> OSD-02067: illegal option specified
>>>>> ORA-06512: at "SYSADM.OPS_ARCH_AND_PURGE", line 29
>>>>> ORA-06512: at "SYSADM.OPS_ARCHIVE_ALL", line 5
>>>>> ORA-06512: at line 2
>>>> You've run out of space for your table to expand into. It also looks
>>>> like
>>>> the tablespace is trying to autoexpand to fit the new requirements, but
>>>> has hit a limit - either you set a maximum limit for the tablespace or
>>>> there is no room on the device.
>>>> Also... you really shouldn't have installed objects in the SYSTEM
>>>> tablespace as there's always the danger that your application problems
>>>> may
>>>> one day cause the database to get very sick.
>>>> --
>>>> Andy
>>> I am kind of a newbie at all this. Any recommendations on a fix?
>> the best option would to move all the tables of the user SYSADM to a
>> new tablespace, you need to create.
>> The alternative is to add another datafile to the tablespace SYSTEM,
>> but this would probably be a real bad idea, depending on the size of
>> the current datafile. A customary maximum for a datafile is 2G. If you
>> have a SYSTEM tablespace of 2G, you don't WANT to add another
>> datafile, you want to get out of hell, before you get burned in the
>> accident.
>> select sum(bytes)/(1024*1024) Mb
>> from dba_data_files
>> where tablespace_name = 'SYSTEM'
>> would tell you the current size of the tablespace.
>> If you decide to create a new tablespace (greatly recommended) you can
>> move your tables by
>> alter table <table_name> move tablespace <new tablespace>
>> and you can move your indexes by
>> alter index <index name> rebuild tablespace <new tablespace>
>> You can also relocate everything by export /import
>> - export the user
>> - drop the user
>> - recreate the user with the new tablespace as default tablespace
>> - make sure the user doesn't have unlimited tablespace privilege
>> - alter user sysadm quota 0 on system quota unlimited on <new
>> tablespace>
>> - imp your export.
>> Hth
>> --
>> Sybrand Bakker, Senior Oracle DBA
> OK...this is getting deep!  Sorry, as I mentioned I am a Newbie at this. 
> Are any of you for hire?  We are in central Ohio. 


He provides great off-site DBA support and from Hawaii. Which means his work day doesn't interfere with yours.

Daniel A. Morgan
(replace x with u to respond)
Received on Tue Mar 14 2006 - 10:51:57 CST

Original text of this message