Spacewalk DB Schema - View-RHNSERVEROVERVIEW

Main Tables Views Materialized Views Indexes Constraints Triggers Procedures Functions Packages Sequences Java Sources Sanity Check Index DDL scrips

Description Columns Query Constraints Triggers

RHNSERVEROVERVIEW

DDL script

Columns

Name	Type	Nullable	Insertable	Updatable	Deletable
ORG_ID	NUMBER(38)	N	YES	YES	YES
SERVER_ID	NUMBER(38)	N	YES	YES	YES
SERVER_NAME	VARCHAR2(128)	Y	YES	YES	YES
NOTE_COUNT	NUMBER(38)	Y	NO	NO	NO
MODIFIED	DATE	N	YES	YES	YES
SERVER_ADMINS	NUMBER(38)	Y	NO	NO	NO
GROUP_COUNT	NUMBER(38)	Y	NO	NO	NO
CHANNEL_ID	NUMBER(38)	Y	NO	NO	NO
CHANNEL_LABELS	VARCHAR2(64)	Y	NO	NO	NO
HISTORY_COUNT	NUMBER(38)	Y	NO	NO	NO
SECURITY_ERRATA	NUMBER(38)	Y	NO	NO	NO
BUG_ERRATA	NUMBER(38)	Y	NO	NO	NO
ENHANCEMENT_ERRATA	NUMBER(38)	Y	NO	NO	NO
OUTDATED_PACKAGES	NUMBER(38)	Y	NO	NO	NO
LAST_CHECKIN_DAYS_AGO	NUMBER(38)	Y	NO	NO	NO
LAST_CHECKIN	VARCHAR2(19)	Y	NO	NO	NO
PENDING_UPDATES	NUMBER(38)	Y	NO	NO	NO
OS	VARCHAR2(64)	N	YES	YES	YES
RELEASE	VARCHAR2(64)	N	YES	YES	YES
SERVER_ARCH_NAME	VARCHAR2(64)	Y	NO	NO	NO
LOCKED	NUMBER(38)	Y	NO	NO	NO

Query:

Legend: comment string keyword reserved word operator

select
    s.org_id, s.id, s.name, 0, s.modified,
    ( select count(user_id) from rhnUserServerPerms ap
      where server_id = s.id ),
    ( select count(server_group_id) from rhnVisibleServerGroupMembers
      where server_id = s.id ),
    ( select C.id
        from rhnChannel C,
	     rhnServerChannel SC
       where SC.server_id = S.id
         and SC.channel_id = C.id
	 and C.parent_channel IS NULL),
    NVL(( select C.name
        from rhnChannel C,
	     rhnServerChannel SC
       where SC.server_id = S.id
         and SC.channel_id = C.id
	 and C.parent_channel IS NULL), '(none)'),
    ( select count(id) from rhnServerHistory
      where
            server_id = S.id),
    ( select count(*) from rhnServerErrataTypeView setv
      where
            setv.server_id = s.id
        and setv.errata_type = 'Security Advisory'),
    ( select count(*) from rhnServerErrataTypeView setv
      where
            setv.server_id = s.id
        and setv.errata_type = 'Bug Fix Advisory'),
    ( select count(*) from rhnServerErrataTypeView setv
      where
            setv.server_id = s.id
        and setv.errata_type = 'Product Enhancement Advisory'),
    ( select count(distinct p.name_id) from rhnPackage p, rhnServerNeededPackageCache snpc
      where
             snpc.server_id = S.id
	 and p.id = snpc.package_id
	 ),
    ( select sysdate - checkin from rhnServerInfo where server_id = S.id ),
    ( select TO_CHAR(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnServerInfo where server_id = S.id ),
    ( select count(1)
        from rhnServerAction
       where server_id = S.id
         and status in (0, 1)),
    os,
    release,
    ( select name from rhnServerArch where id = s.server_arch_id),
    NVL((select 1 from rhnServerLock SL WHERE SL.server_id = S.id), 0)
from
    rhnServer S