[ORACLE]管理方面的脚本收集(二)
rties p
WHERE p.property_name = 'OracleHome'
AND p.target_guid = t.target_guid) oh
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'ServiceName'
AND p.target_guid = t.target_guid) Service_name
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'log_archive_mode'
AND p.target_guid = t.target_guid) logmode
,UPPER((SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'SID'
AND p.target_guid = t.target_guid)) sid
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'CPUCount'
AND p.target_guid = t.target_guid) CPU
,ROUND(SYSDATE - TO_DATE((SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'StartTime'
AND p.target_guid = t.target_guid)
,'YYYY-MM-DD HH24:MI:SS')
,0) Days_Uptime
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'StartTime'
AND p.target_guid = t.target_guid) Uptime
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'VersionCategory'
AND p.target_guid = t.target_guid) VersionCategory
,(SELECT p.property_value
FROM mgmt$target_properties p
WHERE p.property_name = 'VersionBanner'
AND p.target_guid = t.target_guid) VersionBanner
,CASE
WHEN (INSTR((SELECT UPPER(p.property_value)
FROM mgmt$target_properties p
WHERE p.property_name = 'VersionBanner'
AND p.target_guid = t.target_guid)
,'ENTERPRISE')) >
0 THEN
'Enterprise'
ELSE
'Standard/Standard One'
END Edition
,(SELECT b.VALUE
FROM MGMT$ECM_VISIBLE_SNAPSHOTS A
,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
AND a.TARGET_TYPE = 'oracle_database'
AND b.name = 'control_file_record_keep_time'
AND a.target_guid = t.target_guid) control_file_record_keep_time
,(SELECT b.VALUE
FROM MGMT$ECM_VISIBLE_SNAPSHOTS A
,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
AND a.TARGET_TYPE = 'oracle_database'
AND b.name = 'optimizer_features_enable'
AND a.target_guid = t.target_guid) optimizer_features_enable
,(SELECT ROUND(b.VALUE / 1024 / 1024 / 1024, 2)
FROM MGMT$ECM_VISIBLE_SNAPSHOTS A
,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
AND a.TARGET_TYPE = 'oracle_database'
AND b.name = 'memory_target'
AND a.target_guid = t.target_guid) memory_target
,(SELECT sessions_highwater
FROM MGMT$ECM_VISIBLE_SNAPSHOTS A
,SYSMAN.MGMT_DB_license_ECM B
WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
AND TARGET_TYPE = 'oracle_database'
AND a.target_guid = t.target_guid) sessions_highwater
,(SELECT sessions_current
FROM MGMT$ECM_VISIBLE_SNAPSHOTS A
,SYSMAN.MGMT_DB_license_ECM B
WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
AND TARGET_TYPE = 'oracle_database'
AND a.target_guid = t.target_guid) sessions_current
FROM mgmt$target t
WHERE t.target_type IN ('oracle_database')) Raw_data
ORDER BY host_short
,sid;