Change OWB location details

30 10 2009

If you have used your locations to deploy objects to them (who doesn’t?), you cannot change the connection details easily. In the Design Center they are greyed out. The only way you can change them is by starting the control center, unregistering the location, reregister with the new details and deploy all objects again to regain the right deployment status. The last is something you cannot always do because that would mean e.g. dropping tables that already contain data you do not want to lose. Not an ideal situation.
Today I was reading this post about upgrading to 11gR2 which was a good post. Although I’m currently upgrading to OWB 11gR1 as 11gR2 is not yet available on Windows. Stewart Bryson had some trouble because during the upgrade his connection details where automagically updated. He first described how to change the connection details in the ‘old-fashioned’ way I just mentioned. But more importantly he described a second possibility I did not know of. It is possible to change the details using the OWB Repository browser! I do not start the Repository browser very often because it tends to slow down once you have had your share of deploying and executing. A simple view suffices most of the time (I’ll reserve that one for a later post).
But it meant I did not see all functionality, alas. It could have saved me some time.
Anyway, at least in 11gR1 (I’ll look into 10gR2 shortly) you can change host, port, sid and password from a convenient browser screen as presented below. Nice.

BTW, works for File locations too.
BTW2, The link to this screen is perfectly hidden under the link called ‘Unknown’ at the end of the line for each location
BTW3, It is available in OWB 10gR2 as well


One more date trick

29 06 2009

Tyler Muth has a useful addition to the date functions I have published here before.

If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function.

Like this:

select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago

One more for the toolbox.

(Integrity) Constraints in a datawarehouse

24 06 2009

In data warehouse land it is not very common to see constraints in the database. I never felt very comfortable with that, but until now I did not get around to analysing why I felt that way. Until I read this article by Tom Kyte. In the article Tom Kyte shows that the CBO (Cost Based Optimizer) can profit from the information that is derived from the presence of constraints by generating better query plans. Better in this case is defined as ‘producing result sets faster’. The examples in the article are not exactly ‘real world’ data warehouse examples. Following Tom Kyte’s line of reasoning I do agree that constraints are capable of improving the performance of queries.

The reasons for not having constraints in a data warehouse are along the lines of ‘I have checked the integrity when I did my ETL, so why would I need constraints to confirm that? And besides, constraints would only delay my ETL because they have to be checked before they are really enabled’. I see a couple of flaws in this reasoning:

  • I suspect that most constraints in a data warehouse cannot be enabled when actually applied. The quality of the ETL might be good, but is it just as good as a constraint would be? I think not.
  • Enabling constraints might take time, but how often do you have to check constraints? Only when doing the ETL, of course. I hope that in your DWH, doing ETL will be during a small part of the time your DWH is being used. Otherwise your DWH will have a problem. The rest of the time your DWH will be used for querying and Tom Kyte just showed that querying can be sped up by applying constraints.

Summing up my pros and cons of applying constraints.


  • it will improve the data quality of the DWH
  • it can speed up the queries in your DWH (querying it is the purpose of your DWH anyway)


  • it will take more time to do your ETL (which is only a means to create your DWH)

My conclusion is that I wil try to incorporate as many constraints as possible in my next DWH. It also means I will have to be smart enough to enable the constraints at just the right moment during my ETL to have an acceptable loading performance.

More date arithmetic

22 04 2009

I came across this today. I have been having quite a ball these last weeks with dates, periods and mainly overlapping periods in two tables. Therefore I am currently rather interested in date handling. This is definitely one for the Tips & Tricks book I seem to be gathering here. Nice post.

OWB Process Flows that keep saying they are running

20 04 2009

Now and then an OWB started Processflow has trouble finishing. Or more likely I have created an incomplete Processflow. Most of the time this leads to an error when deploying the processflow again:

RPE-02062: ItemType LOAD_CMS cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor

To find out if there are any such processflows I use the following query:

SELECT item_type, item_key
FROM wf_item_activity_statuses_v
WHERE activity_type_code= 'PROCESS'
AND activity_status_code != 'COMPLETE';

And you can tell OWF the processflow is not running anymore with:

t_result VARCHAR2(300);
wf_engine.abortprocess (‘itemtype’,’itemkey’,t_result);

Where Itemtype and ItemKey must be copied from the results of the query.

There are probably more sophisticated methods to handle these situations (probably from OWF-savvy people), but it gives me a handle to know what is going on and what I can do about it.

Edit November 24, 2010:

Sometimes you cannot find any information using the first query above. But still you get the RPE-2062 error. An even more radical approach would be to remove all audit/run-time information about the process flow from OWF. This means you will have to redeploy the process flow, but that is what you had planned anyway. There is a script for this purpose that can be found in <ORACLE_HOME>/wf/admin/sql/wfrmitt.sql. Mind about the slashes or backslashes for your environment. The <ORACLE_HOME>/wf directory is only present on the server where you have installed the workflow software. In most client installations it won’t be there.
Execute the script as the workflow user.

Little known but useful facts about Oracle

31 03 2009

Laurent Schneider has a blog that I follow and always has tiny facts that you think you know the details about. But, when you look closely, the details are somewhat different from what you thought they were.

One such gem today: 1 <> 1 and another about 2 weeks ago: commit suicide

Number of days in a year

26 03 2009

I tend to forget these things. So I\’d better write them down.

select to_date(\'09-sep-2009\') - trunc(\'09-sep-2009\', \'mm\') days_since_month_begin 
,trunc (to_date(\'09-sep-2009\'),\'yyyy\') first_day_of_year
,interval \'1\' year just_curious
,to_char(trunc (to_date(\'09-sep-2009\'),\'yyyy\')+ interval \'1\' year - 1 , \'ddd\') days_in_year
from dual;