Querying the Webcenter Spaces DB tables

While WCS comes loaded with REST services to extract information from the system, sometimes, specially when your application runs in the same backend, it's easier to query the DB directly to extract information from spaces. here is a quick overview of some tables I have used in the past to do just that.

Users' Information

In a standard implementation, all the user information will come from the LDAP server configured as the main Identity Store of the Weblogic Domain. webcenter however, keeps a copy of some user's details that can be reused in your custom applications. remember this information is only populated after the user logs in to the portal, also you need to ensure the GUIDs are maintain across LDAP environments.

The main table to query is  MYPORTAL_WEBCENTER.WC_AS_ACTOR_DETAIL this table contains the user's full name and email addresses, also, it contains the user's ACTOR_ID which is used in other tables and applications to reference the user.

SELECT   ACTOR_ID, DISPLAY_NAME,EMAIL,DEPARTMENT,JOB_TITLE, MANAGER_ID
FROM MYPORTAL_WEBCENTER.WC_AS_ACTOR_DETAIL
WHERE upper(ACTOR_NAME) = 'JSMITH';

Another table that may be useful is
MYPORTAL_WEBCENTER.WC_PEOPLE_CONN_CONNECTION, this table contains the information about the user's friend/connection list, the users are referenced using theri ACTOR_ID. One thing to keep in mind with this table is that the connections are stored unidirectional, which means that you need to search where the user is the person that send the invitation and also when he/she received, here is an example of how to get the list usersnames from the connections of a given username

SELECT CD. DISPLAY_NAME CONNECTIO_NAME
FROM MYPORTAL_WEBCENTER.WC_PEOPLE_CONN_CONNECTION PC, MYPORTAL_WEBCENTER.WC_AS_ACTOR_DETAIL AD, MYPORTAL_WEBCENTER.WC_AS_ACTOR_DETAIL CD
WHERE ((PC.OWNER_ID=AD.ACTOR_ID AND PC.CONNECTEE_ID=CD.ACTOR_ID) OR (PC.CONNECTEE_ID=AD.ACTOR_ID AND PC.OWNER_ID=CD.ACTOR_ID))
AND upper(AD.ACTOR_NAME) = 'JSMITH';

Spaces' Information

The main table to look at is  MYPORTAL_WEBCENTER.WC_SPACE_HEADER  this table stores all the spaces in the system, their status, last updated, and other attributes like parent space and member count.

here is an example of the query including some Oracle XMLDB smarts to retrieve the count of sub-spaces:

SELECT
  DISPLAY_NAME SPACE_NAME,
  DESCRIPTION,
  GS_MEMBER_COUNT MEMBERS,
  SPACE_PUBLIC "IS_PUBLIC",
  DISCOVERABLE SHOW_IN_SEARCHES,
  SPACE_OFFLINE IS_OFFLINE,
  CREATE_DATE CREATED_ON,
  UPDATED_BY,
  LAST_UPDATE_DATE,
  SUB_GS_COUNT SUBSPACES_COUNT,
  (select RTRIM(XMLAGG(XMLELEMENT(e,DISPLAY_NAME || ',')).EXTRACT('//text()'),',')  SUBSPACES FROM MYPORTAL_WEBCENTER.WC_SPACE_HEADER B where B.parent_GUID=A.SPACE_GUID) SUBSPACES
FROM MYPORTAL_WEBCENTER.WC_SPACE_HEADER  A
WHERE CLOSED='N'
order by 2 desc;

Statistics

Also, as webcenter Analytics is keeping record of all activity in the system, those tables can be used if you need a particular report that is not in the OOTB analytics pack.


For example, to get the daily page hits of a particular page in the system, first we need to get the page ID, to do so we check the  MYPORTAL_ACTIVITIES.ASDIM_WC_PAGES_0  table we can query the name of the page or the name of the jspx from the URL as follows:

From page name:

SELECT ID FROM MYPORTAL_ACTIVITIES.ASDIM_WC_PAGES_0 where lower(NAME_)  LIKE '%profile%'

From JSPX:

SELECT ID FROM  MYPORTAL_ACTIVITIES.ASDIM_WC_PAGES_0 where lower(RESOURCEID_)  LIKE '%spacesmainview.jsp%'

Once you have the ID or IDS of the pages you want to query, you can then look at the table  MYPORTAL_ACTIVITIES.ASFACT_WC_PAGES_0 this table stores all the page hits of the system, you can query it as follows:

select ndate, count(*) ntotal from MYPORTAL_ACTIVITIES.asfact_wc_pagevie_0 a where a.page_ in (SELECT ID FROM  MYPORTAL_ACTIVITIES.ASDIM_WC_PAGES_0 where lower(NAME_)  LIKE '%profile%') group by to_char (occurred ,'DD/MM/YYYY')