gmaster gmaster - 4 months ago 10
SQL Question

Script SQL that sums values of tables from different DBs

I need a script that makes the sum of the

space_limit
from the
v$recovery_file_dest
from different databases:
I have a list of 5 DBs and I need it to connect to each one,save that value (space_limit) in memory and in the end give me the sum of all 5 values

Is it possible?

This is what i got so far, in the bottom i need it to show the 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)||'%'
from v$recovery_file_dest
/


/.....And so on 5 times for each DB...../

Prompt #####################################################
Prompt TOTAL FRA
Prompt #####################################################

spool off
set markup html off
disc


UPDATE:
I tried adding this for each DB

begin
:total := total + v$recovery_file_dest.space_limit;
end;
/


But it gives me this error

ERROR at line 2:
ORA-06550: line 2, column 32:
PLS-00357: Table,View Or Sequence reference 'V$RECOVERY_FILE_DEST.SPACE_LIMIT' not allowed in this context
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Answer

You aren't that far off with your attempt to keep a running total, but you need to combine the bind variable and the table value in an SQL context:

begin
  select nvl(:total,0)  + sum(space_limit) into :total from v$recovery_file_dest;
end;
/

You can then either print the total, or to keep your HTML formatting query it from dual. So your script might end up 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)||'%'
from v$recovery_file_dest
/

exec select nvl(:total,0)  + sum(space_limit) into :total from v$recovery_file_dest;

-- repeat for other databases

Prompt #####################################################
Prompt TOTAL FRA
Prompt #####################################################

select :total as total_fra, :total/(1024*1024) as total_fra_mb from dual;

spool off
set markup html off
disc

I've also added the variable declaration, and set the number format so it doesn't go into scientific notation for large values. You can, of course, manipulate the :total value to show it in MB or GB or whatever you prefer - I've shown the raw value and the MB value to match the individual database values.

I've also added set feedback off, which has to be repeated after each connect - some settings are reset whenever you reconnect.

That code produces output like this, when run with two 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