, , , ,

How to Kill The Toad / Oracle session?

Whenever we Procedure taking too much time to compile and we want to close the session or cancle the compilation we need to kill the session.

If we need the PC Name Based Query ::

select * from   v$session where TERMINAL =’Name of user’s Computer’

Query for when we know the Object Name and we need to close the session based upon Object::

SELECT * FROM v$session WHERE SID IN (SELECT UNIQUE SID FROM v$access WHERE OBJECT=’&OBJECT_NAME’);

OR you may use below query and output of below query will give ready made statements to kill these locks and you can directly use it…

SELECT ‘alter system kill session ”’||SID||’,’||serial#||”’;’
FROM v$session
WHERE TERMINAL =’prashku4′
and SID IN (SELECT UNIQUE SID
FROM v$access)
WHERE OBJECT=’&OBJECT_NAME’);

select * from  v$access

Example:
alter system kill session ‘8542,2456258’;

/by
, , , , ,

Tracing techniques in oracle applications

This is one of the good reference documents from metalink.

WHAT TO SET UP BEFORE GENERATING THE TRACE

These steps must be performed by the DBA on the database server.

1. Set TIMED_STATISTICS to TRUE.
   For performance issues, make sure TIMED_STATISTICS is turned on, before attempting to generate the trace.

   Set the following in the init.ora file:
      TIMED_STATISTICS=TRUE
      OR
      in SQL*Plus: ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

2. Set the location of the trace output.
   Set the following in the init.ora file: USER_DUMP_DEST =

3. Create the PLAN_TABLE to hold the output of the explain plan. Run the SQL script called UTLXPLAN.SQL to create this in the apps schema. This script is usually in $ORACLE_HOME/rdbms/admin.

4. If the init.ora file has been updated, you must shut down and restart the database before the changes will take effect.
   TYPES OF TRACE – HOW TO TURN TRACE ON
   Regardless of the type of trace file you create, make sure you note the time that you create it.

1] Form Trace

Toggle trace on/off on the form, to trace specific application functions. Make sure you go in fresh (sign off/on to the application), since somequeries are cached and may not be executed on subsequent visits to theform.

From the menu, select Help–>Diagnostics–>Trace to turn trace on (when checked, it is on).
   Perform the action to be traced.
   From the menu, turn trace off, by selecting Help–>Diagnostics–>Trace (it should now be unchecked).

2] Concurrent Program Trace
   A. This will turn trace on for each execution of this program. In Release 11.0 and lli, check the Enable Trace checkbox for the concurrent program on the Concurrent Programs form. After running the program to be traced, make sure you uncheck the Enable Trace checkbox.
   Select the System Administrator responsibility.
   Navigation = Concurrent -> Programs -> Define. Query the concurrent program you want to trace. Check the Enable Trace checkbox and save.
   OR
   B. How to generate a raw trace file with binds and/or waits for 11.5.10:
1. Log into applications as System Administrator and Navigate to the System Profile Values Form. Select the profile called Concurrent: Allow Debugging and change the value to Yes at the appropriate level.
2. Allow pop-ups on your browser.
3. Navigate to the Submit a New Request form and select a job and enter all parameters for that job
4. Select the Debug Options Button and this will take you to Create Debug Rule in Oracle Application Manager
5. Select the appropriate Debug Option Value for SQL Trace only and then check the box
6. Hit OK twice and then Submit the job
7. Raw trace file with options selected will be located in the appropriate directory.

3] Database Level Trace
   This will turn trace on for all processes that are running in the instance and should only have to be used in Release 10.7, for concurrent programs. (This has to be done by the DBA.)
   Set the following in the init.ora file:SQL_TRACE=TRUE
   Shut down and restart the database.After generating the trace file, shut down and restart the database with the original init.ora.

4] Report Trace
If you are on 10.7 and need to trace an Oracle Report, you can modify the report to turn trace on for that session.
   a. Convert the report from rdf to rex: $ORACLE_HOME/bin/r25convm batch=yes userid=> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
   b. Edit the rexfile and search for the beforerep trigger in the report.
      Locate the following code:
         IF (:p_trace_switch = ‘Y’) THEN
            SRW.DO_SQL(‘alter session set sql_trace TRUE’);
         END IF;
      Comment out the IF and END IF lines.
   c. Save the report.
   d. Convert the report from rex to rdf: $ORACLE_HOME/bin/r25convm batch=yes userid= > stype=rexfile source=REPORT_NAME.rex dtype=rdffile overwrite=yes

5] Self Service page (like a Forms trace, but for self service web apps)
   a. Set the FND:Diagnostics profile: Responsibility = System Administrator
      Navigation: Profile > System User: Enter User name
      Query the Profile: ‘FND:Diagnostics’
      Set the ‘FND:Diagnostics’ profile to Yes at User level

   b. Login to Self Service under the same user the profile was set for.
   c. Turn Trace on: Click the Diagnostic link at the top of the page.
      It shows two options: Show Log and Set Trace Level
      Select ‘Set Trace Level’
      Click Go.
      Select one of the following options:
      Disable Trace – used to end the trace
         Trace (Regular) – just like a forms trace
         Trace with Binds – record the bind variables in the trace
         Trace with waits – Good for performance issues
         Trace with binds and waits – combines both of the above
      Click Save.
    
   d. Perform the action to be traced in Self Service.
      Multiple trace files may be generated in the usual trace directory.
   e. Turn trace off: Select the Diagnostic link
      Click on option: Set Trace Level
      Note all of the trace numbers listed
      Click Go
      Select: Disable Trace
      Click Save

WHAT TO DO AFTER GENERATING THE TRACE FILE

These steps should be performed by the DBA, on the database server.

1. Find the trace directory. Get the location of user_dump_dest. Log into SQL*Plus as the apps user.
   select value from V$PARAMETER where name = ‘user_dump_dest’
2. Find the trace file for your process.Go to the directory you found in step 1 (in UNIX, use cd). Look for a file (.trc) that was created at the time you started your process (in UNIX, use ls -ltr).
3. Run tkprof with explain plan. Go to a directory in which you have write privilege (in UNIX, use cd).

Run tkprof:
tkprof explain=

HOW TO GET AN EXPLAIN PLAN FOR A SQL STATEMENT

Sometimes you may have a need to get an explain plan for a specific SQL statement. If you have the sql statement, you can get the explain plan for it.
This should be run on the same instance that the sql statement came from.
In your apps account —
Run the following script:
delete from plan_table
where statement_id = ‘tmp’

explain plan

set statement_id = ‘tmp’
for

/
set pages 100
col operation format a36
col options format a11
col object_name format a30

select lpad(‘ ‘,2*(level-1))operation operation,

options, object_name
from plan_table
where statement_id = ‘tmp’
connect by prior id = parent_id
and statement_id = ‘tmp’
start with id = 1
and statement_id = ‘tmp’
order by id
/
delete from plan_table
where statement_id = ‘tmp’
/
commit;

/by
, , , , ,

How to configure System Items Flexfield

1.    First define value set for item flexfield segments.
Go to inventory responsibility and navigate to value set.
Setup -> Flexfield->Validation->Sets
2.       Define Value Set for Item Section.
Choices: List Type, Enter format validation of your own choice, validation type.

3.       Define Value Set for Sub Section.
Sub Section Value Set is dependent on item section.
To configure select dependent validation type and click on Edit Information.
4.       Define Value Set For Serial No.
Serial No is also dependent on item section.
5.       Now value set for Condition Code and future segment.
Ok we are done with first part of creating value set. Now for second step .

6.       Navigate to Key Flexfield Segments Definition Form.
         Setup -> Flexfield->Key->Segments
7.       Now search with flexfield title “System Items”.
8.       Click on Segments and navigate to  Segment Summary Window.
Now enter your desired item segments name and their corresponding value sets.
Column indicates the database fields that will hold entered value for specific segment.
9.       After entering segments summary navigate back to KFF segments window. Save form and check freeze flex field definition option.
10.       Now compile your KFF.
      It is configured now.
/by
, , , ,

How to Define Cost Type In Oracle Inventory

Cost type is required while we define any new organization.

Some cost types which are already defined are Average and Frozen.
Steps to Define.
Choose inventory responsibility and navigate to form.
Setup -> Costs -> Cost Types
On Cost Type Window

Default cost type is basically used for items where any specific type is not defined.
Date to inactivate cost is used to inactive your cost at specific date except for average and frozen cost type because frozen and average cost cannot by inactivate.
If multi org is checked then this type is available for all organization but not its costs.
If unchecked then this type is only available for organization which created it.
If allow update is checked then this cost type can be updated by available average cost update processes.

If for this type you want component yield effect in rollup then check this box.

Once Cost Type is defined let’s Move forward to Defining and configuring Inventory Organization

/by
, , , ,

New Terminologies in R12

Some of the new terminologies used in Oracle Applications Release 12 are:

11i Vs. 12

Sets of Books = Ledgers
Global Accounting Engine = Subledger Accounting
AX Posting Sets of Books = Secondary Ledgers
Global Intercompany System = Advanced Global Intercompany System
GL Intercompany Accounts = Intracompany Balancing
Translated Currency = Balance level RCs
Multiple Reporting Currencies = Reporting Currencies
MRC Primary Set of Books = Primary Ledger
MRC Reporting Set of Books = Reporting Currency
Thin MRC Reporting Book = Journal level RC
Full MRC Reporting Book = Subledger level RC
Translated Balance = Balance Level RC

Hope it will be helpful. ………..

/by