Solved

to fix thiis

Posted on 2014-04-03
7
303 Views
Last Modified: 2014-04-08
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
Comment
Question by:tonydba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39976248
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 39976254
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39976257
Add
WHERE SPACE_LIMIT != 0 AND .....
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39976276
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
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 500 total points
ID: 39976817
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
 
LVL 32

Expert Comment

by:awking00
ID: 39978389
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
 

Author Closing Comment

by:tonydba
ID: 39986468
Thank youi.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question