How to use SYSMAN schema without EM

As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.

on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema

on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema

As you can see it doubled up on 11G. New features brings new tables to this schema.

I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER

Lets start with this table and see what we can get from the table

SQL> @printtab "select * from sysman.MGMT_HC_HARDWARE_MASTER"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
VENDOR_NAME                   : Dell Inc.
SYSTEM_CONFIG                 : Latitude D620
MACHINE_ARCHITECTURE          : x86
CLOCK_FREQ_IN_MHZ             : 133
MEMORY_SIZE_IN_MB             : 2038
LOCAL_DISK_SPACE_IN_GB        : 74.53
CPU_COUNT                     : 2
CPU_BOARD_COUNT               :
IOCARD_COUNT                  : 1
FAN_COUNT                     :
POWER_SUPPLY_COUNT            :
-----------------

As you can see Tables under SYSMAN schema can give you very nice information, which you can’t find in any V$ view or DBA_ table especially for Host system.

Lets see what else we can get under MGMT_HC_%

SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';

TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS

We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)

SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : Microsoft Windows XP Workstation
VENDOR_NAME                   : Microsoft Corporation
BASE_VERSION                  : 5.1
UPDATE_LEVEL                  : Service Pack 2
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 3933
ADDRESS_LENGTH_IN_BITS        : 32-bit
PATCHES                       : 152
-----------------

PL/SQL procedure successfully completed.

on HP-UX same table shows

SQL>  @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : 66E304347A471561E044000000000000
NAME                          : HP-UX
VENDOR_NAME                   : Hewlett-Packard Co.
BASE_VERSION                  : B.11.11
UPDATE_LEVEL                  : U
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 15598.066
ADDRESS_LENGTH_IN_BITS        : 64
-----------------

Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you

SQL> @printtab “select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4" SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5 NAME : KB867282 TYPE : Patch VERSION : DESCRIPTION : Windows XP Hotfix - KB867282 INSTALLATION_DATE : ----------------- SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5 NAME : KB873333 TYPE : Patch VERSION : DESCRIPTION : Windows XP Hotfix - KB873333 INSTALLATION_DATE : ----------------- SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5 NAME : KB873339 TYPE : Patch VERSION : DESCRIPTION : Windows XP Hotfix - KB873339 INSTALLATION_DATE : ----------------- PL/SQL procedure successfully completed. [/sourcecode] As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command
select table_name from dba_tables where owner=’SYSMAN’ and num_rows>0;