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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s