Home » RDBMS Server » Enterprise Manager » Querying grid control tables to find the size and name of the databases (Oracle 10g grid control)
Querying grid control tables to find the size and name of the databases [message #567189] Tue, 25 September 2012 01:46 Go to next message
srinirandy
Messages: 3
Registered: September 2012
Location: india
Junior Member
Hi all,

Can anyone tell me the query to get the name and size of all the databases associated to grid..

I need to find this to do capacity planning in our environment..

Thanks in advance..



Re: Querying grid control tables to find the size and name of the databases [message #567207 is a reply to message #567189] Tue, 25 September 2012 04:51 Go to previous messageGo to next message
howtodba
Messages: 4
Registered: September 2012
Location: Bucharest / Romania
Junior Member
Get storage usage, for backup locations, from Oracle Grid Database:
howtodba.com/old/165/storage-usage-for-backup-locations-from-oracle-grid-database


Get storage usage, for $ORACLE_HOME, from Oracle Grid Database:
howtodba.com/old/164/how-storage-usage-for-%24oracle_home-from-oracle-grid-database


Get storage usage, for datafiles, from Oracle Grid Database:
howtodba.com/old/161/how-get-storage-usage-for-datafiles-from-oracle-grid-database

I've used the queries in the links above for some Oracle Grid Dashboard, last month.
Re: Querying grid control tables to find the size and name of the databases [message #567257 is a reply to message #567189] Tue, 25 September 2012 12:31 Go to previous messageGo to next message
srinirandy
Messages: 3
Registered: September 2012
Location: india
Junior Member
Thx for the reply

I can see the sample data in your website as given below

DATABASE_NAME SERVER_NAME TOTALALLOCATEDSPACE_GB USEDSPACE_GB_DATAFILES FREESPACE_PERCENT_DATAFILES Archive Area Used %
------------------------------ ------------------------------ ---------------------- ---------------------- --------------------------- -------------------
Hidden S1 833.51 749.89 10.03 71.59
Hidden S2 3.82 1.39 63.71 0
Hidden S3 64.91 31.73 51.12 0
Hidden S4 128.07 99.28 22.48 0


so for capacity planning i need to collect this information and again store it in a table

can u tell me the table that stores this historical data. i mean in grid we can see the database growth graph how they are fetching historical information.

It will be very helful if u tell the table or views that is used by grid control to show us the historical growth of database.

Thanks in advance.
Re: Querying grid control tables to find the size and name of the databases [message #567281 is a reply to message #567257] Wed, 26 September 2012 02:18 Go to previous messageGo to next message
howtodba
Messages: 4
Registered: September 2012
Location: Bucharest / Romania
Junior Member
Hi,

My queries were made over the view "MGMT$METRIC_DAILY", for daily data about DB parameters.

If you need historic data also, have a look at the other 2 views: MGMT$METRIC_HOURLY & MGMT$METRIC_DAILY

So, in order to get some hist data on datafile size (for example), you'll replace MGMT$METRIC_DAILY with MGMT$METRIC_DAILY in those queries.

More info below:

# Tables that hold paramter data (also historic values):
# ------------------------------------------------------

	Table Name			Retention Parameter		Retention Days
	-----------------------------------------------------------------------
	MGMT_METRICS_RAW		mgmt_raw_keep_window		7
	MGMT_METRICS_1HOUR		mgmt_hour_keep_window		31
	MGMT_METRICS_1DAY		mgmt_day_keep_window		365


* The tables are in SYSMAN schema.
* To query the data in the 3 tables above, you'll need to know the values for "TARGET_GUID" & "METRIC_GUID".
* To make things easier, instead of querying the tabels, use the below 3 views (created over those tables). This views include the columns "target_name" and "metric_name", so it will be easier to search for the params you want.

	MGMT$METRIC_DETAILS
	MGMT$METRIC_HOURLY
	MGMT$METRIC_DAILY


# To change the retention for a given parameter, you can use:
# -----------------------------------------------------------
	INSERT INTO SYSMAN.MGMT_PARAMETERS 
		(PARAMETER_NAME, PARAMETER_VALUE,PARAMETER_COMMENT)
	VALUES 
		(mgmt_day_keep_window,'720','Keep daily agg data for 720 days');
Re: Querying grid control tables to find the size and name of the databases [message #567775 is a reply to message #567281] Fri, 05 October 2012 04:11 Go to previous messageGo to next message
srinirandy
Messages: 3
Registered: September 2012
Location: india
Junior Member
Hi,
sorry to disturb you again
I jusz modified your query as given below for getting the historical information in a monthly basis
select
r1.Database_Name Database_Name,
r1.MachineName Server_Name,
AllocatedSpace_GB,
UsedSpace_GB
from
(
select
Database_Name,
AllocatedSpace_GB,
UsedSpace_GB,
MachineName
from
(
select q1.target_name Server_Name,q1.AllocatedSpace_GB,q2.UsedSpace_GB
from
(select
target_name,
avg(AVERAGE) AllocatedSpace_GB
from
sysman.mgmt$METRIC_DAILY
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Allocated Space(GB)'
and rollup_timestamp>=sysdate-120 and rollup_timestamp<=sysdate-90
group by target_name
) q1,
(
select
target_name,
avg(AVERAGE) UsedSpace_GB
from
sysman.mgmt$METRIC_DAILY
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Used Space(GB)'
and rollup_timestamp>=sysdate-90 and rollup_timestamp<=sysdate-60
group by target_name
) q2
where
q1.target_name=q2.target_name
)w1,
(
select
Database_Name,
MachineName
from
(
select
target_name Database_Name,
HOST_NAME MachineName
from
SYSMAN.MGMT_TARGETS
where
TARGET_TYPE = 'oracle_database' or TARGET_TYPE= 'rac_database'
)
) w2
where
w1.Server_Name=w2.Database_Name
) r1

OUtput is:

50 rows selected.


And

select
q1.target_name Server_Name,
AllocatedSpace_GB,
UsedSpace_GB,
from
(
select
target_name,
avg(AVERAGE) AllocatedSpace_GB
from
sysman.mgmt$METRIC_DETAILS
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Allocated Space(GB)'
and rollup_timestamp>=sysdate-120<=sysdate-90
group by target_name
) q1,
(
select
target_name,
avg(AVERAGE) UsedSpace_GB
from
sysman.mgmt$METRIC_DETAILS
where
metric_name like 'DATABASE_SIZE'
and column_label like 'Used Space(GB)'
and rollup_timestamp>=sysdate-90<=sysdate-60
group by target_name
) q2,
where
q1.target_name=q2.target_name
) w1,
(
select
Database_Name,
MachineName
from
(
select
target_name Database_Name,
HOST_NAME MachineName
from
SYSMAN.MGMT_TARGETS
where
TARGET_TYPE = 'oracle_database'
)
) w2
where
w1.Server_Name=w2.Database_Name
) r1
order by Server_Name

Output is:
66 rows selected.

Why is the difference in this two timestamp??

And also when i query mgmt_targets
SQL> select
distinct(target_name),
HOST_NAME
from
SYSMAN.MGMT_TARGETS
where
TARGET_TYPE = 'oracle_database' 2 3 4 5 6 7
8 ;

i got the output as:
220 rows selected.

my question is why no of rows selected is different in two timestamps???

and can we get the sizes for all the databases in mgmt_targets
AS i showed it is 220

Thanks in advance




[Updated on: Fri, 05 October 2012 04:16]

Report message to a moderator

Re: Querying grid control tables to find the size and name of the databases [message #567777 is a reply to message #567775] Fri, 05 October 2012 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable, please format your query.
If you don't know how then use SQL Formatter.
And Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Edit: missing word]

[Updated on: Fri, 05 October 2012 04:35]

Report message to a moderator

Re: Querying grid control tables to find the size and name of the databases [message #567779 is a reply to message #567777] Fri, 05 October 2012 04:32 Go to previous message
howtodba
Messages: 4
Registered: September 2012
Location: Bucharest / Romania
Junior Member
Hi,

As Michel said, please format your query, and post some lines from the output you get from each query.
You ask about some differances in timestamps ? We cannot reply if you don't show us the values Smile.
Previous Topic: Error after changing ip address of the server
Next Topic: How do I start enterprise manager
Goto Forum:
  


Current Time: Thu Dec 18 17:11:44 CST 2014

Total time taken to generate the page: 0.09907 seconds