| DBA_HIST_SEG_STAT view [message #604923] |
Mon, 06 January 2014 03:22  |
 |
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
Hi Experts,
I executed below query and I'm confused with the output
select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME "Tablespace", s.growth/(1024*1024) "Growth in MB",
(SELECT sum(bytes)/(1024*1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) "Total Size(GB)"
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS# and o.owner='XXXXXX' and o.object_type='TABLE'
ORDER BY 6 DESC) where rownum<6;
OUTPUT
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE Tablespace Growth in MB Total Size(GB)
XXXXXX YYYY1 TABLE USERS 2,405.97 0.02
XXXXXX YYYY2 TABLE USERS 597.20 0.01
XXXXXX YYYY3 TABLE USERS 365.79 0.32
XXXXXX YYYY4 TABLE USERS 360.48 0.05
XXXXXX YYYY5 TABLE USERS 343.07 0.06
Consider object "YYYY1" having growth of 2405 MB but its size is 0.02 GB (0.02*1024=20.48 MB)
How is that possible?
same goes for all the other objects, Please let me know what am I missing ?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DBA_HIST_SEG_STAT view [message #616719 is a reply to message #604929] |
Thu, 19 June 2014 20:49  |
 |
rmadabhushanam
Messages: 1
Registered: June 2014
Location: Nashua,NH
|
Junior Member |
|
|
Hi Michel,
Well. It might be hard to believe that some things in Oracle actually free.. but it is...
Please refer to Oracle Licensing documentation at http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC168.
Quote:All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.
Same question was also discussed in many otn forums.. one of those links is here..
https://community.oracle.com/thread/2361757?start=0&tstart=0
HTH..
Thanks,
Ravi.M
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
, I thought i did some mathematical blunder!! 
