Database Administration (MOSC)

MOSC Banner

how to register a materialized view with only snapid and snaptime

edited Feb 6, 2021 2:53PM in Database Administration (MOSC) 5 commentsAnswered

Hello All,


9.2 and 10.2 database versions:

sql > select sl.*, rs.snapshot_site, rs.owner, rs.name, rs.refresh_method 

from sys.slog$ sl

left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID

where

Master = '****'

ORDER BY snapshot_site DESC

MOWNER  MASTER    SNAPSHOT      SNAPID    SSCN  SNAPTIME           TSCN   USER#  SNAPSHOT_SITE   OWNER      NAME     REFRESH_METHOD

------- ------------- --------------- ---------- ----------  ------------------   ---------- ----------  --------------  ---------------  ---------   --------------

****   ****       null       1331        2021-02-05 19:30:28   null   null     null      null     null       null            

****   ****       null       1356        2021-02-06 02:23:29   null   null   ****.WORLD     ABC      ABC_MV     PRIMARY KEY   


from above query i found that one of the mviews has been refreshed recently with SNAPID 1331 and snaptime 2021-02-05 19:30:28.I would like to find more info on it like mview owner, mview name,SNAPSHOT_SITE and query so i can register it in master site for informational purpose.I have checked all of the mview related data dictionary tables but could not find any info related to above mentioned SNAPID and snaptime.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center