Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: error

From: Shredder <>
Date: Tue, 14 Mar 2006 14:24:59 GMT
Message-ID: <%mARf.13680$>

"Sybrand Bakker" <> wrote in message
> On Mon, 13 Mar 2006 19:30:32 GMT, "Brian Houghtby"
> <> wrote:
>>"Andrew Hardy" <junkmail@[]> wrote in message
>>> 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. Received on Tue Mar 14 2006 - 08:24:59 CST

Original text of this message