generate an dblink

 SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

Steps to start/stop Workflow Notification Mailer--backend

 

Steps to start/stop Workflow Notification Mailer

 

·         Check workflow mailer service current status

 

SQL> select running_processes from fnd_concurrent_queues

 where concurrent_queue_name = 'WFMLRSVC';  

 

RUNNING_PROCESSES

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

                1

Number of running processes should be greater than 0

 

·         Find current mailer status

 

SQL> select component_status

 from fnd_svc_components

 where component_id =

 (select component_id

 from fnd_svc_components

 where component_name = 'Workflow Notification Mailer'); 

 

COMPONENT_STATUS

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

DEACTIVATED_USER

 

 

Possible values are :

RUNNING STARTING,

STOPPED_ERROR,

DEACTIVATED_USER

DEACTIVATED_SYSTEM.

 

·         Stop Workflow mailer

 

declare

p_retcode number;

p_errbuf varchar2(100);

m_mailerid fnd_svc_components.component_id%TYPE;

begin

select component_id

into m_mailerid

from fnd_svc_components

where component_name = 'Workflow Notification Mailer';

fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);

commit;

end;

/

 

·         Start the Notification Mailer

 

declare

p_retcode number;

p_errbuf varchar2(100);

m_mailerid fnd_svc_components.component_id%TYPE;

begin

select component_id

into m_mailerid

from fnd_svc_components

where component_name = 'Workflow Notification Mailer';

fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);

commit;

end;

/

 

 

TKPROF Overview

 

TKPROF Overview.

 

TKPROF is used for diagnosing performance issues.  It formats a trace file into readable format for performance analysis.

Syntax

tkprof    tracefile_name.trc  tracefileoutput.txt sys=no  sort='(prsela,exeela,fchela)'  explain=apps/apps passwd.

 

 

sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements.  Default=enable. 

explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan provides the predicted optimizer execution path without actually executing the statement.  tkprof shows you the actual execution path and statistics after the statement is executed.

 

sort – Sorts the SQL statements in the trace file by the criteria required.It provides SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers.

prsela – The elapsed time spent parsing the SQL.

exeela – The elapsed time spent executing the SQL.

fchela – The elapsed time spent fetching rows.