Archive for the ‘Oracle Queries’ Category

Cancel All Pending Concurrent Request

December 27th, 2017, posted in Oracle Queries
Share

Oracle : Cancel All Pending Concurrent Request

Updating base tables directly is not supported or recommended by Oracle – this update would be OK to execute in a non-production instance. In a production instance, use APIs or cancel the pending requests thru the forms interface

 

update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='P' ;
Share

Database Log Switch Intervals

December 4th, 2017, posted in Oracle Queries
Share

Oracle Database Log Switch Intervals :

 

Hourly Log Switch breakdown :

 

SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 30
/

Archive, Archive Log, Archive Log Mode, Check Oracle Archived, Check Oracle Archived Redo Log, Check Oracle Archiving, Check Oracle Redo Log, How to disable Archive Log Mode, How to Enable Archive Log, Log Mode, Oracle, Oracle Archived Redo, Oracle Archiving, Oracle Archiving Log, Oracle Database 10g, Oracle Database 11g, Oracle DBA, Oracle DBA Task, Redo Lob, Redo Log, Steps To Configure Archive Log Mode in Oracle Database 10g and 11g, Steps To Configure Archive Log Mode in Oracle Database 11g
Share

Oracle Database-Archive Log Sizes Spooled

November 25th, 2017, posted in Oracle Queries
Share

Database – Archive Log Sizes Spooled :Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files,Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,

 

1) Info on archived logs spooled/deleted during the past 24 hours

2) Info on archived logs spooled/deleted during the previous 7 days

3) Info on archived logs spooled/deleted during the previous 31 days

 

1) Info on archived logs spooled/deleted during the past 24 hours:


SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-1
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

 

 

2) Info on archived logs spooled/deleted during the previous 7 days:

SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-7
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

 

3) Info on archived logs spooled/deleted during the past 31 days:

SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-31
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

Share

Disable Auditing In A 11G Database

November 18th, 2017, posted in Oracle Queries
Share

In a RAC Database :

 

SQL> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile sid='*';
System altered.
SQL>

Note : Restart the database to have the change take affect, or do a rolling restart, one instance at a time.




In a Single Instance Database :

 

SQL> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile ;
System altered.
SQL>

Note : Restart the database to have the change take affect.

Share

Find The Inventory Organizations Assigned To Ones Operating Unit

November 6th, 2017, posted in Oracle Queries
Share
SELECT hou.NAME operating_unit_name,Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files,Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,
hou.short_code,
hou.organization_id operating_unit_id,
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id,
ood.chart_of_accounts_id
FROM hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC;
Share