Owb - Upgrade an object (such as a table)

The upgrade plan is performed by the target user and not by the runtime repository user.

You must then give all according privileges to the target user to be able to perform an upgrade plan which will create and perform an update script.

An OWB admin script “grant_upgrade_privileges.sql” give the standard privileges but you may have to give more as for instance when you create public synonym.

From the admin script directory, you can find a script that grant for you the standard privileges:

OWB_HOME\owb\rtp\sql>sqlplus user/pwd@owbservice

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 17 14:49:08 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @grant_upgrade_privileges TARGET_USER
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.

Otherwise, during the updgrade file, you may find this message:

Description :
Runtime User : OWBREP
Started : 2010-08-17 14:42:45.0
Name 	Action 	Status 	        Log 	
        None 	INFORMATIONAL 	RPE-02257: The following Oracle Roles have not been Granted 
                                to the Target User: 'SELECT_CATALOG_ROLE'           
        None 	INFORMATIONAL 	RPE-02258: The following Oracle Privileges have not been Granted to the Target User:
                                'EXECUTE ANY PROCEDURE' 'EXECUTE ANY TYPE' 'SELECT ANY DICTIONARY'           
        None 	INFORMATIONAL 	RPE-02259: Please run script <OWB-HOME>/owb/rtp/sql/grant_upgrade_privileges.sql          
	None 	Error 	        oracle.sysman.vbo.VboDataSourceUpdateException          

The DBA privilege is a checked by OWB and warns you in standard in the upgrade log file if the target user doesn't have it.

Upgrade log file

Start of main script 
  Executing script in direction: Proceed 
  Executing as user TARGET_USER 
  
WARNING checking privs...User TARGET_USER does not have DBA privs.  
 -- The script will fail if it tries to perform operations for which you lack the appropriate privilege. 

You may have to give to the target user others privileges such as DROP or CREATE SYNONYM because they are not standard and can cause an error.

 -- The script will fail if it tries to perform operations for which you lack the appropriate privilege.
 
 -- *** There are RESOURCE WARNINGS in the script. *** 
 -- *** Fix problems before executing script.      *** 
 -- Review the Impact Report. 
 --  
 -- Script Generation for OdbCMUpgradeAdapter_1282049403721 
 --   Plan was last modified:	17-aug-2010 
 --   Target destination db :	STG 
 --   Generation started at:	17-aug-2010 
 --   Generation finished at:	17-aug-2010 
 -- Some unique constraints are being dropped because the supporting index of these constraints cannot be dropped 
 -- if the constraints exist. 
 -- These constraints will be recreated, if necessary, after the indexes that support them are recreated. 
 -- The constraint was being deleted anyway. 
DROP SYNONYM "PUBLIC"."STG_DIM_VORDERINGEN"
ORA-01031: insufficient privileges

DROP SYNONYM "PUBLIC"."DWH_DIM_VORDERINGEN"
 CONNECT AS: DWH 
REVOKE REFERENCES ON "DWH"."DWH_DIM_VORDERINGEN" FROM "DM"
 CONNECT AS: DWH 
REVOKE SELECT ON "DWH"."DWH_DIM_VORDERINGEN" FROM "DM"
 CONNECT AS: OBI_DWH 
ORA-01031: insufficient privileges

-- *** There are WARNINGS in the script. ***
-- Review the Impact Report.
-- 
-- Script Generation for OdbCMUpgradeAdapter_1282051076955
--   Plan was last modified:	17-aug-2010
--   Target destination db :	STG
--   Generation started at:	17-aug-2010
DROP INDEX "STG"."STG_VORDERINGEN_PK"
DROP SYNONYM "PUBLIC"."STG_VORDERINGEN"
CHANGE USER STG
REVOKE SELECT ON "STG"."STG_VORDERINGEN" FROM "OBI_DWH"
-- Renaming the table for the purpose of recovery.  Cleanup will remove the old table.
CHANGE USER STG
RENAME "STG_VORDERINGEN" TO "STG_DIM_VORDERINGEN$$OCMrecove"
CREATE TABLE "STG"."STG_VORDERINGEN" ("CUSTOMER_TRX_ID" 
    NUMBER(15), "ORG_ID" NUMBER(15), "VORDERING_NUMMER" 
    VARCHAR2(30 byte), "PARKEERVORDERING_ID" NUMBER, 
    "EXT_VORD_REFERENTIE" VARCHAR2(30 byte), "VORDERING_TYPE" 
    VARCHAR2(80 byte), "FAKTTYPE_INTERN_EXTERN" VARCHAR2(50 byte),
    "DATUM_VORDERING" DATE, "DAGTEKENING" DATE, 
    "OORSPR_VERVAL_DATUM" DATE, "BELASTINGJAAR" NUMBER, 
    "OPSCHORTEN" VARCHAR2(3 byte), "REDEN_OPSCHORTING" 
    VARCHAR2(100 byte), "STATUS" VARCHAR2(30 byte), 
    "REDEN_CREDITERING" VARCHAR2(80 byte), "LAST_UPDATE_DATE" 
    DATE, "RUN_ID" NUMBER, "RUN_DATE" DATE, "BELASTINGSOORTEN" 
    VARCHAR2(100), "BETAALSTATUS" VARCHAR2(10))  
    TABLESPACE "DWH_DATA" PCTFREE 10 PCTUSED 0 INITRANS 1 
    MAXTRANS 255 
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) 
    LOGGING 
    MONITORING 
INSERT /*+ APPEND */ 
    INTO "STG"."STG_VORDERINGEN" ( "CUSTOMER_TRX_ID", 
    "ORG_ID", "VORDERING_NUMMER", "PARKEERVORDERING_ID", 
    "EXT_VORD_REFERENTIE", "VORDERING_TYPE", 
    "FAKTTYPE_INTERN_EXTERN", "DATUM_VORDERING", "DAGTEKENING", 
    "OORSPR_VERVAL_DATUM", "BELASTINGJAAR", "OPSCHORTEN", 
    "REDEN_OPSCHORTING", "STATUS", "REDEN_CREDITERING", 
    "LAST_UPDATE_DATE", "RUN_ID", "RUN_DATE" ) 
    SELECT "CUSTOMER_TRX_ID", "ORG_ID", "VORDERING_NUMMER", 
    "PARKEERVORDERING_ID", "EXT_VORD_REFERENTIE", 
    "VORDERING_TYPE", "FAKTTYPE_INTERN_EXTERN", "DATUM_VORDERING",
    "DAGTEKENING", "OORSPR_VERVAL_DATUM", "BELASTINGJAAR", 
    "OPSCHORTEN", "REDEN_OPSCHORTING", "STATUS", 
    "REDEN_CREDITERING", "LAST_UPDATE_DATE", "RUN_ID", "RUN_DATE"
    FROM "STG"."STG_VORDERINGEN$$OCMrecove"
COMMIT
CHANGE USER STG
GRANT SELECT 
    ON "STG"."STG_VORDERINGEN" TO "OBI_DWH"
CREATE  PUBLIC SYNONYM "STG_VORDERINGEN" 
    FOR "STG"."STG_VORDERINGEN"
CREATE UNIQUE INDEX "STG"."STG_VORDERINGEN_PK" 
    ON "STG"."STG_VORDERINGEN"  ("CUSTOMER_TRX_ID", "ORG_ID")
    TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) 
    LOGGING
--   Generation finished at:	17-aug-2010
 
 
-- Following code has been generated by OWB.
-- It will be deployed only after 
-- a SUCCESSFUL 'Commit' action.
COMMENT ON COLUMN STG_VORDERINGEN.BELASTINGSOORTEN IS 'Lijst Aggregate van Segment1 van Vorderingen Regels Items'
COMMENT ON COLUMN STG_VORDERINGEN.BETAALSTATUS IS 'Betaal Status van de vordering'

You can see that the script is executed by the target user STG.

This script allow two modifications to be done:

• a column was rename (from EXTENSION_ID to BEZWAAR_REDENEN_ID)
• a column was added (the LAST_UPDATE_DATE column).

-- Start of main script
-- Executing script in direction: Proceed
-- Executing as user STG
-- ...
CREATE TABLE  "STG"."STG_BEZWAAR_REDENEN$$OCMrecove" ( "OCM$$ROWID" ROWID, "BEZWAAR_REDENEN_ID" NUMBER)
ALTER TABLE "STG"."STG_BEZWAAR_REDENEN$$OCMrecove" NOLOGGING
INSERT /*+ APPEND */ INTO "STG"."STG_BEZWAAR_REDENEN$$OCMrecove" ( "OCM$$ROWID", "BEZWAAR_REDENEN_ID" ) SELECT ROWID,
 "BEZWAAR_REDENEN_ID" FROM "STG"."STG_BEZWAAR_REDENEN"
 COMMIT 
ALTER TABLE "STG"."STG_BEZWAAR_REDENEN$$OCMrecove" LOGGING
ALTER TABLE "STG"."STG_BEZWAAR_REDENEN" ADD ("EXTENSION_ID" NUMBER, "LAST_UPDATE_DATE" DATE) 
ALTER TABLE "STG"."STG_BEZWAAR_REDENEN" DROP ("BEZWAAR_REDENEN_ID") CASCADE CONSTRAINTS
-- Starting cleanup of recovery tables...
DROP TABLE "STG"."STG_BEZWAAR_REDENEN$$OCMrecove"
-- Completed cleanup of recovery tables.

When you get this message:

and this message in the deployment log

RPE-02238: The impact report generated for this upgrade contains error(s). The script will not succeed when executed.
 Please review the report and script and make the appropriate changes before performing upgrade again.

it likes that it's a bug because you get no impact report and that the generated script is good.

To overcome this problem, you can try to:

• get the generated script in the “Script Tab”
• connect as the target user
• and to start it.

Control the generated script before to start it