Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Error ora-01653

Re: Error ora-01653

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Feb 1999 15:58:16 GMT
Message-ID: <36c35a26.10497574@192.86.155.100>


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
compute sum of kbytes on report
compute sum of free on report
compute sum of used 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

where a.tablespace_name (+) = b.tablespace_name order by &1
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US