Auto Extend Tablespace scripts

1 - A script for summation of datafiles in tablespaces, ie a tablespace space usage script.

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

2- A script that can give accurate tablespace usage statistics that include auto extend. It determines the space usage WITH auto-extend has taken into account, not just current free space.

select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_data_files a,V$DATAFILE b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID
union
select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_temp_files a,V$tempfile b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID;

Reference: Auto Extend Tablespace scripts (Doc ID 1908578.1)

How to find the OPP log file location in oracle application 11i/R12.1 and R12.2

Below script to find the OPP log file location in oracle E-Business Suite.

Script:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id;

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'?

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'.

How to get it Licensed ?

Please do the following:

• Go to SYSADMIN responsibility / Oracle Application manager / License Manager / Country Specific Localization.
• Select Singapore
• Retest your issue.

Below link to know Licensed enable or not.

http://krishnasudagani.blogspot.in/2017/04/how-to-find-asiapacific-localizations.html 

How to find Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite (R12.1 / R12.2)

Below script to find the Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite instance.

Script:

SELECT SUBSTR(a.application_name, 1,40) product_name
, a.application_id
, SUBSTR(application_short_name, 1,4) short_name,
DECODE(b.status,'I','INSTALLED',
DECODE(b.status,'S','SHARED',
DECODE(b.status,'N','NOTINSTALLED',b.status))) status
FROM fnd_application_vl a, fnd_product_installations b
WHERE a.application_id = b.application_id
and a.application_short_name like Upper('ja')
and upper(a.application_name) like upper('%asia%')
ORDER BY 1;

Output:

How to check the adgrants version in oracle database

Below script to check the adgrants version in oracle database.

Script:

SELECT DISTINCT RPAD(h.filename,15) ,
  RPAD(g.version,20) ,
  RPAD(TRUNC(c.end_date),12) ,
  RPAD(a.bug_number,10) ,
  RPAD(e.patch_name,10) ,
  RPAD(b.applied_flag,4) "File:vers:Date:Bug:Patch:Apply"
FROM ad_bugs a,
  ad_patch_run_bugs b,
  ad_patch_runs c,
  ad_patch_drivers d ,
  ad_applied_patches e,
  ad_patch_run_bug_actions f ,
  ad_file_versions g,
  ad_files h
WHERE a.bug_id              = b.bug_id
AND b.patch_run_id          = c.patch_run_id
AND c.patch_driver_id       = d.patch_driver_id
AND d.applied_patch_id      = e.applied_patch_id
AND b.patch_run_bug_id      = f.patch_run_bug_id
AND f.patch_file_version_id = g.file_version_id
AND g.file_id               = h.file_id
AND h.filename LIKE 'adgrants.sql'
ORDER BY 1 DESC;

Output:

How to check Oracle Application Modules Licensed or Not.

Below script to find Oracle Application Modules are Licensed or Not.

Script:

SELECT fat.application_name
      ,fa.application_id
      ,fpi.patch_level
      ,decode(fpi.STATUS,'I','Licensed', 'N','Not Licensed','S','Shared','Undetermined') STATUS
      ,fpi.*
FROM fnd_product_installations fpi
      ,fnd_application fa
      ,fnd_application_tl fat
WHERE fpi.application_id = fa.application_id
AND fat.application_id = fa.application_id
AND fat.LANGUAGE = 'US';

Output:

How to check schema size in oracle database

Below script to know the schema size in oracle database.

Script:

set linesize 160
set pagesize 10000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

How to find all responsibilities of a user in oracle apps

Below script to know about the all responsibilities of a user in oracle apps.

Script:

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",    
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('KRISHNA')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;

How to find which user have SYSADMIN responsibility in oracle apps

Below script to know the users who have SYSADMIN responsibility in oracle apps.

Script:

SELECT fu.*
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';

Output:

Script for concurrent request details using Request Id

Below script for running concurrent request details using Request Id.

Script:

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Request_ID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600,
'E.SQL_TEXT .............................................: '||E.SQL_TEXT
from v$session s, v$process p, V$SQL E
where p.addr=s.paddr AND s.SQL_ADDRESS = E.ADDRESS and
s.sid in (SELECT d.sid FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b, v$process c,v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID);

Script for scheduled concurrent programs on particular date and time

Below script for scheduled concurrent details on exact date and time.

Script:

SELECT   fcr.request_id,
         DECODE (fcpt.user_concurrent_program_name,
                 'Report Set', 'Report Set:' || fcr.description,
                 fcpt.user_concurrent_program_name)
            CONC_PROG_NAME,
         argument_text PARAMETERS,
         NVL2 (fcr.resubmit_interval,
               'PERIODICALLY',
               NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))
            PROG_SCHEDULE_TYPE,
         DECODE (
            NVL2 (fcr.resubmit_interval,
                  'PERIODICALLY',
                  NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
            'PERIODICALLY',
               'EVERY '
            || fcr.resubmit_interval
            || ' '
            || fcr.resubmit_interval_unit_code
            || ' FROM '
            || fcr.resubmit_interval_type_code
            || ' OF PREV RUN',
            'ONCE',
            'AT :' || TO_CHAR (fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
            'EVERY: ' || fcrc.class_info
         )
            PROG_SCHEDULE,
         fu.user_name USER_NAME,
         requested_start_date START_DATE,
         requested_start_date + 5 / 24 + (30 / (24 * 60)) ISO_Start_date --- +5.30
  FROM   apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_concurrent_requests fcr,
         apps.fnd_user fu,
         apps.fnd_conc_release_classes fcrc
WHERE       fcpt.application_id = fcr.program_application_id
         AND fcpt.concurrent_program_id = fcr.concurrent_program_id
         AND fcr.requested_by = fu.user_id
         AND fcr.phase_code = 'P'
         AND fcr.hold_flag = 'N'
        --AND fcr.requested_start_date > SYSDATE
         and fcr.requested_start_date >= to_date('01/29/2016 22:00:00','mm/dd/yyyy HH24:MI:SS')
         and fcr.requested_start_date <= to_date('01/30/2016 10:30:00','mm/dd/yyyy HH24:MI:SS')
                                AND fcpt.LANGUAGE = 'US'
         AND fcrc.release_class_id(+) = fcr.release_class_id
         AND fcrc.application_id(+) = fcr.release_class_app_id;

Hope this script helps you :)

Script for RMAN restore current status

Below script to know RMAN restore current status and remaining time to complete the refresh.

Script:

set lines 199

select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES, 

trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA 

from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';

Output: