I need help in querying the OQ ORACLE views
Date: 1995/05/04
Message-ID: <3oblgg$gfq_at_lynx.dac.neu.edu>#1/1
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