Home » RDBMS Server » Enterprise Manager » Tablespace Report (Oracle 10g Rel2, Linux AS4)
Tablespace Report [message #389566] Mon, 02 March 2009 12:16 Go to next message
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 #389567 is a reply to message #389566] Mon, 02 March 2009 12:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
After Messages: 207 you still refuse to follow Posting Guidelines.
You do not deserve any assistance in my opinion.

Re: Tablespace Report [message #389569 is a reply to message #389566] Mon, 02 March 2009 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 29 January 2009 12:34
200 posts, congratulations, but you should post accordingly to guidelines.

Regards
Michel


Re: Tablespace Report [message #389570 is a reply to message #389569] Mon, 02 March 2009 12:43 Go to previous messageGo to next message
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.
Re: Tablespace Report [message #389574 is a reply to message #389570] Mon, 02 March 2009 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually I wanted to show you the output in the form of excel sheet , but I'm unable to attach it.

1/ The code is not formatted
2/ The output MUST be posted in formatted text and NOT in excel sheet, this is why you are not allowed to post excel sheet.

Regards
Michel
Re: Tablespace Report [message #632071 is a reply to message #389574] Fri, 23 January 2015 17:05 Go to previous messageGo to next message
Prathapsingh
Messages: 1
Registered: January 2015
Location: United States
Junior Member
Hi Raj,

If you have completed this in your database. Please provide me the steps, I have to perform the same for my DB instances.


Thank you,
prathap
Re: Tablespace Report [message #632072 is a reply to message #632071] Fri, 23 January 2015 18:02 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
prathap,
welcome to this forum.

THANKS for at least trying to search for solution, but realize that this thread is almost 6 years old.
I expect you will have to write your own reporting code if you actually want to see the specific results your desire.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Previous Topic: Changing EM port
Next Topic: OEM - target database is not existed
Goto Forum:
  


Current Time: Thu Mar 28 03:46:51 CDT 2024