Count Total Number of Users Connected to ORACLE Applications

 

Want to determine how many users are connected to Oracle apps 11i,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up. Solution 1:- Can use this SQL statement to count concurrent_users in Oracle apps: select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1) 2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage" option in the Activity region. There you will be able to report on many usage questions, such as: Products Installed Application Users Per Module Summary Applications Usage Reports Suppliers Purchase Line Items Processed - Internet Supplier Portal Purchase Line Items Processed - Purchasing Intelligence Order Entry Lines Processed - Ordering Application Purchase Line Items Processed - iProcurement Expense Reports Processed - Internet Expense Invoice Line Items Processed - Accounts Receivables Note the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility. 3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing. 4:- Run the following queries:-     This will give the number of users on the system in the past 1 hour.      select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';     This will give the number of users on the system in the past 1 day.     select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1 and user_id != '-1';     This will show the activity in the last 15 minutes.     select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where  last_connect > sysdate - 1/96; 5:-  Check the Note:233871.1 which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs. Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms. Factoring in System Overhead =========================== In planning your organizations Sign On Audit implementation, you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.

How to setup Password Security?

 

Signon Password Failure Limit The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user's account is disabled. Users cannot see or update this profile option. The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT. Signon Password Hard to Guess The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be "hard to guess." A password is considered hard-to-guess if it follows these rules: . The password contains at least one letter and at least one number. . The password does not contain the username. . The password does not contain repeating characters. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS. Signon Password Length Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_LENGTH. Signon Password No Reuse This profile option specifies the number of days that a user must wait before being allowed to reuse a password. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE. Signon Password Case This profile option is not available from the beginning. With 11i.ATG_PF.H RUP3  comes the system profile 'Password Case Option' After 11i.ATG_PF.H.RUP4  this system profile option was renamed to  'Signon Password Case'. There are two settings: 'Sensitive' and 'Insensitive'. The default is 'Insensitive'. Setting this profile option to 'Sensitive' will make the password case sensitive. 'Mixed' is no longer supported. These profiles should only be set at Site level. They can be set at other levels, such as User or Responsibility. However, when logging in there is no User context, so if a User is prompted to change their password at login, the profiles are only evaluated at site level. Once logged in and resetting passwords using Preferences->Change Password, or the Security ->User->Define form these other levels will have effect and will confuse the issue. So these profiles should only be set at Site level, for consistent enforcement.

How to compile invalid objects in an APPS Environment

 

Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement : SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID'; For a more detailed query, use the following script : SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' GROUP BY OWNER, OBJECT_TYPE; To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type : alter package <package_name> compile; (package specification) alter package <package_name> compile body; (package body) alter view <view_name> compile; (view) If the object compiles with warnings, use either of the following to see the errors that caused the warnings : show errors OR select * from user_errors where name = '<OBJECT_NAME>'; Another way to correct invalid objects is to run the adadmin utility as follows: UNIX OPERATING PLATFORM 1. Log in as APPS User : <applmgr username>/<applmgr password> 2. Start the adadmin-Utility from the Unix prompt with this command : adadmin The utility will then ask you a series of questions. 3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s) This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall. Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba ) Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls Arguments for ADCOMPSC.pls : 1 - Schema to run in 2 - Password for schema 3 - Check errors for objects starting with #3 NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema's. In case of an ORA-1555 error while running adcompsc.pls, restart the script. The script can be run as followed : cd $AD_TOP/sql sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD % Example : SQL> @adcompsc.pls apps apps % After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing. If there are any objects still left INVALID, verify them by using the script 'aderrchk.sql' to record the remaining INVALID objects. 'Aderrchk.sql' uses the same syntax as 'adcompsc.pls'. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool <file> command in sqlplus. e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD % For objects which will not compile, try the following : select text from user_source where name = 'OBJECTNAME' and text like '%Header%'; This script will provide the script that creates the packages/recreates the packages. SQL>@packageheader SQL>@packagebody If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package : select text from user_errors where name = '<PACKAGENAME>';