How to integrate many SQL query results into one output (Oracle)

Question

I have this server with the following system:

Windows 2003 R2 Ent Edition SP 2 Oracle 1.0.2.0.4.0 Databases: 5

The databases have their archivelogs located in flash recovery area as shown by the NAME column of v$recovery_file_dest and also by invoking "show parameter db_recovery_file_dest" from sqlplus. I'd like to monitor the space usage of archivelog of each database using a SQL or PLSQL script.

If I invoke a query below:

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN name               FORMAT a30                  HEADING 'Name'
COLUMN space_limit        FORMAT 99,999,999,999,999   HEADING 'Space Limit'
COLUMN space_used         FORMAT 99,999,999,999,999   HEADING 'Space Used'
COLUMN space_used_pct     FORMAT 999.99               HEADING '% Used'
COLUMN space_reclaimable  FORMAT 99,999,999,999,999   HEADING 'Space Reclaimable'
COLUMN pct_reclaimable    FORMAT 999.99               HEADING '% Reclaimable'
COLUMN number_of_files    FORMAT 999,999              HEADING 'Number of Files'


prompt 
prompt Current location, disk quota, space in use, space reclaimable by deleting files,
prompt and number of files in the Flash Recovery Area.
prompt 

SELECT
    name
  , space_limit
  , space_used
  , ROUND((space_used / space_limit)*100, 2) space_used_pct
  , space_reclaimable
  , ROUND((space_reclaimable / space_limit)*100, 2) pct_reclaimable
  , number_of_files
FROM
    v$recovery_file_dest
ORDER BY
    name
/

I would get an output like this:

Name                                   Space Limit          Space Used  % Used   Space Reclaimable % Reclaimable Number of Files                 
------------------------------ ------------------- ------------------- ------- ------------------- ------------- ---------------                 
D:\oracle\product\10.2.0\flash     107,374,182,400      34,239,603,712   31.89                   0           .00             804                 
_recovery_area\DBNAME

My question is: How do I integrate the results of each similar query from each database into one output?

The output would look like this:

    Name                                   Space Limit          Space Used  % Used   Space Reclaimable % Reclaimable Number of Files                 
    ------------------------------ ------------------- ------------------- ------- ------------------- ------------- ---------------                 
  D:\oracle\product\10.2.0\flash     107,374,182,400      34,239,603,712   31.89                   0           .00             804                 
_recovery_area\DBNAME1   

D:\oracle\product\10.2.0\flash     107,374,182,400      34,239,603,712   31.89                   0           .00             804                 
    _recovery_area\DBNAME2

D:\oracle\product\10.2.0\flash     107,374,182,400      34,239,603,712   31.89                   0           .00             804                 
    _recovery_area\DBNAME3

Answer 1

Use UNION to join the results of separate queries into a single set. For example:

select name, space_limit, etc...
from v$recovery_file_dest
UNION
select name, space_limit, etc...
from v$recovery_file_dest  -- presumably a different value than the first one

Should be fine since each query returns the same set of columns in the same order.

Answer 2

I assume you want all of the database results in one result set instead of just connecting to each in turn and spooling the output to the same file. You'll have to pick one of the databases and create database links to the other four (selecting or creating a connecting user in each that has SELECT privileges on V$RECOVERY_FILE_DEST.

Then your query becomes

select ...
 from v$recover_file_dest
union all
select ...
from v$recover_file_dest@db2
union all
select ...
from v$recover_file_dest@db3
union all
select ...
from v$recover_file_dest@db4