Oracle Beehive Business Views, Release 2 (2.0.1.8)

Release 2 (2.0.1.8)

E16654-05

Contents

List of Examples

Title and Copyright Information

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

1 Oracle Beehive Business Views

- Overview of Business Views

- Accessing a View

- Saving a View

- Types of Views

- Regular Reporting Views

- Sensitive Reporting Views

- Workspace Service Views

- bee_enterprises

- bee_enterprise_bonds

- bee_enterprise_users

- bee_organizations

- bee_team_wspc_members

- bee_user_markers

- bee_user_marker_totals

- bee_workspaces

- bee_workspace_documents

- bee_workspace_trash

- bee_wspc_member_roles

- bee_wspc_role_groups

- bee_wspc_role_users

- bee_wspc_trash_totals

- Device Management Service Views

- dm_applications

- dm_devices

- dm_device_apps

- dm_device_cmds

- dm_device_logs

- dm_dev_profs_to_dev_types

- Collaboration Service Views

- es_message_headers

- es_message

- Message Delivery Service Views

- mds_delivery_status

- Mobility Service Views

- oma_ds_data_operations

- oma_ds_data_stores

- oma_ds_sessions

- oma_ds_user_devices

- oma_syncml_logs

- Resource Directory Services Views

- bee_resources

- bee_resource_bookings

- User Directory Service Views

- uds_address_books

- uds_contacts

- uds_external_persons

- uds_groups

- uds_group_contact_members

- uds_group_members

- uds_sync_profile

- uds_users

- Creating Schema with Access Only to Oracle Beehive Views

List of Examples

- 1-1 Information In a Regular View

- 1-2 Information In a Sensitive View

Oracle® Beehive

Business Views

Release 2 (2.0.1.8)

E16654-05

August 2013

Documentation that describes the business reporting features of Oracle Beehive.

Oracle Beehive Business Views, Release 2 (2.0.1.8)

E16654-05

Copyright © 2008, 2013, Oracle and/or its affiliates. All rights reserved.

Primary Author:  Sonia.Nagar

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This guide describes the business reporting features of Oracle Beehive.

Audience

This document is intended for users of Oracle Beehive Release 2.0.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

For more information, see the following documents in the Oracle Beehive Release 2 (2.0) documentation library:

Administration Guides

- Oracle® Beehive Administrator's Guide

- Oracle® Beehive Administrator's Reference Guide

- Oracle® Beekeeper Online Help (Integrated UA)

- Oracle® Beehive Integration Guide

Application Development

- Oracle® Beehive Application Developer's Guide

- Oracle® Beehive Java Content Repository Java API Reference

- Oracle® Beehive RESTful Web Services API Reference

- Oracle® Beehive SOAP Web Services API Reference

Installation Guides

- Oracle® Beehive Installation Guide for Linux

- Oracle® Beehive Installation Guide for Microsoft Windows

- Oracle® Beehive Installation Guide for Solaris Operating System

- Oracle® Beehive Installation Help (Integrated UA)

Online Helps

- Oracle® Beehive Central

- Oracle® Beehive Webmail

- Oracle® Beehive Standards-based Clients

- Oracle® Beehive Team Collaboration

- Oracle® Beehive Conferencing

- Oracle® Beehive Extensions for Outlook Supplemental Help and Release Notes

- Oracle® Beehive Extensions for Explorer Supplemental Help and Release Notes

- Oracle® Beehive Extensions for Explorer (OBEE) (Integrated UA)

- Oracle® Beehive Extensions for Outlook (OBEO) (Integrated UA)

Mobile Devices

- Oracle® Beehive Using Windows Mobile Device

- Oracle® Beehive Using iPhone or iPad

- Oracle® Beehive Using BlackBerry

- Oracle® Beehive Registering and Configuring Mobile Devices

Planning Guides

- Oracle® Beehive Concepts

- Oracle® Beehive Deployment Guide

- Oracle® Beehive Licensing Information

Release Notes

- Oracle® Beehive Release Notes

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Oracle Beehive Business Views

This chapter describes Oracle Beehive Business Views. It includes the following sections:

- Overview of Business Views

- Types of Views

- Workspace Service Views

- Device Management Service Views

- Collaboration Service Views

- Message Delivery Service Views

- Mobility Service Views

- Resource Directory Services Views

- User Directory Service Views

- Creating Schema with Access Only to Oracle Beehive Views

Overview of Business Views

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:

- Accessing a View

- Saving a View

Accessing a View

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:

1. Start SQL*Plus at the command prompt:

prompt> sqlplus /NOLOG

2. Connect to Oracle Database as the BEE_CODE user:

SQL> CONNECT BEE_CODE/PASSWORD

3. 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

4. The output of the command is as follows:

Name                                      Null?    Type
----------------------------------------- -------- -------------------------
WORKSPACE_EID                             NOT NULL RAW(22)
WORKSPACE_TYPE                                     CHAR(4)
NUM_MEMBERS                                        NUMBER

Performance Issues

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.

Saving a View

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 to SPOOL.

For example, to append the output to the existing file NOTES, use the following command:

SPOOL NOTES APPEND

See Also:
SQL*Plus User's Guide and Reference for more information about the SPOOL command

Types of Views

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.

Regular Reporting Views

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.

Example 1-1 Information In a Regular View

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

Sensitive Reporting Views

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.

See Also:
Chapter 4, "Configuring Privilege and Role Authorization" in Oracle Database Security Guide.

Workspace Service 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:

- bee_enterprises

- bee_enterprise_bonds

- bee_enterprise_users

- bee_organizations

- bee_team_wspc_members

- bee_wspc_member_roles

- bee_wspc_role_groups

- bee_wspc_role_users

- bee_wspc_trash_totals

- bee_user_markers

- bee_user_marker_totals

- bee_workspaces

- bee_workspace_documents

- bee_workspace_trash

bee_enterprises

This view provides a workspace count, organization count, quota usage, quota state, and basic properties such as name, EID, and visibility for all enterprises.

bee_enterprises_s_rv

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

bee_enterprises_rv

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

1. Total space consumed by all enterprises:

SELECT SUM(quota_used) FROM bee_enterprises_rv;

2. 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';

3. Average quota usage (in bytes) per enterprise:

SELECT AVG(quota_used) FROM bee_enterprises_rv;

4. Space consumed by each enterprise:

SELECT enterprise_eid, quota_used, over_quota
FROM   bee_enterprises_rv;

5. Number of enterprises over hard quota limits:

SELECT COUNT(*) FROM bee_enterprises_rv
WHERE  over_quota = 'Y';

6. Number of organizations per enterprise:

SELECT enterprise_eid, num_organizations FROM bee_enterprises_rv;

7. Number of workspaces per enterprise:

SELECT enterprise_eid, num_workspaces FROM bee_enterprises_rv;

8. Average number of organizations per enterprise:

SELECT AVG(num_organizations) FROM bee_enterprises_rv;

9. Average number of workspaces per enterprise:

SELECT AVG(num_workspaces) FROM bee_enterprises_rv;

bee_enterprise_bonds

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.

bee_enterprise_bonds_s_rv

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)

bee_enterprise_bonds_rv

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

1. 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;

2. 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;

3. 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;

4. 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);

5. 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;

6. 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;

bee_enterprise_users

This view provides a user count for all enterprises.

bee_enterprise_users_s_rv

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

bee_enterprise_users_rv

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

1. Average number of users per enterprise:

SELECT AVG(num_users) FROM bee_enterprise_users_rv;

2. 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;

bee_organizations

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.

bee_organizations_s_rv

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)

bee_organizations_rv

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

1. Total space consumed by all organizations:

SELECT SUM(quota_used) FROM bee_organizations_rv;

2. Average quota usage (in bytes) per organization:

SELECT AVG(quota_used) FROM bee_organizations_rv;

3. Average quota usage (in bytes) per leaf organization:

SELECT AVG(quota_used) FROM bee_organizations_rv
WHERE  has_child_orgs = 'N';

4. Average quota usage (in bytes) per root organization:

SELECT AVG(quota_used) FROM bee_organizations_rv
WHERE  is_toplevel_org = 'Y';

5. Number of organizations over hard quota limits:

SELECT COUNT(*) FROM bee_organizations_rv
WHERE  over_quota = 'Y';

6. Total number of workspaces in each organization:

SELECT organization_eid, total_workspaces FROM bee_organizations_rv;

7. Number of direct child workspaces in each organization:

SELECT organization_eid, child_workspaces FROM bee_organizations_rv;

8. Number of workspaces in each root organization:

SELECT organization_eid, total_workspaces FROM bee_organizations_rv
WHERE  is_toplevel_org = 'Y';

9. Average number of workspaces per organization:

SELECT AVG(total_workspaces) FROM bee_organizations_rv;

10. Average number of direct child workspaces per organization:

SELECT AVG(child_workspaces) FROM bee_organizations_rv;

11. Average number of workspaces per leaf organization:

SELECT AVG(total_workspaces) FROM bee_organizations_rv
WHERE has_child_orgs = 'N';

12. 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;

13. 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);

bee_team_wspc_members

This view provides a count of all distinct user-typed members for all team workspaces, traversing the group hierarchy as needed.

bee_team_wspc_members_s_rv

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

bee_team_wspc_members_rv

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

1. Average number of user members per team workspace:

SELECT AVG(num_members) FROM bee_team_wspc_members_rv;

2. 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;

3. 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;

4. 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);

bee_user_markers

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.

bee_user_markers_s_rv

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)

bee_user_markers_rv

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

1. 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;

2. 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;

bee_user_marker_totals

This view provides a total marker count per user.

bee_user_marker_totals_s_rv

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

bee_user_marker_totals_rv

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

1. Total number of markers defined or owned by each user:

SELECT * FROM bee_user_marker_totals_rv
ORDER BY num_markers DESC;

2. Average number of markers defined or owned by each user:

SELECT AVG(num_markers) FROM bee_user_marker_totals_rv;

3. 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;

4. 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);

bee_workspaces

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.

bee_workspaces_s_rv

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

bee_workspaces_rv

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

1. Total number of workspaces:

SELECT COUNT(*) AS total_workspaces FROM bee_workspaces_rv;

2. Total number of team workspaces:

SELECT COUNT(*) AS team_workspaces FROM bee_workspaces_rv
WHERE  workspace_type = 'wstm';

3. Total number of personal workspaces:

SELECT COUNT(*) AS personal_workspaces FROM bee_workspaces_rv
WHERE  workspace_type = 'wspr';

4. Number of workspaces exceeding soft quota limits:

SELECT COUNT(*) AS workspaces_over_soft_quota FROM bee_workspaces_rv
WHERE  quota_state = 'S';

5. 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';

6. 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';

7. Number of workspaces exceeding hard quota limits:

SELECT COUNT(*) AS workspaces_over_hard_quota FROM bee_workspaces_rv
WHERE  quota_state = 'H';

8. 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';

9. 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';

10. Total space utilization by all workspaces:

SELECT SUM(quota_used) AS total_size FROM bee_workspaces_rv;

11. 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';

12. 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;

13. 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;

14. 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;

15. 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;

16. 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;

17. 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);

bee_workspace_documents

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.

bee_workspace_documents_s_rv

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)

bee_workspace_documents_rv

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

1. 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;

2. 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;

3. 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;

4. 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;

5. 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;

6. 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;

7. 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;

8. 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;

9. 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;

10. 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

bee_workspace_trash

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.

bee_workspace_trash_s_rv

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)

bee_workspace_trash_rv

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

1. 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;

2. 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;

3. 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;

4. 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;

bee_wspc_member_roles

This view lists every direct accessor and accessor type for each (workspace, role name) pair. It does not traverse the group hierarchy.

bee_wspc_member_roles_s_rv

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)

bee_wspc_member_roles_rv

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

1. 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;

2. 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;

bee_wspc_role_groups

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.

bee_wspc_role_groups_s_rv

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

bee_wspc_role_groups_rv

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

1. 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;

2. 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;

bee_wspc_role_users

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.

bee_wspc_role_users_s_rv

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

bee_wspc_role_users_rv

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

1. 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;

2. 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;

bee_wspc_trash_totals

This view adds up the trash size for each workspace and can be used to determine trash size statistics across workspaces.

bee_wspc_trash_totals_s_rv

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

bee_wspc_trash_totals_rv

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

1. 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;

2. 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;

3. Average space consumed by trash items per workspace:

SELECT AVG(total_trash_size) as average_trash_size
FROM   bee_wspc_trash_totals_rv;

4. 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);

Device Management Service Views

This section describes the following views:

- dm_applications

- dm_devices

- dm_device_apps

- dm_device_cmds

- dm_device_logs

- dm_dev_profs_to_dev_types

dm_applications

This view provides information about client applications:

dm_applications_rv

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)

dm_devices

This view provides information about devices and their users:

dm_devices_rv

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)

dm_device_apps

This view provides information about device applications:

dm_device_apps_rv

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

dm_device_cmds

This view provides information about device commands:

dm_device_cmds_rv

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)

dm_device_logs

This view provides information about device logs:

dm_device_logs_rv

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)

dm_dev_profs_to_dev_types

This view provides information about device profiles:

dm_dev_profs_to_dev_types_rv

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)

Collaboration Service Views

This section describes the following views related to e-mail messages:

- es_message_headers

- es_message

es_message_headers

This view provides information about e-mail message headers:

es_message_headers_rv

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
MESSAGE_EID                               NOT NULL RAW(22)
FROM_STR                                           VARCHAR2(4000)

es_message

This view provides messages about e-mail messages:

es_message_rv

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)

Message Delivery Service Views

This section describes the view mds_delivery_status:

mds_delivery_status

This view provides information about the delivery status of e-mail messages.

mds_delivery_status_rv

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)

Mobility Service Views

This section describes the following views:

- oma_ds_data_operations

- oma_ds_data_stores

- oma_ds_sessions

- oma_ds_user_devices

- oma_syncml_logs

oma_ds_data_operations

This view provides information about the types of data that users may synchronize:

oma_ds_data_operations_rv

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)

oma_ds_data_stores

This view provides information about the data itself that users synchronize:

oma_ds_data_stores_rv

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)

oma_ds_sessions

This view provides information about when users synchronize their data:

oma_ds_sessions_rv

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

oma_ds_user_devices

This view provides information about the devices users use to synchronize their data:

oma_ds_user_devices_rv

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)

oma_syncml_logs

This view provides information about the SyncML logs:

oma_syncml_logs_rv

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)

Resource Directory Services Views

This section describes the following views:

- bee_resources

- bee_resource_bookings

bee_resources

This view provides information about resources:

bee_resources_s_rv

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;

bee_resource_bookings

This view provides information about bookings of resources:

bee_resource_bookings_s_rv

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

1. 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;

2. 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 value C (online conference), or A (audio conference) will not be visible due to the special nature of these resource types.

User Directory Service Views

This section describes the following views:

- uds_address_books

- uds_contacts

- uds_external_persons

- uds_groups

- uds_group_contact_members

- uds_group_members

- uds_sync_profile

- uds_users

uds_address_books

This view provides information about address books:

uds_address_books_rv

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)

uds_contacts

This view provides information about contacts:

uds_contacts_rv

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)

uds_external_persons

This view provides information about external users:

uds_external_persons_rv

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)

uds_groups

This view provides information about groups:

uds_groups_rv

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)

uds_group_contact_members

This view provides information about group contact members:

uds_group_contact_members_rv

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)

uds_group_members

This view provides information about group members:

uds_group_members_rv

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)

uds_sync_profile

This view provides information about LDAP synchronization profiles:

uds_sync_profile_rv

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PROFILEID                                 NOT NULL VARCHAR2(256)
CHANGEID                                           VARCHAR2(256)
CHANGETIME                                         DATE

uds_users

These views provide information about users:

uds_users_rv

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)

uds_users_s_rv

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ENTERPRISE_ID                             NOT NULL NUMBER(5)
SITE_ID                                            NUMBER(5)
TYPE                                               CHAR(4)
EID                                                RAW(22)
LOGIN_ID                                           VARCHAR2(1000 CHAR)

Creating Schema with Access Only to Oracle Beehive Views

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:

1. 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
-----------------------+------------------------------------------------

2. 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;

3. 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.