SYS_CONTEXT Basic Functions

The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

Syntax :

SYS_CONTEXT( namespace, parameter [, length] )

Arguments Detail:

namespace

An Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

parameter

A valid attribute that has been set using the DBMS_SESSION.set_context procedure.

length

Optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

Example Script:

SELECT SYS_CONTEXT ('userenv', 'AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,SYS_CONTEXT ('userenv', 'AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
SYS_CONTEXT ('userenv', 'CURRENT_SCHEMA') CURRENT_SCHEMA,
SYS_CONTEXT ('userenv', 'HOST') HOST,
SYS_CONTEXT ('userenv', 'INSTANCE_NAME') INSTANCE_NAME,
SYS_CONTEXT ('userenv', 'IP_ADDRESS') IP_ADDRESS,
SYS_CONTEXT ('userenv', 'LANG') LANG,
SYS_CONTEXT ('userenv', 'LANGUAGE') LANGUAGE,
SYS_CONTEXT ('userenv', 'MODULE') MODULE,
SYS_CONTEXT ('userenv', 'NETWORK_PROTOCOL') NETWORK_PROTOCOL,
SYS_CONTEXT ('userenv', 'NLS_CALENDAR') NLS_CALENDAR,
SYS_CONTEXT ('userenv', 'NLS_CURRENCY') NLS_CURRENCY,
SYS_CONTEXT ('userenv', 'NLS_DATE_FORMAT') NLS_DATE_FORMAT,
SYS_CONTEXT ('userenv', 'OS_USER') OS_USER,
SYS_CONTEXT ('userenv', 'SERVICE_NAME') SERVICE_NAME,
SYS_CONTEXT ('userenv', 'SESSIONID') SESSIONID,
SYS_CONTEXT ('userenv', 'SID') SID,
SYS_CONTEXT ('userenv', 'TERMINAL') TERMINAL
FROM DUAL;

FND_CONCURRENT_QUEUE Control Code Meaning

 In the FND_CONCURRENT_QUEUE table we have a column named control_code.

To get the details of the code present in the column we can use below script.

SQL> select lookup_code,meaning from apps.fnd_lookups where lookup_type = 'CP_CONTROL_CODE' order by lookup_code;

LOOKUP_CODE    MEANING
----------                    ------------------------------
A                               Activating
B                               Activated
D                               Deactivating
E                               Deactivated
H                               System Hold, Fix Manager before resetting counters
N                               Target node/queue unavailable
O                               Suspending concurrent manager
P                                Suspended
Q                               Resuming concurrent manager
R                               Restarting
T                               Terminating
U                               Updating environment information
V                               Verifying
X                               Terminated

FND_CONCURRENT_PROCESSES Table process_status_code columns meaning

 To get the details of the process_status_code column present in the FND_CONCURRENT_PROCESSES table we can use below script.

Query:
select lookup_code, meaning from  apps.fnd_lookups where lookup_type='CP_PROCESS_STATUS_CODE'

LOOKUP_CODE                    MEANING
------------------------------ ------------------------------
A                              Active
C                              Connecting
D                              Deactiviating
G                              Awaiting Discovery
K                              Terminated
M                              Migrating
P                              Suspended
R                              Running
S                              Deactivated
T                              Terminating
U                              Unreachable
Z                              Initializing

Concurrent Processing – Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) & Effected Tables

To avoid running out of space on your disk drives, you should periodically

delete Oracle Applications log files, output files and purge these tables

with the FNDCPPUR program.

The FNDCPUR purge program maintains the number of log and output files the

the operating system retains, and manage tables that store information about

concurrent requests and concurrent manager processes.

You can run the program FNDCPPUR once and automatically resubmit the program

for your specific time intervals.

- Every 30 days for normal usage.

- Every two weeks (14 days) for heavy usage.

- If are using the AGE mode, set the Mode Value to 7 or higher to retain the most recent days of concurrent request data, log files, and report output files. For optimum purging, the request could be scheduled to run on a daily basis off-hours using the AGE that best meets the business needs.

WARNING:When you purge concurrent request information, you lose audit details which are used by the Signon Audit Concurrent Requests report.

FNDCPPUR Program options:

ENTITY = AL : Purge of concurrent requests, concurrent managers, request log files, manager log files and report output files. The following tables are purged

-  Fnd_Concurrent_Processes

-  Fnd_Dual

-  Fnd_Concurrent_Requests,

-  Fnd_Run_Requests

-  Fnd_Conc_Request_Arguments

-  Fnd_Dual

-  Fnd_Context_Env

-  Deletes concurrent requests' log and out files from OS

ENTITY = MANAGER: Purge of concurrent managers and manager log files.

The following tables are purged

-  Fnd_Concurrent_Processes

-  Fnd_Dual

-  Deletes concurrent manager log files from OS

ENTITY = REQUEST: Purge of concurrent requests, request log files, and output files.

The following tables are purged

- Fnd_Concurrent_Requests,

- Fnd_Run_Requests

- Fnd_Conc_Request_Arguments

- Fnd_Dual

- Deletes concurrent requests' log and out files from OS

Mode:

 AGE: The number of days for which to save concurrent request history.

COUNT” Number of records

Mode Value: valid values are 1 - 9999999

User Name: application username

Oracle ID: Oracle ID

Program Application:   application

Program: program

Manager: application associated with the concurrent manager

Manager: concurrent manager

Resp. Application:   application associated with the responsibility

Responsibility:   responsibility or "All".

Report: No       Run the program but do not generate a report.

Yes      Run the program and generate a report.

Tables that are updated when Purge Concurrent Request and/or Manager Data Program Program( FNDCPPUR) is started:

FND_CONCURRENT_REQUESTS

This table contains a complete history of all concurrent requests. This table should ideally be kept at around 4 - 5k records and should be regularly purged as part of normal 'housekeeping' by running the standard FNDCPPUR program. This is a part of basic system administration and it is recommended that for the average instance, it should be run every 30 days or so. Allowing the table ( and others ) to increase in size can affect performance.

FND_RUN_REQUESTS

When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS

This table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL

This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES

This table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST

This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

 This table contains the concurrent programperformance statistics generated by the Purge.

FND_ENV_CONTEXT

This table stores information about environment name and value for each of the concurrent process

Note:

The following tables can also be purged depending on the parameters being used:

FND_FILE_TEMP

FND_CRM_HISTORY

FND_TM_EVENTS

FND_TEMP_FILES

FND_CONFLICTS_DOMAIN

Also the linked tables of

FND_CONC_REQUEST_ARGUMENTS

FND_CONC_PP_ACTIONS

FND_RUN_REQ_PP_ACTIONS

For example, the 'Purge other' parameter has the following function:

No => Do not delete records from FND_DUAL.

Yes => Delete records from FND_DUAL.

Ref: Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) (Doc ID 104282.1)