|
Oh, right, #2 is refering to point-and-click GUI
interfaces that some DBAs depend on that they no longer know how to write SQLs
to navigate the data dictionary.
----- Original Message -----
Sent: Saturday, November 15, 2003 1:19
PM
Subject: Re: SQL Query
Sorry, don't understand the
DBA part ( #2 ).
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 -----
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
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).
|
