Oracle CR and LF

Oracle uses chr(13) to denote an 0D and chr(10) to denote an 0A. If you need to add or remove these from a column you can use these values in your query. I needed to remove them from a notes field so I used the following

replace(column,chr(10)) - this removed all 0As from the column.

OWB Global Variables

NUMBER_OF_ERRORS
NUMBER
Number of errors reported on completion of activity's execution.

NUMBER_OF_WARNINGS
NUMBER
Number of warnings reported on completion of activity's execution.

RETURN_RESULT
VARCHAR2(64)
Textual representation of result. For example, 'SUCCESS', 'WARNING', 'ERROR'.

RETURN_RESULT_NUMBER
NUMBER
Enumeration of RESULT_RESULT1 = SUCCESS2 = WARNING3 = ERROR.

RETURN_CODE
NUMBER
Integer 0-255, specific to activity, synonymous with an Operating System return code.

PARENT_AUDIT_ID
NUMBER
The audit id of the calling Process Flow.

AUDIT_ID
NUMBER
The audit id of the activity.

source: http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223/concept_processflows.htm

How to kill a run away process flow

To kill a process

Select Wf.Item_Key , Wf.*
From Wf_Item_Activity_Statuses_V Wf
Where Activity_Type_Code = 'PROCESS'
And Activity_Status_Code != 'COMPLETE'


begin
wf_engine.abortProcess('ODS0', 'ETL_ODS0_2007_02_25_2007_08_27_11_33');
end;

EXPDP to multiple files

If you know that your export job will span multiple GBs and you want to split it at a known file size you can do so by using a couple of switches.

%U - used in the file name to assign the file #
FILESIZE - allows you to specify the dumpfile size

Example
expdp brian@msdss directory=datapump_dir dumpfile=filename_U%.dmp logfile=filename.log content=ALL filesize=2G tables=table_name

What is the Least busiest RAC

While working on the latest project, I realized I needed away to know what was the least busiest RAC. Also I needed to exclude certain RACS as well as setting the maximum allowed CPU Utilization. This query is based on a call to the RAC via a database link. We needed to know not only the last stat but also the last five minutes of CPU Utilization.


SELECT tmpA.INST_ID
,tmpA.avgpast5mins
,tmpB.laststat
,tmpC.INSTANCE_NUMBER
,tmpC.INSTANCE_NAME
,tmpC.HOST_NAME
,tmpD.N_MAX_CPU_PER_SERVER
FROM
(select INST_ID ,AVG(VALUE) avgpast5mins
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
AND BEGIN_TIME > sysdate - 5*(1/60)/24
GROUP BY INST_ID ) tmpA
INNER JOIN
(select A.INST_ID
,a.BEGIN_TIME
,VALUE laststat
from GV$SYSMETRIC_HISTORY@DLK_RAC A
INNER JOIN
(select INST_ID
, MAX(BEGIN_TIME) BEGIN_TIME
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
GROUP BY INST_ID) B
ON A.INST_ID = B.INST_ID
and A.BEGIN_TIME = B.BEGIN_TIME
where A.metric_name='Host CPU Utilization (%)' ) tmpB
ON tmpA.INST_ID = tmpB.INST_ID
INNER JOIN GV$INSTANCE@DLK_RAC tmpC
ON tmpA.INST_ID = tmpC.INST_ID
INNER JOIN GEAP_PROJECT.TBL_PRG_CONFIGURATION tmpD
ON 1=1
WHERE tmpC.INSTANCE_NAME NOT IN
(SELECT INSTANCE_NAME
FROM "GEAP_PROJECT"."TBL_EXTRACT_RAC_EXCLUSION")
AND AVGPAST5MINS < N_MAX_CPU_PER_SERVER
AND LASTSTAT < N_MAX_CPU_PER_SERVER
ORDER BY LASTSTAT

OWB FOR_LOOP Activity

I finally figured out the FOR_LOOP activity for OWB. It is not the most documented activity, nor the most intuitive in my opinion.

First, create a process flow. Add in a FOR_LOOP and a WAIT activity as seem below.. Don't forget to set the 2 transitions leaving the FOR_LOOP, one for "Loop" the other as "Exit".

Now add in two variables to the process flow. I called one "INTERATOR" and the other "MAX_INTERATION". See the two following figures to see the settings for these variables.

I set the wait activity to 2 seconds. Now we set the properties of the FOR_LOOP

Hope this helps,

Danny

hierarchical queries

This summary is not available. Please click here to view the post.

V$ and GV$ views and permissions

QUOTED FROM" http://www.sapereonline.com/dictionary.html

"The final set of data dictionary objects are not intended for direct access by most users or commonprograms. Over 1,000 of these views (called fixed views) have names beginning with V$ or GV$. (TheV$ views provide information about the instance you are connected to while the GV$ views providea global view of all instances within a Real Application Clusters database environment.) Monitoringand tuning tools as well as SQL scripts developed by system DBAs will commonly select from theseV$ and GV$ views. Developers without access to these tools may find useful tuning information insome of these views such as V$SQLAREA and V$SYSSTAT. (Access to these views is, by default,restricted so developers will generally have to be granted the SELECT ANY DICTIONARY system privilegeby a DBA before they will be able to view this information.)"


Danny

Time zone and Header in Emails using utl_smtp

One of the problems I have had for what seems like ages is the incorrect time sent while using utl_smtp, or maybe I should say how I was using it. Everytime I would send an Email in OWB or any Stored Procedure, it would show a five hour difference (yes I live in the central time zone here in the USA). Now I knew it was something about the time zone, but for the life of me I just didn’t have the time to figure it out. Below is an example of what my Inbox said I got it versus when it was sent. (figure 1)




What I was saying earlier was how I was using it. When I created the message header, it looked like this.

--======================================
-- Build the envelope
--======================================
mesg:=
'Date: 'TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ) crlf
'From: ' p_From_Alias crlf
'Subject: ' p_Subject crlf 'To: ';


Notice the TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ). In the world of email, this is actually incorrect. It does not take into affects of time zones. If you use the SYSTIMESTAMP with the correct format, then your emails will show correctly. Here is an example of how to format the time

TO_CHAR(SYSTIMESTAMP,'DD Mon YYYY HH24:MI:SS TZHTZM')


Danny

SQL Developer version 1.2.1

I finally see the issue I was having with SQL Developer. When I compiled under the version 1.1.0.23, all I saw were warnings. From this I see the package has been compiled and ready to run.





When I compiled under the latest version 1.2.1, but note the difference in the compiler log at the last line. “Only first 20 issues are reported”




After some research, I found a preference that I need to set.

Now see the difference in the messages and compiler log.

I hope this sheds some light on the version differences and how it can help you.

Danny



SOURCE: http://forums.oracle.com/forums/thread.jspa?threadID=379327

Getting the audit execution id

One of the things I have always wanted was the Audit Execution ID at runtime. To get this value I only had two ways after the process begin: query the audit log or look at the control center (see figure 1). WTF Oracle!

figure1

I finally found a way to get this value. There is a global variable I never knew existed called audit_id. Not only that, there is a public function that will give you the parent audit id as well called WB_RT_GET_PARENT_AUDIT_ID
For an example, I have created a process flow with two output parameters and two assignments activities (see figure2).

figure2

For the first I bind the first output parameter to the first activity and give it a value of audit_id and set the literal to false (see figure3).. For the second assignment activity, I bonded it to the second output parameter with a value of WB_RT_GET_PARENT_AUDIT_ID(audit_id) and set the literal to false.

figure 3

I then deployed the process flow and executed. Once the process flow had completed, I open the control center and looked the properties of the execution (see figure5). When reviewing the output parameters, you can see the audit id for the assignment activity and the parent’s audit id, which was the process flow’s .

Out of my brain into the anals of blog land,

hope you can find use for this brain drain,

now where is my beer.

Danny