You can user merge command to perform inserts and updates into single table in a single command .Based on the condition you specify ,oracle will take the source data - either a table ,view or a query - and update existing values if the condition are met.if the conditions are not met, then the row will be inseted.
Let create one table named comfort2 as follows
create table COMFORT2 (
CITY varchar2(13) not null,
SAMPLEDATE DATE not null,
NOON number(3,1),
MIDNIGHT number(3,1),
PRECIPITATION number);
let insert some values into this table
sql>insert into comfort2 values ('KANNUR','21-MAR-01',55,-2.2,4.4);
sql>insert into comfort2 values ('KANNUR','22-DEC-01',55,66,.5);
sql>insert into comfort2 values ('KANNUR','16-MAY-01',55,55,1);
Now create another table named comfort
create table COMFORT (
CITY varchar2(13) not null,
SAMPLEDATE DATE not null,
NOON number(3,1),
MIDNIGHT number(3,1),
PRECIPITATION number);
Also insert some values into comfort table
sql>insert into comfort values ('KANNUR','21-MAR-01',38.8,-2.2,4.4);
sql>insert into comfort values ('KANNUR','22-JUN-01',84.1,65.7,1.3);
sql>insert into comfort values ('KANNUR','23-SEP-01',98,81.6,null);
sql>insert into comfort values ('KANNUR','22-DEC-01',null,66,.5);
SQL> select * from comfort2;
CITY SAMPLEDAT NOON MIDNIGHT PRECIPITATION
------------- --------- ---------- ---------- -------------
KANNUR 21-MAR-01 55 -2.2 4.4
KANNUR 22-DEC-01 55 66 .5
KANNUR 16-MAY-01 55 55 1
SQL> select * from comfort;
CITY SAMPLEDAT NOON MIDNIGHT PRECIPITATION
------------- --------- ---------- ---------- -------------
KANNUR 21-MAR-01 38.8 -2.2 4.4
KANNUR 22-JUN-01 84.1 65.7 1.3
KANNUR 23-SEP-01 98 81.6
KANNUR 22-DEC-01 66 .5
With comfort2 as the data source ,we can now perform a merge on the comfort table.
for the rows that match (the '21-MAR-01' and '22-DEC-01' entries) we will update the
noon values in comfort table.The rows that exist only in comfort2 (the '16-MAY-01' row) will be inserted into comfort . The following listing shows the command to use.
Now execute the following dml
SQL> merge into COMFORT c1
2 using (select city ,sampledate,noon,midnight,precipitation from comfort2) c2
3 on (c1.city = c2.city and c1.sampledate=c2.sampledate)
4 when matched then
5 update set noon=c2.noon
6 when not matched then
7 insert (c1.city,c1.sampledate,c1.noon,c1.midnight,c1.precipitation)
8 values (c2.city,c2.sampledate,c2.noon,c2.midnight,c2.precipitation);
3 rows merged.
The output tell you the number of rows processed from the source ,but does not tell
you how many rows were inserted or updated . You can see the changes by querying
comfort table (note the Noon=55 records)
SQL> select * from comfort;
CITY SAMPLEDAT NOON MIDNIGHT PRECIPITATION
------------- --------- ---------- ---------- -------------
KANNUR 21-MAR-01 55 -2.2 4.4
KANNUR 22-JUN-01 84.1 65.7 1.3
KANNUR 23-SEP-01 98 81.6
KANNUR 22-DEC-01 55 66 .5
KANNUR 16-MAY-01 55 55 1
Note:-
*The using clause provides the source data for merge and here the source is given an aliase of C2.
*The condition for the merge is then specified in the On clause.if source data's city and sampledate
values match those in the target table then the data will be updated.
The When matched then clause tells oracle what columns to update in the source table.
if there is no match , then the row should be inserted as specified in the when not matched then clause.
Some Notes
------------------------------------------------
set lines 333
col tablespace_name for a30
col contents for a25
col status for a15
col con_name for a20
select c.con_id, c.name con_name, t.tablespace_name, t.contents, t.status from v$containers c, cdb_tablespaces t
where c.con_id=t.con_id order by 1,2;
----------------------------------------------------------------------------------------------------------
set lines 400
col con_name for a20
col username for a25
col account_status for a28
-- col LOCK_DATE
-- col EXPIRY_DATE
-- col LAST_LOGIN
col DEFAULT_TABLESPACE for a25
col TEMPORARY_TABLESPACE for a15
--col LOCAL_TEMP_TABLESPACE for a15
col PROFILE for a25
select c.con_id, c.name con_name, u.username,u.account_status,u.common,u.default_tablespace,u.temporary_tablespace,u.profile from v$containers c, cdb_users u
where c.con_id=u.con_id order by 1,2;
----------------------------------------------------------------------------------------------------------
set lines 400
col file_name for a66
col TABLESPACE_NAME for a22
col con_name for a20
col STATUS for a10
select c.con_id, c.name con_name,t.file_name,t.tablespace_name,t.bytes/1024/1024 "size",t.status from v$containers c, cdb_data_files t
where c.con_id=t.con_id order by 1,2;
===============================================
In expdp/impdp also you have remap_schema for the same purpose, but you dont need to create the target schema there.
IMPDP itself creates target schema for you but the schema will be locked and you have to set the password before you start using it.
create ts in OMF
CREATE TABLESPACE APPS_DATA01 DATAFILE SIZE 150M;
create user devuser identified by devuser default tablespace APPS_DATA01;
create user hr2 identified by devuser default tablespace APPS_DATA01;
alter session set container=orclapdb;
conn devuser/devuser@orclapdb
conn hr/hr@orclapdb
conn c##mahidba/mahidba@orclapdb
You may need to re-add object grants to the new martin schema eg
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') FROM DUAL
before you remove the SCOTT schema of course.
========================================================================================
taking backup of entire hr schema and import into same database but into different schema
devuser with different default tablespace
expdp c##mahidba/mahidba@orclapdb directory=dump schemas=hr dumpfile=hr.dmp
impdp c##mahidba/mahidba@orclapdb directory=dump remap_schema=hr:devuser dumpfile=hr.dmp remap_tablespace=users:APPS_DATA01
========================================================================================
taking backup of few tables from hr schema and import into same database but into different schema
hr_new (new schenma that will create as part of impdp)with different default tablespace
expdp c##mahidba/mahidba@orclapdb directory=dump tables=hr.region,hr.countries,hr.jobs dumpfile=hr1.dmp
impdp c##mahidba/mahidba@orclapdb directory=dump remap_schema=hr:hr_new dumpfile=hr1.dmp remap_tablespace=users:APPS_DATA01
above output throws error
ORA-01918: user 'HR_NEW' does not exist
as druing the expdp execution it does not do the following
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
ie the users & grants were not exported in the first place, and so were not created when importing.
so create user and then import
create user hr_new identified by hr_new default tablespace APPS_DATA01;
grant dba to hr_new;
then the import ran successfully]
----------------------------------------------------------------------------------------------------------------
=================QUERY FOR TOTAL SIZE OF ORACLE DATABASE:
SELECT ( SELECT SUM(BYTES)/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) + ( SELECT NVL(SUM(BYTES),0)/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +( SELECT SUM(BYTES)/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE) "SIZE IN GB" FROM DUAL;
=================INDIVIDUALLY CHECKING ALL THE DATABASE FILES AS WELL AS NON-DATABASE FILES:
SET LINES 100 PAGES 999
COL NAME FORMAT A50
SELECT NAME, BYTES FROM (SELECT NAME, BYTES FROM V$DATAFILE UNION ALL SELECT NAME, BYTES FROM V$TEMPFILE UNION ALL SELECT LF.MEMBER "NAME", L.BYTES FROM V$LOGFILE LF , V$LOG L WHERE LF.GROUP# = L.GROUP# UNION ALL SELECT NAME, 0 FROM V$CONTROLFILE) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE;
=================QUERY FOR CHECK THE FREE SPACE AND USED SPACE IN DATABASE:
COL "DATABASE SIZE" FORMAT A20
COL "FREE SPACE" FORMAT A20
COL "USED SPACE" FORMAT A20
SELECT ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "DATABASE SIZE", ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) - ROUND(FREE.P / 1024 /1024 / 1024) || ' GB' "USED SPACE", ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "FREE SPACE" FROM (SELECT BYTES FROM V$DATAFILE UNION ALL SELECT BYTES FROM V$TEMPFILE UNION ALL SELECT BYTES FROM V$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE GROUP BY FREE.P;
=================QUERY FOR CHECK THE FREE SIZE OF DISK IN EXADATA OR ODA FOR ASM DISKS:
=================Also find which cell assigned to which asm diskgroup:
SELECT A.NAME DISKGROUP,B.NAME DISKNAME, B.TOTAL_MB/1024 "TOTAL_SIZE_IN_GB", B.FREE_MB/1024 "FREE_SIZE_IN_GB",(B.TOTAL_MB - B.FREE_MB)/1024 "USAGE_SIZE_IN_GB" ,B.PATH, B.HEADER_STATUS FROM V$ASM_DISK B, V$ASM_DISKGROUP A
WHERE A.GROUP_NUMBER (+) =B.GROUP_NUMBER ORDER BY B.GROUP_NUMBER,B.NAME,B.PATH;
=================QUERY FOR CHECK THE FREE SPACE AND USED SPACE OF TEMP TABLESPACE:
SELECT B.TABLESPACE_NAME, TBS_SIZE SIZEMB, A.FREE_SPACE FREEMB
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024 ,2) AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TBS_SIZE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME order by 3 desc;
=================QUERY FOR CHECKING THE TOP 10 TABLE HAVING MAXIMUM SIZE:
SELECT * FROM ( SELECT OWNER,SEGMENT_NAME,BYTES/1024/1024/1024 SEGMENT_IN_GB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' ORDER BY BYTES/1024/1024 DESC) WHERE ROWNUM <= 10;
=================HOW THE DATABASE SIZE INCREASED IN GBYTES PER MONTH FOR THE LAST YEAR.
SELECT TO_CHAR(CREATION_TIME, 'RRRR MONTH') "MONTH",ROUND(SUM(BYTES)/1024/1024/1024) "GROWTH IN GBYTES" FROM SYS.V_$DATAFILE WHERE CREATION_TIME > SYSDATE-365
GROUP BY TO_CHAR(CREATION_TIME, 'RRRR MONTH');
=================FIND THE SIZE OF TABLE IN DATABASE.
SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='<TABLE_NAME>';
=================FIND THE REDO LOG AND THEIR MEMBER INFORMATION
SELECT L.INST_ID,L.GROUP#,L.THREAD#,SUBSTR(LG.MEMBER,1,35),SUBSTR(L.MEMBERS,1,1),L.BYTES/1024/1024/1024,1,1 FROM GV$LOG L, GV$LOGFILE LG WHERE L.GROUP#=LG.GROUP# ORDER BY L.THREAD# ,L.GROUP#,L.INST_ID;
=================================================================QUERY TO FIND HUGE REDO GENERATION =============================================================
SELECT
S.SID,
SUBSTR(S.USERNAME,1,18) USERNAME,
SUBSTR(S.PROGRAM,1,15) PROGRAM,
DECODE(S.COMMAND,
0,'NO COMMAND',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
15,'ALTER TABLE',
21,'CREATE VIEW',
23,'VALIDATE INDEX',
35,'ALTER DATABASE',
39,'CREATE TABLESPACE',
41,'DROP TABLESPACE',
40,'ALTER TABLESPACE',
53,'DROP USER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
S.COMMAND||': OTHER') COMMAND
FROM
V$SESSION S,
V$PROCESS P,
V$TRANSACTION T,
V$ROLLSTAT R,
V$ROLLNAME N
WHERE S.PADDR = P.ADDR
AND S.TADDR = T.ADDR (+)
AND T.XIDUSN = R.USN (+)
AND R.USN = N.USN (+)
ORDER BY 1;
=================================================================================================================================================================
=================FIND THE USER WHOSE ACCOUNT IS LOCK OR UNLOCK.
SELECT
SUBSTR(FUSER.USER_NAME,1,10)
, SUBSTR(PER.FULL_NAME,1,10)
, SUBSTR(PER.EMPLOYEE_NUMBER,1,5)
FROM
APPLSYS.FND_USER FUSER
, APPS.PER_PEOPLE_F PER
WHERE
FUSER.EMPLOYEE_ID = PER.PERSON_ID(+)
AND FUSER.ENCRYPTED_USER_PASSWORD = 'INVALID'
ORDER BY
FUSER.USER_NAME;
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='DBMS_SHARED_POOL';
SQL> EXECUTE UTL_RECOMP.RECOMP_SERIAL();
SQL>EXECUTE DBMS_REGISTRY_SYS.VALIDATE_COMPONENTS;
==============================================================OPEN CURSOR VALUE==================================================================================
SELECT A.VALUE,
S.USERNAME,
S.SID,
S.SERIAL#
FROM GV$SESSTAT A,
GV$STATNAME B,
GV$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'OPENED CURSORS CURRENT'
AND USERNAME = 'APPS'
ORDER BY 1 DESC;
==========================================================PROCESSES AND SESSIONS VALUE MAXIMUM=================================================================
SELECT * FROM APPS.AD_BUGS ORDER BY LAST_UPDATE_DATE DESC;
SELECT RESOURCE_NAME,MAX_UTILIZATION,CURRENT_UTILIZATION FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('PROCESSES','SESSIONS');
SELECT RESOURCE_NAME,INITIAL_ALLOCATION,MAX_UTILIZATION,CURRENT_UTILIZATION FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('PROCESSES','SESSIONS');
=============================================================UPDATE QUERY FOR WORKFLOW=========================================================================
UPDATE APPS.WF_NOTIFICATIONS SET
MAIL_STATUS = 'SENT',STATUS ='CLOSE' WHERE STATUS = 'OPEN'
OR MAIL_STATUS = 'MAIL' ;
======================================================QUERY TO FIND THE INSTALLED PRODUCTS IN EBS=============================================================
SELECT A.ORACLE_ID, A.LAST_UPDATE_DATE, A.PRODUCT_VERSION,A.PATCH_LEVEL, DECODE(A.STATUS, 'I', 'INSTALLED', 'S', 'SHARED', 'N', 'NOT INSTALLED',A.STATUS) STATUS, A.INDUSTRY, B.APPLICATION_NAME, C.APPLICATION_SHORT_NAME FROM APPS.FND_PRODUCT_INSTALLATIONS A, APPS.FND_APPLICATION_TL B, APPS.FND_APPLICATION C
WHERE A.APPLICATION_ID = B.APPLICATION_ID AND A.APPLICATION_ID = C.APPLICATION_ID AND B.LANGUAGE = 'US' ORDER BY C.APPLICATION_SHORT_NAME;
================================================QUERY TO FIND THE ENCRYPTED TABLE & TABLESPACE ================================================================
SELECT TABLESPACE_NAME, ENCRYPTED, STATUS FROM DBA_TABLESPACES;
SELECT * FROM SYS.DBA_ENCRYPTED_COLUMNS;
=================QUERY TO FIND THE INSTALL LANGUAGE IN EBS
SELECT LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,NLS_TERRITORY FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I', 'B');
=================QUERY TO FIND THE LICENSING PRODUCTS IN INSTANCE
SET PAGES 999
COL C1 HEADING 'FEATURE' FORMAT A45
COL C2 HEADING 'TIMES|USED' FORMAT 999,999
COL C3 HEADING 'FIRST|USED'
COL C4 HEADING 'USED|NOW'
SELECT NAME C1,DETECTED_USAGES C2,FIRST_USAGE_DATE C3,CURRENTLY_USED C4 FROM DBA_FEATURE_USAGE_STATISTICS WHERE FIRST_USAGE_DATE IS NOT NULL ORDER BY 4;
=================FIND DEPENDENCIES WITHIN THE OBJECT
SELECT * FROM ALL_DEPENDENCIES WHERE NAME LIKE '%XX_SEND_PO_ATTACHMENT%';
*****************************************************************************************************************************************************************
MOST IMP LINK:- HTTP://ALLAPPSDBA.BLOGSPOT.IN/2012/04/SQL-QUERIES-TO-CHECK-ACTIVE-INACTIVE.HTML
HTTP://ORACLEAPPSSEARCH.BLOGSPOT.IN/2011/11/ROLE-OF-APPLSYSPUB-GUEST-APPLSYS-APPS.HTML
*******************************************QUERY TO FIND THE AUDIT(IF WE DONT HAVE ACCESS A DBA_AUDIT_SESSION)***************************************************
SELECT * FROM V$SESSION
AND USERNAME IN ('SYSTEM','SYS','APPLSYS','APPS','DBADMIN');
WHERE TRUNC(LOGON_TIME) <= (TRUNC(SYSDATE) - 1) AND TRUNC(LOGON_TIME) >= (TRUNC(SYSDATE)-180)
==========================================================BLOCKING SESSION AND KILL IT===========================================================================
SELECT DECODE(REQUEST,0,'HOLDER: ','WAITER: ')||SID SESS,INST_ID,ID1, ID2, LMODE, REQUEST, TYPE FROM GV$LOCK
WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM GV$LOCK WHERE REQUEST>0) ORDER BY ID1,REQUEST;
SELECT INST_ID,SID,SERIAL#,USER#,STATUS,MACHINE,PROGRAM,SQL_ID,BLOCKING_SESSION_STATUS,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION FROM GV$SESSION WHERE SID='&SID';
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION WHERE SID='&SID';
SELECT SID,SERIAL#,FINAL_BLOCKING_SESSION, INST_ID FROM GV$SESSION WHERE FINAL_BLOCKING_SESSION IS NOT NULL;
SELECT * FROM DBA_BLOCKERS;
SELECT SID,SERIAL#, INST_ID, STATUS, PREV_EXEC_START, LOGON_TIME, PROCESS, PROGRAM, MACHINE, MODULE, SQL_ID, CLIENT_IDENTIFIER FROM GV$SESSION WHERE SID=2200;
SELECT SQL_ID, SQL_FULLTEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME
FROM V$SQL
WHERE UPPER(SQL_ID) = UPPER('7MVUN8RAYPQPX')
ORDER BY ELAPSED_TIME DESC;
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' IMMEDIATE;' FROM V$SESSION WHERE STATUS LIKE 'INACTIVE' AND PROGRAM LIKE '%FRMWEB@ORAWEBPRD%';
SELECT SPID FROM V$PROCESS P, V$SESSION S WHERE PADDR = ADDR AND SID=16;(SOMETIMES I HAVE SEEN IF YOU KILL A SQL PROCESS USING BELOW COMMAND, SESSION STILL NOT DISAPPEARS OR TAKE A VERY LONG TIME TO KILL.)
SELECT SQL_ID, SQL_FULLTEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME
FROM V$SQL
WHERE UPPER(SQL_ID) = UPPER('7MVUN8RAYPQPX')
ORDER BY ELAPSED_TIME DESC;
ALTER SYSTEM KILL SESSION '747,41099' IMMEDIATE;
SELECT 'ALTER SYSTEM KILL SESSION '''|| SID||',' || SERIAL# ||''' IMMEDIATE;' FROM GV$SESSION;]]]]]]]]]]]]
CMIS BLOCKING:-
SELECT C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.SID,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE FROM
V$LOCKED_OBJECT A,V$SESSION B,DBA_OBJECTS C
WHERE
B.SID = A.SESSION_ID
AND
A.OBJECT_ID = C.OBJECT_ID
AND OWNER LIKE '%WIP%';
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SELECT C.OWNER,A.SID,A.SERIAL#,C.OBJECT_NAME,C.OBJECT_TYPE,A.STATUS,B.LOCKED_MODE FROM V$SESSION A,V$LOCKED_OBJECT B,DBA_OBJECTS C
WHERE A.SID=B.SESSION_ID
AND B.OBJECT_ID=C.OBJECT_ID;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SELECT * FROM DBA_WAITERS;
SELECT * FROM DBA_LOCK WHERE BLOCKING_OTHERS LIKE 'BLOCKING';
==========================================================CHECKING THE > 90% TABLESPACE =========================================================================
SELECT TBM.TABLESPACE_NAME,
ROUND(TBM.USED_SPACE * TB.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
ROUND(TBM.TABLESPACE_SIZE * TB.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
ROUND((TBM.TABLESPACE_SIZE - TBM.USED_SPACE) * TB.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
TBM.USED_PERCENT
FROM DBA_TABLESPACE_USAGE_METRICS TBM
JOIN DBA_TABLESPACES TB ON TB.TABLESPACE_NAME = TBM.TABLESPACE_NAME
WHERE TBM.TABLESPACE_NAME LIKE '%QUEUE%'
AND USED_PERCENT > 70 ORDER BY 5 DESC;
SELECT TABLESPACE_NAME,USED_SPACE/1024/1024/1024 "USED SPACE IN GB",TABLESPACE_SIZE/1024/1024/1024 "TABLESPACE SIZE IN GB",USED_PERCENT
FROM DBA_TABLESPACE_USAGE_METRICS WHERE USED_PERCENT > 90 ORDER BY 4 DESC;
==========================================================FIND THE SIZE OF TABLE=================================================================================
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1048576 MB,INITIAL_EXTENT,NEXT_EXTENT,EXTENTS,PCT_INCREASE FROM DBA_SEGMENTS
WHERE OWNER = 'SYS' AND SEGMENT_NAME = 'AUD$' AND SEGMENT_TYPE = 'TABLE';
Query for function attached to menu:-
=======================================
SELECT lvl r_lvl, rownumber rw_num, entry_sequence seq, (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq, menu_name, sub_menu_name, prompt, fm.description, TYPE,
function_name, user_function_name, fff.description form_description
FROM
(SELECT LEVEL lvl, ROW_NUMBER () OVER (PARTITION BY LEVEL, menu_id, entry_sequence ORDER BY entry_sequence)
AS rownumber, entry_sequence,
(SELECT user_menu_name FROM fnd_menus_vl fmvl WHERE 1 = 1
AND fmvl.menu_id = fmv.menu_id) menu_name,
(SELECT user_menu_name FROM fnd_menus_vl fmvl WHERE 1 = 1
AND fmvl.menu_id = fmv.sub_menu_id) sub_menu_name, function_id, prompt, description
FROM apps.fnd_menu_entries_vl fmv START WITH menu_id = (SELECT menu_id FROM apps.fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (:resp_name))
CONNECT BY PRIOR sub_menu_id = menu_id) fm, apps.fnd_form_functions_vl fff
WHERE fff.function_id(+) = fm.function_id ORDER BY lvl, entry_sequence;
Query Form Function for Responsibility:-
===========================================
SELECT frtl.responsibility_name, fr.responsibility_key, fm.menu_id, fm.menu_name, menu.function_id, menu.prompt, fffv.user_function_name, fffv.function_name, fffv.TYPE
FROM (SELECT connect_by_root fmet.menu_id top_menu_id, fmet.menu_id menu_id, fmet.sub_menu_id, fmet.function_id, fmet.prompt
FROM fnd_menu_entries_vl fmet CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id AND PRIOR fmet.prompt IS NOT NULL) menu,
fnd_responsibility fr, fnd_responsibility_tl frtl, fnd_menus fm, fnd_form_functions_vl fffv
WHERE fr.menu_id = menu.top_menu_id
AND fffv.function_id = menu.function_id
AND fffv.TYPE <> 'SUBFUNCTION' AND menu.function_id IS NOT NULL
AND menu.prompt IS NOT NULL AND fm.menu_id = menu.menu_id AND frtl.responsibility_id = fr.responsibility_id
AND frtl.responsibility_name LIKE 'System Administrator'
AND menu.function_id NOT IN
(SELECT ffvl.function_id
FROM apps.fnd_resp_functions frf, applsys.fnd_responsibility_tl frt, apps.fnd_form_functions_vl ffvl
WHERE frf.responsibility_id = frt.responsibility_id
AND frf.action_id = ffvl.function_id AND frf.rule_type = 'F'
AND frt.responsibility_name = frtl.responsibility_name)
AND menu.menu_id NOT IN
(SELECT fmv.menu_id
FROM apps.fnd_resp_functions frf, applsys.fnd_responsibility_tl frt, apps.fnd_menus_vl fmv
WHERE frf.responsibility_id = frt.responsibility_id
AND frf.action_id = fmv.menu_id
AND frf.rule_type = 'M'
AND frt.responsibility_name = frtl.responsibility_name)
ORDER BY fffv.user_function_name;
========================================================QUERY TO FIND THE BELOW==================================================================================
WHICH DATAFILE CONTAIN WHICH TABLE USING SCHEMA:-
SELECT A.SEGMENT_NAME,A.SEGMENT_TYPE,B.NAME
FROM DBA_SEGMENTS A, V$DATAFILE B
WHERE A.HEADER_FILE = B.FILE#
AND A.OWNER='VEL';
==================================================DR IS SYNC WITH PRIMARY OR NOT ===============================================================================
SELECT THREAD#, MAX(SEQUENCE#) "LAST STANDBY SEQ APPLIED" FROM V$ARCHIVED_LOG VAL, V$DATABASE VDB
WHERE VAL.RESETLOGS_CHANGE# = VDB.RESETLOGS_CHANGE#
AND VAL.APPLIED='YES'
GROUP BY THREAD# ORDER BY 1;
SELECT ARCH.THREAD# "THREAD", ARCH.SEQUENCE# "LAST SEQUENCE RECEIVED", APPL.SEQUENCE# "LAST SEQUENCE APPLIED" , ARCH.SEQUENCE# - APPL.SEQUENCE# "DIFFERENCE" FROM (SELECT THREAD# ,max(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) GROUP BY THREAD#) ARCH,(SELECT THREAD# ,max(SEQUENCE#) SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#) GROUP BY THREAD#) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
==============================================FINDING PGA MEMORY USAGE ==============================
set lines 2000
set pages 3000
col LOGON format A20
col spid format A11
col OSUSER format A15
col USERNAME format A12
col MACHINE format A20
col PROGRAM format A40
col MODULE format A40
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' DAYS' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, V$SESSION.SERIAL#, V$PROCESS.SPID , ROUND(V$PROCESS.PGA_USED_MEM/(1024*1024), 2) PGA_MB_USED,
V$SESSION.USERNAME, STATUS, OSUSER, MACHINE, V$SESSION.PROGRAM, MODULE
FROM V$SESSION, V$PROCESS
WHERE V$SESSION.PADDR = V$PROCESS.ADDR
AND STATUS = 'ACTIVE'
AND V$SESSION.PROGRAM NOT LIKE '%ORA%'
--AND V$SESSION.SID = 97
--AND V$SESSION.USERNAME = 'SYSTEM'
--AND V$PROCESS.SPID = 24301
ORDER BY PGA_USED_MEM DESC;
IMP---> select component,current_size/1024/1024 "IN MB",min_size/1024/1024 "IN MB", max_size/1024/1024 "IN MB",oper_count,last_oper_time from V$SGA_DYNAMIC_COMPONENTS;
==========================================DATAGUARD FOR PRIMARY AND STANDBY DATABASE======================================================================
PRIMARY: SQL> SELECT THREAD#, MAX(SEQUENCE#) "LAST PRIMARY SEQ GENERATED" FROM V$ARCHIVED_LOG VAL, V$DATABASE VDB WHERE VAL.RESETLOGS_CHANGE# = VDB.RESETLOGS_CHANGE# GROUP BY THREAD# ORDER BY 1;
PHYSTDBY:SQL> SELECT THREAD#, MAX(SEQUENCE#) "LAST STANDBY SEQ RECEIVED" FROM V$ARCHIVED_LOG VAL, V$DATABASE VDB WHERE VAL.RESETLOGS_CHANGE# = VDB.RESETLOGS_CHANGE# GROUP BY THREAD# ORDER BY 1;
PHYSTDBY:SQL> SELECT THREAD#, MAX(SEQUENCE#) "LAST STANDBY SEQ APPLIED" FROM V$ARCHIVED_LOG VAL, V$DATABASE VDB WHERE VAL.RESETLOGS_CHANGE# = VDB.RESETLOGS_CHANGE# AND VAL.APPLIED='YES' GROUP BY THREAD# ORDER BY 1;
SELECT ARCH.THREAD# "THREAD", ARCH.SEQUENCE# "LAST SEQUENCE RECEIVED", APPL.SEQUENCE# "LAST SEQUENCE APPLIED" , ARCH.SEQUENCE# - APPL.SEQUENCE# "DIFFERENCE"
FROM (SELECT THREAD# ,SEQUENCE# FROM GV$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM GV$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- FOR CANCELLING RECOVERY FROM DR.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- FOR STARTING RECOVERY.
==========================================FIND THE OBJECTS WITH OBJECT DEPENDENCIES========================================================================
SELECT
TYPE || ' ' ||
OWNER || '.' || NAME || ' REFERENCES ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
AS DEPENDENCIES
FROM ALL_DEPENDENCIES
WHERE NAME = UPPER(LTRIM(RTRIM( '&LS_REF_NAME' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
ORDER BY OWNER, NAME,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_NAME;
====================================================TIMESTAMP MISMATCH IN DATABASE =======================================================================
SELECT DU.NAME D_OWNER, D.NAME D_NAME, D.DEFINING_EDITION D_EDITION,
PU.NAME P_OWNER, P.NAME P_NAME, P.DEFINING_EDITION P_EDITION,
CASE
WHEN P.STATUS NOT IN (1, 2, 4) THEN 'P STATUS: ' || TO_CHAR(P.STATUS)
ELSE 'TS MISMATCH: ' ||
TO_CHAR(DEP.P_TIMESTAMP, 'DD-MON-YY HH24:MI:SS') ||
TO_CHAR(P.STIME, 'DD-MON-YY HH24:MI:SS')
END REASON
FROM SYS."_ACTUAL_EDITION_OBJ" D, SYS.USER$ DU, SYS.DEPENDENCY$ DEP,
SYS."_ACTUAL_EDITION_OBJ" P, SYS.USER$ PU
WHERE D.OBJ# = DEP.D_OBJ# AND P.OBJ# = DEP.P_OBJ#
AND D.OWNER# = DU.USER# AND P.OWNER# = PU.USER#
AND D.STATUS = 1 -- VALID DEPENDENT
AND BITAND(DEP.PROPERTY, 1) = 1 -- HARD DEPENDENCY
AND D.SUBNAME IS NULL -- !OLD TYPE VERSION
AND NOT(P.TYPE# = 32 AND D.TYPE# = 1) -- INDEX TO INDEXTYPE
AND NOT(P.TYPE# = 29 AND D.TYPE# = 5) -- SYNONYM TO JAVA
AND NOT(P.TYPE# IN(5, 13) AND D.TYPE# IN (2, 55)) -- TABL/XDBS TO TYPE
AND (P.STATUS NOT IN (1, 2, 4) OR P.STIME != DEP.P_TIMESTAMP);
EXEC UTL_RECOMP.RECOMP_SERIAL('APPLSYS');
SQL> EXECUTE UTL_RECOMP.RECOMP_SERIAL();
SQL>EXECUTE DBMS_REGISTRY_SYS.VALIDATE_COMPONENTS;
==========================================VELCRO AUDIT SESSION========================================================================
SELECT * FROM DBA_AUDIT_SESSION WHERE TRUNC(TIMESTAMP) <= (TRUNC(SYSDATE) - 1) AND TRUNC(TIMESTAMP) >= (TRUNC(SYSDATE)-7)
AND USERNAME IN ('SYSTEM','SYS','APPLSYS','APPS') AND TERMINAL IS NOT NULL AND USERHOST NOT IN ('EXPRDB01.VELCRO.COM','ORACLE-1-MHT.VELCRO.COM','APP-1-MHT.VELCRO.COM','APP-2-MHT.VELCRO.COM','NA\REMOTE-1-MHT','EXPRDB02.VELCRO.COM','NA\MHT-RUTSTE-02LP');
=============================================Velcro Cube Related information======================================================
SELECT
distinct t.user_concurrent_program_name, r.REQUEST_ID, to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'hh12:mi AM') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",
r.argument_text "Parameters",
substr(u.description,1,25) "Who submitted",
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND t.user_concurrent_program_name in( 'VELCRO ORDERS MVs Refresh Program','VELCRO Order Cube refresh Program','VELCRO AR MVs Refresh Program','VELCRO AR GTN MV and Cube Refresh','VELCRO AR Cube refresh Program','VELCRO AR ITC Sales MV And Cube Refresh','VELCRO Sales Budget MV and Cube Refresh',
'Velcro Inventory Cube Refresh','VELCRO OPS MTL TRX MVs Refresh Program','VELCRO OPS MVs Refresh Program','VELCRO OPS Cube refresh Program',
'Velcro GL MV Cube Refresh')
AND trunc(r.actual_completion_date) > trunc (sysdate -2)
--AND r.requested_by=22378
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
AND t.user_concurrent_program_name like '%%'
--order by to_char(r.ACTUAL_COMPLETION_DATE,'hh12:mi:ss AM');
order by 3;
############################################################ APPS QUERIES ##############################################
__________________________________________INDIVIDUALLY CHECKING ALL THE DATABASE FILES AS WELL AS NON-DATABASE FILES____________________
SET LINES 100 PAGES 999
COL NAME FORMAT A50
SELECT NAME, BYTES FROM (SELECT NAME, BYTES FROM V$DATAFILE UNION ALL SELECT NAME, BYTES FROM V$TEMPFILE UNION ALL SELECT LF.MEMBER "NAME", L.BYTES FROM V$LOGFILE LF , V$LOG L WHERE LF.GROUP# = L.GROUP# UNION ALL SELECT NAME, 0 FROM V$CONTROLFILE) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE;
_______________________________________________________QUERY FOR CHECK THE FREE SPACE AND USED SPACE IN DATABASE_______________________
COL "DATABASE SIZE" FORMAT A20
COL "FREE SPACE" FORMAT A20
COL "USED SPACE" FORMAT A20
SELECT ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "DATABASE SIZE", ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) - ROUND(FREE.P / 1024 /1024 / 1024) || ' GB' "USED SPACE", ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "FREE SPACE" FROM (SELECT BYTES FROM V$DATAFILE UNION ALL SELECT BYTES FROM V$TEMPFILE UNION ALL SELECT BYTES FROM V$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE GROUP BY FREE.P
______________________________________________________HOW TO FIND THE ORACLE_ID FROM APPLICATION_ID_____________________________________
SELECT LAST_UPDATE_DATE, APPLICATION_ID, STATUS, ORACLE_ID FROMFND_PRODUCT_INSTALLATIONS WHERE APPLICATION_ID = <--THIS VALUE IS CUSTOMER SPECIFIC-->;
________________________________________________________SID TO PID____________________________________________________________
COL SID FORMAT 999999
COL USERNAME FORMAT A20
COL OSUSER FORMAT A15
SELECT A.SID, A.SERIAL#,A.USERNAME, A.OSUSER, B.SPID
FROM V$SESSION A, V$PROCESS B
WHERE A.PADDR= B.ADDR
AND A.SID='&SID'
ORDER BY A.SID;
_________________________________________________________PID TO SID____________________________________________________________________
SELECT SPID, S.SID, P.SERIAL#, S.TERMINAL, PID, MACHINE, S.PROGRAM, OSUSER,P.PROGRAM
FROM V$SESSION S, V$PROCESS P
WHERE ADDR = PADDR AND (SPID LIKE '%OSID%' OR PROCESS LIKE '%&OSID%');
_________________________________________________________SID TO CONCURRENT REQUEST ID_________________________________________________________
SELECT VP.SPID,VS.SID,FCR.REQUEST_ID
FROM V$PROCESS VP,V$SESSION VS,FND_CONCURRENT_REQUESTS FCR
WHERE VS.PADDR = VP.ADDR
AND FCR.ORACLE_PROCESS_ID = VP.SPID
AND VS.SID = :L_SID;
_________________________________________________________CPU USAGES IN PERCENTAGE _________________________________________________________
SHOW CPU USAGE FOR ACTIVE SESSIONS
====================================
SET PAUSE ON
SET PAUSE 'PRESS RETURN TO CONTINUE'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN USERNAME FORMAT A30
COLUMN SID FORMAT 999,999,999
COLUMN SERIAL# FORMAT 999,999,999
COLUMN "CPU USAGE (SECONDS)" FORMAT 999,999,999.0000
SELECT S.USERNAME,T.SID,S.SERIAL#,SUM(VALUE/100) AS "CPU USAGE (SECONDS)"
FROM V$SESSION S,V$SESSTAT T,V$STATNAME N
WHERE T.STATISTIC# = N.STATISTIC#
AND NAME LIKE '%CPU USED BY THIS SESSION%'
AND T.SID = S.SID
AND S.STATUS='ACTIVE'
AND S.USERNAME IS NOT NULL
GROUP BY USERNAME,T.SID,S.SERIAL#;
SELECT * FROM
(
SELECT USERNAME,SID,ROUND((CPU_USAGE/(
SELECT SUM(VALUE) TOTAL_CPU_USAGE
FROM GV$SESSTAT T
INNER JOIN GV$SESSION S ON ( T.SID = S.SID )
INNER JOIN GV$STATNAME N ON ( T.STATISTIC# = N.STATISTIC# )
WHERE N.NAME LIKE '%CPU USED BY THIS SESSION%'
AND NVL(S.SQL_EXEC_START, S.PREV_EXEC_START) >= SYSDATE-1/24
))*100,2) CPU_USAGE_PER_CENT,
MODULE_INFO,CLIENT_INFO
FROM
(
SELECT NVL(S.USERNAME,'ORACLE INTERNAL PROC.') USERNAME,S.SID,T.VALUE CPU_USAGE, NVL(S.MODULE, S.PROGRAM) MODULE_INFO, DECODE(S.OSUSER,'ORACLE', S.CLIENT_INFO, S.OSUSER) CLIENT_INFO
FROM GV$SESSTAT T
INNER JOIN GV$SESSION S ON ( T.SID = S.SID )
INNER JOIN GV$STATNAME N ON ( T.STATISTIC# = N.STATISTIC# )
WHERE N.NAME LIKE '%CPU USED BY THIS SESSION%'
AND NVL(S.SQL_EXEC_START, S.PREV_EXEC_START) >= SYSDATE-1/24
) S1
)
ORDER BY CPU_USAGE_PER_CENT DESC;
SET LINES 250
SET PAGES 2000
COL NAME FORMAT A26
COL USERNAME FORMAT A15
COL PROGRAM FORMAT A40
COL SESS_CPU_SECS WRA FORMAT 999,999,999.99
COL LAST_CPU_SECS WRA FORMAT 999,999,999.99
COL LOGON_SECS WRA FORMAT 999,999,999
COL PERCENT WRA FORMAT 999.99
SELECT SESS_CPU.SID, NVL(SESS_CPU.USERNAME, 'ORACLE PROCESS') USERNAME, SESS_CPU.STATUS, SESS_CPU.LOGON_TIME, ROUND((SYSDATE - SESS_CPU.LOGON_TIME)*1440*60) LOGON_SECS, SESS_CPU.VALUE/100 SESS_CPU_SECS, (SESS_CPU.VALUE - CALL_CPU.VALUE)/100 LAST_CPU_SECS, ROUND ((SESS_CPU.VALUE/100)/ROUND((SYSDATE - SESS_CPU.LOGON_TIME)*1440*60)*100,2) PERCENT, SESS_CPU.SQL_ID
FROM
(SELECT SE.SQL_ID,SS.STATISTIC#,SE.SID, SE.USERNAME, SE.STATUS, SE.PROGRAM, SE.LOGON_TIME, SN.NAME, SS.VALUE FROM V$SESSION SE, V$SESSTAT SS,
V$STATNAME SN
WHERE SE.SID=SS.SID
AND SN.STATISTIC#=SS.STATISTIC#
AND SN.NAME IN ('CPU USED BY THIS SESSION') ) SESS_CPU,
(SELECT SS.STATISTIC#,SE.SID, SS.VALUE, VALUE/100 SECONDS FROM V$SESSION SE, V$SESSTAT SS, V$STATNAME SN
WHERE SE.SID=SS.SID
AND SN.STATISTIC#=SS.STATISTIC#
AND SN.NAME IN ('CPU USED WHEN CALL STARTED') ) CALL_CPU
WHERE SESS_CPU.SID=CALL_CPU.SID
ORDER BY SESS_CPU_SECS ;
SET LINES 250
SET PAGES 2000
COL NAME FORMAT A26
COL USERNAME FORMAT A15
COL PROGRAM FORMAT A40
COL SESS_CPU_SECS WRA FORMAT 999,999,999.99
COL LAST_CPU_SECS WRA FORMAT 999,999,999.99
COL LOGON_SECS WRA FORMAT 999,999,999
COL PERCENT WRA FORMAT 999.99
SELECT SESS_CPU.SID, NVL(SESS_CPU.USERNAME, 'ORACLE PROCESS') USERNAME, SESS_CPU.STATUS, SESS_CPU.LOGON_TIME, ROUND ((SYSDATE-SESS_CPU.LOGON_TIME)*1440*60) LOGON_SECS, SESS_CPU.VALUE/100 SESS_CPU_SECS, (SESS_CPU.VALUE - CALL_CPU.VALUE)/100 LAST_CPU_SECS,
ROUND((SESS_CPU.VALUE/100)/ROUND((SYSDATE - SESS_CPU.LOGON_TIME)*1440*60)*100,2) PERCENT, SESS_CPU.SQL_ID
FROM
(SELECT SE.SQL_ID,SS.STATISTIC#,SE.SID, SE.USERNAME, SE.STATUS, SE.PROGRAM, SE.LOGON_TIME, SN.NAME, SS.VALUE FROM V$SESSION SE, V$SESSTAT SS, V$STATNAME SN
WHERE SE.SID=SS.SID
AND SN.STATISTIC#=SS.STATISTIC#
AND SN.NAME IN ('CPU USED BY THIS SESSION') ) SESS_CPU,
(SELECT SS.STATISTIC#,SE.SID, SS.VALUE, VALUE/100 SECONDS FROM V$SESSION SE, V$SESSTAT SS, V$STATNAME SN
WHERE SE.SID=SS.SID
AND SN.STATISTIC#=SS.STATISTIC#
AND SN.NAME IN ('CPU USED WHEN CALL STARTED') ) CALL_CPU
WHERE SESS_CPU.SID=CALL_CPU.SID
ORDER BY SESS_CPU_SECS ;
SELECT SE.USERNAME,SS.SID,SE.TERMINAL,SE.MACHINE,SE.PROGRAM ,SE.STATUS, ROUND (VALUE/100) "CPU USAGE"
FROM V$SESSION SE, V$SESSTAT SS, V$STATNAME ST
WHERE SS.STATISTIC# = ST.STATISTIC#
AND NAME LIKE '%CPU USED BY THIS SESSION%'
AND SE.SID = SS.SID
AND SE.USERNAME IS NOT NULL
ORDER BY VALUE DESC;
=============================================SCHEDULED CONCURRENT REQUEST IN EBS ========================================================================
SELECT R.REQUEST_ID,
P.USER_CONCURRENT_PROGRAM_NAME || NVL2(R.DESCRIPTION,' ('||R.DESCRIPTION||')',NULL) CONC_PROG,
S.USER_NAME REQUESTOR,
R.ARGUMENT_TEXT ARGUMENTS,
R.REQUESTED_START_DATE NEXT_RUN,
R.LAST_UPDATE_DATE LAST_RUN,
R.HOLD_FLAG ON_HOLD,
R.INCREMENT_DATES,
DECODE(C.CLASS_TYPE,
'P', 'PERIODIC',
'S', 'ON SPECIFIC DAYS',
'X', 'ADVANCED',
C.CLASS_TYPE) SCHEDULE_TYPE,
CASE
WHEN C.CLASS_TYPE = 'P' THEN
'REPEAT EVERY ' ||
SUBSTR(C.CLASS_INFO, 1, INSTR(C.CLASS_INFO, ':') - 1) ||
DECODE(SUBSTR(C.CLASS_INFO, INSTR(C.CLASS_INFO, ':', 1, 1) + 1, 1),
'N', ' MINUTES',
'M', ' MONTHS',
'H', ' HOURS',
'D', ' DAYS') ||
DECODE(SUBSTR(C.CLASS_INFO, INSTR(C.CLASS_INFO, ':', 1, 2) + 1, 1),
'S', ' FROM THE START OF THE PRIOR RUN',
'C', ' FROM THE COMPLETION OF THE PRIOR RUN')
WHEN C.CLASS_TYPE = 'S' THEN
NVL2(DATES.DATES, 'DATES: ' || DATES.DATES || '. ', NULL) ||
DECODE(SUBSTR(C.CLASS_INFO, 32, 1), '1', 'LAST DAY OF MONTH ') ||
DECODE(SIGN(TO_NUMBER(SUBSTR(C.CLASS_INFO, 33))),
'1', 'DAYS OF WEEK: ' ||
DECODE(SUBSTR(C.CLASS_INFO, 33, 1), '1', 'SU ') ||
DECODE(SUBSTR(C.CLASS_INFO, 34, 1), '1', 'MO ') ||
DECODE(SUBSTR(C.CLASS_INFO, 35, 1), '1', 'TU ') ||
DECODE(SUBSTR(C.CLASS_INFO, 36, 1), '1', 'WE ') ||
DECODE(SUBSTR(C.CLASS_INFO, 37, 1), '1', 'TH ') ||
DECODE(SUBSTR(C.CLASS_INFO, 38, 1), '1', 'FR ') ||
DECODE(SUBSTR(C.CLASS_INFO, 39, 1), '1', 'SA '))
END AS SCHEDULE,
C.DATE1 START_DATE,
C.DATE2 END_DATE,
C.CLASS_INFO
FROM
APPS.FND_CONCURRENT_REQUESTS R,
APPS.FND_CONC_RELEASE_CLASSES C,
APPS.FND_CONCURRENT_PROGRAMS_TL P,
APPS.FND_USER S,
(WITH DATE_SCHEDULES AS (
SELECT RELEASE_CLASS_ID,
RANK() OVER(PARTITION BY RELEASE_CLASS_ID ORDER BY S) A, S
FROM (SELECT C.CLASS_INFO, L,
C.RELEASE_CLASS_ID,
DECODE(SUBSTR(C.CLASS_INFO, L, 1), '1', TO_CHAR(L)) S
FROM (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 31),
APPS.FND_CONC_RELEASE_CLASSES C
WHERE C.CLASS_TYPE = 'S'
AND INSTR(SUBSTR(C.CLASS_INFO, 1, 31), '1') > 0)
WHERE S IS NOT NULL)
SELECT RELEASE_CLASS_ID, SUBSTR(MAX(SYS_CONNECT_BY_PATH(S, ' ')), 2) DATES
FROM DATE_SCHEDULES
START WITH A = 1
CONNECT BY NOCYCLE PRIOR A = A - 1
GROUP BY RELEASE_CLASS_ID) DATES
WHERE R.PHASE_CODE = 'P'
AND C.APPLICATION_ID = R.RELEASE_CLASS_APP_ID
AND C.RELEASE_CLASS_ID = R.RELEASE_CLASS_ID
AND NVL(C.DATE2, SYSDATE + 1) > SYSDATE
AND C.CLASS_TYPE IS NOT NULL
AND P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND P.LANGUAGE = 'US'
AND DATES.RELEASE_CLASS_ID(+) = R.RELEASE_CLASS_ID
AND P.USER_CONCURRENT_PROGRAM_NAME LIKE 'PICK SELECTION LIST GENERATION - SRS'
AND R.REQUESTED_BY = S.USER_ID
ORDER BY CONC_PROG, ON_HOLD, NEXT_RUN;
--------REQUEST ID TO SID.---------------
SELECT A.REQUEST_ID, D.SID, D.SERIAL# ,D.OSUSER,D.PROCESS , C.SPID ,D.INST_ID
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
GV$PROCESS C,
GV$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 =&REQ_ID
AND A.PHASE_CODE = 'R';
_________________________________________________FIND THE LIST AND COUNT OF RESPONSIBILITIES IN INSTANCE____________________________________________________
SELECT RESPONSIBILITY_NAME FROM APPLSYS.FND_RESPONSIBILITY_TL;
SELECT COUNT(*) FROM APPLSYS.FND_RESPONSIBILITY_TL;
IMPORTANT SCHEMA AS PER THE USER "APPS" SIDE.
(FURG.USER_ID )
________________________________________________________________FIND THE LONG RUNNING CONCURRENT PROGRAM_________________________________________________________
SELECT * FROM
(
SELECT OPNAME,START_TIME,TARGET,SOFAR,TOTALWORK,UNITS,ELAPSED_SECONDS,MESSAGE
FROM V$SESSION_LONGOPS
ORDER BY START_TIME DESC
)
WHERE ROWNUM <=1;
________________________________________________________________FIND THE NAME OF CONCURRENT REQUEST NAME________________________________________________________
SELECT DISTINCT C.USER_CONCURRENT_PROGRAM_NAME,
ROUND(((A.ACTUAL_COMPLETION_DATE-A.ACTUAL_START_DATE)*24*60*60/60),2) AS PROCESS_TIME,
A.REQUEST_ID,A.PARENT_REQUEST_ID,TO_CHAR(A.REQUEST_DATE,'DD-MON-YY HH24:MI:SS'),TO_CHAR(A.ACTUAL_START_DATE,'DD-MON-YY HH24:MI:SS'),
TO_CHAR(A.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24:MI:SS'), (A.ACTUAL_COMPLETION_DATE-A.REQUEST_DATE)*24*60*60 AS END_TO_END,
(A.ACTUAL_START_DATE-A.REQUEST_DATE)*24*60*60 AS LAG_TIME,
D.USER_NAME, A.PHASE_CODE,A.STATUS_CODE,A.ARGUMENT_TEXT,A.PRIORITY
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROGRAMS B ,
APPS.FND_CONCURRENT_PROGRAMS_TL C,
APPS.FND_USER D
WHERE A.CONCURRENT_PROGRAM_ID= B.CONCURRENT_PROGRAM_ID AND
B.CONCURRENT_PROGRAM_ID=C.CONCURRENT_PROGRAM_ID AND
A.REQUESTED_BY =D.USER_ID
AND D.USER_NAME LIKE 'JHEWETT'
AND A.PHASE_CODE = 'R' AND
-- TRUNC(A.ACTUAL_COMPLETION_DATE) = '24-AUG-2005'
C.USER_CONCURRENT_PROGRAM_NAME='VEL WSH STANDARD PICK SLIP REPORT' -- AND ARGUMENT_TEXT LIKE '%, , , , ,%';
-- AND STATUS_CODE!='C'
--------------------------------------------------------FIND BELOW USING THIS QUERY-----------------------------------------------------------------------------
FUNCTION_NAME AND USER_FUNCTION_NAME USING THE RESPONSIBILITY
SELECT DISTINCT RTL.RESPONSIBILITY_NAME, FF.FUNCTION_NAME, FFL.USER_FUNCTION_NAME
FROM FND_COMPILED_MENU_FUNCTIONS CMF,
FND_FORM_FUNCTIONS FF,
FND_FORM_FUNCTIONS_TL FFL,
FND_RESPONSIBILITY R,
FND_RESPONSIBILITY_TL RTL,
FND_USER_RESP_GROUPS URG,
FND_USER U
WHERE CMF.FUNCTION_ID = FF.FUNCTION_ID
AND R.MENU_ID = CMF.MENU_ID
AND URG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RTL.RESPONSIBILITY_NAME LIKE 'SYSTEM%ADMINISTRATOR%'
--AND U.USER_NAME LIKE 'GBORASANIYA'
--AND FF.FUNCTION_NAME LIKE '%INSTALL%'
AND FFL.USER_FUNCTION_NAME LIKE '%PRINTER%'
AND CMF.GRANT_FLAG = 'Y'
AND R.APPLICATION_ID = URG.RESPONSIBILITY_APPLICATION_ID
-- AND U.USER_ID = URG.USER_ID
-- AND UPPER (FFL.USER_FUNCTION_NAME) = UPPER ('RCVTXVTX')
AND FF.FUNCTION_ID = FFL.FUNCTION_ID
--ORDER BY U.USER_NAME;
_________________________________________________DETAILS OF CONCURRENT PROGRAM AND EXECUTABLE METHOD AS WELL ____________________________________________________
SELECT FCPT.USER_CONCURRENT_PROGRAM_NAME ,
FCP.CONCURRENT_PROGRAM_NAME SHORT_NAME ,
FAT.APPLICATION_NAME PROGRAM_APPLICATION_NAME ,
FET.EXECUTABLE_NAME ,
FAT1.APPLICATION_NAME EXECUTABLE_APPLICATION_NAME ,
FLV.MEANING EXECUTION_METHOD ,
FET.EXECUTION_FILE_NAME ,
FCP.ENABLE_TRACE
FROM FND_CONCURRENT_PROGRAMS_TL FCPT ,
FND_CONCURRENT_PROGRAMS FCP ,
FND_APPLICATION_TL FAT ,
FND_EXECUTABLES FET ,
FND_APPLICATION_TL FAT1 ,
FND_LOOKUP_VALUES FLV
WHERE 1 =1
AND FCPT.USER_CONCURRENT_PROGRAM_NAME='SUPPLIER AUDIT REPORT'
AND FCPT.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPT.APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.APPLICATION_ID = FAT.APPLICATION_ID
AND FCPT.APPLICATION_ID = FAT.APPLICATION_ID
AND FCP.EXECUTABLE_ID = FET.EXECUTABLE_ID
AND FCP.EXECUTABLE_APPLICATION_ID = FET.APPLICATION_ID
AND FET.APPLICATION_ID = FAT1.APPLICATION_ID
AND FLV.LOOKUP_CODE = FET.EXECUTION_METHOD_CODE
AND FLV.LOOKUP_TYPE ='CP_EXECUTION_METHOD_CODE';
FIND THE EXACAT RESULT THAT WHICH OBJECT HAS GRANT ON WHICH SCHEMA:-
SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME LIKE 'XX_CONVERT_BUDGET_USD_FNC' AND TYPE LIKE 'VIEW' AND REFERENCED_TYPE LIKE 'FUNCTION';
_________________________________________________FOR CHECKING THE LOCKS IN CONCURRENT JOBS_______________________________________________________________________
SELECT DECODE(REQUEST,0,'HOLDER: ','WAITER: ')||SID SESS,INST_ID,ID1, ID2, LMODE, REQUEST, TYPE FROM GV$LOCK
WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM GV$LOCK WHERE REQUEST>0) ORDER BY ID1,REQUEST;
HTTPS://GITHUB.COM/GWENSHAP/ORACLE-DBA-SCRIPTS/BLOB/MASTER/LOCKS.SQL
_______________________________FOR CHECKING THE CONCURRENT PROGRAMS RUNNING CURRENTLY WITH DETAILS OF PROCESSED TIME-- AND START DATE____________________________
SELECT DISTINCT C.USER_CONCURRENT_PROGRAM_NAME,ROUND(((SYSDATE-A.ACTUAL_START_DATE)*24*60*60/60),2) AS PROCESS_TIME,
A.REQUEST_ID,A.PARENT_REQUEST_ID,A.REQUEST_DATE,A.ACTUAL_START_DATE,A.ACTUAL_COMPLETION_DATE,(A.ACTUAL_COMPLETION_DATE-A.REQUEST_DATE)*24*60*60 AS END_TO_END,
(A.ACTUAL_START_DATE-A.REQUEST_DATE)*24*60*60 AS LAG_TIME,D.USER_NAME, A.PHASE_CODE,A.STATUS_CODE,A.ARGUMENT_TEXT,A.PRIORITY
FROM APPS.FND_CONCURRENT_REQUESTS A,APPS.FND_CONCURRENT_PROGRAMS B,APPS.FND_CONCURRENT_PROGRAMS_TL C,APPS.FND_USER D
WHERE A.CONCURRENT_PROGRAM_ID=B.CONCURRENT_PROGRAM_ID AND B.CONCURRENT_PROGRAM_ID=C.CONCURRENT_PROGRAM_ID AND
A.REQUESTED_BY=D.USER_ID AND STATUS_CODE='R' ORDER BY PROCESS_TIME DESC;
___________________________________QUERY 3:FOR CHECKING LAST RUN OF A CONCURRENT PROGRAM ALONG WITH PROCESSED TIME_______________________________________________
-- USEFUL TO FIND THE DETAILS OF CONCURRENT PROGRAMS WHICH RUN DAILY AND COMPARISON PURPOSE
SELECT DISTINCT C.USER_CONCURRENT_PROGRAM_NAME,
ROUND(((A.ACTUAL_COMPLETION_DATE-A.ACTUAL_START_DATE)*24*60*60/60),2) AS PROCESS_TIME,
A.REQUEST_ID,A.PARENT_REQUEST_ID,TO_CHAR(A.REQUEST_DATE,'DD-MON-YY HH24:MI:SS'),TO_CHAR(A.ACTUAL_START_DATE,'DD-MON-YY HH24:MI:SS'),
TO_CHAR(A.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24:MI:SS'), (A.ACTUAL_COMPLETION_DATE-A.REQUEST_DATE)*24*60*60 AS END_TO_END,
(A.ACTUAL_START_DATE-A.REQUEST_DATE)*24*60*60 AS LAG_TIME,
D.USER_NAME, A.PHASE_CODE,A.STATUS_CODE,A.ARGUMENT_TEXT,A.PRIORITY
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROGRAMS B ,
APPS.FND_CONCURRENT_PROGRAMS_TL C,
APPS.FND_USER D
WHERE A.CONCURRENT_PROGRAM_ID= B.CONCURRENT_PROGRAM_ID AND
B.CONCURRENT_PROGRAM_ID=C.CONCURRENT_PROGRAM_ID AND
A.REQUESTED_BY =D.USER_ID AND
TRUNC(A.ACTUAL_COMPLETION_DATE) = '24-AUG-2005'
C.USER_CONCURRENT_PROGRAM_NAME='INCENTIVE COMPENSATION ANALYTICS - ODI' -- AND ARGUMENT_TEXT LIKE '%, , , , ,%';
AND STATUS_CODE!='C'
_________________________________________BY USING THE BELOW QUERY WE CAN GET SID,SERIAL#,SPID OF THE CONCURRENT REQUEST__________________________________________
SELECT A.REQUEST_ID, D.SID, D.SERIAL# , C.SPID
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
AND A.PHASE_CODE = 'R';
---------------------------------------------------FIND THE BUSINESS GROUP -------------------------------------------------------
SELECT * FROM PER_BUSINESS_GROUPS WHERE NAME LIKE '%GERMANY%';
___________________________________________________QUERY TO FIND REQUEST ID FROM SESSION ID____________________________________________________________________
SELECT VP.SPID,
VS.SID,
FCR.REQUEST_ID
FROM V$PROCESS VP,
V$SESSION VS,
FND_CONCURRENT_REQUESTS FCR
WHERE VS.PADDR = VP.ADDR
AND FCR.ORACLE_PROCESS_ID = VP.SPID
AND VS.SID = :L_SID;
________________________________________________BY USING BELOW CONCURRENT MANAGER AND PROGRAM RULES______________________________________________________________
GIVES DETAIL OF THE CONCURRENT_QUEUE_NAME AND USER_CONCURRENT_PROGRAM_NAME
SELECT B.CONCURRENT_QUEUE_NAME, C.USER_CONCURRENT_PROGRAM_NAME
FROM FND_CONCURRENT_QUEUE_CONTENT A, FND_CONCURRENT_QUEUES B, FND_CONCURRENT_PROGRAMS_VL C
WHERE A.QUEUE_APPLICATION_ID = 283
AND A.CONCURRENT_QUEUE_ID = B.CONCURRENT_QUEUE_ID
AND A.TYPE_ID = C.CONCURRENT_PROGRAM_ID
ORDER BY DECODE(INCLUDE_FLAG, 'I', 1, 2), TYPE_CODE;
_______________________________________________________GIVES DETAILS OF RUNNING CONCURRENT JOBS__________________________________________________________________
SELECT DISTINCT C.USER_CONCURRENT_PROGRAM_NAME,
ROUND(((SYSDATE-A.ACTUAL_START_DATE)*24*60*60/60),2) AS PROCESS_TIME,
A.REQUEST_ID,A.PARENT_REQUEST_ID,A.REQUEST_DATE,A.ACTUAL_START_DATE,A.ACTUAL_COMPLETION_DATE,
(A.ACTUAL_COMPLETION_DATE-A.REQUEST_DATE)*24*60*60 AS END_TO_END,
(A.ACTUAL_START_DATE-A.REQUEST_DATE)*24*60*60 AS LAG_TIME,
D.USER_NAME, A.PHASE_CODE,A.STATUS_CODE,A.ARGUMENT_TEXT,A.PRIORITY
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROGRAMS B ,
APPS.FND_CONCURRENT_PROGRAMS_TL C,
APPS.FND_USER D
WHERE A.CONCURRENT_PROGRAM_ID=B.CONCURRENT_PROGRAM_ID AND
B.CONCURRENT_PROGRAM_ID=C.CONCURRENT_PROGRAM_ID AND
A.REQUESTED_BY=D.USER_ID AND
STATUS_CODE='R' ORDER BY PROCESS_TIME DESC;
____________________________________________________GIVES DETAIL OF CONCURRENT JOB COMPLETED AND PENDING_________________________________________________________
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- ADD AS REQUIRED
--AND REFERENCED_OWNER = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&PACKAGE_NAME')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND FCR.PHASE_CODE NOT IN ( 'C','P');
__________________________________________QUERY TO FIND THE NAME OF USERS WHO HAVE SYSTEM ADMINISTRATOR RESPONSIBILITY___________________________________________
SELECT DISTINCT(C.RESPONSIBILITY_NAME),B.USER_NAME,A.START_DATE, A.END_DATE
FROM FND_USER_RESP_GROUPS_DIRECT A, FND_USER B, FND_RESPONSIBILITY_TL C
WHERE A.USER_ID = B.USER_ID
AND A.RESPONSIBILITY_ID = C.RESPONSIBILITY_ID
AND USER_NAME LIKE '%DQUINTAS%'
AND A.START_DATE LIKE SYSDATE
ORDER BY A.START_DATE;
_____________________________________________PARAMETER VALUES OF WORKFLOW IN INSTANCE____________________________________________________________________________
SELECT FSCPV.PARAMETER_VALUE FROM FND_SVC_COMP_PARAMS_TL FSCPT ,FND_SVC_COMP_PARAM_VALS FSCPV WHERE FSCPT.DISPLAY_NAME = 'TEST ADDRESS' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID;
__________________________________FIDN ALL USERS WITH ACTIVE TRANSACTION ROLLBACK SEGMENTS_______________________________________________________________________
NOTE:- WE ALSO CAN FIND FROM THE $AD_TOP/SQL/ADXUPSRU.SQL FILE
SELECT R.NAME, L.SID, S.USERNAME, S.OSUSER, S.PROGRAM FROM V$ROLLNAME R, V$SESSION S, V$LOCK L WHERE R.USN = TRUNC(L.ID1/65536)
AND L.SID = S.SID AND L.TYPE = 'TX' ORDER BY R.NAME;
________________________________________FIND THE LOG AND OUT FILE SIZE________________________________________________
SELECT USER_CONCURRENT_PROGRAM_NAME, CNT, SUMOFOUT_IN_GB,SUMOFLOG_IN_GB FROM
(
SELECT CONCURRENT_PROGRAM_ID AS CPID, COUNT(*) AS CNT , SUM(OFILE_SIZE/1024/1024/1024) AS SUMOFOUT_IN_GB,SUM(LFILE_SIZE/1024/1024/1024) AS SUMOFLOG_IN_GB FROM FND_CONCURRENT_REQUESTS
GROUP BY CONCURRENT_PROGRAM_ID
-- HAVING COUNT(*) > 1000
)
,
FND_CONCURRENT_PROGRAMS_TL
WHERE CONCURRENT_PROGRAM_ID = CPID
AND LANGUAGE = 'US'
ORDER BY SUMOFLOG_IN_GB;
__________________________________USER'S ORACLE ACCOUNT NAME THAT WILL DISPLAY THE USER'S LAST ORACLE LOGIN DATE._______________________________________________
SELECT USER_NAME USERNAME, DESCRIPTION NAME, TO_CHAR(B.LAST_CONNECT,'YYYY-MON-DD') LASTCONNECT, A.PASSWORD_DATE LAST_CHANGED_PASSWORD_DATE
FROM APPS.FND_USER A,
(SELECT MAX (LAST_CONNECT) LAST_CONNECT, LAST_UPDATED_BY USER_ID
FROM APPS.ICX_SESSIONS GROUP BY LAST_UPDATED_BY) B
WHERE A.USER_ID = B.USER_ID
AND USER_NAME LIKE '%MSILVIA%';
____________________________________________FIND THE ACTUAL AND TARGET PROCESS ________________________________________
SELECT DECODE(CONCURRENT_QUEUE_NAME,'FNDICM','INTERNAL MANAGER','FNDCRM','CONFLICT RESOLUTION MANAGER','AMSDMIN','MARKETING DATA MINING MANAGER','C_AQCT_SVC','C AQCART SERVICE','FFTM','FASTFORMULA TRANSACTION MANAGER','FNDCPOPP','OUTPUT POST PROCESSOR','FNDSCH','SCHEDULER/PRERELEASER MANAGER','FNDSM_AQHERP','SERVICE MANAGER: AQHERP','FTE_TXN_MANAGER','TRANSPORTATION MANAGER','IEU_SH_CS','SESSION HISTORY CLEANUP','IEU_WL_CS','UWQ WORKLIST ITEMS RELEASE FOR CRASHED SESSION','INVMGR','INVENTORY MANAGER','INVTMRPM','INV REMOTE PROCEDURE MANAGER','OAMCOLMGR','OAM METRICS COLLECTION MANAGER','PASMGR','PA STREAMLINE MANAGER','PODAMGR','PO DOCUMENT APPROVAL MANAGER','RCVOLTM','RECEIVING TRANSACTION MANAGER','STANDARD','STANDARD MANAGER','WFALSNRSVC','WORKFLOW AGENT LISTENER SERVICE','WFMLRSVC','WORKFLOW MAILER SERVICE','WFWSSVC','WORKFLOW DOCUMENT WEB SERVICES SERVICE','WMSTAMGR','WMS TASK ARCHIVING MANAGER','XDP_APPL_SVC','SFM APPLICATION MONITORING SERVICE','XDP_CTRL_SVC','SFM CONTROLLER SERVICE','XDP_Q_EVENT_SVC','SFM EVENT MANAGER QUEUE SERVICE','XDP_Q_FA_SVC','SFM FULFILLMENT ACTIONS QUEUE SERVICE','XDP_Q_FE_READY_SVC','SFM FULFILLMENT ELEMENT READY QUEUE SERVICE','XDP_Q_IN_MSG_SVC','SFM INBOUND MESSAGES QUEUE SERVICE','XDP_Q_ORDER_SVC','SFM ORDER QUEUE SERVICE','XDP_Q_TIMER_SVC','SFM TIMER QUEUE SERVICE','XDP_Q_WI_SVC','SFM WORK ITEM QUEUE SERVICE','XDP_SMIT_SVC','SFM SM INTERFACE TEST SERVICE') AS "CONCURRENT MANAGER'S NAME", MAX_PROCESSES AS "TARGET PROCESSES", RUNNING_PROCESSES AS "ACTUAL PROCESSES" FROM APPS.FND_CONCURRENT_QUEUES WHERE CONCURRENT_QUEUE_NAME IN ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');
___________________________________________________________WORKFLOW MAILER IS RUNNING OR NOT_____________________________________________________________________
SELECT COMPONENT_NAME AS COMPONENT, COMPONENT_STATUS AS STATUS FROM FND_SVC_COMPONENTS WHERE COMPONENT_TYPE = 'WF_MAILER';
___________________________________________________________PATCH RELATED QUERIES _____________________________________________________________________
SELECT TO_CHAR(R.PATCH_RUN_ID) PATCH_RUN_ID ,T.NAME APPL_TOP ,D.DRIVER_FILE_NAME DRIVER_FILE_NAME ,L.LANGUAGE LANGUAGE ,TO_CHAR(R.START_DATE,'YYYY/MM/DD HH24:MI:SS') START_DATE ,TO_CHAR(R.END_DATE,'YYYY/MM/DD HH24:MI:SS') END_DATE ,R.PATCH_TOP PATCH_TOP
FROM AD_APPL_TOPS T,AD_PATCH_DRIVERS D,AD_PATCH_RUNS R,AD_PATCH_DRIVER_LANGS L
WHERE T.APPL_TOP_ID = R.APPL_TOP_ID
AND R.PATCH_DRIVER_ID = D.PATCH_DRIVER_ID
AND L.PATCH_DRIVER_ID = D.PATCH_DRIVER_ID
AND D.DRIVER_FILE_NAME LIKE '%9245525%'
ORDER BY R.PATCH_RUN_ID;
===============================
HOW TO KNOW, WHAT ALL HAS BEEN DONE DURING APPLICATION OF PATCH?
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 = 'MERGED'))
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;
===============================
SELECT APP_SHORT_NAME, MAX(PATCH_LEVEL) FROM AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME;
===============================
SELECT AAT.APPLICATIONS_SYSTEM_NAME DATABASE, AAT.NAME SERVER, APPS.PATCH_NAME, APPS.PATCH_TYPE,
DECODE (APPS.RAPID_INSTALLED_FLAG, NULL, 'NO', 'YES') RAPID_INSTALL,
APPS.SOURCE_CODE, APD.DRIVER_FILE_NAME, APR.PATCH_TOP,
APR.PATCH_ACTION_OPTIONS, APR.START_DATE, APR.END_DATE,
APD.PLATFORM, APR.SERVER_TYPE_NODE_FLAG NODE, APR.SERVER_TYPE_ADMIN_FLAG ADMIN,
APR.SERVER_TYPE_FORMS_FLAG FORMS, APR.SERVER_TYPE_WEB_FLAG WEB
FROM APPLSYS.AD_APPLIED_PATCHES APPS,
APPLSYS.AD_APPL_TOPS AAT,
APPLSYS.AD_PATCH_DRIVERS APD,
APPLSYS.AD_PATCH_RUNS APR
WHERE APPS.APPLIED_PATCH_ID = APD.APPLIED_PATCH_ID
AND APD.PATCH_DRIVER_ID = APR.PATCH_DRIVER_ID
AND APR.APPL_TOP_ID = AAT.APPL_TOP_ID
-- AND TRUNC(APPS.CREATION_DATE) > TRUNC(TO_DATE('06/04/2006', 'DD/MM/YYYY'))
AND PATCH_NAME LIKE '9245674'
ORDER BY APR.START_DATE ASC;
================================
SELECT APPS.PATCH_NAME,Q.PATCH_TOP,A.CREATION_DATE,A.LAST_UPDATE_DATE
FROM APPLSYS.AD_PATCH_RUN_BUGS A,APPLSYS.AD_BUGS Z,
APPLSYS.AD_PATCH_RUNS Q,APPLSYS.AD_PATCH_DRIVERS APD, APPLSYS.AD_APPLIED_PATCHES APPS
WHERE A.BUG_ID= Z.BUG_ID
AND Z.BUG_NUMBER = '9245674'
AND Q.PATCH_RUN_ID = A.PATCH_RUN_ID
AND APPS.APPLIED_PATCH_ID = APD.APPLIED_PATCH_ID
AND APD.PATCH_DRIVER_ID = Q.PATCH_DRIVER_ID;
______________________________________QUERY TO FIND THE STATUS AND PHASE OF REQUEST___________________________________________
RUNNING REQUEST WITH THE START AND COMPLETION TIME:
SELECT S.SID,S.SERIAL#,ORACLE_PROCESS_ID "OS_PROCESS_ID",FUSR.DESCRIPTION "USER_NAME" ,FCP.USER_CONCURRENT_PROGRAM_NAME "PROG_NAME",FCR.ACTUAL_START_DATE START_TIME,FCR.ACTUAL_COMPLETION_DATE "COMPLETION_TIME"
FROM
FND_CONCURRENT_REQUESTS FCR,
FND_CONCURRENT_PROGRAMS_TL FCP,
FND_USER FUSR,
V$SESSION S
WHERE FCP.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.LANGUAGE = 'US'
AND FCR.PHASE_CODE = 'R'
AND FCR.STATUS_CODE = 'R'
AND FCR.REQUESTED_BY = FUSR.USER_ID
ORDER BY 5 DESC;
PENDING REQUEST:
SELECT S.SID,S.SERIAL#,ORACLE_PROCESS_ID "OS_PROCESS_ID",FUSR.DESCRIPTION "USER_NAME" ,FCP.USER_CONCURRENT_PROGRAM_NAME "PROG_NAME",FCR.ACTUAL_START_DATE "START_TIME",FCR.ACTUAL_COMPLETION_DATE "COMPLETION_TIME"
FROM
FND_CONCURRENT_REQUESTS FCR,
FND_CONCURRENT_PROGRAMS_TL FCP,
FND_USER FUSR,
V$SESSION S WHERE
FCP.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.LANGUAGE = 'US'
AND FCR.PHASE_CODE = 'P'
AND FCR.STATUS_CODE = 'R' --NORMAL
AND FCR.REQUESTED_BY = FUSR.USER_ID
ORDER BY 5 DESC;
FAILED CONCURRENT REQUESTS:-
SELECT B.REQUEST_ID, A.USER_CONCURRENT_PROGRAM_NAME,
B.PHASE_CODE AS COMPLETED, B.STATUS_CODE AS ERROR,
U.USER_NAME REQUESTOR,
TO_CHAR (B.ACTUAL_START_DATE, 'MM/DD/YY HH24:MI:SS') STARTTIME,
ROUND ((B.ACTUAL_COMPLETION_DATE - B.ACTUAL_START_DATE) * (60 * 24),
2
) RUNTIME,
B.COMPLETION_TEXT
FROM FND_CONCURRENT_PROGRAMS_TL A, FND_CONCURRENT_REQUESTS B, FND_USER U
WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND B.PHASE_CODE = 'C'
AND B.STATUS_CODE = 'E'
AND B.ACTUAL_START_DATE > SYSDATE - 1
AND B.REQUESTED_BY = U.USER_ID
AND A.LANGUAGE = 'US';
_________________________________________________________________SCHEDULED CONCURRENT FROM EBS USER___________________________________________________________
SELECT CR.REQUEST_ID,
DECODE (CP.USER_CONCURRENT_PROGRAM_NAME,
'REPORT SET', 'REPORT SET:' || CR.DESCRIPTION,
CP.USER_CONCURRENT_PROGRAM_NAME)
NAME,
ARGUMENT_TEXT,
CR.RESUBMIT_INTERVAL,
NVL2 (CR.RESUBMIT_INTERVAL,
'PERIODICALLY',
NVL2 (CR.RELEASE_CLASS_ID, 'ON SPECIFIC DAYS', 'ONCE'))
SCHEDULE_TYPE,
DECODE (
NVL2 (CR.RESUBMIT_INTERVAL,
'PERIODICALLY',
NVL2 (CR.RELEASE_CLASS_ID, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY', 'EVERY '
|| CR.RESUBMIT_INTERVAL
|| ' '
|| CR.RESUBMIT_INTERVAL_UNIT_CODE
|| ' FROM '
|| CR.RESUBMIT_INTERVAL_TYPE_CODE
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (CR.REQUESTED_START_DATE, 'DD-MON-RR HH24:MI'),
'EVERY: ' || FCR.CLASS_INFO)
SCHEDULE,
FU.USER_NAME,
REQUESTED_START_DATE
FROM APPS.FND_CONCURRENT_PROGRAMS_TL CP,
APPS.FND_CONCURRENT_REQUESTS CR,
APPS.FND_USER FU,
APPS.FND_CONC_RELEASE_CLASSES FCR
WHERE CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = FU.USER_ID
AND CR.PHASE_CODE = 'P'
AND CR.REQUESTED_START_DATE > SYSDATE
AND CP.LANGUAGE = 'US'
AND FCR.RELEASE_CLASS_ID(+) = CR.RELEASE_CLASS_ID
AND FCR.APPLICATION_ID(+) = CR.RELEASE_CLASS_APP_ID
AND USER_NAME LIKE '%USERNAME%;
=========================================QUERY FOR SPECIFIC MENU FOR ALL RESPONSIBILITIES=======================================================================
SELECT DISTINCT FRTL.RESPONSIBILITY_NAME
FROM (SELECT CONNECT_BY_ROOT FMET.MENU_ID TOP_MENU_ID,
FMET.MENU_ID MENU_ID,
FMET.SUB_MENU_ID,
FMET.FUNCTION_ID,
FMET.PROMPT
FROM FND_MENU_ENTRIES_VL FMET
CONNECT BY PRIOR FMET.SUB_MENU_ID = FMET.MENU_ID
AND PRIOR FMET.PROMPT IS NOT NULL) MENU,
FND_RESPONSIBILITY FR,
FND_RESPONSIBILITY_TL FRTL,
FND_MENUS FM
WHERE FR.MENU_ID = MENU.TOP_MENU_ID
AND MENU.FUNCTION_ID IS NOT NULL
AND FRTL.LANGUAGE='US'
AND MENU.PROMPT IS NOT NULL
AND FM.MENU_ID = MENU.MENU_ID
AND FRTL.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
-- AND FRTL.RESPONSIBILITY_NAME LIKE 'SYSTEM ADMINISTRATOR'
AND MENU.FUNCTION_ID NOT IN (SELECT FFVL.FUNCTION_ID
FROM APPS.FND_RESP_FUNCTIONS FRF,
APPLSYS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_FORM_FUNCTIONS_VL FFVL
WHERE
FRF.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
AND FRF.ACTION_ID = FFVL.FUNCTION_ID
AND FRF.RULE_TYPE = 'F'
AND
FRT.RESPONSIBILITY_NAME = FRTL.RESPONSIBILITY_NAME)
AND MENU.MENU_ID NOT IN (SELECT FMV.MENU_ID
FROM APPS.FND_RESP_FUNCTIONS FRF,
APPLSYS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_MENUS_VL FMV
WHERE
FRF.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
AND FRT.LANGUAGE='US'
AND FRF.ACTION_ID = FMV.MENU_ID
AND FRF.RULE_TYPE = 'M'
AND
FRT.RESPONSIBILITY_NAME = FRTL.RESPONSIBILITY_NAME)
AND FM.MENU_NAME ='FND_NAVCUSTOM4.0';
__________________________________________QUERY TO FIND THE PRINTER DETAILS WITH USER NAME AND CONCURRENT REQUEST________________________________________________
QUERY EXECUTED IN SQLPLUS:-
COLUMN USER_CONCURRENT_PROGRAM_NAME FORMAT A100
COLUMN PRINTER FORMAT A20
SET PAGES200
SELECT UNIQUE USER_CONCURRENT_PROGRAM_NAME,PRINTER
FROM APPS.FND_CONCURRENT_REQUESTS FCR,
APPS.FND_CONCURRENT_PROGRAMS_TL FCP,
APPS.FND_USER FU
WHERE FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.REQUESTED_BY = FU.USER_ID
AND REQUEST_ID IN (
SELECT REQUEST_ID
FROM APPS.FND_CONCURRENT_REQUESTS
WHERE LOWER(PRINTER) IN ('GEN_N03_CAN','GEN_P01_CAN')
AND STATUS_CODE!='I' AND PHASE_CODE!='P'
AND MONTHS_BETWEEN (TRUNC(SYSDATE,'MM'), TRUNC(ACTUAL_COMPLETION_DATE,'MM'))<6;
SELECT NUMBER_OF_COPIES,NLS_LANGUAGE,NLS_TERRITORY,PRINTER,PRINT_STYLE,COMPLETION_TEXT,OUTPUT_FILE_TYPE,NLS_CODESET,OUTFILE_NODE_NAME,OUTFILE_NAME FROM APPS.FND_CONCURRENT_REQUESTS WHERE REQUEST_ID = &REQUEST_ID;
=======================================IF ANY USER REQUEST STUCK IN RESPONSIBILITY APPROVAL SO USE THIS QUERY ==================================================
SELECT * FROM APPS.FND_USER WHERE USER_NAME LIKE 'BSAMSO';
SELECT * FROM DBA_TABLES WHERE TABLE_NAME LIKE '%XX_RE%';
SELECT * FROM APPS.XX_RESP_REQUEST_TBL WHERE REQUEST_ID=69831;
=====================================QUERY TO FIND ALL THE SCHEDULED CONCURRENT PROGRAM LIST IN EBS ===========================================================
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
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.REQUESTED_START_DATE > SYSDATE
AND FCPT.LANGUAGE = 'US'
AND FCRC.RELEASE_CLASS_ID(+) = FCR.RELEASE_CLASS_ID
AND FCRC.APPLICATION_ID(+) = FCR.RELEASE_CLASS_APP_ID;
=======================================================================================================================
HOW TO KNOW, WHAT ALL HAS BEEN DONE DURING APPLICATION OF PATCH?
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 = 'MERGED'))
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;
***************************************************************************COMMANDS*****************************************************************************
FNDCPASS LOGON 0 Y SYSTEM/PASSWORD MODE USERNAME NEW_PASSWORD
WHERE MODE IS SYSTEM/USER/ORACLE/ALLORACLE
-----------------------------------------------------------------
WE CAN FIND THE VERSION OF JAVA USING BELOW COMMAND:-
SH -C "'AWK -F= '$1 ~ /^JSERVJAVA.*$/ {PRINT $2}' $ADMIN_SCRIPTS_HOME/JAVA.SH' -VERSION;"
-----------------------------------------------------------------
Scheduler
Installed by default, the database comes with three preconfigured Automatic Maintenance Tasks, called clients. First, the collection of optimizer statistics (Optimizer Statistics Collection), the Segment Advisor (Space Advisor) and the automatic SQL tuning (SQL Tuning Advisor).
a. Status
First, the query for the current state of the database in DBA_AUTOTASK_CLIENTS. Only in Enterprise Edition is the SQL Tuning Advisor ENABLED.
SQL> select client_name, status, attributes from dba_autotask_client;
CLIENT_NAME STATUS ATTRIBUTES
--------------------------------- ------- ----------------------------------------
auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
As a further query the configured time window in DBA_AUTOTASK_WINDOW_CLIENTS:
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
By default seven time windows are configured:
select * from DBA_SCHEDULER_WINDOWS;
Time Window
| Start | Duration | Status |
| MONDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| TUESDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| WEDNESDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| THURSDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| FRIDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| SATURDAY_WINDOW | 6 am | 20h | enabled |
| SUNDAY_WINDOW | 6 am | 20h | enabled |
DBA_AUTOTASK_SCHEDULE supplies the configured time windows for the next 32 days:
select * from DBA_AUTOTASK_SCHEDULE order by start_time;
b. Activation / Deactivation
Quite generally, the auto tasks can be controlled with the DBMS_AUTO_TASK_ADMIN package.
To start and stop AUTO_TASKS enable or disable is sufficient.
exec dbms_auto_task_admin.disable;
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
Following command deactivates only Optimizer Stats Collection:
begin
dbms_auto_task_admin.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);end;/
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS --------------------------------- --------- auto optimizer stats collection DISABLED auto space advisor ENABLEDsql tuning advisor ENABLED
c. Changing START time
If you want to change the start time, you have to adjust the REPEAT_INTERVAL. Below we will change for the MONDAY_WINDOW the start time to 5 clock with DBMS_SCHEDULER.SET_ATTRIBUTE:
begin
dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0');end;/
select window_name, repeat_interval, from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';
WINDOW_NAME REPEAT_INTERVAL
d. Changing DURATION
Is the time window of 4 hours not enough to work through all the tables and the Optimizer Statistics Collection runs out of the predetermined windows, the DURATION must be adjusted. Below, we set the window up to 5 hours.
begin
dbms_scheduler.set_attribute(
name => 'MONDAY_WINDOW',
attribute => 'duration',
value => numtodsinterval(5, 'hour'));
end;
/
select window_name, duration from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';
WINDOW_NAME REPEAT_INTERVAL
e. Creating a new WINDOW
begin
dbms_scheduler.create_window(
window_name => 'SPECIAL_WINDOW',
duration => numtodsinterval(3, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'SPECIAL_WINDOW');
end;
/
