Home » SQL & PL/SQL » SQL & PL/SQL » Wrong output from sql query (Linux)
Wrong output from sql query [message #394474] Fri, 27 March 2009 05:29 Go to next message
ivpraveen
Messages: 5
Registered: March 2009
Junior Member
While running below mentioned No.2 query, I'm getting very high value under total_size_in_MB as compared to that of No.1. Could anyone please tell me why it is so.


1. Select tablespace_name, sum(bytes/1024/1024) from dba_data_files group by tablespace_name;


2. SELECT df.TABLESPACE_NAME,
SUM(seg.BYTES/1024/1024) "USED SIZE IN MB",
SUM(df.BYTES/1024/1024) "TOTAL SIZE IN MB" FROM
dba_segments seg, DBA_DATA_FILES df
where df.tablespace_name=seg.tablespace_name
GROUP BY df.TABLESPACE_NAME;
Re: Wrong output from sql query [message #394479 is a reply to message #394474] Fri, 27 March 2009 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
see what this gives

SELECT tablespace_name, "TOTAL SIZE IN MB", count_of_rows, ("TOTAL SIZE IN MB"/ count_of_rows) adjusted_count
FROM(
SELECT df.TABLESPACE_NAME, 
SUM(seg.BYTES/1024/1024) "USED SIZE IN MB", 
SUM(df.BYTES/1024/1024) "TOTAL SIZE IN MB",
COUNT(*) count_of_rows
 FROM 
dba_segments seg, DBA_DATA_FILES df 
where df.tablespace_name=seg.tablespace_name 
GROUP BY df.TABLESPACE_NAME); 
Re: Wrong output from sql query [message #394480 is a reply to message #394474] Fri, 27 March 2009 05:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because you're counting the Dba_Data_files size once for each segment in the tablespace.- you've got a partial cartesian join between the two tables.
Previous Topic: how to rewrite the beloe said query
Next Topic: VARCHAR2 cannot take a data more than 32KB.
Goto Forum:
  


Current Time: Wed Dec 07 06:43:28 CST 2016

Total time taken to generate the page: 0.10202 seconds