Oracle Business Intelligence Application 7.9.6.3 Customization Types, Tips and Best Practice Guidelines – Part A

I would like to share some of the Oracle Business Intelligence Application (OBIA) customization tips and best practices guidelines from my knowledge base. This document is based on my experiences of BI Apps implementation over the period of last 10/11 years since Siebel days and extract from Oracle’s standard documentation around BI Apps.

OBIA Architecture

OBIA Architecture has following components i.e. ETL Architecture, Data Model, Metadata (Repository), Dashboard/Webcat along with security.

OBIA Pre-Built Contents

Oracle Business Intelligence Applications 7.9.6.3 are pre-built solutions from source to dashboard. The pre-built solution contents are:   

Pre-built Analytics Data Warehouse Model

The Business Analytics Data Warehouse is the persistent layer where information is stored for retrieval when required. The data warehouse resides in a relational database platform like Oracle, IBM DB2 LUW, Teradata or Microsoft SQL Server. It consists of transaction grain tables used to capture facts from the key business processes across the organization. The model consists of current snapshots, periodic snapshots and aggregate tables to support complex analysis requirements along with the hundreds of dimension tables conformed across the fact tables which contribute to enrich the analyses.

Pre-built Data Integration Components (ETL/E-LT)  

Data Integration components ( data extraction routines) are used to integrate data from various operational systems such as E-Business Suite, PeopleSoft Enterprise, Oracle's Siebel CRM Applications, Oracle JD Edwards Applications, and third party legacy systems, and load the information in the Business Analytics Data Warehouse.

The data integration components are built using either the Informatica Power Center data integration platform (ETL) or Oracle Data Integrator (E-LT). It uses a layered approach based on data warehouse best practices, which is open and flexible which provides ease of configuration and customization to enable changing the out of the box transformation rules, adding additional attributes, facts and dimensions.

Pre-built Metadata (BI Repository)

The metadata layer includes the definition of role based views, dimensions, metrics, hierarchies, calculations, aggregation rules, canonical dime dimensions, time series metrics, roles and security. The metadata layer is built on the Oracle Business Intelligence Enterprise Edition platform using the Admin Tool, a core component of the BI server, which is designed to meet the requirements of the Common Information Model to deliver consistent and integrated information to business users, by capturing the data from multiple source systems. Oracle Business Intelligence Applications provides ready to use pre-built metadata covering dimensions, metrics and hierarchies. In addition, the pre-built metadata includes the capability to leverage the security profiles inherited from operational systems such as Oracle EBS, PeopleSoft and JD Edwards.

Pre-built OBI-EE dashboard and report content (Catalog)

The OBIEE Presentation Catalog contains pre-built metrics, analytic decision workflows, reports, dashboards and alerts used to deliver information, stored in the Business Analytics Data Warehouse, to information consumers. These services are built on the Oracle Business Intelligence Enterprise Edition delivery platform, which is designed to meet the requirements of an enterprise business intelligence solution. Pre-built reports and dashboards are built on industry best practices, and provide rich content covering functional areas including sales, service, marketing, finance, supply chain, order management, procurement and human resources.

Customization Best Practice – Keep it to Minimum

In line with other Oracle applications products in general, customization should be kept to minimum and wherever possible out-of-the-box (OOTB) functionality should be employed.  This should be done in such manner that future upgrades to the product will not require significant amount of rework, i.e. where there is a good match between the requirements and the out-of-the-box functionality. However, in reality it is often unavoidable. 

Customization Reality – It is Unavoidable

It is often seen that certain key business requirements are not covered by the pre-built contents (Out of Box contents), For example the OOTB Data Warehouse may not include certain attributes customer would like to report on from the source system, or more likely, require an external secondary data store (file, RDBMS, XML, and OLAP) to include additional reporting matrices.

For a rapid development we must re-use as much of the OOTB data model as possible to avoid “re-inventing the wheel” by developing data objects that have already been populated by BI-Apps. There are number of situations where there is a need to modify the pre-built contents.

In these cases it is important to follow certain guidelines and techniques when developing non-standard BI elements. This blog is an attempt to detail guidelines on how to develop custom objects to integrate into BI Apps. The idea is to follow the same design techniques and guidelines that BI Apps uses to build OOTB contents for customization. This approach would be helpful to maintain the oracle’s high standard development practices and keep “look and feel” like standard BI-Apps content.

Types of Customization and Impacts

There are 6 types of customizations that will be implemented.  These customizations can be as simple as exposing already existing attributes in the DW to the Presentation Layer of OBI-EE.  They can also be as complex as creating new dimension, facts and external data sources. The new report creation is not in scope of the this discussion.

The following are the main extensions covered in this blog.

TYPE A- Simple Extensions and Adding Simple/Complex Logical Definition

The Data Warehouse has a vast amount of attributes within its data model.  However, not all this data is exposed to the repository and dashboards.  One of the simplest customizations is to expose the attribute top the Presentation Layer of OBI-EE.

Creating new logical definitions (simple-moderate-complex) based on physical/logical columns already exposed should also be included into the Type A category. Time Series Logical columns are an example of this type of customization.  

Impact:

·     Changes only in the OBI-EE repository in the Physical, Logical and Presentation Layer are needed.

TYPE B- Attribute Extensions

Adding attributes to existing dimensions and measures to existing facts.  The complexity of this customization depends on the SQL required to populate the attribute.  If the attribute is part of an existing table in an existing query then the inclusion is very simple.

Impact: 

• Additional column(s) to the physical staging and target tables.
• Changing existing SDE source mapping to add the additional column(s) to the SQL query and the target table.
• Changing existing SIL target mapping to add the additional column(s) to the SQL extraction query to the target table.
• Changes required to the OBI-EE repository to add the column to the Physical and modeled through to the Business and Presentation Layers.

TYPE C- Additional Fact, Parent Child Hierarchies Table, Bridge Table

Adding new Facts, Parent Child Hierarchies (value hierarchies) or Bridge tables (a technique for modeling Many-to-Many relationships) will require new SDE mappings for extraction to the DW staging area and new SIL loading routines from the staging area to the DW 

Impact:

• Additional physical tables created in the staging and DW areas.
• Add new SDE source mapping to populate the target staging table
• Add new SIL target mapping to populate the DW target table
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Optionally applying security filtering.
• Adding new SDE and SIL to the DAC dependency

TYPE D- Localization of Oracle Business Intelligence (Repository Metadata, Oracle BI Data or Report and Dashboard)

Localization of Oracle Business Intelligence requires three configurations i.e. localization of Oracle BI Repository Metadata, localization of Oracle BI Data and localization of Reports and Dashboards. These configurations require configuration file changes, creating metadata objects to support it which includes initialization block, externalize metadata objects etc , DW tables and routine to populate the language dependent data.

Impact:

• Additional translation tables/ Modification to existing columns in staging and DW areas.
• Add/Modify SDE source mapping to populate the target staging table.
• Add/Modify SIL target mapping to populate the DW target table.
• Session Variables and Configuration Settings and creation of supporting metadata objects
• Import the data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Adding new SDE and SIL to the DAC dependency if required  

TYPE E- Fact Granularity Change

Add new Dimensions to an existing Fact requires changes to existing SDE and SIL mappings for the Fact table.  In addition, SDE and SIL mappings are required to implement new dimensions.

Impact:

• Additional physical dimension table created in the staging & DW areas.
• Additional key fields to staging and DW fact tables.
• Add new SDE source mapping to populate the new target staging table.
• Add new SIL target mapping to populate the DW target table.
• Changing existing SDE source mapping to add the additional keys staging fact table.
• Changing existing SIL target mapping to add the additional key column(s) to the DW target fact table.  Also, include the key lookup to the new dimension.
• Create SDE & SIL workflows.
• Create DAC tasks with required dependencies.
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Changes required to the OBI-EE repository to model new Physical model and modeled through to the Business and Presentation Layers.
• Optionally applying security filtering.

TYPE F- New Star Schema

New facts and some new dimensions integrated with existing dimensions.

Impact:

• Additional physical dimension and fact table(s) created in the staging & DW areas.
• For each new physical dimension table and fact table a new SDE source mapping to populate the new target staging tables.
• For each new physical dimension table and fact table a new SIL target mapping to populate the DW target table.
• Create SDE & SIL workflows
• Create DAC tasks with required dependencies.
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Optionally applying security filtering.

TYPE G- Additional Source Systems or Using Universal Adaptors

Adding a second home-grown or non-supported data source.  OOTB BI-Apps has one supported data source.  However, it is possible that you want to integrate other data sources into the Business Analytic Warehouse (BAW).  Additional sources can include simple spreadsheets to interlacing data into existing data models. 

Interlacing is a method of loading data into the existing data model without matching & merging into the supported data source.  For example, there might be Purchase Orders from a different ERP system that you want to provide integrated reporting with eBS.  In this case you will need to integrate dimensional and fact data.

Another example is to use the universal adaptors to push the data into the BI Apps. The universal adaptors implementation lists following impacts.

Impact:

• Additional columns created for additional attributes in dimension and fact table(s) created in the staging & DW areas.
• Modify the universal adaptor SDE mapping &  SIL mappings to include additional attributes if any
• Create SDE workflow
• Create DAC tasks with required dependencies.
• Import the new columns data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.

In the next blog of this series I would like to cover the naming conventions and development guidelines that should be followed when developing new attributes, tables, dimensions and facts in the Business Analytics Warehouse (BAW) and new/changed repository objects in the OBIEE repository.

Read 2nd Article on topic :

http://bidirect.blogspot.co.uk/2013/01/oracle-bi-apps-7963-tips-and-best.html

Oracle Database 11g Data Dictionary Reports – Part A – Database, Database Parameters and Sessions Reports

Data Dictionary is one of the most important parts of oracle database. The data dictionary is a read-only set of tables which provides information about database. Information and knowledge about the database and data is a key asset for BI Architect/Developer.

This is an attempt to provide introduction to the oracle data dictionary and set of standard data dictionary reports (with SQL syntax) which might be useful for better understanding of the data/database.

I would also like to write about the option to provide a subject area and set of reports within OBIEE 11g around the data dictionary reports in my future blogs.

Oracle Data Dictionary

A data dictionary tables and views which are stored in database’s SYSTEM tablespace contains

·         The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)

·         How much space has been allocated for, and is currently used by, the schema objects

·         Default values for columns

·         Integrity constraint information

·         The names of Oracle Database users

·         Privileges and roles each user has been granted

·         Auditing information, such as who has accessed or updated various schema objects

·         Other general database information

Data Dictionary Reports

The data dictionary reports about the database and its objects can be grouped into the following categories.

·         Database Reports

·         Database Administration Reports

·         Table reports

·         PL/SQL reports

·         Security Reports

·         XML reports

·         Jobs Reports

·         Streams Reports

·         All Objects Reports

·         Data Dictionary Reports

·         OLAP reports

This blog is an attempt to provide insight about the database parameters and session reports build by using database dictionary. User can create many more reports as and when needed to improve the understanding about overall database/data.

Note – You do need the right access to execute these reports provided below.

Database Reports

National Language Support Parameters

SQL Script

Select parameter "Parameter", value "Value" from v$nls_parameters order by 1

Standard Output

National Language Support Parameters

SQL Script

Select banner "Your_Database_Settings" from v$version

Standard Output

Database Administration Reports

Database Parameters

All Parameters

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5, 'Reserved', 6, 'Big Integer') type,

  value "Value",

  isdefault "Default",

  isses_modifiable "Session_Modifiable",

  issys_modifiable "System_Modifiable",

  description "Description"

FROM v$parameter

WHERE (:PARAMETER_NAME                      IS NULL

OR instr(lower(name),lower(:PARAMETER_NAME)) > 0)

AND SUBSTR(name,1,2)                        != '__'

ORDER BY name

Standard Output

Non- Default Parameters

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5, 'Reserved', 6, 'Big Integer') type,

  value "Value",

  isses_modifiable "Session_Modifiable",

  issys_modifiable "System_Modifiable",

  description "Description"

FROM v$parameter

WHERE (:PARAMETER_NAME                      IS NULL

OR instr(lower(name),lower(:PARAMETER_NAME)) > 0)

AND isdefault = 'FALSE'

AND SUBSTR(name,1,2)!= '__'

ORDER BY name

Standard Output

Locks by User

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5,

SELECT p.username username ,

  p.pid pid ,

  s.sid sid ,

  s.serial# serial ,

  p.spid spid ,

  s.username ora ,

  DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL' , 'RT','REDO-LOG' , 'TS','TEMPORARY SEGMENT ' , 'TD','TABLE LOCK' , 'TM','ROW LOCK' , l2.type ) vlock,

  DECODE(l2.type, 'TX','DML LOCK' , 'RT','REDO LOG' , 'TS','TEMPORARY SEGMENT' , 'TD',DECODE(l2.lmode+l2.request , 4,'PARSE '

  || u.name

  || '.'

  || o.name , 6,'DDL' , l2.lmode+l2.request), 'TM','DML '

  || u.name

  || '.'

  || o.name , l2.type ) type ,

  DECODE(l2.lmode+l2.request , 2 ,'RS' , 3 ,'RX' , 4 ,'S' , 5 ,'SRX' , 6 ,'X' , l2.lmode+l2.request ) lmode ,

  DECODE(l2.request , 0,NULL , 'WAIT' ) wait

FROM v$process p ,

  v$_lock l1,

  v$lock l2,

  v$resource r ,

  sys.obj$ o ,

  sys.user$ u ,

  v$session s

WHERE s.paddr = p.addr

AND s.saddr   = l1.saddr

AND l1.raddr  = r.addr

AND l2.addr   = l1.laddr

AND l2.type  <> 'MR'

AND r.id1     = o.obj# (+)

AND o.owner#  = u.user# (+)

  --AND  u.name = 'GME'

AND (:USER_NAME IS NULL

OR s.username LIKE upper(:USER_NAME))

ORDER BY p.username,

  p.pid,

  p.spid,

  ora,

  DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL' , 'RT','REDO-LOG' , 'TS','TEMPORARY SEGMENT ' , 'TD','TABLE LOCK' , 'TM','ROW LOCK' , l2.type )

Standard Output

Sessions

Active Session Count

SQL Script

Select inst_id, count(case when status='ACTIVE' then 1 else null end) active,

count(*) total from gv$session where type !='BACKGROUND' GROUP by inst_id

Standard Output

Active Sessions

SQL Script

SELECT inst_id,

  program,

  module,

  event,

  SQL_ID,

  machine,

  lpad( TO_CHAR( TRUNC(24   *(sysdate-s.logon_time)) )

  || TO_CHAR(TRUNC(sysdate) + (sysdate-s.logon_time) , ':MI:SS' ) , 10, ' ') AS UP_time

FROM gv$session s

WHERE type! ='BACKGROUND'

AND status  ='ACTIVE'

AND sql_id IS NOT NULL

Standard Output

Background Sessions

SQL Script

WITH vs AS

  (SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser,module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE type = 'BACKGROUND')

SELECT vs.sid,

  vs.username,

  CASE

    WHEN vs.status = 'ACTIVE'

    THEN last_call_et

    ELSE NULL

  END seconds_in_wait,

  DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,

  'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command,

  vs.machine "Machine",

  vs.osuser,

  lower(vs.status) status,

  vs.module "Module",

  vs.action "Action",

  vs.resource_consumer_group,

  vs.client_info,

  vs.client_identifier

FROM vs

ORDER BY 1

Standard Output

Inactive Sessions

SQL Script

WITH vs AS(SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser, module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE status != 'ACTIVE')SELECT vs.sid, vs.username,CASE WHEN vs.status = 'ACTIVE' THEN last_call_et ELSE NULL END seconds_in_wait, DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command, lower(vs.machine) machine, vs.machine machine2, vs.osuser,lower(vs.status) status,vs.module "Module",vs.action "Action", vs.resource_consumer_group,vs.client_info, vs.client_identifier FROM vs WHERE vs.USERNAME      IS NOT NULL AND NVL(vs.osuser,'x') <> 'SYSTEM' AND vs.type            <> 'BACKGROUND' ORDER BY 1

Standard Output

Session Counts by OS User

SQL Script

SELECT osuser "OS_User", COUNT(*) "Count" FROM v$session GROUP BY osuser ORDER BY 1

Standard Output

Session Counts by Status

SQL Script

SELECT status "Status",COUNT(DISTINCT osuser) "Distinct_OS_Users", type "Type", COUNT(*) "Count" FROM v$session GROUP BY status,type ORDER BY 1

Standard Output

Sessions

SQL Script

WITH vs AS (SELECT rownum rnum,sid,serial#,status,username,last_call_et,command, machine,osuser,module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session)SELECT vs.sid ,serial# serial,vs.username "Username",CASE WHEN vs.status = 'ACTIVE'THEN last_call_et ELSE NULL END "Seconds in Wait",DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) "Command", vs.machine "Machine", vs.osuser "OS User",lower(vs.status) "Status", vs.module "Module",vs.action "Action", vs.resource_consumer_group, vs.client_info, vs.client_identifier FROM vs WHERE vs.USERNAME IS NOT NULL AND NVL(vs.osuser,'x') <> 'SYSTEM' AND vs.type            <>'BACKGROUND' ORDER BY 1

Standard Output

Sessions by Module

SQL Script

SELECT NVL(module,'Unidentified') "Module",COUNT(*) "Session_Count" FROM v$session GROUP BY NVL(module,'Unidentified') ORDER BY 1

Standard Output

Sessions by Username

SQL Script

SELECT NVL(username,'Unidentified') "Username", COUNT(*) "Session_Count" FROM v$session GROUP BY NVL(username,'Unidentified') ORDER BY 1

Standard Output

System Session

SQL Script

SELECT status "Status",COUNT(DISTINCT osuser) "Distinct_OS_Users", type "Type", COUNT(*) "Count" FROM v$session GROUP BY status,type ORDER BY 1 WITH vs AS(SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser,module,action, resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE osuser = 'SYSTEM') SELECT vs.sid, vs.username, CASE WHEN vs.status = 'ACTIVE' THEN last_call_et ELSE NULL END seconds_in_wait, DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command,vs.machine "Machine", vs.osuser,lower(vs.status) status, vs.module "Module",vs.action "Action",vs.resource_consumer_group, vs.client_info, vs.client_identifier FROM vs ORDER BY 1