| Oracle® Beehive Business Views Release 2 (2.0.1.8) Part Number E16654-05 |
|
|
PDF · Mobi · ePub |
| Oracle® Beehive Business Views Release 2 (2.0.1.8) Part Number E16654-05 |
|
|
PDF · Mobi · ePub |
This chapter describes Oracle Beehive Business Views. It includes the following sections:
Oracle Database is the information store for Oracle Beehive and, in addition to other data, contains a set of business views. A business view, or a view, is a tailored presentation of Oracle Beehive data.
Database administrators can query a view to obtain statistical reports of Oracle Beehive data. For instance, you can query a view to obtain reports about workspace count, quota usage, quota limits, and so on.
Note:
Business views are different from data provided by Oracle Enterprise Manager Grid Control. A business view provides information about how users use resources rather than about system uptime or memory usage.This section includes the following topics:
Database administrators access views by logging on to Oracle Database as the BEE_CODE user. The BEE_CODE user is an Oracle Beehive user who makes database transactions. The BEE_CODE user can then query the view using a SQL query tool such as SQL* Plus.
For example, to query the view bee_team_wspc_members_rv to get a count of all distinct user-typed members for all team workspaces, perform the following steps:
Start SQL*Plus at the command prompt:
prompt> sqlplus /NOLOG
Connect to Oracle Database as the BEE_CODE user:
SQL> CONNECT BEE_CODE/PASSWORD
You can either query a view or use the DESCRIBE command to get a list of all the columns in a view:
DESCRIBE bee_team_wspc_members_rv
The output of the command is as follows:
Name Null? Type ----------------------------------------- -------- ------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE CHAR(4) NUM_MEMBERS NUMBER
A view requires storage only for its definition. A view is not allocated any storage space because it does not actually contain any data. Instead, a view references a base table each time you query the view and displays the output.
So, administrators should consider that querying a view takes time and affects the database performance while the view is being queried.
By default, the output of a view appears at the command prompt. However, you can store this output to a file by using the SQL*Plus command SPOOL.
Note:
If you are using the SQL query tool, then you can use a command offered by the SQL query tool you are using and which is similar in functionality toSPOOL.For example, to append the output to the existing file NOTES, use the following command:
SPOOL NOTES APPEND
This section describes the following kinds of views:
Regular Reporting Views: The names of these views end with _rv. Results of regular views do not contain sensitive information.
Sensitive Reporting Views: The names of these views end with _s_rv.
Most business views have both an _rv and an _s_rv version; the only difference is that entity names are included in the _s_rv version.
You can query a regular view to obtain non-sensitive information about the data contained in Oracle Beehive. Results of regular views do not contain sensitive information such as entity names of objects like files and workspaces. As per the naming conventions, regular views end with _rv.
Example 1-1 shows information in a regular view.
You can query a sensitive view to obtain sensitive information about the data contained in Oracle Beehive. Results of these views include entity names of objects such as files and workspaces.
For example, a sensitive view might contain information about the company budget and only a senior level executive of the finance division or the administrator can query it. As per the naming conventions, sensitive views end with an _s_rv.
Example 1-2 shows the information in a sensitive view.
Example 1-2 Information In a Sensitive View
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) VISIBILITY NOT NULL CHAR(1 CHAR) NUM_WORKSPACES NUMBER QUOTA_USED NUMBER OVER_QUOTA CHAR(1) NUM_ORGANIZATIONS NUMBER
Note:
You can restrict access to sensitive views by creating a database user with restricted privileges. Such a user will have access only to regular views.This section contains a comprehensive list of business views related to the Workspace Service. This section also includes sample queries that you can run for each view.
This section lists the following views:
This view provides a workspace count, organization count, quota usage, quota state, and basic properties such as name, EID, and visibility for all enterprises.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) VISIBILITY NOT NULL CHAR(1 CHAR) NUM_WORKSPACES NUMBER QUOTA_USED NUMBER OVER_QUOTA CHAR(1) NUM_ORGANIZATIONS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) VISIBILITY NOT NULL CHAR(1 CHAR) NUM_WORKSPACES NUMBER QUOTA_USED NUMBER OVER_QUOTA CHAR(1) NUM_ORGANIZATIONS NUMBER
Suggested Queries for This View
Total space consumed by all enterprises:
SELECT SUM(quota_used) FROM bee_enterprises_rv;
Total space utilization by all enterprises which are not active/visible:
SELECT SUM(quota_used) AS total_size FROM bee_enterprises_rv WHERE visibility != 'V';
Average quota usage (in bytes) per enterprise:
SELECT AVG(quota_used) FROM bee_enterprises_rv;
Space consumed by each enterprise:
SELECT enterprise_eid, quota_used, over_quota FROM bee_enterprises_rv;
Number of enterprises over hard quota limits:
SELECT COUNT(*) FROM bee_enterprises_rv WHERE over_quota = 'Y';
Number of organizations per enterprise:
SELECT enterprise_eid, num_organizations FROM bee_enterprises_rv;
Number of workspaces per enterprise:
SELECT enterprise_eid, num_workspaces FROM bee_enterprises_rv;
Average number of organizations per enterprise:
SELECT AVG(num_organizations) FROM bee_enterprises_rv;
Average number of workspaces per enterprise:
SELECT AVG(num_workspaces) FROM bee_enterprises_rv;
This view provides bond properties such as EID, name, type, root type, root EID, and modification time for each enterprise, allowing for aggregation queries on bonds per enterprise.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) ENTERPRISE_VISIBILITY NOT NULL CHAR(1 CHAR) BOND_EID NOT NULL RAW(22) BOND_NAME NOT NULL VARCHAR2(1000 CHAR) BOND_TYPE NOT NULL VARCHAR2(1 CHAR) BOND_ROOT_EID NOT NULL RAW(22) BOND_ROOT_TYPE NOT NULL VARCHAR2(4 CHAR) BOND_MODIFIED_ON NOT NULL TIMESTAMP(6) BOND_DELETED NOT NULL CHAR(1 CHAR)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_VISIBILITY NOT NULL CHAR(1 CHAR) BOND_EID NOT NULL RAW(22) BOND_TYPE NOT NULL VARCHAR2(1 CHAR) BOND_ROOT_EID NOT NULL RAW(22) BOND_ROOT_TYPE NOT NULL VARCHAR2(4 CHAR) BOND_MODIFIED_ON NOT NULL TIMESTAMP(6) BOND_DELETED NOT NULL CHAR(1 CHAR)
Suggested Queries for This View
Total number of bonds in each enterprise:
SELECT enterprise_eid,
enterprise_visibility,
COUNT(bond_eid) as num_bonds
FROM bee_enterprise_bonds_rv
GROUP BY enterprise_eid, enterprise_visibility
ORDER BY num_bonds DESC;
Number of bonds of type related materials in each enterprise. Use F for follow up and D for discuss this bonds:
SELECT enterprise_eid,
enterprise_visibility,
COUNT(bond_eid) as num_related_bonds
FROM bee_enterprise_bonds_rv
WHERE bond_type = 'R'
GROUP BY enterprise_eid, enterprise_visibility
ORDER BY num_related_bonds DESC;
Number of bonds with root of type document in each enterprise:
SELECT enterprise_eid,
enterprise_visibility,
COUNT(bond_eid) as num_docroot_bonds
FROM bee_enterprise_bonds_rv
WHERE bond_root_type = 'adoc'
GROUP BY enterprise_eid, enterprise_visibility
ORDER BY num_docroot_bonds DESC;
Average number of bonds per enterprise:
SELECT AVG(num_bonds) as avg_num_bonds
FROM (SELECT COUNT(bond_eid) as num_bonds
FROM bee_enterprise_bonds_rv
GROUP BY enterprise_eid);
Number of bonds of each type in the system:
SELECT DECODE(bond_type, 'R', 'RELATED_MATERIALS',
'F', 'FOLLOW_UP',
'D', 'DISCUSS_THIS') as bond_type,
COUNT(bond_eid) as num_bonds
FROM bee_enterprise_bonds_rv
GROUP BY bond_type
ORDER BY bond_type;
Number of bonds with root of each entity type in the system:
SELECT bond_root_type as bond_root_type,
COUNT(bond_eid) as num_bonds
FROM bee_enterprise_bonds_rv
GROUP BY bond_root_type
ORDER BY bond_root_type;
This view provides a user count for all enterprises.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) VISIBILITY NOT NULL CHAR(1 CHAR) NUM_USERS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_EID NOT NULL RAW(22) VISIBILITY NOT NULL CHAR(1 CHAR) NUM_USERS NUMBER
Suggested Queries for This View
Average number of users per enterprise:
SELECT AVG(num_users) FROM bee_enterprise_users_rv;
Ten most populous enterprises and their user counts:
SELECT * FROM (SELECT enterprise_eid, visibility, num_users FROM bee_enterprise_users_rv ORDER BY num_users DESC) WHERE ROWNUM <= 10;
This view provides a total workspace count, child workspace count, quota usage, flags for top_level and has_child_orgs, and basic properties such as name, EID, enterprise name, and enterprise EID for all organizations.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ORGANIZATION_EID NOT NULL RAW(22) ORGANIZATION_NAME NOT NULL VARCHAR2(1000 CHAR) VISIBILITY NOT NULL CHAR(1 CHAR) ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) TOTAL_WORKSPACES NUMBER CHILD_WORKSPACES NUMBER QUOTA_USED NUMBER IS_TOPLEVEL_ORG CHAR(1) HAS_CHILD_ORGS CHAR(1) OVER_QUOTA CHAR(1)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- ORGANIZATION_EID NOT NULL RAW(22) VISIBILITY NOT NULL CHAR(1 CHAR) ENTERPRISE_EID NOT NULL RAW(22) TOTAL_WORKSPACES NUMBER CHILD_WORKSPACES NUMBER QUOTA_USED NUMBER IS_TOPLEVEL_ORG CHAR(1) HAS_CHILD_ORGS CHAR(1) OVER_QUOTA CHAR(1)
Suggested Queries for This View
Total space consumed by all organizations:
SELECT SUM(quota_used) FROM bee_organizations_rv;
Average quota usage (in bytes) per organization:
SELECT AVG(quota_used) FROM bee_organizations_rv;
Average quota usage (in bytes) per leaf organization:
SELECT AVG(quota_used) FROM bee_organizations_rv WHERE has_child_orgs = 'N';
Average quota usage (in bytes) per root organization:
SELECT AVG(quota_used) FROM bee_organizations_rv WHERE is_toplevel_org = 'Y';
Number of organizations over hard quota limits:
SELECT COUNT(*) FROM bee_organizations_rv WHERE over_quota = 'Y';
Total number of workspaces in each organization:
SELECT organization_eid, total_workspaces FROM bee_organizations_rv;
Number of direct child workspaces in each organization:
SELECT organization_eid, child_workspaces FROM bee_organizations_rv;
Number of workspaces in each root organization:
SELECT organization_eid, total_workspaces FROM bee_organizations_rv WHERE is_toplevel_org = 'Y';
Average number of workspaces per organization:
SELECT AVG(total_workspaces) FROM bee_organizations_rv;
Average number of direct child workspaces per organization:
SELECT AVG(child_workspaces) FROM bee_organizations_rv;
Average number of workspaces per leaf organization:
SELECT AVG(total_workspaces) FROM bee_organizations_rv WHERE has_child_orgs = 'N';
Distribution percentile for number of child workspaces in each organization. Percentile of all organizations with fewer or equal child workspaces:
SELECT organization_eid, child_workspaces,
(CUME_DIST() OVER (ORDER BY child_workspaces NULLS FIRST)) as percentile
FROM bee_organizations_rv;
Number of direct child workspaces for median, or other percentile organizations:
SELECT organization_eid, child_workspaces, 0.50 as percentile
FROM bee_organizations_rv
WHERE child_workspaces = (SELECT (PERCENTILE_DISC(0.50) WITHIN GROUP
(ORDER BY child_workspaces ASC NULLS FIRST)) FROM bee_organizations_rv);
This view provides a count of all distinct user-typed members for all team workspaces, traversing the group hierarchy as needed.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE CHAR(4) NUM_MEMBERS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE CHAR(4) NUM_MEMBERS NUMBER
Suggested Queries for This View
Average number of user members per team workspace:
SELECT AVG(num_members) FROM bee_team_wspc_members_rv;
Ten most populous team workspaces and their membership counts:
SELECT * FROM
{SELECT workspace_eid, workspace_type, num_members
FROM bee_team_wspc_members_rv
ORDER BY num_members DESC)
WHERE ROWNUM <= 10;
Distribution percentile of number of members in each workspace. Percentile of all workspaces with fewer or equal members:
SELECT workspace_eid, num_members,
(CUME_DIST() OVER (ORDER BY num_members NULLS FIRST)) as percentile
FROM bee_team_wspc_members_rv;
Number of members for median or other percentile workspaces:
SELECT workspace_eid, num_members, 0.50 as percentile
FROM bee_team_wspc_members_rv
WHERE num_members = (SELECT (PERCENTILE_DISC(0.50) WITHIN GROUP
(ORDER BY num_members ASC NULLS FIRST)) FROM bee_team_wspc_members_rv);
This view lists all markers, marker names, marker types, and marker classifications for markers owned by each user in the system. It allows for queries of marker counts or types by user and enterprise.
Oracle Beehive clients, such as Oracle Beehive Zimbra, represent markers as tags. For example, in Oracle Beehive Zimbra, you may tag your tasks, mail, contacts, folders, and other entities. You may use predefined tags that come with Oracle Beehive Zimbra or create your own. This enables you to group and organize your entities in categories you have defined yourself.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_EID NOT NULL RAW(22) USER_NAME VARCHAR2(1000 CHAR) USER_CREATED_ON TIMESTAMP(6) USER_MODIFIED_ON TIMESTAMP(6) USER_VISIBILITY VARCHAR2(1) ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) MARKER_EID RAW(22) MARKER_NAME VARCHAR2(1000 CHAR) MARKER_TYPE VARCHAR2(4 CHAR) MARKER_CLASSIFICATION VARCHAR2(1 CHAR) MARKER_VISIBILITY CHAR(1 CHAR)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_EID NOT NULL RAW(22) USER_CREATED_ON TIMESTAMP(6) USER_MODIFIED_ON TIMESTAMP(6) USER_VISIBILITY VARCHAR2(1) ENTERPRISE_EID NOT NULL RAW(22) MARKER_EID RAW(22) MARKER_TYPE VARCHAR2(4 CHAR) MARKER_CLASSIFICATION VARCHAR2(1 CHAR) MARKER_VISIBILITY CHAR(1 CHAR)
Suggested Queries for This View
Number of markers of type label defined or owned by each user:
SELECT user_eid, enterprise_eid,
COUNT(marker_eid) as num_labels
FROM bee_user_markers_rv
WHERE marker_type = 'labl'
GROUP BY user_eid, enterprise_eid
ORDER BY num_labels DESC;
Number of markers of type category defined or owned by each user:
SELECT user_eid, enterprise_eid,
COUNT(marker_eid) as num_categories
FROM bee_user_markers_rv
WHERE marker_type = 'catg'
GROUP BY user_eid, enterprise_eid
ORDER BY num_categories DESC;
This view provides a total marker count per user.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_EID NOT NULL RAW(22) USER_NAME VARCHAR2(1000 CHAR) ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) NUM_MARKERS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_EID NOT NULL RAW(22) ENTERPRISE_EID NOT NULL RAW(22) NUM_MARKERS NUMBER
Suggested Queries for This View
Total number of markers defined or owned by each user:
SELECT * FROM bee_user_marker_totals_rv ORDER BY num_markers DESC;
Average number of markers defined or owned by each user:
SELECT AVG(num_markers) FROM bee_user_marker_totals_rv;
Distribution percentile for number of markers for each user. Percentile of all users with fewer or equal markers:
SELECT user_eid, num_markers,
(CUME_DIST() OVER (ORDER BY num_markers NULLS FIRST)) as percentile
FROM bee_user_marker_totals_rv
ORDER BY num_markers DESC;
Number of markers for median or other percentile users:
SELECT user_eid, num_markers, 0.50 as percentile
FROM bee_user_marker_totals_rv
WHERE num_markers =
(SELECT (PERCENTILE_DISC(0.50) WITHIN GROUP
(ORDER BY num_markers ASC NULLS FIRST))
FROM bee_user_marker_totals_rv);
This view provides information such as quota usage, quota limits, quota state, and basic properties such as workspace type, enterprise name, parent EID, and last modified time for all workspaces in the system.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) PARENT_EID NOT NULL RAW(22) ENTERPRISE_EID NOT NULL RAW(22) ENTERPRISE_NAME VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) MODIFIED_ON NOT NULL TIMESTAMP(6) VISIBILITY NOT NULL CHAR(1 CHAR) QUOTA_USED NUMBER(38) QUOTA_STATE VARCHAR2(1 CHAR) SOFT_QUOTA NUMBER(38) HARD_QUOTA NUMBER(38) PCT_SOFT_USAGE NUMBER PCT_HARD_USAGE NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) PARENT_EID NOT NULL RAW(22) ENTERPRISE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) MODIFIED_ON NOT NULL TIMESTAMP(6) VISIBILITY NOT NULL CHAR(1 CHAR) QUOTA_USED NUMBER(38) QUOTA_STATE VARCHAR2(1 CHAR) SOFT_QUOTA NUMBER(38) HARD_QUOTA NUMBER(38) PCT_SOFT_USAGE NUMBER PCT_HARD_USAGE NUMBER
Suggested Queries for This View
Total number of workspaces:
SELECT COUNT(*) AS total_workspaces FROM bee_workspaces_rv;
Total number of team workspaces:
SELECT COUNT(*) AS team_workspaces FROM bee_workspaces_rv WHERE workspace_type = 'wstm';
Total number of personal workspaces:
SELECT COUNT(*) AS personal_workspaces FROM bee_workspaces_rv WHERE workspace_type = 'wspr';
Number of workspaces exceeding soft quota limits:
SELECT COUNT(*) AS workspaces_over_soft_quota FROM bee_workspaces_rv WHERE quota_state = 'S';
Number of team workspaces exceeding soft quota limits:
SELECT COUNT(*) AS team_wspcs_over_soft_quota FROM bee_workspaces_rv WHERE quota_state = 'S' AND workspace_type = 'wstm';
Number of personal workspaces exceeding soft quota limits:
SELECT COUNT(*) AS pers_wspcs_over_soft_quota FROM bee_workspaces_rv WHERE quota_state = 'S' AND workspace_type = 'wspr';
Number of workspaces exceeding hard quota limits:
SELECT COUNT(*) AS workspaces_over_hard_quota FROM bee_workspaces_rv WHERE quota_state = 'H';
Number of team workspaces exceeding hard quota limits:
SELECT COUNT(*) AS team_wspcs_over_hard_quota FROM bee_workspaces_rv WHERE quota_state = 'H' AND workspace_type = 'wstm';
Number of personal workspaces exceeding hard quota limits:
SELECT COUNT(*) AS pers_wspcs_over_hard_quota FROM bee_workspaces_rv WHERE quota_state = 'H' AND workspace_type = 'wspr';
Total space utilization by all workspaces:
SELECT SUM(quota_used) AS total_size FROM bee_workspaces_rv;
Total space utilization by all workspaces which are inactive or visible:
SELECT SUM(quota_used) AS total_size FROM bee_workspaces_rv WHERE visibility != 'V';
Percentile of soft quota used by all workspaces which have explicit soft quota limits:
SELECT (SUM(quota_used) / SUM(soft_quota)) AS pct_soft_usage FROM bee_workspaces_rv WHERE soft_quota >= 0;
Percentile of hard quota used by all workspaces which have explicit hard quota limits:
SELECT (SUM(quota_used) / SUM(hard_quota)) AS pct_hard_usage FROM bee_workspaces_rv WHERE hard_quota >= 0;
Ten largest workspaces (by data size):
SELECT * FROM (SELECT workspace_eid, workspace_type, data_size FROM bee_workspaces_rv ORDER BY data_size DESC) WHERE ROWNUM <= 10;
Ten most recently modified workspaces:
SELECT * FROM (SELECT workspace_eid, workspace_type, modified_on FROM bee_workspaces_rv ORDER BY modified_on DESC) WHERE ROWNUM <= 10;
Quota usage distribution percentile for all workspaces. Percentile of all workspaces with lower or equal quota usage for each workspace:
SELECT workspace_eid, quota_used,
(CUME_DIST() OVER (ORDER BY quota_used NULLS FIRST)) as percentile
FROM bee_workspaces_rv;
Quota usage for median (or other percentile) workspaces:
SELECT workspace_eid, quota_used, 0.50 as percentile
FROM bee_workspaces_rv w
WHERE quota_used = (SELECT (PERCENTILE_DISC(0.50) WITHIN GROUP
(ORDER BY quota_used ASC NULLS FIRST)) FROM bee_workspaces_rv);
This view provides document properties such as creation time, modification time, size, document type, media (MIME) type, and visibility for all documents mapped to their containing workspaces. It allows for queries of document sizes or counts by workspace, document type, date ranges, and so on.
This view includes all documents contained in heterogeneous folders except the ones in trash or archive folders. Also, this view includes unversioned or family document entities while filtering out version entities as redundant.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_MODIFIED_ON NOT NULL TIMESTAMP(6) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) DOCUMENT_EID NOT NULL RAW(22) DOCUMENT_NAME VARCHAR2(1000 CHAR) DOCUMENT_CREATED_ON NOT NULL TIMESTAMP(6) DOCUMENT_MODIFIED_ON NOT NULL TIMESTAMP(6) DOCUMENT_VISIBILITY NOT NULL CHAR(1 CHAR) DOCUMENT_DATA_SIZE NOT NULL NUMBER(38) DOCUMENT_IS_HIDDEN NOT NULL CHAR(1 CHAR) DOCUMENT_MEDIA_TYPE VARCHAR2(4000 CHAR) DOCUMENT_DATA_TYPE VARCHAR2(8)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_MODIFIED_ON NOT NULL TIMESTAMP(6) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) DOCUMENT_EID NOT NULL RAW(22) DOCUMENT_CREATED_ON NOT NULL TIMESTAMP(6) DOCUMENT_MODIFIED_ON NOT NULL TIMESTAMP(6) DOCUMENT_VISIBILITY NOT NULL CHAR(1 CHAR) DOCUMENT_DATA_SIZE NOT NULL NUMBER(38) DOCUMENT_IS_HIDDEN NOT NULL CHAR(1 CHAR) DOCUMENT_MEDIA_TYPE VARCHAR2(4000 CHAR) DOCUMENT_DATA_TYPE VARCHAR2(8)
Suggested Queries for This View
Total number of documents for each workspace:
SELECT workspace_eid, workspace_type,
COUNT(document_eid) AS num_documents
FROM bee_workspace_documents_rv
GROUP BY workspace_eid, workspace_type
ORDER BY num_documents DESC;
Total number of BLOB-stored documents for each workspace:
SELECT workspace_eid, workspace_type,
COUNT(document_eid) AS num_blob_documents
FROM bee_workspace_documents_rv
WHERE document_data_type = 'BLOB'
GROUP BY workspace_eid, workspace_type
ORDER BY num_blob_documents DESC;
Total number of XDB or BLOB-stored documents for each workspace:
SELECT workspace_eid, workspace_type,
COUNT(document_eid) AS num_xdbblob_documents
FROM bee_workspace_documents_rv
WHERE document_data_type = 'XDB_BLOB'
GROUP BY workspace_eid, workspace_type
ORDER BY num_xdbblob_documents DESC;
Total number of BFILE documents for each workspace:
SELECT workspace_eid, workspace_type,
COUNT(document_eid) AS num_bfile_documents
FROM bee_workspace_documents_rv
WHERE document_data_type = 'BFILE'
GROUP BY workspace_eid, workspace_type
ORDER BY num_bfile_documents DESC;
Total number of documents with media type text for each workspace:
SELECT workspace_eid, workspace_type,
COUNT(document_eid) AS num_text_documents
FROM bee_workspace_documents_rv
WHERE document_media_type like 'text%'
GROUP BY workspace_eid, workspace_type
ORDER BY num_text_documents DESC;
Total number of documents for all team and all personal workspaces:
SELECT DECODE(workspace_type, 'wstm', 'TEAM', 'wspr', 'PERSONAL')
as workspace_type,
COUNT(document_eid) AS num_documents
FROM bee_workspace_documents_rv
GROUP BY workspace_type
ORDER BY num_documents DESC;
Total space consumed by documents for each workspace:
SELECT workspace_eid, workspace_type,
SUM(document_data_size) AS total_doc_size
FROM bee_workspace_documents_rv
GROUP BY workspace_eid, workspace_type
ORDER BY total_doc_size DESC;
Average document size for each workspace:
SELECT workspace_eid, workspace_type,
AVG(document_data_size) AS avg_doc_size
FROM bee_workspace_documents_rv
GROUP BY workspace_eid, workspace_type
ORDER BY avg_doc_size DESC;
Total size of documents for all team workspaces and all personal workspaces:
SELECT DECODE(workspace_type, 'wstm', 'TEAM', 'wspr', 'PERSONAL')
as workspace_type,
SUM(document_data_size) AS total_doc_size
FROM bee_workspace_documents_rv
GROUP BY workspace_type
ORDER BY total_doc_size DESC;
Ten workspaces with the most recently modified documents:
SELECT * FROM
(SELECT * FROM
(SELECT workspace_eid, workspace_type,
MAX(document_modified_on) as latest_doc_modified_on
FROM bee_workspace_documents_rv
GROUP BY workspace_eid, workspace_type)
ORDER BY latest_doc_modified_on DESC)
WHERE ROWNUM <= 10
This view provides trash item properties such as item EID, item name, original entity EID, original name, original parent type, deleted by EID, deleted by type, deleted on, and data size for every item in a workspace trash folder. This view can be used to determine current trash usage statistics across workspaces and for specific workspaces.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) ITEM_EID RAW(22) ITEM_NAME VARCHAR2(1000 CHAR) ITEM_ENTITY_EID RAW(22) ITEM_ENTITY_TYPE VARCHAR2(4 CHAR) ITEM_ORIGINAL_NAME VARCHAR2(1000 CHAR) ITEM_ORIGINAL_PARENT_EID RAW(22) ITEM_ORIGINAL_PARENT_TYPE VARCHAR2(4 CHAR) DELETED_BY_EID RAW(22) DELETED_BY_TYPE VARCHAR2(4 CHAR) DELETED_ON TIMESTAMP(6) DATA_SIZE NUMBER(38)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) ITEM_EID RAW(22) ITEM_ENTITY_EID RAW(22) ITEM_ENTITY_TYPE VARCHAR2(4 CHAR) ITEM_ORIGINAL_PARENT_EID RAW(22) ITEM_ORIGINAL_PARENT_TYPE VARCHAR2(4 CHAR) DELETED_BY_EID RAW(22) DELETED_BY_TYPE VARCHAR2(4 CHAR) DELETED_ON TIMESTAMP(6) DATA_SIZE NUMBER(38)
Suggested Queries for This View
Total number of trash items in each workspace:
SELECT workspace_eid, workspace_type,
COUNT(item_eid) as num_trash_items
FROM bee_workspace_trash_rv
GROUP BY workspace_eid, workspace_type
ORDER BY num_trash_items DESC;
Total number of trash items of type document in each workspace:
SELECT workspace_eid, workspace_type,
COUNT(item_eid) as num_trash_items
FROM bee_workspace_trash_rv
WHERE item_entity_type = 'adoc'
GROUP BY workspace_eid, workspace_type
ORDER BY num_trash_items DESC;
All trash items larger than 10MB in size:
SELECT workspace_eid, workspace_type,
item_eid, item_entity_eid, item_entity_type,
data_size
FROM bee_workspace_trash_rv
WHERE data_size > 10485760
ORDER BY data_size DESC;
All trash items deleted before January 25, 2007:
SELECT workspace_eid, workspace_type,
item_eid, item_entity_eid, item_entity_type,
deleted_by_eid, deleted_on, data_size
FROM bee_workspace_trash_rv
WHERE deleted_on < TO_TIMESTAMP('25-Jan-07 12:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF')
ORDER BY deleted_on;
This view lists every direct accessor and accessor type for each (workspace, role name) pair. It does not traverse the group hierarchy.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) ACCESSOR_EID NOT NULL RAW(22) ACCESSOR_TYPE NOT NULL VARCHAR2(4)
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) ACCESSOR_EID NOT NULL RAW(22) ACCESSOR_TYPE NOT NULL VARCHAR2(4)
Suggested Queries for This View
Number of direct accessors of type user for each (workspace, role) pair:
SELECT workspace_eid, role_name,
COUNT(accessor_eid) as num_user_accessors
FROM bee_wspc_member_roles_rv
WHERE accessor_type = 'user'
GROUP BY workspace_eid, role_name
ORDER BY workspace_eid, role_name;
Number of direct accessors of type group for each (workspace, role) pair:
SELECT workspace_eid, role_name,
COUNT(accessor_eid) as num_group_accessors
FROM bee_wspc_member_roles_rv
WHERE accessor_type = 'grup'
GROUP BY workspace_eid, role_name
ORDER BY workspace_eid, role_name;
This view lists the complete distinct user count for each group accessor for each (workspace, role name) pair. It traverses the group hierarchy to calculate user totals.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) ACCESSOR_EID NOT NULL RAW(22) NUM_GROUP_USERS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) ACCESSOR_EID NOT NULL RAW(22) NUM_GROUP_USERS NUMBER
Suggested Queries for This View
Number of users for each accessor of type group for each (workspace, role) pair:
SELECT * FROM bee_wspc_role_groups_rv ORDER BY workspace_eid, role_name, accessor_eid;
Total number of group users for each (workspace, role) pair:
SELECT workspace_eid, role_name,
SUM(num_group_users) as num_group_users
FROM bee_wspc_role_groups_rv
GROUP BY workspace_eid, role_name
ORDER BY workspace_eid, role_name;
This view provides a total user count for each (workspace, role name) pair, including all users assigned both directly and indirectly by groups. Note that the group hierarchy is traversed to compute the user totals.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) TOTAL_ROLE_USERS NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) ROLE_NAME NOT NULL VARCHAR2(1000 CHAR) TOTAL_ROLE_USERS NUMBER
Suggested Queries for This View
Total number of users (both direct and from groups) for each (workspace,role) pair:
SELECT * from bee_wspc_role_users_rv ORDER BY workspace_eid, role_name;
Ten workspaces with the most workspace-coordinator members:
SELECT * FROM (SELECT * from bee_wspc_role_users_rv WHERE role_name = 'workspace-coordinator' ORDER BY total_role_users DESC) WHERE ROWNUM <= 10;
This view adds up the trash size for each workspace and can be used to determine trash size statistics across workspaces.
This sensitive view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_NAME NOT NULL VARCHAR2(1000 CHAR) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) TOTAL_TRASH_SIZE NUMBER
This regular view displays the following information:
Name Null? Type ----------------------------------------- -------- ---------------------------- WORKSPACE_EID NOT NULL RAW(22) WORKSPACE_TYPE NOT NULL VARCHAR2(4 CHAR) WORKSPACE_VISIBILITY NOT NULL CHAR(1 CHAR) TOTAL_TRASH_SIZE NUMBER
Suggested Queries for This View
Total space consumed by all trash items in each workspace:
SELECT workspace_eid, workspace_type, total_trash_size FROM bee_wspc_trash_totals_rv ORDER BY total_trash_size DESC;
Ten team workspaces with most trash space consumption:
SELECT * FROM (SELECT workspace_eid, workspace_type, total_trash_size FROM bee_wspc_trash_totals_rv WHERE workspace_type = 'wstm' ORDER BY total_trash_size DESC) WHERE ROWNUM <= 10;
Average space consumed by trash items per workspace:
SELECT AVG(total_trash_size) as average_trash_size FROM bee_wspc_trash_totals_rv;
Distribution percentile for total space consumed by trash items in each workspace. Percent of all workspaces with less than or equal trash space consumption:
SELECT workspace_eid, workspace_type, total_trash_size,
(CUME_DIST() OVER (ORDER BY total_trash_size NULLS FIRST)) as percentile
FROM bee_wspc_trash_totals_rv;
Total trash space consumption for median (or other percentile) workspace(s):
SELECT workspace_eid, workspace_type, total_trash_size, 0.50 as percentile
FROM bee_wspc_trash_totals_rv
WHERE total_trash_size = (SELECT (PERCENTILE_DISC(0.50) WITHIN GROUP
(ORDER BY total_trash_size ASC NULLS FIRST)) FROM bee_wspc_trash_totals_rv);
This section describes the following views:
This view provides information about client applications:
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NOT NULL NUMBER(5) DESCRIPTION VARCHAR2(4000) NAME VARCHAR2(1000 CHAR) ISPLATFORM CHAR(1 CHAR) VENDOR VARCHAR2(1000 CHAR) OS VARCHAR2(1000 CHAR) PROCESSOR VARCHAR2(1000 CHAR) DEVICECLASS VARCHAR2(1000 CHAR)
This view provides information about devices and their users:
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NOT NULL NUMBER(5) OWNER_EID NOT NULL RAW(22) OWNER_TYPE NOT NULL VARCHAR2(4) OWNER_SITE_ID NOT NULL NUMBER(5) CREATOR_EID NOT NULL RAW(22) CREATOR_TYPE NOT NULL VARCHAR2(4) CREATED_ON NOT NULL TIMESTAMP(0) NAME VARCHAR2(1000 CHAR) DEVICE_ID NOT NULL VARCHAR2(1000 CHAR) DEV_INF_DTD_VERSION VARCHAR2(1000 CHAR) SOFTWARE_VERSION VARCHAR2(1000 CHAR) OS VARCHAR2(256 CHAR) PROCESSOR VARCHAR2(256 CHAR) DEVICE_CLASS VARCHAR2(256 CHAR) MANUFACTURER VARCHAR2(256 CHAR) MODEL VARCHAR2(256 CHAR) DEVICE_INFO SYS.XMLTYPE STATUS VARCHAR2(20 CHAR)
This view provides information about device applications:
Name Null? Type ----------------------------------------- -------- ---------------------------- DEV_EID NOT NULL RAW(22) DEV_SITE_ID NOT NULL NUMBER(5) DEV_ENPR_ID NOT NULL NUMBER(5) DEV_DEVICE_ID NOT NULL VARCHAR2(1000 CHAR) APP_EID NOT NULL RAW(22) VER_VERSION VARCHAR2(1000 CHAR) VER_VERSIONNUM NOT NULL NUMBER PATCH_PATCHNUM NOT NULL NUMBER
This view provides information about device commands:
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NOT NULL NUMBER(5) CREATED_ON NOT NULL TIMESTAMP(6) EXECUTED_ON TIMESTAMP(0) EXECUTION_TIME NUMBER(20) ACTION NOT NULL VARCHAR2(32 CHAR) DEVICE_EID NOT NULL RAW(22) DEVICE_SITE_ID NOT NULL NUMBER(5) STATUS NOT NULL VARCHAR2(32 CHAR) STATUS_MSG VARCHAR2(1000 CHAR)
This view provides information about device logs:
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NOT NULL NUMBER(5) DEVICE_ID NOT NULL RAW(22) UPLOADED_ON TIMESTAMP(0) NAME NOT NULL VARCHAR2(500 CHAR) APP_NAME NOT NULL VARCHAR2(1000 CHAR) STREAMID RAW(22) LOG_SIZE NUMBER VISIBILITY_STATUS CHAR(1)
This view provides information about device profiles:
Name Null? Type ----------------------------------------- -------- ---------------------------- DEV_TYPE_EID NOT NULL RAW(22) DEV_TYPE_SITE_ID NOT NULL NUMBER(5) DEV_TYPE_ENPR_ID NOT NULL NUMBER(5) DEV_PROF_EID NOT NULL RAW(22) DEV_PROF_SITE_ID NOT NULL NUMBER(5) DEV_PROF_ENPR_ID NOT NULL NUMBER(5) DEV_INF_DTD_VERSION VARCHAR2(1000 CHAR) DEV_NAME VARCHAR2(1000 CHAR) DEV_TYPE_SOFTWARE_VERSION VARCHAR2(1000 CHAR) DEV_TYPE_OS VARCHAR2(256 CHAR) DEV_TYPE_PROCESSOR VARCHAR2(256 CHAR) DEV_TYPE_DEVICE_CLASS VARCHAR2(256 CHAR) DEV_TYPE_MANUFACTURER VARCHAR2(256 CHAR) DEV_TYPE_MODEL VARCHAR2(256 CHAR) DEV_TYPE_STATUS VARCHAR2(256 CHAR) DEV_PROF_NAME VARCHAR2(1000 CHAR)
This section describes the following views related to e-mail messages:
This view provides information about e-mail message headers:
Name Null? Type ----------------------------------------- -------- ---------------------------- MESSAGE_EID NOT NULL RAW(22) FROM_STR VARCHAR2(4000)
This view provides messages about e-mail messages:
Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER_EID NOT NULL RAW(22) FOLDER_EID NOT NULL RAW(22) MESSAGE_EID NOT NULL RAW(22) RECEIVED_DATE DATE MESSAGE_SIZE NUMBER SUBJECT NOT NULL VARCHAR2(1000 CHAR)
This section describes the view mds_delivery_status:
This view provides information about the delivery status of e-mail messages.
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NOT NULL NUMBER(5) OWNER_EID RAW(22) MSG_TYPE VARCHAR2(4) MSG_EID RAW(22) CHANNEL NOT NULL VARCHAR2(128 CHAR) STATUS NOT NULL VARCHAR2(128 CHAR)
This section describes the following views:
This view provides information about the types of data that users may synchronize:
Name Null? Type ----------------------------------------- -------- ---------------------------- STORE_ID NOT NULL RAW(22) USER_ID VARCHAR2(512) DEVICE_ID NOT NULL VARCHAR2(512) SERVER_STORE_URI VARCHAR2(128) TGUID NUMBER LUID VARCHAR2(1024) OPERATION_TYPE VARCHAR2(1) OPERATION_ORIGIN VARCHAR2(1) OPERATION_TIME TIMESTAMP(6)
This view provides information about the data itself that users synchronize:
Name Null? Type ----------------------------------------- -------- ---------------------------- STORE_ID NOT NULL RAW(22) DEVICE_ID NOT NULL VARCHAR2(512) USER_ID VARCHAR2(512) SERVER_STORE_URI VARCHAR2(128) CLIENT_STORE_URI VARCHAR2(128) SERVER_LAST_ANCHOR VARCHAR2(128) CLIENT_LAST_ANCHOR VARCHAR2(128) LAST_SYNC_TIME TIMESTAMP(6) MAN VARCHAR2(128) MODEL VARCHAR2(128)
This view provides information about when users synchronize their data:
Name Null? Type ----------------------------------------- -------- ---------------------------- SESSION_ID NOT NULL NUMBER CLIENT_SESSION_ID VARCHAR2(128) DEVICE_ID VARCHAR2(512) USER_ID VARCHAR2(512) SESSION_STATUS VARCHAR2(30) SESSION_START_TIME TIMESTAMP(6) SESSION_END_TIME TIMESTAMP(6) SESSION_LAST_ACCESS_TIME TIMESTAMP(6) SESSION_PROCESS_TIME NUMBER SESSION_AUTH_TYPE VARCHAR2(30) SESSION_AUTHENTICATED VARCHAR2(1) SYNC_PHASE VARCHAR2(512) SYNC_TYPE VARCHAR2(512) UPLOAD_BYTES NUMBER UPLOAD_ADD_COUNT NUMBER UPLOAD_DELETE_COUNT NUMBER UPLOAD_REPLACE_COUNT NUMBER DOWNLOAD_BYTES NUMBER DOWNLOAD_ADD_COUNT NUMBER DOWNLOAD_DELETE_COUNT NUMBER DOWNLOAD_REPLACE_COUNT NUMBER SESSION_MESSAGE CLOB
This view provides information about the devices users use to synchronize their data:
Name Null? Type ----------------------------------------- -------- ---------------------------- DEVICE_ID NOT NULL VARCHAR2(512) USER_ID VARCHAR2(512) SESSION_ID NUMBER MAN VARCHAR2(128) MODEL VARCHAR2(128) LAST_SYNC_TIME TIMESTAMP(6)
This view provides information about the SyncML logs:
Name Null? Type ----------------------------------------- -------- ---------------------------- REQUEST_ID NOT NULL NUMBER REQUEST_STATUS VARCHAR2(30) DEVICE_ID VARCHAR2(512) USER_ID VARCHAR2(512) SESSION_ID NUMBER CLIENT_SESSION_ID VARCHAR2(128) MESSAGE_ID NUMBER(38) REQUEST_TIME TIMESTAMP(6) REQUEST_META VARCHAR2(4000) RESPONSE_TIME TIMESTAMP(6) RESPONSE_META VARCHAR2(4000)
This section describes the following views:
This view provides information about resources:
Name Null? Type ----------------------------------------- -------- ---------------------------- EID NOT NULL RAW(22) ENTITY_TYPE NOT NULL VARCHAR2(4) SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NUMBER(5) NAME VARCHAR2(1000 CHAR) IDENTIFIER VARCHAR2(32 CHAR) RESOURCE_TYPE NOT NULL VARCHAR2(1 CHAR) CLASSIFICATION VARCHAR2(1000 CHAR) TIMEZONE_EID RAW(22) TIMEZONE_NAME VARCHAR2(1000 CHAR)
RESOURCE_TYPE may have a value of R (room), E (equipment), O (others), C (online conference) or A (audio conference).
Suggested Queries for This View
All resources and their time zones:
SELECT name, timezone_name FROM bee_resources_s_rv;
This view provides information about bookings of resources:
Name Null? Type ----------------------------------------- -------- ---------------------------- RESOURCE_EID NOT NULL RAW(22) ENTITY_TYPE NOT NULL VARCHAR2(4 CHAR) RESOURCE_SITE_ID NOT NULL NUMBER(5) ENTERPRISE_ID NOT NULL NUMBER(5) OCCURRENCE_TYPE NOT NULL VARCHAR2(1 CHAR) STARTTIME_TIMESTAMP NOT NULL TIMESTAMP(6) STARTTIME_IS_DATEONLY VARCHAR2(1 CHAR) STARTTIME_IS_FLOATING VARCHAR2(1 CHAR) ENDTIME_TIMESTAMP NOT NULL TIMESTAMP(6) ENDTIME_IS_DATEONLY VARCHAR2(1 CHAR) ENDTIME_IS_FLOATING VARCHAR2(1 CHAR) EFFECTIVE_INVITEE_TRANSPARENCY VARCHAR2(2 CHAR)
OCCURRENCE_TYPE should be M. EFFECTIVE_INVITEE_TRANSPARANCY is either Op (for Opaque which blocks time) or Te (for Tentative).
Suggested Queries for This View
All bookings related to the resource room1:
SELECT res.name,bookings.* FROM bee_resources_s_rv res,bee_resource_bookings_s_rv bookings WHERE res.name='room1' AND res.eid=bookings.resource_eid;
All bookings in the last twenty four hours:
SELECT res.name,bookings.*
FROM bee_resources_s_rv res,bee_resource_bookings_s_rv bookings
WHERE res.eid=bookings.resource_eid
AND bookings.starttime_timestamp >
SYS_EXTRACT_UTC(SYSTIMESTAMP) - NUMTODSINTERVAL(1,'DAY')
AND bookings.endtime_timestamp <= SYS_EXTRACT_UTC(SYSTIMESTAMP);
Note:
Bookings for RESOURCE_TYPE of valueC (online conference), or A (audio conference) will not be visible due to the special nature of these resource types.This section describes the following views:
This view provides information about address books:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NOT NULL NUMBER(5) TYPE NOT NULL VARCHAR2(4 CHAR) EID NOT NULL RAW(22) CREATED_ON NOT NULL TIMESTAMP(6) MODIFIED_ON TIMESTAMP(6) VISIBILITY VARCHAR2(1 CHAR) PARENT_SITE_ID NUMBER(5) PARENT_TYPE VARCHAR2(4 CHAR) PARENT_EID NOT NULL RAW(22)
This view provides information about contacts:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NUMBER(5) SITE_ID NUMBER(5) TYPE VARCHAR2(4 CHAR) EID RAW(22) CREATED_ON TIMESTAMP(6) MODIFIED_ON TIMESTAMP(6) VISIBILITY VARCHAR2(1 CHAR) PARENT_VISIBILITY CHAR(1 CHAR) PARENT_SITE_ID NUMBER(5) PARENT_TYPE VARCHAR2(4 CHAR) PARENT_EID RAW(22) DATA_SIZE NUMBER(38) PEOPLE_LIST_ENTRY VARCHAR2(1 CHAR) BOOKMARK_SITE_ID NUMBER(5) BOOKMARK_TYPE VARCHAR2(4 CHAR) BOOKMARK_EID RAW(22)
This view provides information about external users:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NOT NULL NUMBER(5) PARENT_SITE_ID NUMBER(5) EID NOT NULL RAW(22) VISIBILITY VARCHAR2(1) CREATED_ON TIMESTAMP(6) MODIFIED_ON TIMESTAMP(6)
This view provides information about groups:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NOT NULL NUMBER(5) EID NOT NULL RAW(22) TYPE NOT NULL VARCHAR2(4) VISIBILITY VARCHAR2(1) CREATED_ON TIMESTAMP(6) MODIFIED_ON TIMESTAMP(6)
This view provides information about group contact members:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NUMBER(5) GROUP_SITE_ID NUMBER(5) GROUP_TYPE VARCHAR2(4 CHAR) GROUP_EID NOT NULL RAW(22) MEMBER_SITE_ID NUMBER(5) MEMBER_TYPE VARCHAR2(4 CHAR) MEMBER_EID NOT NULL RAW(22)
This view provides information about group members:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) GROUP_SITE_ID NOT NULL NUMBER(5) GROUP_TYPE NOT NULL VARCHAR2(4) GROUP_EID NOT NULL RAW(22) MEMBER_SITE_ID NOT NULL NUMBER(5) MEMBER_TYPE NOT NULL VARCHAR2(4) MEMBER_EID NOT NULL RAW(22)
This view provides information about LDAP synchronization profiles:
Name Null? Type ----------------------------------------- -------- ---------------------------- PROFILEID NOT NULL VARCHAR2(256) CHANGEID VARCHAR2(256) CHANGETIME DATE
These views provide information about users:
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NOT NULL NUMBER(5) PARENT_SITE_ID NUMBER(5) EID NOT NULL RAW(22) VISIBILITY VARCHAR2(1) IS_EXTERNAL_INBOX VARCHAR2(1) IS_EXTENDED_ENTERPRISE_USER VARCHAR2(1) CREATED_ON TIMESTAMP(6) MODIFIED_ON TIMESTAMP(6)
Name Null? Type ----------------------------------------- -------- ---------------------------- ENTERPRISE_ID NOT NULL NUMBER(5) SITE_ID NUMBER(5) TYPE CHAR(4) EID RAW(22) LOGIN_ID VARCHAR2(1000 CHAR)
Suppose you want to create an application that allows non-administrators (such as upper-level management) to view statistical reports on information from Oracle Business Views. In this situation, create a database schema that has access only to Oracle Beehive Views. Your application accesses Oracle Beehive views with this schema. This will prevent non-administrators accessing or modifying sensitive data in your Oracle Beehive database.
Follow these steps to create a database schema that has access only to Oracle Business Views:
Obtain the enterprise ID of your Oracle Beehive deployment. In the following example, the EnterpriseId is 13131.
beectl list_properties --component _CURRENT_SITE --name EnterpriseId -----------------------+------------------------------------------------ Property name | Property value -----------------------+------------------------------------------------ EnterpriseId | 13131 -----------------------+------------------------------------------------
As a user with SYSDBA privileges, create a new tablespace in the Oracle Beehive Database. This example creates a tablespace named BEE_RVIEWS_TBLSPC:
sqlplus /nolog SQL> connect SYS/<Password of SYS user> as SYSDBA Connected SQL> create tablespace BEE_RVIEWS_TBLSPC;
As a user with SYSDBA privileges, run the manage_reporting_user.sql script.
In the following example, BEE_RVIEWS is the name of the new schema. Welcome1 is the password for the new schema. TEMP is the name of the default temporary tablespace (you may specify another temporary tablespace). 13131 is the EnterpriseId of your Oracle Beehive deployment. Line breaks have been inserted in the following example for clarity.
SQL> @<Oracle Beehive home>/beehive/db/framework/ manage_reporting_user.sql create BEE_RVIEWS Welcome1 BEE_RVIEWS_TBLSPC TEMP 13131
|
 Copyright © 2008, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|