Re: Divisor is equal to zero

muddu a écrit :

> Hello,
>
> I am running a sql script to determine how much % the tablespaces are
> occupying.
> But I am getting this error:
>
> ERROR at line 3:
> ORA-01476: divisor is equal to zero
>
> line 3: round(100 * (us.usedspace / df.totalspace)) per.

Although I do agree with Sybrand, I provide you with a query I use personnally :

select
U.tablespace_name tbs,nvl(U.used,0) used ,nvl(F.free,0) free, nvl(U.used,0)+nvl(F.free,0) total,
round(100*(nvl(U.used,0)/(nvl(U.used,0)+nvl(F.free,0))),1) pct_used, round(100*(nvl(F.free,0)/(nvl(U.used,0)+nvl(F.free,0))),1) pct_free from
(select v1.tablespace_name,round(sum(v2.bytes/1024/1024),3) used  from dba_tablespaces v1,

      dba_segments v2
 where v1.tablespace_name=v2.tablespace_name(+)    and v1.contents <> 'TEMPORARY' and v1.status<>'OFFLINE'  group by v1.tablespace_name) U,
 (select v1.tablespace_name, round(sum(v3.bytes/1024/1024),3) free   from dba_tablespaces v1,

       DBA_FREE_SPACE v3
  where v1.tablespace_name=v3.tablespace_name(+)   and v1.contents <> 'TEMPORARY' and v1.status<>'OFFLINE'     group by v1.tablespace_name) F
where F.tablespace_name=U.tablespace_name;

The criteria <> 'TEMPORARY' and <>'OFFLINE' prevent you from getting 0 values as divisor.

HTH

-- 
SL