Monday, February 1, 2010

System Adminstrator Guide

INDEX
Introduction of applications.
1. User Creation and Maintenance
1.1. Defining Application Users.
1.2. Navigation.
1.3. User Screen.
1.4. Disabling a user.
2. Responsibilities
2.1. Responsibility definition.
2.2. Navigation.
2.3. Responsibilities screen.
2.4. Disabling a Responsibility.
3. Menus.
3.1. Menu.
3.2. Navigation.
3.3. Menus Screen.
4. Concurrent Programs.
4.1. Definitions.
4.2. Limiting Active Requests by User.
4.3. Stages.
4.4 Request Sets as Concurrent Programs.
4.5. Request set and Owners.
4.6. Concurrent Programs screen shots.
4.7. Request Set screen shot.
4.8. Request Groups screen shot.
5. Profiles.
5.1. Overview.
5.2. Assigning a profile to user.
5.3. How to Define a Profile?
5.4. Setting up Profiles.
6. Printers
6.1. Definitions.
6.2. Printer Types.
6.3. Printer Register.
6.4. Printer Driver.
7. Concurrent Manager.
7.1 Defining new managers.
7.2 Concurrent Managers Window.
7.3. Work shift Window.
7.4. Specialization Rules Window.
8. Businesses and Instances.
9. Support Central.
10. Cases / Solutions.


Oracle Applications System Administration
Oracle application modules integrate corporate data across functional departments such as finance, human resources, supply chain management, manufacturing, e-business, and customer relationship management.
A System Administrator is a person responsible for controlling access to Oracle Applications and assuring smooth ongoing operation.

The tasks performed by System Administrator
Security Control and its management: Responsible for assigning the access of each application and in each application forms, functions, reports etc to the users.
Defining new users: Includes registering new Oracle Application users and giving them access to the required forms, function and reports to do their jobs.
Auditing users activities: Monitoring the user activities and to choose user and type of data to audit.
Setting user profiles: To set user profile values at site, application, responsibility and user levels.

Oracle Application System Administrator vs. Oracle DBA
An Oracle Applications System Administrator administers the user interface or applications side of Oracle Applications.
An Oracle Database Administrator (DBA) administers the data that users enter, update, and delete while using Oracle Applications.



1. User Creation and Maintenance
An application user is an authorized user of Oracle Applications who is uniquely identified by an application username. Once defined, a new application user can sign on to Oracle Applications and access data through Oracle Applications windows.
1.1. Defining Application Users
The system administrator allows a new user to sign-on to Oracle Applications by defining an application user. An application user has a username and a password. An initial password should be defined, and then the first time the application user signs on, they must enter a new password.
When an application user is defined, the system administrator assigns one or more responsibilities to the user. If only one responsibility is assigned, the user, after signing on, immediately enters an application.
If two or more responsibilities are assigned, the user, after signing on, sees a window listing available responsibilities.

1.2. Navigation: SecurityUsersDefine
1.3. User Screen:

User Name:
The earlier convention that was followed to identify the users was last name followed by their first initials (6+2). But now the SS0 is used to identify the users.
Password:
The system administrator assigns the initial password for the user. The first time an application user signs on, they must change the password.
If the user forgets the password, then the system administrator can reassign the password.

Password Expiration
Days
The maximum number of days between password changes should be entered here. A pop–up window prompts an application user to change her or his password after the maximum number of days a system administrator specified has elapsed.
Accesses
The maximum allowed number of sign–ons to Oracle Applications allowed between password changes. A pop–up window prompts an application user to change her or his password after the maximum number of accesses a system administrator specified has elapsed.

Effective Dates:
From/To:
The user is active only between the effective dates. The default for the start date is current date and if the end time is not mentioned then the user is active indefinitely.

Responsibilities Block:
Responsibility:
Select a responsibility, which is to be assigned to the user.

1.4. Disabling a user:
To disable a user, end date a user.
2. RESPONSIBILITIES
2.1. Responsibility:
A responsibility is a level of authority in Oracle Applications that lets users access only those Oracle Applications functions and data appropriate to their roles in an organization.
A user has at least one or more responsibilities and several users can share the same responsibility. A system administrator can assign users any of the standard responsibilities provided with Oracle Applications, or create new custom responsibilities.

2.2. Navigation: SecurityResponsibilityDefine
2.3. Responsibilities screen:

Responsibilities block:
Responsibility Name: The name of the responsibility is to be mentioned here.
Application: The application under which the responsibility is to be assigned has to be mentioned here.
Responsibility Key: This is a unique name for a responsibility.
Effective Dates:
From/to: Enter the start/end dates on which the responsibility becomes active/inactive. The default value for the start date is current date and if the end date is not entered then the responsibility is valid indefinitely.
Data Group: The data group defines the pairing of application and Oracle username.
Menu: Select a menu name, which is already defined with Oracle Applications.
Menu Exclusions Block: Define function and menu exclusion rules to restrict the application functionality accessible to a responsibility.
Type: Select either function or menu as the type of exclusion rule to apply against the responsibility.
Name: Select the name of function or menu that is to be excluded from the responsibility.
The function or menu specified must already be defined in Oracle Applications.

2.4. Disabling a Responsibility: A responsibility cannot be deleted. It can be deactivated at any time by setting the end date to current date. If a responsibility is to be reactivated then set end date to a date after current date or clear the end date.










3.MENUS
3.1. Menu:
A menu is a hierarchical arrangement of functions and menus of functions. Each responsibility has a menu assigned to it. A system administrator can restrict the functionality a responsibility provides by defining rules to exclude specific functions
or menus of functions.
Define the lowest–level submenus first. A submenu must be defined before it can be called by another menu.

3.2. Navigation: ApplicationMenu

3.3. Menus Screen:

Menus Block

Menu
Choose a name that describes the purpose of the menu. Users do not see this menu name.
View Tree: Once a menu is defined, its hierarchical structure can be seen using the ”View Tree...” button.
User Menu Name: User menu name is used when a responsibility calls a menu or when one menu calls another.

Menu Entries Block :

Sequence: Enter a sequence number to specify where a menu entry appears relative to other menu entries in a menu. A menu entry with a lower sequence number appears before a menu entry with a higher sequence number.

Navigator Prompt:
The prompt entered here is seen in the hierarchy list of the navigator window.
Submenu:
Call another menu and allow a user to select menu entries from that menu.
Function:
Call a function which is to be included in the menu.
Description:
Descriptions appear in a field at the top of the Navigate window when a menu entry is highlighted.
Grant:
If grant is enabled, only then the prompt will be visible on the navigator window.









4. Concurrent Programs

4.1. Definitions:
A concurrent program is an executable file that runs simultaneously with other concurrent programs.
When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program.
Request group is a collection of reports or concurrent programs. A System Administrator defines report groups in order to control user access to reports and concurrent programs. Only a System Administrator can create a request group.
Request sets define run and print options, and possibly, parameter values, for a collection of reports or concurrent program. End users and System Administrators can define request sets. A System Administrator has request set privileges beyond those of an end user.
Standard Request Submission is an Oracle Applications feature that allows selecting and running all the reports and other concurrent programs from a single, standard form. The standard submission form is called Submit Request.
The reports and concurrent programs that may be selected from the Submit Requests form belong to a request security group, which is a request group assigned to a responsibility.
The reports and concurrent programs that may be selected from a customized Submit Request form belong to a request group that uses a code.

4.2. Limiting Active Requests by User:

A System Administrator can limit the number of requests that may be active (status of Running) for an individual user. This ensures that a user cannot monopolize the request queue. For example, if a user with an Active Request Limit of 5 submits 20 requests, only 5 requests will be run at the same time. The remaining requests will be run when the number of active requests for the user drops below 5. Use the Profile Options window to set the Concurrent: Active Request Limit profile. To set a global limit for all users, set this option at the site level. You can then modify limits for individual users by setting this profile option at the User level.



4.3. Stages:
Organizing Request Sets into Stages
Request sets are divided into one or more ”stages” which are linked to determine the sequence in which requests are run. Each stage consists of one or more requests that you want to run in parallel (at the same Time in any order).



To run requests in sequence, assign requests to different stages, and then link the stages in the order as user wants the requests to run.



The concurrent manager allows only one stage in a request set to run at a time. When one stage is complete, the following stage is submitted. A stage is not considered to be complete until all of the requests in the stage are complete.
One advantage of using stages is the ability to run several requests in parallel and then move sequentially to the next stage. This allows for a more versatile and efficient request set.

.

Using Stage Status
Like request sets and concurrent requests, stages can complete with different statuses. Each stage can complete with a status of Success, Warning, or Error.
For example: a request set always begins with Stage 1. If Stage 1 completes with the status Success, then the Success link is followed, and Stage 2 is submitted. After Stage 2 completes, the set ends. If Stage 1 completes with Warning, then the Warning link is followed, and Stage 3 is submitted. After Stage 3 completes, the set ends. If Stage 1 completes with Error, then the Error link is followed, and Stage 4 is submitted. After Stage 4 completes, the request set ends.



Linking of Stages
There are no restrictions on linking stages within a request set. Any stage may be linked to any other stage, including itself. Two or more links can point to the same stage. For example, Stage 1 can link to Stage 2 if the completion status of Stage 1 is Success or Warning, and link to Stage 3 if the status is Error.




Stage Evaluation Function
The completion status of a stage is determined by a predefined function. The Oracle Applications Standard Stage Evaluation function uses the completion status of the requests it contains. Use this function to determine the status of a stage.
Request Set Completion Status
When a stage completes with a status for which there is no link defined, the request set ends. The completion status for the request set is determined by one of the following methods:

Using the completion status of the last stage run in the request set. This method is used by default.
The user can override the default behavior by defining a specific stage within the set to be”critical”. If the request set runs a critical stage, then the completion status of the set will be the same as the completion status of the most recently run critical stage. This can be useful if the final stage of the set is a ”clean up” stage and is not considered important to the overall status of the set.

Note: If a printer is defined for a concurrent program using the Concurrent Programs form, then that value cannot be updated, either by a user profile option setting, a request set definition, or when running the program or request set.
4.4 Request Sets as Concurrent Programs
When a user define a request set or a stage within a request set that allows incompatibilities, a concurrent program is created to run the requests in the request set according to the instructions you enter. All concurrent programs that run request sets are titled Request Set , and programs that run request set stages are titled Request Set Stage .
A request to run the request set concurrent program or the request set stage concurrent program is a Parent request, while the requests to run the programs and reports are Child requests.

The status of a request set can be reviewed and the programs it contains using the Concurrent Requests form. The following table displays request phase and status information that pertains to request sets.


Modifying Request Sets
A System Administrator can only modify by its owner or a request set. To make modifications, query the request set that has to be modified in the Request Set window.
Note: If modifications to request sets provided by Oracle application during upgrades is to be retained, then rename or recreate the request set using a different name before upgrade. If a user modifies a predefined request set without changing the name, then modifications are overwritten while upgrading Oracle Applications.
4.5. Request Sets and Owners
There are significant differences between end user and System Administrator privileges when defining or editing request sets.
End users own the request sets they create
An end user can create a request set by selecting reports, other request sets, or concurrent programs that are part of the report security group assigned to his responsibility.
When an end user creates a request set, the user automatically becomes the “owner” of the request set.
End users use the Request Set form to create a new request set, or to query and update any request sets they own. End users can only edit request sets they own.
Other users cannot access your private request sets using the Submit Requests window unless the System Administrator assigns that request set to a request security group.
When a user signs on to Oracle Applications, the user can run requests, request sets, and concurrent programs included in:
their responsibility’s request security group
any request sets they own.

When User does not own request set:
All users can submit request sets that are added to a their request security group even if they contain requests that are not in the request security group. If the user does not own the request set, they:
cannot edit the request set.
cannot run an individual report by itself, but can only run the entire request set.
When User owns request set:
If the user owns the request set, they:
Can add any other requests in their request security group to the request set.
Can delete any request from the request set, regardless of whether that report is in their request security group.
Can update print options or parameters for an individual report in the request set, if the report is in their request security group.
Cannot run an individual report by itself, but can only run the entire request set.

4.6. Concurrent Programs screen shots:

Step 1: Define Executable

Navigation: Concurrent -> Program -> Executable



Execution File Name – Is the name of the actual executable file in the Top Directory.

Step 2: Define Concurrent Program:

Navigation: Concurrent -> Program -> Define

See the Screen Shot below:

Enter Executable Short Name as the Executable Name for the concurrent program.

To disable the concurrent program uncheck the Enable options.

To enable trace check the Enable Trace option.


To enter parameters click on the Parameters button.




4.7. Request set screen shots

When we use the same set of concurrent program regularly we define a request set. Request Sets are divided into ‘stages’ which are linked to determine the sequence in which the request are run.

Navigation: Concurrent -> Set



Click on Define Stages & then define Stages.


Click on Request & specify the concurrent program.



Click on Parameter to view the parameters of the program.




Request stage status:
To submit a request:
1. Navigate to the Submit a New Request window (Other -> Requests -> Run).
2. Check the Request option to submit single requests, or choose to submit a predefined group of requests by checking Request Set.
3. Choose OK.
Submitting requests
4. Use the Copy... button to take advantage of previously entered request submissions.
5. Select the Name of the request (report or program), which is to be run from the list of available requests.
Note: Responsibility's request group determines which requests appear in the list.
To see the status of submitted request






4.8. Request Groups screen shot

Once the concurrent programs & request sets are defined they need to assigned to the request group. The request group is intern assigned to the responsibility & thus the users can submit the programs.

Navigation: Security -> Responsibility -> Request.


















5. Profiles


5.1. Overview:
A User profile is a set of changeable options that affect the way the application looks and behaves. A system administrator has to control how oracle applications operate by setting user profile options to the value that is desired.

Profile can be set at four levels:
Site Level.
Application Level.
Responsibility Level.
User level.

Site level profile: The site level profile is an option settings pertain to all users at an installation site.
Application level profile: The Application level profile is an option settings pertain to all users of any responsibility associated with the application.
Responsibility level profile: The Responsibility level profile is an option settings pertain to all users at an installation site.
User level profile: The User level profile is an option settings pertain to an individual user.

The system administrator can set default option values at any of these levels.


5.2. Assigning a profile to the user:

A system administrator uses the system profile values window to set profile options for the user community. If an administrator changes a user profile option value changes takes effect as soon as users log on again or change responsibilities.

Navigation:

If only specific user profile options is to be displayed,
1. First choose Enter from the View, Query by Example menu to enter search criteria in the Profile Name field, and then choose Run from the View, Query by Example menu to run the search.
The name of the user profile option appears in the Profile Name field while the Default Value field displays the run-time value of that option. The System Administrator sets default values for many of the profile options. Some profile options may not display a default value.

2. Move the cursor to the User Value field of the option whose value is to be modified..

3. Enter a new value for the option if it is updatable, or if the lamp appears, choose a value from the list of available values.
If the profile option is not updatable, the message "Item is protected against update," appears on the message line when the value is changed. Most of the user profile options can be changed; values entered in the User Value field override values preset by the System Administrator. A few profile options cannot be changed, but are displayed for informational purposes only.
For most personal profile options, Oracle Applications automatically checks the value entered to ensure it is valid.
Attention: Number or date values are not validated, therefore, it is to be made sure that a valid value is entered for profile options that require a number or date; otherwise, the personal profile option may not work as expected.
Though a profile option cannot be deleted from personal profile, its value (if it is updatable) can be cleared by highlighting the field and pressing [Backspace] or by choosing Clear, Field from the Edit menu. If the value is cleared, the change does not take effect until relogin or responsibilities are changed.
4. Choose Save from the File menu to save the change.
The change will take effect when either responsibility are changed or log out and log back in

5.3. How to Define a Profile?

Responsibility: Application Developer

Navigation: Profile.




5.4. Setting up of Profiles

Profiles are set at four levels, viz

Site
Application
Responsibility
User

Responsibility: System Administrator

Navigation: Profile -> System







Click on Find.











6. Printers

The Printer setup is normally performed by the system administrator (responsibility).
6.1. Definitions:

Print Styles: The dimensions of a report are determined by the column and row values in the print style defined in the Print Styles form that overrides the width and height values in the SRW driver.

Printer Drivers: A printer driver includes the initialization and reset strings. A defined printer driver is needed for each print style that will be used with a specific printer type on a specific platform.

Navigation: Install  printer




The following steps to setup printer(s) should be performed in the following order:

6.2. Printer Types: Install  printer  Types

The printer types must be defined if it is not predefined with Oracle Applications. Any name can be chosen. It is on this form on which the print style is being associated with a printer driver for the particular printer type.

Printer Types Form Screen



An example for a line printer, it can be named as LINE. This name will be associated to the actual printer name when the printer is registered to Oracle Applications.

6.3. Printer Register
The Printer type should be defined before registering a new printer.

The value for printer name will be the operating system printer name, and then choose the appropriate printer type.

Printer Types
Associate the required printer style and printer driver to the printer type from this form.

Print Styles
To define new print styles, this form is used. The sample print styles are Portrait, Landscape, A4, etc.


6.4. Printer Driver
To define new printer driver, this form is used.



NOTE:
Many printers can be registered as the same printer type.
A printer type can support multiple print styles.
A printer driver must be assigned to a printer type for each print style.
Many printer drivers can support the same print style.
Many printer drivers can support the same printer type.


























7. Concurrent Manager

A concurrent manager is itself a concurrent program that starts other concurrent programs running. When an application user submits a request to run a program, the request is entered into a database table that lists all of the requests.

Concurrent managers read requests from the table and start programs running.
7.1 Defining new managers
While defining new managers:
Assign a predefined library of immediate concurrent programs to the manager. Immediate concurrent programs are subroutines associated with concurrent managers.
Assign work shifts to the manager, which determines what days and times the manager works.
For each work shift, define the maximum number of operating system processes the manager can run concurrently to read requests (start programs) during the work shift.
Specialize the manager to read only certain kinds of requests.


The Internal Concurrent Manager:
It functions as the "boss" of all the other managers. The Internal Concurrent Manager starts up, verifies the status of, resets, and shuts down the individual managers.

The Standard manager:
It accepts any and all requests and has no specialization. The Standard manager is active all the time; it works 365 days a year, 24 hours a day.

Transaction Managers
Transaction managers support synchronous processing of particular requests from client machines. They are implemented as immediate concurrent programs. At runtime, concurrent processing starts a number of these managers. Rather than polling the concurrent requests table to determine what to do, a transaction manager waits to be signaled by a client program.
Each transaction manager can process only the programs contained in its program library.
A transaction manager is associated with a particular data group, and uses that data group to connect to the database.

7.2 Concurrent Managers Window:



Concurrent Managers Block
The combination of an application and the name defined for manager uniquely identifies the manager. To restrict a manager to only running programs associated with certain applications, go to the Specialization Rules window.


Type
The type of manager (concurrent manager, internal monitor, transaction manager) is selected here.

Cache Size (Concurrent Manager only)
The number of requests the manager remembers each time it reads which requests to run is entered here. In reading requests, the managers will only put requests it is allowed run into its cache.

Data Group (Transaction Manager only)
The data group the transaction manager uses to connect to the database. Transaction managers only run programs submitted from responsibilities that use the same data group as the transaction manager.

Program Library
Concurrent managers can run only those immediate concurrent programs listed in their program library. They can also run concurrent programs that use any other type of concurrent program executable as long as the specialization rules include them. Transaction Managers can only run programs listed in their program library.

Work shifts:
A work shift defines the dates and times the manager is enabled. The number of programs the manager can run simultaneously can be varied with each work shift.

7.3. Work shift Window



Work shifts are defined using the Work Shifts form.

Work Shift
The work shift that has to be assigned to the manager is selected here.
Processes
The number of operating system processes the work shift has to run simultaneously is entered here. Each process can run a concurrent request.
Sleep Seconds
Sleep time is the number of seconds the manager waits between checking the list of pending concurrent requests. The default value is 60 (seconds).

Specialization Rules:
These rules are used to define what kinds of requests the manager has to read. Without specialization rules, a manager accepts requests to start any concurrent program.

7.4. Specialization Rules Window



Include/Exclude
Select from the pop list whether or not to include or exclude those requests that are based on the rule to run.
Type
The type of specialization rule that is to be assigned to the manager is selected.







8. Businesses and Instances

11.0.3
gepsesd38
gepsese38
gepsesp39
gepsest37
gepsord38
gepsorp39
gepsort37

CMF (Customer master file/Support master file)
CMF Prod-gpsorc77
CMF Prod-gpsorc40
gpsorc81-dev
FRMT (Field Resource Management tool)

Dev
frp82-frmtprod
SIT-test instance-gpsrt117
NRS (Network reliability services ERP)

apd40-NRPSDEV8
ASCP (patch)-gpsamx79
ASCP(SIT)-gpsamd79
ASCP(test)-gpsamt79
ASCPProd-gpsasp10
Dev-gpsemd40
gpsed140-Dev instance
gpspt117
NRS Prod gpsnsp11
Patch-gpsemx40
SIT-gpsme120
Test-gpsms120


Parts-CPG
gpsapa79
gpsapi79-Ascp sit
gpsapi79
gpsapm79
gpsapt79
GPSCSX40
gpses117
gpsesd81
gpsesd81-oltp Dev
gpsesm81
gpsesp76
gpsesp82
gpset117
T117


Products-BOP (Balance of power)
General Integrated Dev-gpsord81
GL Team Dev-gpsgld81
GPSAPX81
gpshxe40
gpspad81
Hungary Integrated Test-gpsort40
Hungary Prod-gpsorp77-GL
Hungary Prod Support-gpsore40
Manufacturing Dev-gpsmfd81


Wind
gpswdd81-Dev
gpswdp82
gpswds40
gpswdt40-Test1
gpswdt40
Aero
*
*
*
*

Instance details site: http://3.235.168.71:7777/dba/main.htm

Note:

All the instances end with d development instances.
All the instances end with e, s, t, m, x and c Test instances.
All the instances end with p Production instances
























9. SUPPORT CENTRAL
Support central is the tool used by System Administration team to receive cases.

After getting into support central, we can choose any of the seven options available under show depending on the requirement.
The seven options are:
All cases
Open cases
New cases
Pending [Expert]
Pending [User]
Closed cases
Archived cases
ALL CASES:
This gives the information of all the cases like open cases, pending cases, new cases etc.

Details available in all cases:

Case#: A unique number identifies every case.
Logged by: This gives the name of the person who has submitted the case.
Case owner: This gives the name of the system administrator who has opted to attend the case.
Date: This gives the date of submission of the case.
Subject: This gives the subject of the case like user management, profile management, etc.
Case description: This gives the description of the case in brief.
Status: This gives the status of the case whether new, closed or pending.
Severity: Based on the severity the system administrator has to prioritize the case. Severity of the case may be normal, urgent or business critical (First priority).
Mode: This gives the mode through which the case has been received (like email).
The screen shot for all cases:





Open Cases: This gives the information of all the opened cases.
New Cases: This gives the information of all the new cases.
Pending [EXPERT] Cases: This gives the information of all the cases that has been forwarded to another expert.
Pending [USER] Cases: This gives the information of all the cases that needs more information from the user in order to solve the case. The system administrator asks the user to update the case with the missing information.
Closed Cases: This gives the information of all the closed cases.
Archived cases: All the cases that are required for future reference are stored here





.

How to go about Support Central:

Go to new cases and check for a case that has not been owned.
While taking up a case give first priority to business critical cases.
Check for the subject and know what type of case it is.
Open the case and understand the case by going to the request link.
If the case can be solved then take ownership by clicking on take ownership option, which appears on right side of the screen when a new case is opened.
Check for the information required to solve the case. If all the information is available then solve the case.
Else ask the user to update the case by providing all the missing information (by replying to the user in the reply box and clicking the send button on the left side). This case now comes under pending [user] cases.
Once the case is solved, then update the user about the same by giving a reply in the reply box.
Note down the case# which has been solved in excel sheet.
Close the case by clicking the send/close button on the right side.
If the case cannot be solved under any circumstances then pass it on to another expert by clicking on Check to keep the case in "Pending (Expert) " Queue. Write a comment in the comment box about the proceedings of the case for the other expert to understand. Then send it by clicking the send button on the left side. This case now comes under pending [expert] cases.


Notes:
All the GL cases are notified in the mailbox (not in support central).
Take up a case based on severity and act accordingly.
Always case escalations are to be avoided.
If a case cannot be solved before the expected closure date then modify the expected closure date (this will save the case from escalation).
Always try to solve escalated cases.
If any information is required from the user then try to ping (same time) him or contact him through DC (dial comm. incase of Employee) or mail and get the details.
If case is needed for future reference, then put it under archived cases.
It is always advisable to maintain a checklist of all the cases you solved.






10. Cases / Solutions

10.1. User Management Cases:

New User Creation:
If there is a case for new user creation, then first the employee is created in the Oracle Application and then the user is created with the requested responsibilities. Here it is very important to know last name and first name of the user, email id, SS0 and the employment type i.e. whether the user is an employee of GE or a contractor. If these informations are not provided, then the user is asked to update the case with required information.

10.1.1. Employee Creation in CPG – gpsesp76:

Responsibility – CS US Purchasing Super User

Navigation – Setup -> Personnel -> Employees








10.1.2. Employee Creation in Product (BOP) – gpsorp77

Responsibility – Global HR Manager

Navigation – People -> Enter and Maintain


To find an employee enter the Employee Number & click find. To create a new employee record click on new.




If there is already a user with user id in the form of 6+2 and he wants his id to be changed to SSO form, then first delete the employee details; save it and end date the user (6+2 form). Now create a new employee with the mentioned details and create the user as requested (employee details has to be unique for every employee).

Password Reset:
If a user requests for an account creation, but his id already exists in the application and he has been end dated. Then the end date is to be removed and password has to be reset to the default i.e. oracle. The user id and the password have to be updated to the user.

Disabling a user:
If a user account is to be closed, then the user is to be end dated in the user form.

10.2. Responsibility Management Cases:

Assigning Responsibilities to a user:
Add the requested responsibilities in the user form and update the user about the same.
If a user already has few responsibilities but wants more responsibilities to be added, then those responsibilities are to be added without making any changes to the previous responsibilities.
If a user requests for a responsibility that has been end dated from his account, then modify the end date from the user form and update the user about the change.
If a user requests for a responsibility that itself has been end dated, then update the user about the same without making any change to the responsibility.

Usage of Transportable Tablespace (Moving a Tablespace from one database to another)

Here is steps from moving TB from PROD to TEST

PROD


EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TS', incl_constraints => TRUE);

SELECT * FROM transport_set_violations;

ALTER TABLESPACE TS READ ONLY;

exp TRANSPORT_TABLESPACE=y TABLESPACES=TS FILE=TS.dmp

ALTER TABLESPACE TS READ WRITE;

Demo:
--------------------------------------------------------------------------------
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TS', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

SQL> ALTER TABLESPACE TS READ ONLY;

Tablespace altered.

[oracle10g1@cesora 10gR1_home]$ exp TRANSPORT_TABLESPACE=y TABLESPACES=TS FILE=TS.dmp

Export: Release 10.2.0.1.0 - Production on Mon Mar 2 17:30:14 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Username: system as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table EMP1
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
-------------------------------------------------------------------------------------

TEST

create user TS_TEST identified by TS_TEST;

imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS_TEST01.dbf' TABLESPACES=TS
FILE=/u02/10gR1_home/TS.dmp

Demo:
-------------------------------------------------
[oracle10g@cesora TEST]$ cp ../PRD/TS01.dbf .
[oracle10g@cesora TEST]$ imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS01.dbf'
TABLESPACES=TS FILE=/u02/10gR1_home/TS.dmp fromuser=TS touser=TS_TEST
[oracle10g@cesora TEST]$ pwd
/u02/oradata/TEST
[oracle10g@cesora TEST]$ ls -ltr
total 1391512
-rw-r----- 1 oracle10g dba 20979712 Feb 28 06:00 temp01.dbf
-rw-r----- 1 oracle10g dba 52429312 Mar 1 18:50 redo02.log
-rw-r----- 1 oracle10g dba 52429312 Mar 2 09:00 redo03.log
-rw-r----- 1 oracle10g dba 5251072 Mar 2 09:05 users01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 09:05 rman01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 09:05 example01.dbf
-rw-r----- 1 oracle10g dba 31465472 Mar 2 18:21 undotbs01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 18:21 TS01.dbf
-rw-r----- 1 oracle10g dba 513810432 Mar 2 18:22 system01.dbf
-rw-r----- 1 oracle10g dba 377495552 Mar 2 18:22 sysaux01.dbf
-rw-r----- 1 oracle10g dba 52429312 Mar 2 18:22 redo01.log
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control03.ctl
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control02.ctl
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control01.ctl
[oracle10g@cesora TEST]$ imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS01.dbf'
TABLESPACES=TS FILE=/u02/10gR1_home/TS.dmp fromuser=TS touser=TS_TEST

Import: Release 10.2.0.1.0 - Production on Mon Mar 2 18:22:44 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: system as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TS's objects into TS_TEST
. . importing table "EMP1"
Import terminated successfully without warnings.
[oracle10g@cesora TEST]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 2 18:23:44 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: ts_test
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from emp1;

COUNT(*)
----------
1835008

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST

ALTER TABLESPACE TS READ WRITE;
------------------------------------------------------------------

Now Trying to clone DB TEST to TS:


backup control file to trace in TEST

Shutdown TEST database

copy all the datafiles from TEST to target(TS)

startup nomount

run create control file script

alter database open reset logs

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata_TS/TS/temp01.dbf'SIZE 20971520 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

SQL> select status,instance_name from v$instance;

STATUS INSTANCE_NAME
------------ ----------------
OPEN TS

Important useful Apps DBA sql's and some linux commands

To find the session from Os process id:

select * from v$session where paddr in (select addr from v$process where spid='1773')

To find the concurrent request from session id:

select * from fnd_concurrent_requests where oracle_process_id in (select spid from v$process where addr in (select paddr from v$session where sid='191'));

To find sid serial# from request id

SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

Note : oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. If you are canceling a request from "Submit Request Submission" form then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been cancelled and then it will kill database process. So ideally if you are canceling a request from "Submit Request Submission" then you should wait for some time and then check associated database process.

SELECT SID
FROM v$session
WHERE paddr in
(SELECT addr FROM v$process
WHERE spid in (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = '11865131'));

High CPU Consuming Sessions:

select ss.sid,ss.value CPU , se.username,se.program from v$sesstat ss,v$session se
where ss.statistic# in (select statistic# from v$statname where name='CPU user by this session')
and se.sid=ss.sid
order by CPU;

To find the sessions which are locked a particular table:

SELECT *
FROM v$session
WHERE SID IN (SELECT session_id
FROM v$locked_object
WHERE object_id IN (SELECT object_id
FROM dba_objects
WHERE object_name LIKE 'MTL_SYSTEM_ITEMS%'));

SELECT sid, serial#,status,module,username,machine,action FROM v$session
WHERE SID IN (SELECT session_id
FROM v$locked_object
WHERE object_id IN (SELECT object_id
FROM dba_objects
WHERE object_name LIKE 'MTL_SYSTEM_ITEMS%'));

To find trace file for a Concurrent Request:

SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
'Trace Flag: ' || req.enable_trace,
'Trace Name:
'
|| dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc',
'Prog. Name: ' || prog.user_concurrent_program_name,
'File Name: ' || execname.execution_file_name
|| execname.subroutine_name,
'Status : '
|| DECODE (phase_code, 'R', 'Running')
|| '-'
|| DECODE (status_code, 'R', 'Normal'),
'SID Serial: ' || ses.SID || ',' || ses.serial#,
'Module : ' || ses.module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE req.request_id = &request
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id;

Abstract: This script can be used to find out the Users connected to Oracle Applications. I use the script for imitating the Users before bouncing Apache or Forms Services. There are several MetaLink Notes for doing the same, but not giving exact output in various instances. This works in 11.5.9 and 11.5.10.XX environments.

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility from fnd_user fu,
fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;


select rpad('-',100,'-') || chr(10) ||
'USER: ' || s.username || '/' || s.osuser ||
'.....STATUS:' || s.status ||
'.....LOGON_TIME:' ||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS') || chr(10) ||
'.....SID:' || s.sid ||
'.....SERIAL#:' || s.serial# ||
'.....SPID:' || P.SPID || chr(10) ||
'.....MACHINE:' || s.machine ||
'.....PROGRAM:' || s.program || chr(10) ||
'.....SQL: ' || s.sql_address || chr(10) ||
decode(sa.sql_text,null,'<---no sql stmt--->',sa.sql_text)
from v$session s, v$process p, v$sqlarea sa
where s.username is not null
and s.paddr = p.addr
and s.sql_address = sa.address (+)
order by s.USERNAME,s.LOGON_TIME


To find the tablespaces which are having less than 20% free space

SELECT 'TABLESPACE: ' || df.tablespace_name,
'Percent Free: ' || ROUND (fs.sumbytes * 100 / df.sumbytes, 2) || '%',
'Free Space: ' || ROUND (fs.sumbytes / 1048576, 2) || ' MB',
'Total Size: ' || ROUND (df.sumbytes / 1048576, 2) || ' MB'
FROM (SELECT tablespace_name, SUM (BYTES) sumbytes
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM (BYTES) sumbytes
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
AND ((fs.sumbytes <
(df.sumbytes * DECODE (df.tablespace_name, 'SYSTEM', 0.07, 0.20)
)
)
)
AND df.tablespace_name != 'RBS';


To query the requests which are taking more than 1hr in current day

SELECT t.request_id, t.actual_start_date, t.actual_completion_date,
TRUNC ((SYSDATE - t.actual_start_date) * 24 * 60,
2
) AS "Total Time (Min)"
FROM fnd_concurrent_requests t
WHERE t.actual_start_date LIKE SYSDATE
AND (SYSDATE - t.actual_start_date) * 24 > 1
AND status_code = 'R'
AND phase_code = 'R'

To find the requests which will run more than an hour

SELECT t.request_id, t.actual_start_date, t.actual_completion_date,
TRUNC ((t.actual_completion_date - t.actual_start_date) * 24 * 60,
2
) AS "Total Time (Min)"
FROM fnd_concurrent_requests t
WHERE t.actual_start_date LIKE t.actual_completion_date
AND (t.actual_completion_date - t.actual_start_date) * 24 > 1


To find Sid and serial# for a particular request:

SELECT SID, serial#, action, paddr
FROM v$session
WHERE process IN (SELECT os_process_id
FROM fnd_concurrent_requests
WHERE request_id = 'request_id');

SELECT SID
FROM v$session
WHERE paddr LIKE (SELECT addr
FROM v$process
WHERE spid = (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id))


To verify that the table has gathered statistics recently, use the following SQL:

SELECT last_analyzed, sample_size
FROM dba_tables
WHERE table_name = &table_name;


SELECT column_name, last_analyzed, sample_size
FROM all_tab_columns
WHERE table_name = &table_name;


SELECT index_name, last_analyzed, sample_size
FROM all_indexes
WHERE table_name = &table_name;


Gather schema Statistics

exec dbms_stats.gather_table_stats('ONT', 'ONT.OE_ORDER_HEADERS_ALL')

exec dbms_stats.gather_schema_stats('ONT')

select distinct trunc(last_analyzed), OWNER from dba_tables where owNer not in ('SYS', 'SYSTEM','MDSYS','ORDSYS','CTXSYS','OUTLN')
ORDER BY trunc(LAST_ANALYZED) ASC

select distinct trunc(last_analyzed), OWNER from dba_indexes where owNer not in ('SYS', 'SYSTEM','MDSYS','ORDSYS','CTXSYS','OUTLN')
ORDER BY trunc(LAST_ANALYZED) ASC

SELECT *
FROM dba_indexes
WHERE TRUNC (last_analyzed) < TRUNC (SYSDATE) - 4
AND owner = 'AP'
AND table_name NOT IN (
SELECT table_name
FROM dba_tables
WHERE TRUNC (last_analyzed) < TRUNC (SYSDATE) - 4
AND owner = 'AP'
AND tablespace_name = 'APD')

exec dbms_stats.gather_schema_stats(
ownname => 'APPS',
estimate_percent => dbms_stats.auto_sample_size, ----> or you can give % 10..30...etc
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
cascade => TRUE);

exec DBMS_STATS.GATHER_INDEX_STATS (
ownname => 'BOM',
indname => 'CST_STD_COST_ADJ_TEMP_N2',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE );

execute dbms_stats.gather_schema_stats(ownname => 'JMDISC', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'JMAPPS', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'SADBAM', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'MSACCESS', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);


Moving tables to new tablespace, rebuild indexes and compute statistics


A) Moving Tables from one tablespace to another tablespace

select ‘alter table ‘||table_name||’ move tablespace COSTPOINT_DATA4;’
from user_tables where tablespace_name=’COSTPOINT_DATA’;
spool off;

B) Rebuilding Indexexes

1)select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLESPACE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’);

2) select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where table_name=’COSTPOINT_DATA’);

C) compute Statistics

a)select ‘analyze table ‘|| table_name|| ‘ compute statistics;’ from dba_tables where owner_name=’ONT’;

b)Analyze table Table_name compute statistics;


To move table (Re-Org)

a)alter table table_name move;

b)alter table table_name move tablespace tablespace_name;


To find Invalid indexes

a)select index_name from dba_indexes where status not in ('VALID')---UNUSABLE'

b)select owner||'.'||table_name,Index_name,status
from dba_indexes where status not in ('VALID') and owner in('MSC','APPLSYS','AR','APPS')


View for finding Database locks

CREATE OR REPLACE VIEW session_waits (waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
)
AS
SELECT /*+ordered */
w.SID, s.ksusenum,
DECODE (r.ksqrsidt,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
r.ksqrsidt
),
DECODE (l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
l.lmode
),
DECODE (BITAND (w.p1, 65535),
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR (BITAND (w.p1, 65535))
),
r.ksqrsid1, r.ksqrsid2
FROM v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
WHERE w.wait_time = 0
AND w.event = 'enqueue'
AND r.ksqrsid1 = w.p2
AND r.ksqrsid2 = w.p3
AND r.ksqrsidt =
CHR (BITAND (p1, -16777216) / 16777215)
|| CHR (BITAND (p1, 16711680) / 65535)
AND l.BLOCK = 1
AND l.saddr = s.addr
AND l.raddr = r.addr
AND s.inst_id = USERENV ('Instance')
/

And User below query to find locks

select * from session_waits where holding_session not in (select waiting_session from session_waits);

select * from dba_locks where blocking_others !='Not Blocking';

Here holding_session is lock session we need to kill this holding_session session

To find Plm patch set level and rollup patch

select 'Current PLM Patchset: '||ver||' ('||bug||')' "Description" from (
select x.* from
(select 1 seq, '2714179' bug,'11.5.0' pv,'A' ver from dual
union select 2,'3001751','11.5.0','B' from dual
union select 3,'3298205','11.5.0','C' from dual
union select 4,'3990439','11.5.0','D' from dual
union select 5,'4203793','11.5.0','E' from dual
union select 6,'4508296','12.0.0','A' from dual) x,
(select product_version pv from fnd_product_installations
where application_id=431) i,
ad_bugs bugs
where x.bug = bugs.bug_number and x.pv = i.pv order by 1 desc) where rownum = 1
union
select 'Current PLM Rollup: '||ver||' ('||bug||')' "Description" from (
select x.* from
(select 1 seq, '4574625' bug,'11.5.0' pv,'E2' ver from dual
union select 2, '4662995','11.5.0','E3' from dual
union select 3, '4743765','11.5.0','E4' from dual
union select 4, '4894197','11.5.0','E5' from dual
union select 5, '5031825','11.5.0','E6' from dual
union select 6, '5170870','11.5.0','E7' from dual
union select 7, '5239326','11.5.0','E8' from dual
union select 8, '5462432','11.5.0','E9' from dual
union select 9, '5671752','11.5.0','E10' from dual
union select 10,'5892657','11.5.0','E11' from dual
union select 11,'6192001','11.5.0','E12' from dual
union select 12,'6459400','11.5.0','E13' from dual
) x,
(select product_version pv from fnd_product_installations
where application_id=431) i,
ad_bugs bugs
where x.bug = bugs.bug_number and x.pv = i.pv order by 1 desc) where rownum = 1
union
select 'Product Installation: '||patch_level||': '||decode(status,
'I','Installed','S','Shared','Not Installed')
from fnd_product_installations
where application_id=431
order by 1;


SELECT (bug_number) "Patch Number", decode((bug_number),
'7291444','PO 11.5.10 RUP6',
'7168377','PO 11.5.10 RUP5',
'6778767','PO 11.5.10 RUP4',
'6505228','PO 11.5.10 RUP3',
'3384350', 'SCM_PF.J',
'2700001','PRC_PF.I',
'2320032','PRC_PF.H',
'2141229','PRC_PF.G',
'1891381','PRC_PF.F',
'1745369','PRC_PF.E',
'1554100','PRC_PF.D',
'2123967','11.5.7 11i.PRC_PF.G',
'2293243','11.5.8 11i.PRC_PF.H',
'2669606','11.5.9 11i.PRC_PF.I',
'3140000','11.5.10 11i.SCM_PF.J',
'3240000','11.5.10.1',
'3480000','11.5.10.2',
'4017300','ATG CU 1',
'4125550','ATG CU 2',
'4334965','ATG CU 3',
'4676589','ATG CU 4',
'5473858','ATG CU 5',
'5903765','ATG CU 6',
'5082400','12.0.1 RUP1',
'5484000','12.0.2 RUP2',
'6141000','12.0.3 RUP3',
'6435000','12.0.4 RUP4',
'7355145', '12.0 Update September 08',
'Other') "Patch Name" FROM ad_bugs
WHERE bug_number IN
('7291444','7355145','3384350','2700001','2320032', '2141229',
'1891381' ,'1745369','1554100','2123967',
'2293243','2669606','3140000','4017300','4125550','3480000',
'4334965','3240000','4676589','5473858','5903765','5082400','5484000','6141000', '6505228','6435000','6778767','7168377')
order by bug_number desc;

TO FIND THE PATCH LEVEL FOR APPLICATIONS

select substr(a.APPLICATION_NAME,1,60) "Application Name"
, substr(i.PRODUCT_VERSION,1,4) "Version"
, i.PATCH_LEVEL "Patch Level"
, i.APPLICATION_ID "Application ID"
, i.LAST_UPDATE_DATE "Last Update"
from APPS.FND_PRODUCT_INSTALLATIONS i
, APPS.FND_APPLICATION_ALL_VIEW a
where i.APPLICATION_ID = a.APPLICATION_ID
-- not all applications update the next field correctly
-- and i.PATCH_LEVEL like '11i%'
-- these are the applications that concern me most
-- and i.APPLICATION_ID in ('0','140','260','101','200','275','201','222','185')
order by a.APPLICATION_NAME


To Flush Shared Pool:

alter system flush shared_pool;


To Find Session details

SELECT s.sid, s.serial#, s.status, s.Action ,s.module, s.sql_address,s.machine, SUBSTR(s.username ,1,18) username, SUBSTR(s.program,1,15) program,
DECODE (s.command,0, 'No Command', 1, 'Create table', 2, 'Insert', 3, 'select', 6, 'update', 7, 'Delete', 9, 'Create Index', 15, 'Alter Table', 21, 'Create View', 23, 'Validate Index', 35, 'Alter Database', 39, 'Create Tablespace', 41, 'Drop Tablespace', 40, 'Alter Tablespace', 53, 'Drop User', 62, 'Analyze table', 63, 'Analyze Index',
s.command ||':other') Command
FROM v$session s,
v$process p ,
v$transaction t ,
v$rollstat r ,
v$rollname n
WHERE s.paddr=p.addr
AND s.taddr =t.addr (+)
AND t.xidusn =r.usn (+)
AND r.usn =n.usn (+)
order by 7;

select * from v$session_wait where sid='96'

select * from v$system_event where event='db file sequential read'

select * from v$session where paddr in (select addr from v$process where spid='6279')

select pid from v$process where spid =6279

select * from v$session_longops where sid='269'

select * from v$sql where module_hash='544722961'

select * from v$active_session_history where sid=197


select
event c1,
total_waits c2,
time_waited / 100 c3,
total_timeouts c4,
average_wait /100 c5
from
sys.v_$system_event
where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
AND
event not like 'DFS%'
and
event not like '%done%'
and
event not like '%Idle%'
AND
event not like 'KXFX%'
order by
c2 desc
;


select
b.sid c1,
decode(b.username,NULL,c.name,b.username) c2,
event c3,
a.total_waits c4,
round((a.time_waited / 100),2) c5,
a.total_timeouts c6,
round((average_wait / 100),2) c7,
round((a.max_wait / 100),2) c8
from
sys.v_$session_event a,
sys.v_$session b,
sys.v_$bgprocess c
where
event NOT LIKE 'DFS%'
and
event NOT LIKE 'KXFX%'
and
a.sid = b.sid
and
b.paddr = c.paddr (+)
and
event NOT IN
(
'lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'io done',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by 4 desc
;



SELECT
a.sid c1,
decode(
b.username,
NULL,c.name,
b.username) c2,
a.event c3,
a.seconds_in_wait c4,
a.wait_time c5,
a.state c6,
a.p1text c7,
a.p1 c8,
-- a.p1raw c9,
a.p2text c10,
a.p2 c11,
-- a.p2raw c12,
a.p3text c13,
a.p3 c14
-- a.p3raw c15
FROM
sys.v_$session_wait a,
sys.v_$session b,
sys.v_$bgprocess c
where
event NOT LIKE 'DFS%'
and
event NOT LIKE 'KXFX%'
and
a.sid = b.sid
and
b.paddr = c.paddr (+)
and
event NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by
4 desc
;


To find request group for particular concurrent program

select fcp.user_concurrent_program_name, fcp.concurrent_program_name, frg.request_group_name
from
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcp,
fnd_request_groups frg
where fcp.user_concurrent_program_name='Jacmel 810 Report'
and frgu.request_unit_id = fcp.concurrent_program_id
and frg.request_group_id = frgu.request_group_id


To make sequence to no cache;

alter sequence FND_DOC_SEQ_1215_S increment by 1 nocache;

To create sequence:

create sequence AS_SALES FORCE_S increment By 1 start with 1 minvalue 1
maxvalue 2000000000 nocycle noorder nocache


To find the profile values to change when we clone the instance


select spo.profile_option_name,spov.profile_option_value,spov.level_id,spov.level_value
from fnd_profile_options spo,fnd_profile_option_values spov where spo.profile_option_id=spov.profile_option_id
and spov.profile_option_value like '%PRD%';


select spo.profile_option_name,spov.profile_option_value,spov.level_id,spov.level_value,fpov.user_profile_option_name
from fnd_profile_options spo,fnd_profile_option_values spov,fnd_profile_options_vl fpov where spo.profile_option_id=spov.profile_option_id
and fpov.profile_option_name=spo.profile_option_name and fpov.user_profile_option_Name like '%Debug%'

TO find the user_profile_name

select * from fnd_profile_options_TL where profile_option_name ='ECE_OUT_FILE_PATH';

select * from fnd_responsibility_tl where responsibility_id='20674'

select * from fnd_application_vl where application_id='521'

AOL/J Test

http(s)://:/OA_HTML/jsp/fnd/aoljtest.jsp


To Kill Concurrent Manager manually (Not recommended all time This should be user when you find ICM Actual '0' and Target 1 in CM Administer form)

ps -ef | grep FND | grep -v grep | awk '{print $2}' | xargs kill -9

To kill VNC session

Vncserver -kill: 22009
vncserver -kill: 2

To Find more Memory consuming processes

ps -eo pcpu,%mem,cmd,pid|sort -k2 -r|more

To Remove file like logs,out files...etc older and 10,20,30 days corresondingly(You can change mtime variable as per your req)

find /path -type f -mtime +10 -exec rm {} \;

find /path -type f -mtime +20 -exec rm {} \;

find /path -type f -mtime +30 -exec rm {} \;

Applying a Patch Using adpatch Defaults File:

adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt logfile=654321.log patchtop=/patches/654321 driver=u654321.drv interactive=no workers=4

Select forecast_designator, forecast_set, creation_date, last_update_date from MRP_FORECAST_DESIGNATORS;

To Find DB object using any Session (here 175 is session ID when event like "db file sequential read")

select * from v$session_wait where sid=175

In below query uses values from above for ex: below 164 file id from above query output P1 Column and 60859(Block#) from P2 column

select segment_name,segment_type from dba_extents
where file_id = 164 and 60859 between block_id and (block_id + blocks - 1);

How to deal with inactive JDBC

select sid,serial#,
module, program, status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program from v$session
where type='USER' and program like 'JDBC%'
and module not like'%SVC%'
and logon_timeand (floor(mod(last_call_et,3600)/60)>30 or floor(last_call_et/3600)>1)
order by last_call_et;


select spid from v$process where addr in
(select paddr from v$session where
type='USER' and program like 'JDBC%'
and module not like'%SVC%'
and logon_timeand (floor(mod(last_call_et,3600)/60)>30 or floor(last_call_et/3600)>1))


select * from v$session where
type='USER' and program like 'JDBC%'
and module like'%SVC%'

How To Identify The apps User Using The O/S PID For Forms Users In 11i

You have to pass the UNIX process id to this script


column "User Name" format a10
column "ClPID" format a8

select
d.user_name "User Name",
b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
from
fnd_logins a, v$session b, v$process c, 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)
and a.SPID = &PID

To Check Temp tablespace utilization:

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

Long Running Requests(The below query will show request took more than 1 hr to complete)

SELECT request_id, user_concurrent_program_name,
actual_start_date date_started, actual_completion_date,
TO_CHAR (NVL ((((actual_completion_date - actual_start_date) * 100000)/60
),
0
),
'99999.999'
) time_taken
FROM fnd_conc_req_summary_v
WHERE actual_start_date > SYSDATE - 10
AND NVL (((actual_completion_date - actual_start_date) * 100000), 0) >3600
ORDER BY 3 DESC



Error out Requests

SELECT request_id, user_concurrent_program_name, completion_text,
argument_text, actual_start_date, actual_completion_date, requestor
FROM fnd_conc_req_summary_v
WHERE actual_start_date > SYSDATE - 10 AND phase_code = 'C' AND status_code = 'E'
ORDER BY user_concurrent_program_name

Order Import stats


select order_number,h.creation_date, count(*)
from oe_order_headers_all h,
oe_order_lines_all l
where h.header_id=l.header_id
and trunc(h.creation_date)>trunc(sysdate)-1
group by order_number,h.creation_date;

SELECT COUNT(*)
FROM oe_order_headers_all
WHERE creation_date > SYSDATE – 1;

SELECT COUNT(*)
FROM oe_order_lines_all
WHERE orig_sys_document_ref IN (
SELECT orig_sys_document_ref
FROM oe_order_headers_all
WHERE creation_date > SYSDATE – 1);


select sum(count(*))
from oe_order_headers_all h,
oe_order_lines_all l
where h.header_id=l.header_id
and trunc(h.creation_date)>trunc(sysdate)-1
group by order_number,h.creation_date;

SELECT COUNT(*)
FROM oe_order_lines_all
WHERE orig_sys_document_ref IN (
SELECT orig_sys_document_ref
FROM oe_order_headers_all
WHERE sold_to_org_id = 3284
AND creation_date > SYSDATE – 1);


SELECT COUNT(*)
FROM oe_order_headers_all
WHERE sold_to_org_id = 3284
AND creation_date > SYSDATE – 1;

To update responsibility effective end date (Unable to find responsibility even after end date removed)

Update WF_LOCAL_USER_ROLES set effective_end_date=to_date(’01-01-9999’,’dd-mm-yyyy’) where role_name=’&role_name’ and user_name=’&username’;


Useful Concurrent request quires

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v
where to_char(actual_start_date,'DD-MM-YYYY HH24MISS') between '13-10-2008 210000' and '14-10-2008 100000'
order by actual_start_date,'DD-MM-YYYY HH24MISS'

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v WHERE TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') BETWEEN '10162008 210000' AND '10172008 100000'
ORDER BY actual_start_date

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v WHERE TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') > '101308 210000'
AND TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') < '10142008 100000'
ORDER BY actual_start_date

Workflow:

1. select item_type, count from wf_items where end_date is null group by item_type order by 2 desc;

2. select item_type, count from wf_items where end_date is not null group by item_type order by 2 desc;


3. select item_type, count from WF_ITEM_ATTRIBUTE_VALUES group by item_type order by 2 desc;

4. select item_type, count from WF_ITEM_ACTIVITY_STATUSES group by item_type order by 2 desc;

5. select text from dba_source where name = 'WF_PURGE' and line = 2;

6. select parent_item_type, count from wf_items where item_type = 'WFERROR' and end_date is not null group by parent_item_type order by 2 desc;

7. select parent_item_type, count from wf_items where item_type = 'WFERROR' and end_date is null group by parent_item_type order by 2 desc;


To find out the raw trace name and location for the concurrent program
Input will be request id:


column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;


To check the time line of the request:

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');


SELECT M1.ORGANIZATION_ID ,M2.COMPILE_DESIGNATOR,COUNT(M1.INVENTORY_ITEM_ID) FROM MTL_SYSTEM_ITEMS_B M1,MRP_SYSTEM_ITEMS M2 WHERE M1.INVENTORY_ITEM_ID = M2.INVENTORY_ITEM_ID AND M1.ORGANIZATION_ID = M2.ORGANIZATION_ID AND M1.ORGANIZATION_ID IN (41,4) AND M2.COMPILE_DESIGNATOR IN(SELECT DISTINCT COMPILE_DESIGNATOR FROM MRP_SYSTEM_ITEMS) GROUP BY M1.ORGANIZATION_ID,M2.COMPILE_DESIGNATOR ORDER BY M1.ORGANIZATION_ID ,M2.COMPILE_DESIGNATOR

check version of Oracle Xml parser for java

under java_top

unzip -l appsborg2.zip | grep 9.0.4

For ORA-54 error:

select do.owner, do.object_name,do.object_type,dl.session_id, vs.serial#, vs.program,vs.machine, vs.osuser from dba_locks dl, dba_objects do,v$session vs where do.object_name='' and do.object_type = 'TABLE' and dl.lock_id1 = do.object_id and vs.sid = dl.session_id;

For MRP workbench performance:

SELECT *
FROM dba_indexes
WHERE table_name IN
('FND_LOOKUP_VALUES',
'MTL_ABC_CLASSES',
'MTL_SYSTEM_ITEMS_B',
'MRP_SYSTEM_ITEMS',
'MRP_PLAN_ORGANIZATIONS',
'MRP_PLANS');

alter index APPLSYS.FND_LOOKUP_VALUES_U1 rebuild online
alter index APPLSYS.FND_LOOKUP_VALUES_U2 rebuild online
alter index INV.MTL_ABC_CLASSES_U1 rebuild online
alter index INV.MTL_ABC_CLASSES_U2 rebuild online
alter index MRP.MRP_PLANS_U1 rebuild online
alter index MRP.MRP_PLAN_ORGANIZATIONS_U1 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_N1 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_N2 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_U1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N10 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N12 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N13 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N14 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N8 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N9 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N11 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N6 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_U1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N2 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N3 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N7 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N4 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N5 rebuild online

About TEMP Tablespace:

select * from v$temp_space_header

select file_id, bytes/1024/1024,user_bytes/1024/1024 from dba_temp_files

select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;

select * from v$sort_segment where tablespace_name='TEMP';

Creator of Sort Segment in Oracle 8 and above


SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

How Do You Find Who And What SQL Is Using Temp Segments?

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

For 10.1 and above:

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

The concurrent request was hold on by whom


SQL> select REQUEST_ID,To_char(LAST_UPDATE_DATE,'DD-MON-YYYY=>hh24:mi:ss') LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_DATE,REQUESTED_BY,PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_LOGIN from fnd_concurrent_requests where REQUEST_ID='&reqid';

SQL> select USER_ID,USER_NAME from fnd_user where LAST_UPDATED_BY=9852;

script to find out NLS patches applied or nor


SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;

Running concurrent request


set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999
col "Parent" form a7
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,nvl(a.os_process_id,b.os_process_id) "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
and vs.inst_id = vp.inst_id
order by 1,2
/


concurrent request status

col os form A6
col program form A40
set pages 38
set verify off
col time head Elapsed form 9999.99
col "Req Id" form 9999999
col "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a10
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a40
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep off
accept cmreqid number prompt 'What is the concurrent request id : '
select l2.meaning phasetxt
,l1.meaning statustxt
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,to_char(a.actual_start_date,'mm/dd/yy hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'mm/dd/yy hh:mi:ssAM') "Finished On"
,u.user_name || ' - ' || u.description "Submitted By"
,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
where u.user_id = a.requested_by
and a.request_id = &cmreqid
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y' and (l2.start_date_active <= sysdate and l2.start_date_active is not null)
and (l2.end_date_active > sysdate or l2.end_date_active is null)

User Log time and status:

select distinct user_name,ss.action responsibility,ss.Module form,to_char(start_time,'DD-MM-YYYY HH24:MI:SS') last_login_time,fl.end_time
from fnd_logins fl, fnd_user fu ,v$process pr,v$session ss
where fu.user_id= fl.user_id and fl.pid=pr.pid and ss.paddr=pr.addr;

select * from v$session where status='INACTIVE' and paddr in (select addr from v$process where pid in
(select pid from fnd_logins where user_id in (select user_id from fnd_user where user_name ='&USERNAME')));

To get DDL statements for Table Or Indexes:

select dbms_metadata.get_ddl('TABLE','','') from dual;

select dbms_metadata.get_ddl('INDEX','','') from dual;

DB Object Fragmentation(HWM):

Analyze table compute statistics ;

SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;