Resolution:
BWC installation scripts create tables, views and sequences required for BWC in the database. For this, the BWC installation document mentions:
**
For Oracle, this user must have connect and resource privileges. (TIBCO BunisessWorks Collaboratot -> Installation Guide -> Chapter 2 Installation Steps -> Installing TIBCO BusinessWorks Collaborator on All Platforms)
**

For some security consideration, Oracle has changed the right scheme of CONNECT role in Oracle 10G Release 2.

The privileges for the CONNECT role in the prior releases of Oracle were as follows:

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
---------------------------------------------------------------------------------------

In Oracle 10G Release2 the privileges have been changed and only CREATE SESSION is included in CONNECT Role:

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO
-------------------------------------------------------------------------------

If you are using Oracle 10G R2 with BWC, the installation scripts are not able to create the required views if only CONNECT and RESOURCE roles have been assigned to the BWC – Oracle user. Since the appropriate installation is not able to complete, BWC then fails to execute.

To resolve this issue, after granting the CONNECT and RESOURCE roles, you need to grant those privileges for the BWC-Oracle user explicitly, that were a part of CONNECT / RESOURCE roles in the prior versions of Oracle, and are not granted with CONNECT and RESOURCE roles in Oracle 10G R2. You need to grant all the required privileges before BWC installation.  

Please contact your Oracle DBA for more details on Oracle roles and privileges.

With BWC installation using Oracle 10G Release 2, the following error is encountered and workflow server instance is not able to run properly:"ORA-01031: insufficient privileges"