| Tablespace Report [message #389566] |
Mon, 02 March 2009 12:16  |
rsreddy28
Messages: 295
Registered: May 2007
|
Senior Member |
|
|
Hi All,
This is in response to my query regarding OEM Grid Control Report . After a long time on this , I could come to some conclusion where I can have a single report for all the production instances that are hosted in the Grid Control.
This report has all the instances database size , free space and used space information of all the instances in the grid.
And now I'm looking for even the Tablespace report for all the instances in the same Grid Control report .
I'm attaching the output what I got and also below is the query for the information regarding the tablespaces that I've taken from the OEM:
SELECT
ts.tablespace_name AS TBNAME,
round((ts.tablespace_size/1024/1024),2) AS SIZE_MB,
round((ts.tablespace_used_size/1024/1024),2) AS USED_MB,
round(((ts.tablespace_size - ts.tablespace_used_size)/1024/1024),2) AS FREE_MB,
round(((ts.tablespace_used_size * 100)/
decode(ts.tablespace_size,0,1,ts.tablespace_size))
,2) AS USED_PCT,
decode(df.auto_extend, 0, 'NO' , 'YES') AS AUTO_EXTEND,
ts.status AS STATUS,
round(df.df_count,0) AS DATAFILES,
ts.contents AS TYPE,
ts.extent_management AS EXTENT_MANAGEMENT,
ts.segment_space_management AS SEGMENT_MANAGEMENT
FROM
mgmt$db_tablespaces ts,
(SELECT d.target_guid, d.tablespace_name, count(d.file_name) df_count,
sum(decode(d.autoextensible, 'YES', 1, 0)) auto_extend
FROM mgmt$db_datafiles d, mgmt$target t
WHERE t.target_guid = ??EMIP_BIND_TARGET_GUID?? AND
(t.target_type='rac_database' OR
(t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
t.target_guid = d.target_guid
GROUP BY d.target_guid, d.tablespace_name) df
WHERE
ts.target_guid = df.target_guid AND
df.tablespace_name = ts.tablespace_name
ORDER BY ts.tablespace_size
Can anybody help me on this please.
Regards,
Raj
[Updated on: Mon, 02 March 2009 12:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Tablespace Report [message #389570 is a reply to message #389569] |
Mon, 02 March 2009 12:43  |
rsreddy28
Messages: 295
Registered: May 2007
|
Senior Member |
|
|
Mike,
Actually I wanted to show you the output in the form of excel sheet , but I'm unable to attach it.
Please help me if there's a way where I can attach a excel sheet so that the my doubt will be in clarity.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
