• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

to fix thiis

SQL> SELECT
  2  NAME,
  3  TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
  4  TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
  5  '999,999,999,999') AS SPACE_AVAILABLE,
  6  ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
  7  AS PERCENT_FULL
  8  FROM V$RECOVERY_FILE_DEST;
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
                                      *
ERROR at line 6:
ORA-01476: divisor is equal to zero


What is the error here? how to fix..
0
tonydba
Asked:
tonydba
1 Solution
 
sdstuberCommented:
space_limit is 0 for at least one row.


how to fix is up to you.  What do you want to be displayed when the space limit is 0?
0
 
paquicubaCommented:
Put a decode around it (I put 1 but may be wrong)
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/decode(SPACE_LIMIT,0,1,SPACE_LIMIT) * 100, 1)

Or add a condition:
WHERE SPACE_LIMIT > 0
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Add
WHERE SPACE_LIMIT != 0 AND .....
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Another method:
Try this:-

SELECT
    NAME,
    TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
    TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
    '999,999,999,999') AS SPACE_AVAILABLE,
    DECODE(to_char(PACE_LIMIT),'0','NaN',to_char(ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)))
    AS PERCENT_FULL
    FROM V$RECOVERY_FILE_DEST;

I haven't ran above SQL, but should work fine.
Before calculating PERCENT_FULL, it will validate if SPACE_LIMIT=0, if it does, then NaN(Not a Number) will be displayed.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,'999,999,999,999') AS SPACE_AVAILABLE,
case SPACE_LIMIT
  when 0 then 'ZERO SPACE_LIMIT'
  else ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
end AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
0
 
awking00Commented:
To get the proper answer, you need to answer sdstuber's question as to what you want to see in the case where space_limit is zero. Your basic choices are 0, null, space_used minus space_reclaimable, or some message string (which I wouldn't recommend given the numeric nature of the fields).
0
 
tonydbaAuthor Commented:
Thank youi.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now