Re: Help with SQL Puzzle..?
Date: 4 Feb 1995 20:05:31 GMT
Message-ID: <3h0mmb$6rt_at_dcsun4.us.oracle.com>
qmick_at_pipeline.com (Michael Joseph) writes:
>I am trying to write a one line piece of SQL to return the following data
>
>TABLESPACE FREE SPACE USED SPACE
>
>SYSTEM 10000000 2000000
>UTILS 5000000 3000000
>
>etc.
>
>I have tried this with:
>
>SELECT F.TABLESPACE_NAME, SUM(F.BYTES), SUM(U.BYTES)
>FROM SYS.DBA_FREESPACE F, SYS.DBA_SEGMENTS U
>WHERE F.TABLESPACE_NAME=U.TABLESPACE_NAME
>GROUP BY F.TABLESPACE_NAME
>
>But I get incorrect results coming back.....
>
>How do I make this work...?
>
>MJ
>
>
I know the following query looks ugly but it works great. The bottom of this note shows what the output would look like:
set wrap off set pagesize 66 column dummy noprint column pct_used format 999.9 heading "%|Used" column name format a16 heading "Tablespace Name"column bytes format 9,999,999,999,999 heading "Bytes" column used format 99,999,999,999 heading "Used" column free format 999,999,999,999 heading "Free" break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report
select a.tablespace_name name, b.tablespace_name dummy, sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes, sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) - sum(a.bytes)/count( distinct b.file_id ) used, sum(a.bytes)/count( distinct b.file_id ) free, 100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) - (sum(a.bytes)/count( distinct b.file_id ) )) / (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_usedfrom sys.dba_free_space a, sys.dba_data_files b where a.tablespace_name = b.tablespace_name group by a.tablespace_name, b.tablespace_name /
SQL> _at_free
% Tablespace Name Bytes Used Free Used ---------------- ------------------ --------------- ---------------- ------ CAREDB_INDEX 20,971,520 9,504,768 11,466,752 45.3 CAREDB_USERS 20,971,520 9,601,024 11,370,496 45.8 COBE_TB 3,145,728 206,848 2,938,880 6.6 XXX 10,485,760 63,488 10,422,272 .6 IDX 176,990,208 10,641,408 166,348,800 6.0 SYSTEM 44,040,192 36,864,000 7,176,192 83.7 TEMP 176,990,208 20,965,376 156,024,832 11.8 USR 176,990,208 72,224,768 104,765,440 40.8 ------------------ --------------- ---------------- sum 630,585,344 160,071,680 470,513,664
8 rows selected.
SQL> quit Received on Sat Feb 04 1995 - 21:05:31 CET