Oracle Apps DB: Active Sessions

SELECT DISTINCT fu.user_name user_name              
               ,to_char(ic.first_connect, 'DD-Mon-YYYY HH12: MI: SS AM') "First conn"
                ,to_char(ic.last_connect, 'DD-Mon-YYYY HH12: MI: SS AM')"Last conn"
                ,decode((ic.disabled_flag), 'N', 'ACTIVE', 'Y', 'INACTIVE') status
                ,fvl.responsibility_name "Resp"
                ,ic.function_type
                ,ic.time_out
                ,fu.user_id
                ,ic.org_id
                ,fr.menu_id
  FROM fnd_user                   fu
      ,fnd_responsibility         fr
      ,icx_sessions               ic
      ,apps.fnd_responsibility_vl fvl
 WHERE fu.user_id = ic.user_id
   AND fr.responsibility_key = fvl.responsibility_key
   AND fr.responsibility_id = ic.responsibility_id
   AND ic.disabled_flag = 'N'
   AND ic.responsibility_id IS NOT NULL
   AND ic.last_connect > SYSDATE - (ic.time_out / 60) / 96;

NOTE: Copied Code. 

Oracle DB : TableSpace Used Details

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name
--and rownum < 5
ORDER BY 1;

NOTE: Copied Code. 

Oracle DB: CPU Usage By USER

Displays CPU usage for each User. Useful to understand database load by user.


SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

NOTE: Copied Code. 

OBIEE 11g Configuration for MS SQL Database

Step 1: Apply the updated/recent ODBC driver.

Considered , OBIEE version: 11.1.1.7.1 and Linux 64 bit server. 

Need to download the latest Data driver 7.1.5 from https://support.oracle.com

Patch #: 21440314

Apply the patches as per the Oracle instructions. Post to that we need to manually copy and unzip the driver to below location.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant

Step 2: System DSN Setup.

Create a System DSN to connect to MS SQL database

Step 3: OPMN.XML file Configuration.

Navigate to below location to do the change for opmn.xml file.

/u01/BIUAT/OBIEE/instances/instance1/config/OPMN/opmn

Backup copies of opmn.xml. Make edits to opmn.xml.

Search for BI components in opmn.xml. These are XML elements, <ias-component>, with the id "coreapplication_obi<...>".

In each of these BI component elements, modify and make sure that these environment variables point to recent ODBC driver.    

 $LD_LIBRARY_PATH

 $PATH

Step 4: ODBC.INI file Configuration.

Navigate to below location to do the change for odbc.ini file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup of odbc.ini file.

Need to do below changes for [ODBC] tag. TraceDll and InstallDir need to be pointed to new data driver.

Need to modify the below mentioned line for MS SQL Data driver section.

·         Tag name should be same as System DSN name which we have created.

·         Driver path should point to 7.1.5 .

·         Database

·         EnableQuotedIdentifiers should set to 1.

·         HostName. ( Along with Instance if any)

·         PortNumber

Once this changes are done , we need to copy the file to new Data driver folder as well.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant/7.1.5

Step 5: USER.SH file Configuration.

Navigate to below location to do the change for user.sh file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup the user.sh file.

In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:

·         For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.

·         For HP-UX, the library path variable is SHLIB_PATH.

·         For AIX, the library path variable is LIBPATH.

Step 6: BI-INIT.SH file Configuration.

bi-init.sh file used to source env , should point to the DataDirect 7.1.5 driver

Back up bi-init.sh, usually located under

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Make edit to bi-init.sh.

Modify the EPM_ODBC_DRIVER_DIR parameter to point it the location of the DD 7.1.5 ODBC driver library directory.

   For example: EPM_ODBC_DRIVER_DIR=$ORACLE_HOME/common/ODBC/Merant/7.1.5/lib

   Note that bi-init.sh is only needed only for running OBIEE applications directly from a shell command line.

Step 7: RPD Changes.

Create a new connection pool to connect to MS SQL database.

Disable function "NESTED_ORDERBY_SUPPORTED" in the rpd .

Step 8: Restart BI Services.

Restart all servers (AdminServer and all Managed server(s))

Enjoy and Stay Well !! :)

Enable CLOB Field in OBIEE

Today we are going to discuss how to enable CLOB field in OBIEE. Below are the steps which need to be followed.

Lets assume , ACTION_ITEM table has a column called ACTION_TAKEN which datatype is CLOB.

1. There is not CLOB datatype in OBIEE RPD. For CLOB please select LONGVARCHAR type in RPD physical layer.

 2.   Its mandatory to define a Keys for that Physical Table in RPD.

3.Open the same column in BMM layer. Select that particular column and click on Edit under “Column Source Type”.

4. Open that Column from Logical Table Source.

5. Need to use LOOKUP DENSE funcation.

Syntax : LOOKUP (DENSE  CLOB Column, PK Column)

6. Move it to Presentation layer and use the same in BI report.

Enjoy and Stay Well !! :)