I need help in querying the OQ ORACLE views

Hi,

        I am qurying two views which ORACLE provides. DBA_DATA_FILES and DBA_FREE_SPACE.         I want the total allocated space to a TABLESPACE and total free space in a tablespace. A tablespace has more than one file.

IF DBA_FREE_SPACE returns the following from the following query:  

select dba_data_files.tablespace_name, file_id, dba_data_files.bytes from dba_data_files where tablespace_name= 'AVN01';

TABLESPACE_NAME                   FILE_ID      BYTES


------------------------------ ---------- ----------

AVN01                                   6  734003200
AVN01                                  10  314572800

AND DBA_DATA_FILES returns the following from the following query:

select dba_free_space.tablespace_name, file_id, dba_free_space.bytes from dba_free_space where tablespace_name= 'AVN01'

TABLESPACE_NAME                   FILE_ID      BYTES


------------------------------ ---------- ----------

AVN01                                  10       4096
AVN01                                  10       4096

Now the problem is:

        How can I get the combined total bytes from DBA_DATA_FILES and total bytes for DBA_FREE_SPACE. I wrote the following query but it does not work.

        select distinct dba_data_files.tablespace_name,         

        sum(dba_data_files.bytes), sum(dba_free_space.bytes) from         

        dba_data_files, dba_free_space where dba_data_files.tablespace_name =         

        dba_free_space.tablespace_name group by dba_data_files.tablespace_name         

        order by dba_data_files.tablespace_name;

        This does not give correct result but just adds the free bytes as many number of blocks are there in the dba_free_space.

        Please reply at manoj_at_advalue.com.

        Thanks in advance.

        Manoj Received on Thu May 04 1995 - 00:00:00 CEST