| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: error
On Mon, 13 Mar 2006 19:30:32 GMT, "Brian Houghtby"
<b.houghtby_at_eaglecrusher.com> wrote:
>
>"Andrew Hardy" <junkmail@[127.0.0.1]> wrote in message
>news:dv4esd$eqn$1_at_sun-cc204.lut.ac.uk...
>> 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 <newtablespace>
Hth
-- Sybrand Bakker, Senior Oracle DBAReceived on Mon Mar 13 2006 - 16:17:13 CST
 |
 |