ORA-01476: divisor is equal to zero
Cause:
This error is often observed while conversion of units of an integer value by division in a complex SQL query . If the output after dividing the number returns zero, then this error is thrown.
SQL> col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
SQL> SQL> col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
SQL> SQL> select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824; 2 3 4 5 6
ERROR:
ORA-01476: divisor is equal to zero
Workaround:
Example: If you convert bytes to GB, then instead convert bytes to MB. Likewise , if you convert seconds to day, then instead convert seconds to hours etc…
SQL> col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
SQL> SQL> col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1048576),2) as TOTAL_SPACE_GB,
round(x.bytes/1048576,2) - round(sum(y.bytes/1048576),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1048576),2)/round(x.bytes/1048576,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1048576;SQL> SQL> SP2-0734: unknown command beginning "SQL> SQL> ..." - rest of line ignored.
SQL> SQL> 2 3 4 5 6
TABLESPACE_NAME TOTAL_SPACE_GB TOTAL_FREE_SPACE_GB UTIL_PCT
-------------------- -------------- ------------------- --------
USERS 5 2.94 41
SYSTEM 800 797.12 0
UNDOTBS1 225 40.06 82
SYSAUX 700 661.44 6