OWB site with nice info

Insider tip:
http://www.oracledwh.de

Some orignal oracle course material and indepth info. (some is in german)

Till Next Time

OWB10.1: Export of recently changed objects

Due to a hickup in communications a lot of changes where implemented in a OWB10.1 enviroment which we were migrating to OWB11gR2. To make things worse the changes weren't well documented. This meant I had to query the OWB10.1 repository (ALL_IV_ tables) to see which objects we changed and make the proper export to mdl statement:

select
'OMBEXPORT TO MDL_FILE ''$ExportDir/TAB_'||TABLE_NAME||
''' \
COMPONENTS (TABLE ''ENECO_DWH/'||SCHEMA_NAME||'/'||TABLE_NAME||''') \
OUTPUT LOG TO ''$LogDir/TAB_'||TABLE_NAME||'.log'';' as EXPORT_CMD
from OWB_OWN.ALL_IV_TABLES@load_p.world T
where T.UPDATED_ON >= to_date('1-jan-2012')
union all
select
'OMBEXPORT TO MDL_FILE ''$ExportDir/SEQ_'||SEQUENCE_NAME||
''' \
COMPONENTS (SEQUENCE ''ENECO_DWH/'||SCHEMA_NAME||'/'||SEQUENCE_NAME||''') \
OUTPUT LOG TO ''$LogDir/SEQ_'||SEQUENCE_NAME||'.log'';' as EXPORT_CMD
from OWB_OWN.ALL_IV_SEQUENCES@load_p.world T
where T.UPDATED_ON >= to_date('1-jan-2012')
union all
select 
'OMBEXPORT TO MDL_FILE ''$ExportDir/MAP_'||MAP_NAME||
''' \
COMPONENTS (MAPPING ''ENECO_DWH/'||INFORMATION_SYSTEM_NAME||'/'||MAP_NAME||''') \
OUTPUT LOG TO ''$LogDir/MAP_'||MAP_NAME||'.log'';' as EXPORT_CMD
from OWB_OWN.ALL_IV_XFORM_MAPS@load_p.world T
where T.UPDATED_ON >= to_date('1-jan-2012')
;

 Till Next Time

OWB: ORA-20005: object statistics are locked (stattype = ALL)

After doing a database import (structures and tables) we had a lot of "ORA-20005: object statistics are locked (stattype = ALL)" errors. This was caused because the export was made when the statistics package was still running on the source machine.

I used this query to generate unlock statements:

SELECT distinct 'exec dbms_stats.unlock_table_stats('''||owner||''', '''||table_name||''');'
FROM all_tab_statistics
where stattype_locked = 'ALL'
and OWNER not in ( 'SYS' , 'SYSTEM', 'WMSYS')

You need analyse any table priveleges.

Till Next Time 

OWB101 10.1 to 11gR2 migration

During the migration constants with the value NULL where migration as 'NULL' (with quotes). I create a script to replace them trough the whole repository with TO_CHAR(NULL).

Found here: Repair NULL

Till Next Time

OWB101 Repository on AL32UTF8

Don't do it!
AL32UTF8 sees VARCHAR(N) as VARCHAR(N bytes) while OWB means VARCHAR(N characters). 
Yes you can play around with NLS_LENGTH_SEMANTICS=CHAR, but you might still run into trouble when using an API.
Use a set like WE8ISO8859P15 instead.


Till Next Time

OWB No access for repository owner

Recently we had a strange problem. The repository assistant wouldn’t let us in:

After some brilliant google-ing from our DBA an old OTN entry from borkur (The Rittman Mead guy?) gave the solution (https://forums.oracle.com/forums/thread.jspa?threadID=1086294). Being lazy we had granted the OWB_USER role to public… Revoking it and giving it to the actual users did the trick.

Till Next Time

OWB Deployment is_local_to_repos

Ran into a strange issue the other day. We had a separated repository db from the target db. In order to get the deployment to work we had to manually update the OWBSYS.wb_rt_stores table. For all deployment targets both “is_deployment_target” and “is_local_to_repos” had to be set to 1.

Couldn’t find how to do this in the Client (Any tips?) so did it directly on the DB

 

Till Next Time