EBS R12 Find all Tablespace Free Script

--Find all Free Tablespace

SELECT /* + RULE */ df.tablespace_name "Tablespace",

df.bytes / (1024 * 1024) "Size (MB)",

SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

FROM dba_free_space fs,

(SELECT tablespace_name,SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

fs.bytes / (1024 * 1024),

SUM(df.bytes_free) / (1024 * 1024),

Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

FROM dba_temp_files fs,

(SELECT tablespace_name,bytes_free,bytes_used

FROM v$temp_space_header

GROUP BY tablespace_name,bytes_free,bytes_used) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

ORDER BY 4 DESC;

Oracle EBS query to find profile option Printer has a value at site level

---Oracle R12 Printer profile setup

 SELECT t.user_profile_option_name "Profile Option",

decode(a.level_id, 10001, 'Site',

10002, 'Application',

10003, 'Responsibility',

10004, 'User') "Level",

decode(a.level_id, 10001, 'Site',

10002, b.application_short_name,

10003, c.responsibility_key,

10004, d.user_name) "Level Value",

a.profile_option_value "Profile Value"

FROM fnd_profile_option_values a,

fnd_application b,

fnd_responsibility c,

fnd_user d,

fnd_profile_options e,

fnd_profile_options_tl t

WHERE a.profile_option_id = e.profile_option_id

AND e.profile_option_name = 'PRINTER' ---

AND a.level_value = b.application_id(+)

AND a.level_value = c.responsibility_id(+)

AND a.level_value = d.user_id(+)

AND t.profile_option_name = e.profile_option_name

AND t.LANGUAGE = 'US'

ORDER BY e.profile_option_name, a.level_id DESC;

How to Get Application User Login Session Details

---Get Application User Login Session Details  Oracle R12

SELECT

SUBSTR(d.user_name,1,50) User_Name

, a.pid Process_ID

, b.sid Session_ID

, b.serial#

, to_char(c.start_time,'DD-MON-RRRR HH:MI:SS AM') start_time

, to_char(c.end_time,'DD-MON-RRRR HH:MI:SS AM') end_time

FROM

  v$process a

, v$session b

, fnd_logins c

, fnd_user d

WHERE a.pid = c.pid

AND d.user_name = UPPER (:p_user_name)

AND d.user_id = c.user_id

AND a.addr = b.paddr ;

Find Tablespace Free

/*Query Find Tablespace Free */

  SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / POWER (2, 20)) FREE_MB

    FROM (SELECT BYTES, TABLESPACE_NAME

            FROM DBA_FREE_SPACE

           WHERE TABLESPACE_NAME IN

                    ('APPS_TS_TX_DATA',

                     'APPS_TS_TX_IDX',

                     'APPS_TS_SUMMARY',

                     'APPS_TS_INTERFACE',

                     'APPS_TS_NOLOGGING',

                     'APPS_TS_ARCHIVE'))

GROUP BY TABLESPACE_NAME; 

Hold and Unhold concurrent program schedule

-----Check how many program are hold-------------------

SELECT *

  FROM fnd_concurrent_requests

 WHERE hold_flag = 'Y' AND request_id = NVL(:p_request_id,request_id)

-----all holded program to Unhold---------------------

Update  fnd_concurrent_requests SET hold_flag='N' 

where hold_flag='Y'

-----------Hold-------------------

Update  fnd_concurrent_requests SET hold_flag='Y' 

where hold_flag='N'

 and request_id 

IN(

Select fcr.request_id

FROM apps.fnd_concurrent_requests fcr ,

  apps.fnd_user fu ,

  apps.fnd_concurrent_programs fcp ,

  apps.fnd_concurrent_programs_tl fcpt ,

  apps.fnd_printer_styles_tl fpst ,

  apps.fnd_conc_release_classes fcrc ,

  apps.fnd_responsibility_tl frt ,

  apps.fnd_lookups fl

WHERE fcp.application_id       = fcpt.application_id

AND fcr.requested_by           = fu.user_id

AND fcr.concurrent_program_id  = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id  = fcpt.concurrent_program_id

AND fcr.responsibility_id      = frt.responsibility_id

AND fcr.print_style            = fpst.printer_style_name(+)

AND fcr.release_class_id       = fcrc.release_class_id(+)

AND fcr.status_code            = fl.lookup_code

AND fl.lookup_type             = 'CP_STATUS_CODE'

AND fcr.phase_code             = 'P'

AND frt.language               = 'US'

AND fpst.language              = 'US'

AND fcpt.language              = 'US')

 

Commit; 

---------------------Unhold--------------------------------

Update  fnd_concurrent_requests SET hold_flag='N' 

where hold_flag='Y'

 and request_id 

IN(

Select fcr.request_id

FROM apps.fnd_concurrent_requests fcr ,

  apps.fnd_user fu ,

  apps.fnd_concurrent_programs fcp ,

  apps.fnd_concurrent_programs_tl fcpt ,

  apps.fnd_printer_styles_tl fpst ,

  apps.fnd_conc_release_classes fcrc ,

  apps.fnd_responsibility_tl frt ,

  apps.fnd_lookups fl

WHERE fcp.application_id       = fcpt.application_id

AND fcr.requested_by           = fu.user_id

AND fcr.concurrent_program_id  = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id  = fcpt.concurrent_program_id

AND fcr.responsibility_id      = frt.responsibility_id

AND fcr.print_style            = fpst.printer_style_name(+)

AND fcr.release_class_id       = fcrc.release_class_id(+)

AND fcr.status_code            = fl.lookup_code

AND fl.lookup_type             = 'CP_STATUS_CODE'

AND fcr.phase_code             = 'P'

AND frt.language               = 'US'

AND fpst.language              = 'US'

AND fcpt.language              = 'US')

How to enable oracle EBS Diagnostics or Examine menu

Steps 1:

Navigate to System Administrator responsibility -> Profile -> System ->

Enter profile name: Utilities:Diagnostics

Enter Application User for whom you want to enable Diagnostics-> Examine

Steps 2: 

Entry Yes at User level and Save the Changes

Note :–

Don't set Yes at Site level.       (if you want to enable this option for all Oracle application users)

Steps 3:

Again navigate to System Administrator responsibility -> Profile -> System ->

Enter profile name: Hide Diagnostics menu entry

Enter Application User for whom you do not want to hide Diagnostics menu entry

Note :–

Don't change value at Site level.

Oracle Apps R12: Check if the patch applied or not

--Check if the patch applied or not

This table includes the defined bugs on the system:

SELECT    bug_number

       || '.'

       || aru_release_name

       || '.'

       || UPPER (trackable_entity_abbr)

       || '.'

       || baseline_name

          Patch

FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:

SELECT patch_name

FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'

Oracle EBS Windows Name Changes

System Admin Responsibility: 

Go to  Profile -> System 

Find the Profile Name: Site Name

Calculate Average Completed Time on Concurrent Program

/*Calculate Average Oracle Concurrent Program Completed Time*/

select avg(fr1.actual_completion_date - fr1.actual_start_date) avgCPtime

  from fnd_concurrent_requests fr1

         , fnd_concurrent_requests fr2

  where fr2.request_id = NVL(:P_request_ID, fr2.request_id)

  and fr1.concurrent_program_id = fr2.concurrent_program_id

  and fr1.program_application_id = fr2.program_application_id

  and fr1.actual_start_date is not null

  and fr1.actual_completion_date is not null;

How to Kill the session in Oracle

select  *
  from  v$lock
  where id1 = (
               select  object_id
                 from  dba_objects
                 where owner = 'XXCUS'
                   and object_name = 'TEMP_JE_DETAILS_K_GLOBAL'
              );
           
           
ALTER SYSTEM KILL SESSION '265,2978';

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and s.sid =265 ; 

Getting Error in R12 FNDLOAD: command not found

uploading or downloading FNDLOADS we may get following error

FNDLOAD: command not found

Solution: Apply . Env file as shown below

After apply .env file the error will be resoled FNDLOAD: command not found issue 

Run command:

cd /prod/oracle/apps/apps_st/appl/

. APPSGST_testr12.env

File transfer from Windows to Linux

---CSV file move

The PuTTY Secure Copy client(PSCP), is a tool for transferring files securely between computers using an SSH connection:

First of  Install from https://www.putty.org/
 
Putty installer least version. other wise Set PATH in environment variable
C:\Program Files\PuTTY\;

Open Command Prompt or CMD.

Run the below command :

pscp C:\Users\Documents\AR_INVOICE_UPLOAD.csv appldev@192.99.999.99:/u01/DEV/apps/apps_st/appl/xxcus/12.0.0/bin/AR_INVOICE_UPLOAD.csv

Then csv file transfer from windows to server .

How to find the Output file path in oracle EBS

cd $APPLCSF/log

cd $APPLCSF/out – PDF/excel/out

For eg : In stage :-

Location :-  /u03/app/STAGE/inst/apps/STAGE_XXXXtest01/logs/appl/conc/out

/u03/app/STAGE/inst/apps/STAGE_XXXXtest01/logs/appl/conc/out$ ls -lrt *50966765*

How to find the Lock table in oracle apps

SELECT   a.session_id,
         a.oracle_username,
         a.os_user_name,
         b.owner "OBJECT OWNER",
         b.object_name,
         b.object_type,
         a.locked_mode
  FROM   (SELECT   object_id,
                   SESSION_ID,
                   ORACLE_USERNAME,
                   OS_USER_NAME,
                   LOCKED_MODE
            FROM   v$locked_object) a, (SELECT   object_id,
                                                 owner,
                                                 object_name,
                                                 object_type
                                          FROM   dba_objects) b
 WHERE   a.object_id = b.object_id;

/

select sid,

decode(block ,0,'NO','YES') BLOCKER,

decode(request,0,'NO','YES') WAITER

from v$lock

where request > 0 or block > 0 order by block desc;

How to find Application Instance Login URL same database

---Application Instance Login URL

SELECT profile_option_value URL

  FROM fnd_profile_option_values

 WHERE     profile_option_id =

              (SELECT profile_option_id

                 FROM fnd_profile_options

                WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')

       AND level_value = 0;

How to Check the File Version in Oracle Application

Below command for 12.1.X and 

Run the same command for 12.2.X then set the environment file e.g. $ . EBSapps.env

Check the File version

Two ways to check version of file.

    1. Adident    2. Strings

 ADIDENT Utility in ORACLE application is used to find version of any file.

 1st Method

   Syntax : adident Header filename.

   e.g If you want to find out the version of appvndrb.pls

        $ cd AP_TOP/patch/115/sql

        $ adident Header appvndrb.pls

        o/p :

         appvndrb.pls:

         $Header appvndrb.pls 120.78.12010000.83 2010/04/27 21:00:55 vinaik ship

2nd Method :

 Using Strings:

  Syntax : strings -a Top_name/location of file/filename | grep '$Header'

   e.g $ strings -a $AP_TOP/patch/115/sql/appvndrb.pls | grep '$Header'

   o/p : /* $Header: appvndrb.pls 120.78.12010000.83 2010/04/27 21:00:55 vinaik ship $ */

                                              OR

      $ cd $AP_TOP/patch/115/sql

      $ strings -a appvndrb.pls | grep '$Header'

OR

$ grep '$Header' $INV_TOP/patch/115/sql/INVTXGGB.pls

--

Best Sellers in Shoes & Handbags

How to Find the Node, Platform and IP address in Instance

 Oracle apps Node, Platform and IP address in Instance

Navigation: System Administrator -> Install -> Nodes

How to Find Application Top by particular application name

  SELECT    B.APPLICATION_ID,
                    B.APPLICATION_SHORT_NAME,
                    B.BASEPATH APPLICATION_TOP,
                    T.APPLICATION_NAME,
                    T.DESCRIPTION,
                     B.PRODUCT_CODE
     FROM   FND_APPLICATION_TL T
                  ,FND_APPLICATION B
    WHERE   B.APPLICATION_ID = T.APPLICATION_ID
     AND    T.APPLICATION_NAME = :KNOW_APPLICATION_NAME

How to increase oracle froms session times

Session Timeout

Log in : System Administrator

Select the Profile option (Profile - > System -> ICX:Session Timeout) in user level and set time.

How to Hide Diagnostics Menu in Apps Forms

Hide Diagnostics Menu