Useful Queries

**************Sort Space Usage by Session***********************

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;


------------------------

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

******* OEM repository tables names*****************

MGMT$AVAILABILITY_HISTORY
MGMT$TARGET,
MGMT$ALERT_HISTORY.



****************to check unused tables or least acessesed tables*************
USER_TAB_MODIFICATIONS
DBA_TAB_MODIFICATIONS --> To find insert,update,delete operations
ALL_TAB_MODIFICATIONS
DBA_HIST_SQL_PLAN


*****************Compression Segment ***********************

declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('PSAPSR3'),
ownname => 'SAPSR3',
tabname => upper('/BIC/EZGAT_C14'),
partname => NULL,
comptype => dbms_compression.COMP_FOR_OLTP,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str);
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;

/

Number of rows in the table
=====================
SELECT COUNT(*) FROM SAPSR3./BIC/EZGAT_C14;

 COUNT(*)
----------
  14021433

Identify no of blocks allocated to that table
===============================
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='/BIC/EZGAT_C14' AND OWNER='SAPSR3';

SEGMENT_NAME                                                                      TABLESPACE_NAME                   EXTENTS     BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ---------- ----------
/BIC/EZGAT_C14                                                                    PSAPSR3                               184     109568

To check the original table size now before compress
=======================================
SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='/BIC/EZGAT_C14' AND OWNER='SAPSR3';

SEGMENT_NAME                                                                      BYTES/1024/1024    EXTENTS

--------------------------------------------------------------------------------- --------------- ----------
/BIC/EZGAT_C14                                                                          1572.0625        194

To check the block size
=================
SHOW PARAMETER db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

************************

Script to take backup of concurrent Request table backup:

create table  APPS.XXBILT_DAILY_CONC_REQ_BKP  

AS select to_char( ACTUAL_START_DATE, 'dd-mon-yyyy hh24:mi:ss' ) ACTUAL_START_DATE_FORMAT,

       trunc( ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe ) day, 

       trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24, 24 ) )  Hour, 

       trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24*60, 60 ) )  Minute, 

 trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24*60*60, 60 ) ) Second, i.*

FROM FND_CONC_REQ_SUMMARY_V i

WHERE trunc(ACTUAL_START_DATE) = trunc(sysdate-1);

select * from APPS.XXBILT_DAILY_CONC_REQ_BKP  

CREATE OR REPLACE PROCEDURE APPS.XXBILT_DAILY_CONC_REQ_BKP_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)

AS

BEGIN

INSERT INTO APPS.XXBILT_DAILY_CONC_REQ_BKP (select to_char( ACTUAL_START_DATe, 'dd-mon-yyyy hh24:mi:ss' ) ACTUAL_START_DATE_FORMAT,

       trunc( ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe ) day, 

       trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24, 24 ) )  Hour, 

       trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24*60, 60 ) )  Minute, 

 trunc( mod( (ACTUAL_COMPLETION_DATE-ACTUAL_START_DATe)*24*60*60, 60 ) ) Second, i.*

FROM APPS.FND_CONC_REQ_SUMMARY_V i

WHERE trunc(ACTUAL_START_DATE) = trunc(sysdate-1));

COMMIT;

END;

delete from XXBILT_DAILY_CONC_REQ_BKP

***********************************************************************************************************

Redo Log Information:

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select trunc(first_time, 'HH') , count(*) from   v$loghist group by trunc(first_time, 'HH') order by trunc(first_time, 'HH');

TRUNC(FIRST_TIME,'HH')       COUNT(*)

-------------------------- ----------

24-JAN-2014 15:00:00                1

24-JAN-2014 16:00:00                1

24-JAN-2014 17:00:00                3

24-JAN-2014 18:00:00                1

24-JAN-2014 20:00:00                1

24-JAN-2014 21:00:00                1

24-JAN-2014 22:00:00                3

25-JAN-2014 00:00:00                1

25-JAN-2014 01:00:00                1

25-JAN-2014 02:00:00                1

25-JAN-2014 04:00:00                1

===========check the archived applied on DR===============

select sequence#, archived, applied from v$archived_log order by sequence#;

============check archive difference======================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

===================================================

Manually terminate scheduled requests submitted by end dated users. Following SQL can be used to find out the scheduled request submitted by end-dated users.(Doc.ID: 1075684.1)

select fcr.REQUEST_ID,fu.USER_NAME
from fnd_concurrent_requests fcr,
     fnd_user fu
where fcr.REQUESTED_BY = fu.USER_ID
and fcr.PHASE_CODE <> 'C'
and fcr.REQUESTED_BY in
      (select fui.user_id
         from fnd_user fui
        where nvl(fui.END_DATE, sysdate) < sysdate ) ;

===============================================================

(Doc.Id:287679.1)

• SYSAUX tablespace grows much larger than expected
• Automatic Workload Repository (AWR) is taking a significant amount of space (often 2Gb or more) as seen in the following select:

  column OCCUPANT_NAME format a15
  
  SELECT occupant_name,
    occupant_desc,
    space_usage_kbytes
  FROM v$sysaux_occupants
  WHERE occupant_name LIKE '%AWR%';
  
  OCCUPANT_NAME   OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
  --------------- ---------------------------------------------------------------- ------------------
  SM/AWR          Server Manageability - Automatic Workload Repository                         218304
  

  In the example above the usable is only 200M which is reasonable.

==============================================


Profile options Information from back end:


SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
--and f.user_profile_option_name in ('FND: Branding Size')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
and user_profile_option_name like '%Hide Diagnostics menu entry%'
--and e.profile_option_name like '%ICX:Session Timeout%'

ORDER BY e.profile_option_name;


output:

"user_profile_option_name" "profile" "level" "lvalue" "value" "updated_date"

Hide Diagnostics menu entry FND_HIDE_DIAGNOSTICS Site Site Y 26-AUG-15