Home » RDBMS Server » Enterprise Manager » Querying grid control tables to find the size and name of the databases (Oracle 10g grid control)
|
|
|
|
|
|
| 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   |
 |
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 |
 |
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 04:08:04 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.