Posts by Doyensys

Query to find the concurrent programs completed in specific day.

Query to find the concurrent programs completed in specific day. ===================================================== select f.request_id , pt.user_concurrent_program_name user_conc_program_name, to_char(f.actual_start_date,’DD-MON-YY HH24:MI:SS’) start_on, to_char(f.actual_completion_date,’DD-MON-YY HH24:MI:SS’) end_on, floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600) || ‘ HOURS ‘ || floor((((f.actual_completion_date-f.actual_start_date)…

Read More

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Error ==== ORA-39166: Object APEX_050100.APEX was not found or could not be exported or imported. ORA-31655: no data or metadata objects selected for job Job “ROOT”.”SYS_EXPORT_TABLE_01″ completed with 2 error(s)…

Read More

Identifying the FND_LOBS segments attached to the database

Total number of attachments per program —————————————– select distinct program_name, count(*) from fnd_lobs group by program_name order by 2 desc; Total number of program with Expiration Date ——————————————— select program_name,count(*)…

Read More

Queries related to workflow

Important queries related to workflow 1.Check the status of Notifications: ==================================== col RECIPIENT_ROLE format a20 col FROM_USER format a20 col TO_USER format a20 set lines 170 select NOTIFICATION_ID,MESSAGE_TYPE,MESSAGE_NAME,RECIPIENT_ROLE,STATUS,FROM_USER,TO_USER from wf_notifications…

Read More

Export and Import from On-premise database to AWS cloud database

Schema Export and Import from  On-premise database to AWS cloud database. Step 1:Connect the on-premise database and the export the schema DECLARE hdnl NUMBER; BEGIN hdnl := DBMS_DATAPUMP.OPEN( operation => ‘EXPORT’,…

Read More

ORA-16816: incorrect database role

In Data Guard, for some reason, standby database rule are incorrectly shown as primary database, while primary database shows as standby rule. DGMGRL> show configuration; Configuration - TESTDB Protection Mode:…

Read More

ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device

When try to start up an Oracle database, the below errors occur:   ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device Additional information:…

Read More

DBMS_METADATA.GET_DDL Fails with ORA-04063 Error

Symptoms Call to dbms_metadata.get_ddl failed with following error – ORA-04063: package body “SYS.DBMS_METADATA” has errors expdp failed with following error – Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA ORA-39126: Worker unexpected fatal error…

Read More

ORA-4063 when Executing DBMS_LOGMNR_D.SET_TABLESPACE (Doc ID 577426.1)

Symptoms — Problem Statement:Setiting logminer to a specific tablespace reports the following error SQL> create tablespace logmnr_tbs datafile ‘${vtbsp_dir01}/logmnr_tbs01.dbf’ size 700m ;SQL> BEGIN dbms_logmnr_d.set_tablespace(‘logmnr_tbs’); end;/2 BEGIN dbms_logmnr_d.set_tablespace(‘logmnr_tbs’); end;*ERROR at line…

Read More

Resolving ORA-02374: conversion error loading table during impdp

Error: ORA-02374: conversion error loading table  “SCHEMA”.”TABLENAME” ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64) ORA-02372: data for row: MINE_TEXT : 0X’45737061C3B16F6C2020202020202020202020202020202020′ More About the Error: 1.        Used…

Read More