DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Sunday, April 26, 2015

   Query to check wordsize ( 32 bit or 64 bit ) of Oracle Database



select (case length(address) when 16 then '64-bit Oracle'when 8 then '32-bit Oracle' when 32 then '128-bit Oracle' end) Wordsize
from v$sql where rownum <2;

Friday, April 17, 2015

Query to find Rman backupset sizes




select ctime "Date"  
       , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type  
      , bsize "Size MB"  
   from (select trunc(bp.completion_time) ctime  
      , backup_type  
       , round(sum(bp.bytes/1024/1024),2) bsize  
      from v$backup_set bs, v$backup_piece bp  
       where bs.set_stamp = bp.set_stamp  
      and bs.set_count  = bp.set_count  
      and bp.status = 'A'  
     group by trunc(bp.completion_time), backup_type)  

   order by 1, 2; 



To find out the Temp Tablespace Usage




set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;


To find the jobs running under all concurrent 

managers in the instance


set linesize 140
col CONTAINER_NAME for a28
col PROCID for 999999
col PROCID for a10
col TARGET for a6
col TARGET for 999
col ACTUAL for a6
col ACTUAL for 999
col ENABLED for a7
col COMPONENT_NAME for a30
col STARTUP_MODE for a12
col COMPONENT_STATUS for a17
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name,
DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,fcq.ENABLED_FLAG ENABLED,fsc.COMPONENT_NAME,fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp,
fnd_svc_components fsc where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%' order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE

Query to get  the latest application version in oracle Apps



select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done", BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL


Query to check List of languages installed in oracle Apps 



Select distinct NLS_LANGUAGE, LANGUAGE_CODE,NLS_TERRITORY,INSTALLED_FLAG
from fnd_languages
ORDER BY NLS_LANGUAGE


Query to see the top 10 io datafiles for write activity





select * from (
select substr(f.name,8,(instr(f.name,'/',9,1) -8)) fsmtpt,
substr(f.name,(instr(f.name,'/',-1,1)+1)) fname,
s.phyrds, s.phywrts,s.lstiotim,s.avgiotim
from v$datafile f, v$filestat s
where f.file# = s.file#
order by 4 desc)
where rownum < 11;

Query to get Patch details in the oracle Apps 


select
D.PATCH_NAME,
B.APPLICATIONS_SYSTEM_NAME,
-- B.INSTANCE_NAME,
B.NAME,
C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID,
A.PATCH_RUN_ID,
A.SESSION_ID,
A.PATCH_TOP,
A.START_DATE,
A.END_DATE,
A.SUCCESS_FLAG,
A.FAILURE_COMMENTS
from
AD_PATCH_RUNS A,
AD_APPL_TOPS B,
AD_PATCH_DRIVERS C,
AD_APPLIED_PATCHES D
where
A.APPL_TOP_ID = B.APPL_TOP_ID AND
A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID AND
C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID AND
A.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID
in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&patch_number'))
ORDER BY 3;

Query to know what changes a patch has made on the 

instance


Query to check changes made by the patch in oracle application as follows,.

Select
J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
max(F.VERSION) latest,
E.ACTION_CODE action
from
AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F,
AD_PATCH_RUNS G,
AD_APPL_TOPS H,
AD_PATCH_DRIVERS I,
AD_APPLIED_PATCHES J
where
A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and
C.FILE_ID = D.FILE_ID and
E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and
D.FILE_ID = F.FILE_ID and
G.APPL_TOP_ID = H.APPL_TOP_ID and
G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and
B.PATCH_RUN_ID = G.PATCH_RUN_ID and
C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&Patch_Number'))
GROUP BY
J.PATCH_NAME,
H.APPLICATIONS_SYSTEM_NAME,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;

Oracle SQL query that shows Oracle users connected and the sessions number for user



SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
    FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;


Find Top 10 SQL by reads per execution



SELECT *
  FROM SELECT ROWNUM,
                 SUBSTR (a.sql_text, 1, 200) sql_text,
                 TRUNC (
                    a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                    reads_per_execution,
                 a.buffer_gets,
                 a.disk_reads,
                 a.executions,
                 a.sorts,
                 a.address
            FROM v$sqlarea a
        ORDER BY 3 DESC)
 WHERE ROWNUM < 10;