Would have thought:
1. A developer would have known this - a SQL
many-to-many join
2. A DBA would have known this - how else would you
know what's happening with your tablespaces? (Clickety-pointy answers not
allowed)
----- Original Message -----
Sent: Saturday, November 15, 2003 9:54
AM
Subject: Re: SQL Query
You can't join DBA_EXTENTS and
DBA_DATA_FILES based on an equality of
tablespace_name, and then add up the bytes of the files for the tablespace.
ie.
select
b.tablespace_name,
b.bytes from dba_extents a,
dba_data_files b where
a.tablespace_name=b.tablespace_name
Try running that query, and it may become clear.
Your first query correctly aggregates the file sizes.
The second query determines
tablespace size based on the number of
extents allocated to it.
Drop all
the objects in the tablespace, and your tablespace will no longer appear to have any space.
HTH
Jared
| "Bellow, Bambi"
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED]
11/14/2003 09:44 AM
Please respond to ORACLE-L
| To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]> cc:
Subject: SQL
Query |
Friends --
Why would these two queries return different
results?
This query works.
SQL> l 1
select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100
pct 2 from (select tablespace_name,sum(bytes)/(1024*1024)
megs_used 3 from dba_extents group by tablespace_name)
a, 4 (select tablespace_name,sum(bytes)/(1024*1024)
megs_allocated 5 from dba_data_files group by
tablespace_name) b 6 where
a.tablespace_name=b.tablespace_name 7* and
a.tablespace_name='NAUAT' SQL> /
TABLESPACE_NAME
MEGS_ALLOCATED MEGS_USED
PCT ------------------------------ --------------
---------- ---------- NAUAT
22924.25
11509 50
This query
does not work
1 select
a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2
sum(a.bytes)/(1024*1024) megs_used, 3
round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from
dba_extents a, dba_data_files b 5 where
a.tablespace_name=b.tablespace_name 6 and
a.tablespace_name='NAUAT' 7* group by
a.tablespace_name,b.tablespace_name SQL> /
TABLESPACE_NAME
MEGS_ALLOCATED MEGS_USED
PCT ------------------------------
-------------- ---------- ---------- NAUAT
31773010.5 23018
.07
Bambi. -- Please see the official ORACLE-L FAQ:
http://www.orafaq.net -- Author: Bellow, Bambi INET:
[EMAIL PROTECTED]
Fat City Network Services --
858-538-5051 http://www.fatcity.com San Diego, California
-- Mailing list and web hosting
services --------------------------------------------------------------------- To
REMOVE yourself from this mailing list, send an E-Mail message to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
list you want to be removed from). You may also send the HELP command
for other information (like
subscribing).
|