Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Error ora-01653
A copy of this was sent to Richard WHEELDON <R.Wheeldon_at_cs.ucl.ac.uk>
(if that email address didn't require changing)
On Tue, 9 Feb 1999 14:01:29 GMT, you wrote:
>Hi. I keep getting this error from oracle, and am rapidly running out of
>ideas
>for fixing it. Does anyone know what may be causing it (lack of space is
>not)?
>
>Thanks in advance,
>
>Richard
>
>
>sqlplus output:
>
>SQL*Plus: Release 3.3.2.0.0 - Production on Tue Feb 9 13:58:47 1999
>
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>
>Connected to:
>Oracle7 Server Release 7.3.2.1.0 - Production Release
>With the parallel query and Spatial Data options
>PL/SQL Release 2.3.2.0.0 - Production
>
>SQL> insert into keyword(keyword,url_uid) values ('fred',-1);
>insert into keyword(keyword,url_uid) values ('fred',-1)
> *
>ERROR at line 1:
>ORA-01653: unable to extend table HPAROB.KEYWORD by 3596 in tablespace
>USERS
but it is lack of space. Perhaps the users tablespace is fragmented (so there
is more then enough space in the tablespace to satisfy the request BUT not
enough contigous space).
below is a script you can run in sqlplus -- it'll show you space by tablespace and importantly -- the largest contigous block of space in the tablespace. If thats smaller then what you are trying to allocate you can:
column dummy noprint
column pct_used format 999.9 heading "%|Used" column name format a16 heading "Tablespace Name"column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes, kbytes_alloc-nvl(kbytes_free,0) used, nvl(kbytes_free,0) free, ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used, nvl(largest,0) largest from ( select sum(bytes)/1024 Kbytes_free, max(bytes)/1024 largest, tablespace_name from sys.dba_free_space group by tablespace_name ) a, ( select sum(bytes)/1024 Kbytes_alloc, tablespace_name from sys.dba_data_files group by tablespace_name ) b
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Feb 09 1999 - 09:58:16 CST