Owb - Upgrade an object (such as a table)
When upgrading a table/view
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.
Articles Related
Privileges Steps
Standard Privileges: grant_upgrade_privileges
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
DBA
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.
Others privileges
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.
SYNONYM
-- 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
CONNECT AS
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
Example of Upgrade Script
With existent synonym on the table
-- *** 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'
Rename of a column
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.
Support
RTC-5270 followed with a RPE-02238 in the log
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