To see this working, head to your live site.

Failure of the Oracle Database MView refresh executed as DBMS REFRESH. DBMS VIEW.REFRESH instead of REFRESH

Receiving below errors :

ORA-02245: invalid ROLLBACK SEGMENT name

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020

ORA-06512: at "SYS.DBMS_TRANSACTION", line 74

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2842

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3322

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 44

ORA-06512: at "SYS.DBMS_IREFRESH", line 1177

ORA-06512: at "SYS.DBMS_REFRESH", line 263

1 answer0 replies

1 Comment

Piyush Ranjan

Sep 05, 2022

•

We can provide the name of the rollback segment to utilise while refreshing refresh group materialised views when creating refresh groups using DBMS REFRESH.MAKE. The rollback segment is used by default when NULL is selected. Therefore, it is most probable that the refresh group, which instructs Oracle to utilise a certain rollback segment even if it no longer exists, was formed a long time ago when manual undo management was in use. Anyhow, problem

SELECT  *
  FROM  DBA_REFRESH
  WHERE ROWNER = 'OWNER' -- replce with real owner
    AND RNAME = 'MVNAME' -- replace with real refresh group name
/

Then use dbms_refresh.change to either remove (set to null) rollback segment or set it to valid rollabck segment name.