i need script makes sum of space_limit v$recovery_file_dest different databases: have list of 5 dbs , need connect each one,save value (space_limit) in memory , in end give me sum of 5 values
is possible?
this got far, in bottom need show sum:
undefine user accept user char prompt 'user : ' undefine pswuser accept pswuser char prompt 'password : ' hide set trimout off set verify off set markup html on spool z:\....\...\fra_report_&data._&ora..html prompt ##################################################### prompt database 1 prompt ##################################################### connect &user/&pswuser@db1 select name, round(space_limit / 1024/ 1024), to_char(round(space_used / 1048576),'999g999g990','nls_numeric_characters=,.'), round(((space_used / 1048576) / (space_limit / 1048576)*100),2)||'%' v$recovery_file_dest / /.....and on 5 times each db...../
prompt ##################################################### prompt total fra prompt ##################################################### spool off set markup html off disc update: tried adding each db
begin :total := total + v$recovery_file_dest.space_limit; end; / but gives me error
error @ line 2: ora-06550: line 2, column 32: pls-00357: table,view or sequence reference 'v$recovery_file_dest.space_limit' not allowed in context ora-06550: line 2, column 2: pl/sql: statement ignored
you aren't far off attempt keep running total, need combine bind variable , table value in sql context:
begin select nvl(:total,0) + sum(space_limit) :total v$recovery_file_dest; end; / you can either print total, or keep html formatting query dual. script might end looking like:
variable total number; undefine user accept user char prompt 'user : ' undefine pswuser accept pswuser char prompt 'password : ' hide set trimout off set verify off set markup html on set numformat 999999999999 spool z:\....\...\fra_report_&data._&ora..html prompt ##################################################### prompt database 1 prompt ##################################################### connect &user/&pswuser@db1 set feedback off select name, round(space_limit / 1024/ 1024), to_char(round(space_used / 1048576),'999g999g990','nls_numeric_characters=,.'), round(((space_used / 1048576) / (space_limit / 1048576)*100),2)||'%' v$recovery_file_dest / exec select nvl(:total,0) + sum(space_limit) :total v$recovery_file_dest; -- repeat other databases prompt ##################################################### prompt total fra prompt ##################################################### select :total total_fra, :total/(1024*1024) total_fra_mb dual; spool off set markup html off disc i've added variable declaration, , set number format doesn't go scientific notation large values. can, of course, manipulate :total value show in mb or gb or whatever prefer - i've shown raw value , mb value match individual database values.
i've added set feedback off, has repeated after each connect - settings reset whenever reconnect.
that code produces output this, when run 2 databases listed:
##################################################### <br> database 1 <br> ##################################################### <br> <p> <table border='1' width='90%' align='center' summary='script output'> <tr> <th scope="col"> name </th> <th scope="col"> round(space_limit/1024/1024) </th> <th scope="col"> to_char(roun </th> <th scope="col"> round(((space_used/1048576)/(space_limit/ </th> </tr> <tr> <td> +fra </td> <td align="right"> 30720 </td> <td> 24.570 </td> <td> 79.98% </td> </tr> </table> <p> ##################################################### <br> database 2 <br> ##################################################### <br> <p> <table border='1' width='90%' align='center' summary='script output'> <tr> <th scope="col"> name </th> <th scope="col"> round(space_limit/1024/1024) </th> <th scope="col"> to_char(roun </th> <th scope="col"> round(((space_used/1048576)/(space_limit/ </th> </tr> <tr> <td> +fra </td> <td align="right"> 24576 </td> <td> 12.698 </td> <td> 51.67% </td> </tr> </table> <p> ##################################################### <br> total fra <br> ##################################################### <br> <p> <table border='1' width='90%' align='center' summary='script output'> <tr> <th scope="col"> total_fra </th> <th scope="col"> total_fra_mb </th> </tr> <tr> <td align="right"> 57982058496 </td> <td align="right"> 55296 </td> </tr> </table> <p>
Comments
Post a Comment