Question
ING Belgium SA NV
BE
Last activity: 5 Jun 2018 2:54 EDT
Pega Marketing Upgrade - error while running Marketing setup utility
We are upgrading pega marketing to 7.3.1 version and its mentioned in db_specif file that privileges must be granted to deploy and data user.
GRANT CREATE TABLE TO Datauser;
GRANT CREATE ANY TABLE TO DEPLOY_USER;
GRANT CREATE ANY MATERIALIZED VIEW TO DEPLOY_USER ;
But , create table and create any materialized view privilege is already been added to the user/schema.
Error :[sql] java.sql.SQLSyntaxErrorException: ORA-01031: Onvoldoende rechten
Kindly advise what is missing here? should privileges be provided with "with admin option"?
ING Belgium SA NV
BE
Hi ,
Please find below error in run_sql_errors_<Dev>-db.dev.be.intranet file,
[sql] Failed to execute: DROP TABLE <Dataschema>.MKT_CAMPAIGN_OFFER_STATS
[sql] java.sql.SQLException: ORA-12083: U moet DROP MATERIALIZED VIEW gebruiken om "<DataSchema>"."MKT_CAMPAIGN_OFFER_STATS" te verwijderen.
[sql] Failed to execute: DROP MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS
[sql] java.sql.SQLSyntaxErrorException: ORA-01031: Onvoldoende rechten
Hi ,
Please find below error in run_sql_errors_<Dev>-db.dev.be.intranet file,
[sql] Failed to execute: DROP TABLE <Dataschema>.MKT_CAMPAIGN_OFFER_STATS
[sql] java.sql.SQLException: ORA-12083: U moet DROP MATERIALIZED VIEW gebruiken om "<DataSchema>"."MKT_CAMPAIGN_OFFER_STATS" te verwijderen.
[sql] Failed to execute: DROP MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS
[sql] java.sql.SQLSyntaxErrorException: ORA-01031: Onvoldoende rechten
[sql] Failed to execute: CREATE MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS (LAST_REFRESHED, ISSUENAME, GROUPNAME, OFFER, PENDING, IMPRESSION, CLICKED, ACCEPTED) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE NEXT SYSDATE + 30/1440 AS WITH all_stats AS (SELECT ifr.pxfactid AS pxfactid ,ifr.pysubjectid AS customerid ,CASE isr.pyoutcome WHEN 'Pending' THEN 1 ELSE 0 END AS pending ,CASE isr.pyoutcome WHEN 'Impression' THEN 1 ELSE 0 END AS impression ,CASE isr.pyoutcome WHEN 'Clicked' THEN 1 ELSE 0 END AS clicked ,CASE isr.pyoutcome WHEN 'Accepted' THEN 1 ELSE 0 END AS accepted ,irc.pycategory AS pycategory ,action.pyissue AS pyissue ,action.pygroup AS pygroup ,action.pyname AS offer FROM CDHIH.pr_data_ih_fact ifr LEFT OUTER JOIN CDHIH.pr_data_ih_dim_context irc ON (ifr.pzcontextid = irc.pzid) INNER JOIN CDHIH.pr_data_ih_dim_outcome isr ON (ifr.pzoutcomeid = isr.pzid) LEFT OUTER JOIN CDHIH.pr_data_ih_dim_action action ON (ifr.pzactionid = action.pzid) WHERE isr.pyoutcome IN ('Pending' ,'Impression' ,'Clicked' ,'Accepted') AND irc.pycategory LIKE 'Data-BatchOutCMPR%' WITH READ ONLY) ,offer_category_customer AS (SELECT MAX (impression) AS impression ,MAX (clicked) AS clicked ,MAX (accepted) AS accepted ,MAX (pending) AS pending ,pyissue ,pygroup ,offer ,pycategory ,customerid FROM all_stats GROUP BY pyissue ,pygroup ,offer ,pycategory ,customerid) SELECT SYSDATE AS last_refreshed ,pyissue AS issuename ,pygroup AS groupname ,offer ,SUM (pending) AS pending ,SUM (impression) AS impression ,SUM (clicked) AS clicked ,SUM (accepted) AS accepted FROM offer_category_customer GROUP BY pyissue, pygroup, offer
[sql] java.sql.SQLException: ORA-12006: Snapshot of zonemap "<Dataschema>"."MKT_CAMPAIGN_OFFER_STATS" bestaat al.
[sql] Failed to execute: COMMENT ON MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS IS 'snapshot table for snapshot MKT_CAMPAIGN_OFFER_STATS'
[sql] java.sql.SQLSyntaxErrorException: ORA-01031: Onvoldoende rechten
But we checked the access, all the required access exist as below,
| Grantee | Provilege | Admin_Option | ommon |
| DataSchema | CREATE MATERIALIZED VIEW | NO | NO |
| DataSchema | CREATE TABLE | YES | NO |
| DataSchema | CREATE SYNONYM | NO | NO |
| DataSchema | ADVISOR | NO | NO |
| DataSchema | SELECT ANY TABLE | NO | NO |
| DataSchema | CREATE ANY MATERIALIZED VIEW | NO | NO |
| Deploy user | CREATE MATERIALIZED VIEW | YES | NO |
| Deploy user | CREATE TABLE | YES | NO |
| Deploy user | CREATE ANY TABLE | YES | NO |
| Deploy user | CREATE ANY MATERIALIZED VIEW | YES | NO |
| ExternakMktUser | CREATE MATERIALIZED VIEW | NO | NO |
| ExternakMktUser | GLOBAL QUERY REWRITE | NO | NO |
| ExternakMktUser | CREATE VIEW | NO | NO |
| ExternakMktUser | CREATE SYNONYM | NO | NO |
| ExternakMktUser | ADVISOR | NO | NO |
| ExternakMktUser | SELECT ANY TABLE | NO | NO |
| ExternakMktUser | CREATE DATABASE LINK | NO | NO |
| All 3 users are having adequate access. Kindly advise where we are missing |
Pegasystems Inc.
US
The DROP TABLE command is failing because the table is already a materialized view. However it also appears that the user that is running the command does not have the privileges needed to drop a materialized view in another schema. Therefore the DROP MATERIALIZED VIEW statement is failing. Since it is not dropped, the subsequent CREATE MATERIALIZED VIEW statement fails as it already exists. The existing MV can be dropped either by the dataschema user, or by the deployment user if granted the DROP ANY MATERIALIZED VIEW privilege.
ING Belgium SA NV
BE
Hi WINKM,
Thanks for your reply. I checked the privileges for the deploy user and it does have the mentioned privileges.
PFA, list of privileges with all details(role privileges, explicit privileges)
could you please advise if any privileges has to be explicitly, not through role. I think, it doesn't matter here.
Please help on this issue.
Pegasystems Inc.
US
From the file, it appears that your deployment user only has the CREATE ANY MATERIALIZED VIEW privilege. It also needs to have the DROP ANY MATERIALIZED VIEW privilege so that it can drop the MV in the PegaDATA schema. This step appears to be missing from the documentation. If this privilege cannot be granted, you could also log in as the PegaDATA user and drop the materialized view prior to running the setup utility.
ING Belgium SA NV
BE
I logged in with PegaDATA user and drop the materialized view and ran the setup utility which was working fine. But, encountered with another issue while running upgradepega activity,
I logged in with PegaDATA user and drop the materialized view and ran the setup utility which was working fine. But, encountered with another issue while running upgradepega activity,
ING Belgium SA NV
BE
I logged in with PegaDATA user and drop the materialized view and ran the setup utility which was working fine. But, encountered with another issue while running upgradepega activity,
I logged in with PegaDATA user and drop the materialized view and ran the setup utility which was working fine. But, encountered with another issue while running upgradepega activity,