rem 
rem $Header: catsnap.sql 08-oct-99.15:32:03 rshaikh Exp $
rem 
Rem  Copyright (c) 1991, 1996, 1997, 1998, 1999 by Oracle Corporation 
Rem    NAME
Rem      catsnap.sql
Rem    DESCRIPTION
Rem      Creates data dictionary views for snapshots
Rem    NOTES
Rem      Must be run while connected to SYS or INTERNAL.
Rem    MODIFIED   (MM/DD/YY)
Rem     rshaikh    10/08/99 -  bug 1025636: fix for sqlplus
Rem     slawande   09/23/99 -  Remove fast/complete_refresh_time from *_mviews
Rem     bpanchap   08/11/99 -  Last refresh date should be null when build defe
Rem     igreenbe   07/29/99  - adjust after_fast_refresh for MAVs              
Rem     bpanchap   08/06/99 -  Adding decode statement for last refresh method; Bug 923186
Rem     igreenbe   07/16/99  - add *_mviews views                              
Rem     hasun      11/13/98  - BUG#730154: Fix *_snapshot for MJV and NEVER REF
Rem     wesmith    11/02/98  - Fix view dba_refresh                            
Rem     nshodhan   09/09/98  - Modify views to exclude RepAPI snapshots
Rem     hasun      05/21/98  - Support UNKNOWN status for snapshots            
Rem     hasun      05/21/98  - Change NONE to NEVER for *_snapshots.type       
Rem     hasun      05/14/98  - Add prebuilt to *_snapshots
Rem     hasun      04/28/98  - Add refresh_mode to *_snapshots               
Rem     hasun      04/10/98  - Add status to *_snapshots                       
Rem     hasun      04/08/98  - Support REFRESH FORCE option                    
Rem     jgalagal   01/20/98  - Add aggregate snapshot in dba_snapshots         
Rem     masubram   03/27/97 -  fix comment on reg_snap$.rep_type
Rem     masubram   03/11/97 -  Modify the snapshot registration views
Rem     hasun      03/10/97 -  Expose parallel prop parameters
Rem     hasun      10/21/96 -  Add master_rollback_seg column to *_snapshots
Rem     masubram   10/11/96 -  Add dba_snapshot_log_filter_cols view
Rem     masubram   10/04/96 -  Fixed dba_snapshots to get snaptime from snap$
Rem     masubram   09/25/96 -  Fixed all_registered_snapshots
Rem     masubram   09/24/96 -  Add all_snapshot_logs,snap_ref_times, mod dba_sn
Rem     hasun      06/12/96 -  Add column comments
Rem     tpystyne   06/01/96 -  change rgchild$.type to rgchild$.type#
Rem     mmonajje   05/21/96 -  Replace interval col name with interval#
Rem     asurpur    05/15/96 -  Dictionary Protection: granting privileges on vi
Rem     asurpur    04/08/96 -  Dictionary Protection Implementation
Rem     hasun      05/06/96 -  replmerge
Rem     adowning   04/22/96 -  merge obj to main
Rem     hasun      04/18/96 -  Modify views for additional snapshot states
Rem     hasun      04/12/96 -  Add new COMPLEX snapshot type
Rem     hasun      04/10/96 -  Modify views for new snapshot id
Rem     hasun      03/26/96 -  Make DBA_SNAPSHOTS more robust
Rem     hasun      03/15/96 -  Add STATUS column to dba_snapshots
Rem     hasun      02/27/96 -  Merge from BIG
Rem     hasun      02/13/96 -  Add support for filter columns
Rem     ashgupta   02/08/96 -  Merging  snapshot registration code (proj 2045)
Rem     hasun      12/05/95 -  Modify []_snapshots and  []_snapshot_logs
Rem                            for primary key snapshots
Rem     jcchou     11/15/95 -  bug#302137 - refgroup in sys.snap$
Rem     jcchou     11/09/95 -  #310646 - rbs missing from views
Rem     jcchou     09/26/95 -  bug#310646 - rgroup$ views
Rem     ashgupta   02/08/96 -  Merging  snapshot registration code (proj 2045)
Rem     adowning   12/23/94 -  merge changes from branch 1.7.720.1
Rem     adowning   12/21/94 -  merge changes from branch 1.4.710.4
Rem     hasun      12/05/95 -  Modify []_snapshots and  []_snapshot_logs
Rem                            for primary key snapshots
Rem     adowning   12/05/94 -  fix all_snapshots
Rem     adowning   11/11/94 -  merge changes from branch 1.4.710.3
Rem     adowning   10/14/94 -  fix typo USER to DBA
Rem     wmaimone   05/26/94 -  #186155 add public synoyms for dba_
Rem     rjenkins   01/19/94 -  merge changes from branch 1.4.710.2
Rem     jbellemo   12/17/93 -  merge changes from branch 1.4.710.1
Rem     rjenkins   12/17/93 -  creating job queue
Rem     jbellemo   11/09/93 -  #170173: change uid to userenv schemaid
Rem     rjenkins   07/06/93 -  adding updatable snapshots
Rem     wbridge    12/03/92 -  fix error handling for refresh all 
Rem     tpystyne   11/08/92 -  use create or replace view 
Rem     glumpkin   10/25/92 -  Renamed from SNAPVEW.SQL 
Rem     mmoore     06/02/92 - #(96526) remove v$enabledroles 
Rem     rjenkins   01/14/92 -  copying changes from catalog.sql 
Rem     rjenkins   05/20/91 -         Creation 

remark
remark    FAMILY "SNAPSHOTS"
remark    Table replication definitions.
remark

create or replace view DBA_SNAPSHOTS
(OWNER, NAME, TABLE_NAME, MASTER_VIEW, MASTER_OWNER, MASTER, MASTER_LINK,
 CAN_USE_LOG, UPDATABLE, REFRESH_METHOD, LAST_REFRESH, ERROR,
 FR_OPERATIONS, CR_OPERATIONS, TYPE, NEXT, START_WITH, REFRESH_GROUP, 
 UPDATE_TRIG, UPDATE_LOG, QUERY, MASTER_ROLLBACK_SEG, STATUS, REFRESH_MODE,
 PREBUILT)
as
select s.sowner, s.vname, tname, mview, t.mowner, t.master, mlink,
       decode(bitand(s.flag,1),  0, 'NO', 'YES'), 
       decode(bitand(s.flag,2),  0, 'NO', 'YES'), 
       decode(bitand(s.flag,16),             16, 'ROWID', 
	      (decode(bitand(s.flag,32),     32, 'PRIMARY KEY', 
	      (decode(bitand(s.flag,8192), 8192, 'JOIN VIEW', 
	      (decode(bitand(s.flag,4096), 4096, 'AGGREGATE', 
              (decode(bitand(s.flag,256),   256, 'COMPLEX', 
                                                 'UNKNOWN'))))))))),
       s.snaptime, s.error#, 
       decode(bitand(s.status,1), 0, 'REGENERATE', 'VALID'),
       decode(bitand(s.status,2), 0, 'REGENERATE', 'VALID'),
       decode(s.auto_fast, 
              'C',  'COMPLETE', 
              'F',  'FAST', 
              '?',  'FORCE',
              'N',  'NEVER',
              NULL, 'FORCE', 'ERROR'), 
       s.auto_fun, s.auto_date, r.refgroup, s.ustrg, s.uslog, 
       s.query_txt, s.mas_roll_seg,
       decode(bitand(s.status,4),         4, 'INVALID',
	      (decode(bitand(s.status,8), 8, 'UNKNOWN',
                                             'VALID'))),
       decode(NVL(s.auto_fun, 'null'), 
              'null', decode(s.auto_fast,                  'N', 'NEVER', 
                             (decode(bitand(s.flag, 32768),  0, 'DEMAND',
                                                                'COMMIT'))),
	      'PERIODIC'),
       decode(bitand(s.flag,131072), 0, 'NO', 'YES')
from sys.snap$ s, sys.rgchild$ r, sys.snap_reftime$ t
where t.sowner = s.sowner
and t.vname = s.vname
and t.instsite = 0
and s.instsite = 0
and t.tablenum = 0
and t.sowner  = r.owner (+) 
and t.vname = r.name (+)
and nvl(r.instsite,0) = 0
and r.type# (+) = 'SNAPSHOT'
/
drop public synonym DBA_SNAPSHOTS
/
create public synonym DBA_SNAPSHOTS for DBA_SNAPSHOTS
/
comment on table DBA_SNAPSHOTS is
'All snapshots in the database'
/
comment on column DBA_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column DBA_SNAPSHOTS.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column DBA_SNAPSHOTS.TABLE_NAME is
'Table the snapshot is stored in -- has an extra column for the master rowid'
/
comment on column DBA_SNAPSHOTS.MASTER_VIEW is
'View of the master table, owned by the snapshot owner, used for refreshes'
/
comment on column DBA_SNAPSHOTS.MASTER_OWNER is
'Owner of the master table'
/
comment on column DBA_SNAPSHOTS.MASTER is
'Name of the master table that this snapshot is a copy of'
/
comment on column DBA_SNAPSHOTS.MASTER_LINK is
'Database link name to the master site'
/
comment on column DBA_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and will never use a log'
/
comment on column DBA_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only.  Look up REPLICATION'
/
comment on column DBA_SNAPSHOTS.REFRESH_METHOD is
'The values used to drive a fast refresh of the snapshot'
/
comment on column DBA_SNAPSHOTS.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
comment on column DBA_SNAPSHOTS.ERROR is
'The number of failed automatic refreshes since last successful refresh'
/
comment on column DBA_SNAPSHOTS.FR_OPERATIONS is
'If REGENERATE, then the fast refresh operations have not been generated'
/
comment on column DBA_SNAPSHOTS.CR_OPERATIONS is
'If REGENERATE, then the complete refresh operations have not been generated'
/
comment on column DBA_SNAPSHOTS.TYPE is
'The type of refresh (complete,fast,force) for all automatic refreshes'
/
comment on column DBA_SNAPSHOTS.NEXT is
'The date function used to compute next refresh dates'
/
comment on column DBA_SNAPSHOTS.START_WITH is
'The date function used to compute next refresh dates'
/
comment on column DBA_SNAPSHOTS.REFRESH_GROUP is
'All snapshots in a given refresh group get refreshed in the same transaction'
/
comment on column DBA_SNAPSHOTS.UPDATE_TRIG is
'The name of the trigger which fills the UPDATE_LOG'
/
comment on column DBA_SNAPSHOTS.UPDATE_LOG is
'The table which logs changes made to an updatable snapshots'
/
comment on column DBA_SNAPSHOTS.QUERY is
'The original query that this snapshot is an instantiation of'
/
comment on column DBA_SNAPSHOTS.MASTER_ROLLBACK_SEG is
'Rollback segment to use at the master site'
/
comment on column DBA_SNAPSHOTS.STATUS is
'The status of the contents of the snapshot'
/
comment on column DBA_SNAPSHOTS.REFRESH_MODE is
'This indicates how and when the snapshot will be refreshed'
/
comment on column DBA_SNAPSHOTS.PREBUILT is
'If YES, this snapshot uses a prebuilt table as the base table'
/
grant select on DBA_SNAPSHOTS to select_catalog_role
/
/*
 * define the ability to "access a snapshot" as the ability to
 * "access the snapshot's table_name (e.g., snap$_foo).
 */
create or replace view ALL_SNAPSHOTS
as select s.* from dba_snapshots s, sys.obj$ o, sys.user$ u
where o.owner#     = u.user#
  and s.table_name = o.name
  and u.name       = s.owner
  and o.type#      = 2                     /* table */
  and ( u.user# in (userenv('SCHEMAID'), 1)
        or
        o.obj# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      )
                  )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
comment on table ALL_SNAPSHOTS is
'Snapshots the user can access'
/
comment on column ALL_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column ALL_SNAPSHOTS.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column ALL_SNAPSHOTS.TABLE_NAME is
'Table the snapshot is stored in -- has an extra column for the master rowid'
/
comment on column ALL_SNAPSHOTS.MASTER_VIEW is
'View of the master table, owned by the snapshot owner, used for refreshes'
/
comment on column ALL_SNAPSHOTS.MASTER_OWNER is
'Owner of the master table'
/
comment on column ALL_SNAPSHOTS.MASTER is
'Name of the master table that this snapshot is a copy of'
/
comment on column ALL_SNAPSHOTS.MASTER_LINK is
'Database link name to the master site'
/
comment on column ALL_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and will never use a log'
/
comment on column ALL_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only.  Look up REPLICATION'
/
comment on column ALL_SNAPSHOTS.REFRESH_METHOD is
'The values used to drive a fast refresh of the snapshot'
/
comment on column ALL_SNAPSHOTS.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
comment on column ALL_SNAPSHOTS.ERROR is
'The error returned last time an automatic refresh was attempted'
/
comment on column ALL_SNAPSHOTS.TYPE is
'The type of refresh (complete,fast,force) for all automatic refreshes'
/
comment on column ALL_SNAPSHOTS.NEXT is
'The date function used to compute next refresh dates'
/
comment on column ALL_SNAPSHOTS.START_WITH is
'The date function used to compute next refresh dates'
/
comment on column ALL_SNAPSHOTS.REFRESH_GROUP is
'All snapshots in a given refresh group get refreshed in the same transaction'
/
comment on column ALL_SNAPSHOTS.UPDATE_TRIG is
'The name of the trigger which fills the UPDATE_LOG'
/
comment on column ALL_SNAPSHOTS.UPDATE_LOG is
'The table which logs changes made to an updatable snapshots'
/
comment on column ALL_SNAPSHOTS.QUERY is
'The original query that this snapshot is an instantiation of'
/
comment on column ALL_SNAPSHOTS.MASTER_ROLLBACK_SEG is
'Rollback segment to use at the master site'
/
comment on column ALL_SNAPSHOTS.STATUS is
'The status of the contents of the snapshot'
/
comment on column ALL_SNAPSHOTS.REFRESH_MODE is
'This indicates how and when the snapshot will be refreshed'
/
comment on column ALL_SNAPSHOTS.PREBUILT is
'If YES, this snapshot uses a prebuilt table as the base table'
/
drop public synonym ALL_SNAPSHOTS
/
create public synonym ALL_SNAPSHOTS for ALL_SNAPSHOTS
/
grant select on ALL_SNAPSHOTS to public with grant option
/
create or replace view USER_SNAPSHOTS
as select s.* from dba_snapshots s, sys.user$ u
where u.user# = userenv('SCHEMAID')
  and s.owner = u.name
/
comment on table USER_SNAPSHOTS is
'Snapshots the user can look at'
/
comment on column USER_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column USER_SNAPSHOTS.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column USER_SNAPSHOTS.TABLE_NAME is
'Table the snapshot is stored in -- has an extra column for the master rowid'
/
comment on column USER_SNAPSHOTS.MASTER_VIEW is
'View of the master table, owned by the snapshot owner, used for refreshes'
/
comment on column USER_SNAPSHOTS.MASTER_OWNER is
'Owner of the master table'
/
comment on column USER_SNAPSHOTS.MASTER is
'Name of the master table that this snapshot is a copy of'
/
comment on column USER_SNAPSHOTS.MASTER_LINK is
'Database link name to the master site'
/
comment on column USER_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and will never use a log'
/
comment on column USER_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only.  Look up REPLICATION'
/
comment on column USER_SNAPSHOTS.REFRESH_METHOD is
'The values used to drive a fast refresh of the snapshot'
/
comment on column USER_SNAPSHOTS.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
comment on column USER_SNAPSHOTS.ERROR is
'The error returned last time an automatic refresh was attempted'
/
comment on column USER_SNAPSHOTS.TYPE is
'The type of refresh (complete,fast,force) for all automatic refreshes'
/
comment on column USER_SNAPSHOTS.NEXT is
'The date function used to compute next refresh dates'
/
comment on column USER_SNAPSHOTS.START_WITH is
'The date function used to compute next refresh dates'
/
comment on column USER_SNAPSHOTS.REFRESH_GROUP is
'All snapshots in a given refresh group get refreshed in the same transaction'
/
comment on column USER_SNAPSHOTS.UPDATE_TRIG is
'The name of the trigger which fills the UPDATE_LOG'
/
comment on column USER_SNAPSHOTS.UPDATE_LOG is
'The table which logs changes made to an updatable snapshots'
/
comment on column USER_SNAPSHOTS.QUERY is
'The original query that this snapshot is an instantiation of'
/
comment on column USER_SNAPSHOTS.MASTER_ROLLBACK_SEG is
'Rollback segment to use at the master site'
/
comment on column USER_SNAPSHOTS.STATUS is
'The status of the contents of the snapshot'
/
comment on column USER_SNAPSHOTS.REFRESH_MODE is
'This indicates how and when the snapshot will be refreshed'
/
comment on column USER_SNAPSHOTS.PREBUILT is
'If YES, this snapshot uses a prebuilt table as the base table'
/
drop public synonym USER_SNAPSHOTS
/
create public synonym USER_SNAPSHOTS for USER_SNAPSHOTS
/
grant select on USER_SNAPSHOTS to public with grant option
/

create or replace view DBA_SNAPSHOT_REFRESH_TIMES
(OWNER, NAME, MASTER_OWNER, MASTER, LAST_REFRESH)
as select sowner, vname, mowner, master, snaptime
from sys.snap_reftime$ t
where t.instsite = 0 
/
drop public synonym DBA_SNAPSHOT_REFRESH_TIMES
/
create public synonym DBA_SNAPSHOT_REFRESH_TIMES for DBA_SNAPSHOT_REFRESH_TIMES
/
comment on table DBA_SNAPSHOT_REFRESH_TIMES is
'All fast refreshable snapshots and their last refresh times for each master table'
/
comment on column DBA_SNAPSHOT_REFRESH_TIMES.OWNER is
'Owner of the snapshot'
/
comment on column DBA_SNAPSHOT_REFRESH_TIMES.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column DBA_SNAPSHOT_REFRESH_TIMES.MASTER_OWNER is
'Owner of the master table'
/
comment on column DBA_SNAPSHOT_REFRESH_TIMES.MASTER is
'Name of the master table'
/
comment on column DBA_SNAPSHOT_REFRESH_TIMES.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
grant select on DBA_SNAPSHOT_REFRESH_TIMES to select_catalog_role
/

create or replace view ALL_SNAPSHOT_REFRESH_TIMES
as select s.* from dba_snapshot_refresh_times s, all_snapshots a
where s.owner = a.owner
and   s.name  = a.name
/
comment on table ALL_SNAPSHOT_REFRESH_TIMES is
'Snapshots and their last refresh times  for each master table that the user can look at'
/
comment on column  ALL_SNAPSHOT_REFRESH_TIMES.OWNER is
'Owner of the snapshot'
/
comment on column ALL_SNAPSHOT_REFRESH_TIMES.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column ALL_SNAPSHOT_REFRESH_TIMES.MASTER_OWNER is
'Owner of the master table'
/
comment on column ALL_SNAPSHOT_REFRESH_TIMES.MASTER is
'Name of the master table'
/
comment on column ALL_SNAPSHOT_REFRESH_TIMES.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
drop public synonym ALL_SNAPSHOT_REFRESH_TIMES
/
create public synonym ALL_SNAPSHOT_REFRESH_TIMES for ALL_SNAPSHOT_REFRESH_TIMES
/
grant select on ALL_SNAPSHOT_REFRESH_TIMES to public with grant option
/
create or replace view USER_SNAPSHOT_REFRESH_TIMES
as select s.* from dba_snapshot_refresh_times s, sys.user$ u
where u.user# = userenv('SCHEMAID')
  and s.owner = u.name
/
comment on table USER_SNAPSHOT_REFRESH_TIMES is
'Snapshots and their last refresh times for each master table that the user can look at'
/
comment on column  USER_SNAPSHOT_REFRESH_TIMES.OWNER is
'Owner of the snapshot'
/
comment on column USER_SNAPSHOT_REFRESH_TIMES.NAME is
'The view used by users and applications for viewing the snapshot'
/
comment on column  USER_SNAPSHOT_REFRESH_TIMES.MASTER_OWNER is
'Owner of the master table'
/
comment on column  USER_SNAPSHOT_REFRESH_TIMES.MASTER is
'Name of the master table'
/
comment on column USER_SNAPSHOT_REFRESH_TIMES.LAST_REFRESH is
'SYSDATE from the master site at the time of the last refresh'
/
drop public synonym USER_SNAPSHOT_REFRESH_TIMES
/
create public synonym USER_SNAPSHOT_REFRESH_TIMES for USER_SNAPSHOT_REFRESH_TIMES
/
grant select on USER_SNAPSHOT_REFRESH_TIMES to public with grant option
/

create or replace view DBA_SNAPSHOT_LOGS
( LOG_OWNER, MASTER, LOG_TABLE, LOG_TRIGGER, ROWIDS, PRIMARY_KEY,
  FILTER_COLUMNS, CURRENT_SNAPSHOTS, SNAPSHOT_ID)
as
select m.mowner, m.master, m.log, m.trig, 
       decode(bitand(m.flag,1), 0, 'NO', 'YES'),
       decode(bitand(m.flag,2), 0, 'NO', 'YES'),
       decode(bitand(m.flag,4), 0, 'NO', 'YES'),
       s.snaptime, s.snapid
from sys.mlog$ m, sys.slog$ s
where s.mowner (+) = m.mowner
  and s.master (+) = m.master
/
drop public synonym DBA_SNAPSHOT_LOGS
/
create public synonym DBA_SNAPSHOT_LOGS for DBA_SNAPSHOT_LOGS
/
comment on table DBA_SNAPSHOT_LOGS is
'All snapshot logs in the database'
/
comment on column DBA_SNAPSHOT_LOGS.LOG_OWNER is
'Owner of the snapshot log'
/
comment on column DBA_SNAPSHOT_LOGS.MASTER is
'Name of the master table which  changes are logged'
/
comment on column DBA_SNAPSHOT_LOGS.LOG_TABLE is
'Log table; with rowids and timestamps of rows which changed in the master'
/
comment on column DBA_SNAPSHOT_LOGS.LOG_TRIGGER is
'An after-row trigger on the master which inserts rows into the log'
/
comment on column DBA_SNAPSHOT_LOGS.ROWIDS is
'If YES, the snapshot log records rowid information'
/
comment on column DBA_SNAPSHOT_LOGS.PRIMARY_KEY is
'If YES, the snapshot log records primary key information'
/
comment on column DBA_SNAPSHOT_LOGS.FILTER_COLUMNS is
'If YES, the snapshot log records filter column information'
/
comment on column DBA_SNAPSHOT_LOGS.CURRENT_SNAPSHOTS is
'One date per snapshot -- the date the snapshot of the master last refreshed'
/
comment on column DBA_SNAPSHOT_LOGS.SNAPSHOT_ID is
'Unique identifier of the snapshot'
/
grant select on DBA_SNAPSHOT_LOGS to select_catalog_role
/
create or replace view ALL_SNAPSHOT_LOGS
as select s.* from dba_snapshot_logs s, sys.obj$ o, sys.user$ u
where o.owner#     = u.user#
  and s.log_table = o.name
  and u.name       = s.log_owner
  and o.type#      = 2                     /* table */
  and ( u.user# in (userenv('SCHEMAID'), 1)
        or
        o.obj# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      )
                  )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
comment on table ALL_SNAPSHOT_LOGS is
'All snapshot logs in the database that the user can see'
/
comment on column ALL_SNAPSHOT_LOGS.LOG_OWNER is
'Owner of the snapshot log'
/
comment on column ALL_SNAPSHOT_LOGS.MASTER is
'Name of the master table which chnages are logged'
/
comment on column ALL_SNAPSHOT_LOGS.LOG_TABLE is
'Log table; with  rowids and timestamps of rows which changed in the master'
/
comment on column ALL_SNAPSHOT_LOGS.LOG_TRIGGER is
'An after-row trigger on the master which inserts rows into the log'
/
comment on column ALL_SNAPSHOT_LOGS.ROWIDS is
'If YES, the snapshot log records rowid information'
/
comment on column ALL_SNAPSHOT_LOGS.PRIMARY_KEY is
'If YES, the snapshot log records primary key information'
/
comment on column ALL_SNAPSHOT_LOGS.FILTER_COLUMNS is
'If YES, the snapshot log records filter column information'
/
comment on column ALL_SNAPSHOT_LOGS.CURRENT_SNAPSHOTS is
'One date per snapshot -- the date the snapshot of the master last refreshed'
/
comment on column ALL_SNAPSHOT_LOGS.SNAPSHOT_ID is
'Unique identifier of the snapshot'
/
drop public synonym ALL_SNAPSHOT_LOGS
/
create public synonym ALL_SNAPSHOT_LOGS for ALL_SNAPSHOT_LOGS
/
grant select on ALL_SNAPSHOT_LOGS to public with grant option
/
create or replace view USER_SNAPSHOT_LOGS
( LOG_OWNER, MASTER, LOG_TABLE, LOG_TRIGGER, ROWIDS, PRIMARY_KEY,
  FILTER_COLUMNS, CURRENT_SNAPSHOTS, SNAPSHOT_ID)
as
select log_owner, master, log_table, log_trigger, rowids, primary_key,
       filter_columns, current_snapshots, snapshot_id
from dba_snapshot_logs s, sys.user$ u
where s.log_owner = u.name
  and u.user# = userenv('SCHEMAID')
/
comment on table USER_SNAPSHOT_LOGS is
'All snapshot logs owned by the user'
/
comment on column USER_SNAPSHOT_LOGS.LOG_OWNER is
'Owner of the snapshot log'
/
comment on column USER_SNAPSHOT_LOGS.MASTER is
'Name of the master table which changes are logged'
/
comment on column USER_SNAPSHOT_LOGS.LOG_TABLE is
'Log table; with rowids and timestamps of rows which changed in the
master'
/
comment on column USER_SNAPSHOT_LOGS.LOG_TRIGGER is
'Trigger on master table; fills the snapshot log'
/
comment on column USER_SNAPSHOT_LOGS.ROWIDS is
'If YES, the snapshot log records rowid information'
/
comment on column USER_SNAPSHOT_LOGS.PRIMARY_KEY is
'If YES, the snapshot log records primary_key information'
/
comment on column USER_SNAPSHOT_LOGS.FILTER_COLUMNS is
'If YES, the snapshot log records filter column information'
/
comment on column USER_SNAPSHOT_LOGS.CURRENT_SNAPSHOTS is
'Dates that all known simple snapshots last refreshed'
/
drop public synonym USER_SNAPSHOT_LOGS
/
create public synonym USER_SNAPSHOT_LOGS for USER_SNAPSHOT_LOGS
/
grant select on USER_SNAPSHOT_LOGS to public with grant option
/


create or replace view DBA_RCHILD 
as select REFGROUP, OWNER, NAME, TYPE# from rgchild$ r
   where r.instsite = 0 
/
comment on table DBA_RCHILD is
'All the children in any refresh group.  This view is not a join.'
/
drop public synonym DBA_RCHILD
/
create public synonym DBA_RCHILD for DBA_RCHILD
/
grant select on DBA_RCHILD to select_catalog_role
/

create or replace view DBA_RGROUP
as select REFGROUP, OWNER, NAME,
          decode(bitand(flag,1),1,'Y',0,'N','?') IMPLICIT_DESTROY,
          decode(bitand(flag,2),2,'Y',0,'N','?') PUSH_DEFERRED_RPC,
          decode(bitand(flag,4),4,'Y',0,'N','?') REFRESH_AFTER_ERRORS,
          ROLLBACK_SEG,
          JOB,
          purge_opt#   PURGE_OPTION,
          parallelism# PARALLELISM,
          heap_size#   HEAP_SIZE
  from rgroup$ r
   where r.instsite = 0 
/
comment on table DBA_RGROUP is
'All refresh groups.  This view is not a join.'
/
comment on column DBA_RGROUP.REFGROUP is
'Internal identifier of refresh group'
/
comment on column DBA_RGROUP.OWNER is
'Owner of the refresh group'
/
comment on column DBA_RGROUP.NAME is
'Name of the refresh group'
/
comment on column DBA_RGROUP.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column DBA_RGROUP.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column DBA_RGROUP.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column DBA_RGROUP.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column DBA_RGROUP.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column DBA_RGROUP.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column DBA_RGROUP.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column DBA_RGROUP.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym DBA_RGROUP
/
create public synonym DBA_RGROUP for DBA_RGROUP
/
grant select on DBA_RGROUP to select_catalog_role
/

create or replace view DBA_REFRESH
as select r.owner ROWNER, r.name RNAME, r.REFGROUP,
          decode(bitand(r.flag,1),1,'Y',0,'N','?') IMPLICIT_DESTROY,
          decode(bitand(r.flag,2),2,'Y',0,'N','?') PUSH_DEFERRED_RPC,
          decode(bitand(r.flag,4),4,'Y',0,'N','?') REFRESH_AFTER_ERRORS,
          r.rollback_seg ROLLBACK_SEG,
          j.JOB, j.NEXT_DATE, j.INTERVAL# interval, 
          decode(bitand(j.flag,1),1,'Y',0,'N','?') BROKEN,
          r.purge_opt#   PURGE_OPTION,
          r.parallelism# PARALLELISM,
          r.heap_size#   HEAP_SIZE
  from rgroup$ r, job$ j
  where r.instsite = 0
  and   r.job = j.job(+)
/
comment on table DBA_REFRESH is
'All the refresh groups'
/
comment on column DBA_REFRESH.ROWNER is
'Name of the owner of the refresh group'
/
comment on column DBA_REFRESH.RNAME is
'Name of the refresh group'
/
comment on column DBA_REFRESH.REFGROUP is
'Internal identifier of refresh group'
/
comment on column DBA_REFRESH.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column DBA_REFRESH.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column DBA_REFRESH.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column DBA_REFRESH.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column DBA_REFRESH.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column DBA_REFRESH.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column DBA_REFRESH.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column DBA_REFRESH.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column DBA_REFRESH.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column DBA_REFRESH.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column DBA_REFRESH.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym DBA_REFRESH
/
create public synonym DBA_REFRESH for DBA_REFRESH
/
grant select on DBA_REFRESH to select_catalog_role
/

create or replace view ALL_REFRESH
as select * from dba_refresh where user = rowner
  or userenv('SCHEMAID') = 0 or exists
  (select kzsrorol
     from x$kzsro x, sys.system_privilege_map m, sys.sysauth$ s
     where x.kzsrorol = s.grantee# and
           s.privilege# = m.privilege and
           m.name = 'ALTER ANY SNAPSHOT')
/
comment on table ALL_REFRESH is
'All the refresh groups that the user can touch'
/
comment on column ALL_REFRESH.ROWNER is
'Name of the owner of the refresh group'
/
comment on column ALL_REFRESH.RNAME is
'Name of the refresh group'
/
comment on column ALL_REFRESH.REFGROUP is
'Internal identifier of refresh group'
/
comment on column ALL_REFRESH.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column ALL_REFRESH.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column ALL_REFRESH.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column ALL_REFRESH.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column ALL_REFRESH.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column ALL_REFRESH.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column ALL_REFRESH.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column ALL_REFRESH.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column ALL_REFRESH.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column ALL_REFRESH.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column ALL_REFRESH.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym ALL_REFRESH
/
create public synonym ALL_REFRESH for ALL_REFRESH
/
grant select on ALL_REFRESH to public with grant option
/


create or replace view USER_REFRESH 
as select * from dba_refresh where rowner = user
/
comment on table USER_REFRESH is
'All the refresh groups'
/
comment on column USER_REFRESH.ROWNER is
'Name of the owner of the refresh group'
/
comment on column USER_REFRESH.RNAME is
'Name of the refresh group'
/
comment on column USER_REFRESH.REFGROUP is
'Internal identifier of refresh group'
/
comment on column USER_REFRESH.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column USER_REFRESH.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column USER_REFRESH.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column USER_REFRESH.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column USER_REFRESH.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column USER_REFRESH.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column USER_REFRESH.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column USER_REFRESH.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column USER_REFRESH.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column USER_REFRESH.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column USER_REFRESH.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym USER_REFRESH
/
create public synonym USER_REFRESH for USER_REFRESH
/
grant select on USER_REFRESH to public with grant option
/


create or replace view DBA_REFRESH_CHILDREN
as select rc.owner OWNER, rc.name NAME, rc.TYPE# TYPE, 
          r.owner ROWNER, r.name RNAME, r.REFGROUP,
          decode(bitand(r.flag,1),1,'Y',0,'N','?') IMPLICIT_DESTROY,
          decode(bitand(r.flag,2),2,'Y',0,'N','?') PUSH_DEFERRED_RPC,
          decode(bitand(r.flag,4),4,'Y',0,'N','?') REFRESH_AFTER_ERRORS,
          r.rollback_seg ROLLBACK_SEG,
          j.job, j.NEXT_DATE, j.INTERVAL# interval,
          decode(bitand(j.flag,1),1,'Y',0,'N','?') BROKEN,
          r.purge_opt#   PURGE_OPTION,
          r.parallelism# PARALLELISM,
          r.heap_size#   HEAP_SIZE          
  from rgroup$ r, rgchild$ rc, job$ j
  where r.refgroup = rc.refgroup
    and r.instsite = 0
    and rc.instsite = 0
    and r.job = j.job (+)
/
comment on table DBA_REFRESH_CHILDREN is
'All the objects in refresh groups'
/
comment on column DBA_REFRESH_CHILDREN.OWNER is
'Owner of the object in the refresh group'
/
comment on column DBA_REFRESH_CHILDREN.NAME is
'Name of the object in the refresh group'
/
comment on column DBA_REFRESH_CHILDREN.TYPE is
'Type of the object in the refresh group'
/
comment on column DBA_REFRESH_CHILDREN.ROWNER is
'Name of the owner of the refresh group'
/
comment on column DBA_REFRESH_CHILDREN.RNAME is
'Name of the refresh group'
/
comment on column DBA_REFRESH_CHILDREN.REFGROUP is
'Internal identifier of refresh group'
/
comment on column DBA_REFRESH_CHILDREN.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column DBA_REFRESH_CHILDREN.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column DBA_REFRESH_CHILDREN.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column DBA_REFRESH_CHILDREN.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column DBA_REFRESH_CHILDREN.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column DBA_REFRESH_CHILDREN.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column DBA_REFRESH_CHILDREN.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column DBA_REFRESH_CHILDREN.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column DBA_REFRESH_CHILDREN.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column DBA_REFRESH_CHILDREN.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column DBA_REFRESH_CHILDREN.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym DBA_REFRESH_CHILDREN
/
create public synonym DBA_REFRESH_CHILDREN for DBA_REFRESH_CHILDREN
/
grant select on DBA_REFRESH_CHILDREN to select_catalog_role
/

create or replace view ALL_REFRESH_CHILDREN
as select * from dba_refresh_children where user = rowner 
  or userenv('SCHEMAID') = 0 or exists
  (select kzsrorol
     from x$kzsro x, sys.system_privilege_map m, sys.sysauth$ s    
     where x.kzsrorol = s.grantee# and
           s.privilege# = m.privilege and
           m.name = 'ALTER ANY SNAPSHOT')
/
comment on table ALL_REFRESH_CHILDREN is
'All the objects in refresh groups, where the user can touch the group'
/
comment on column ALL_REFRESH_CHILDREN.OWNER is
'Owner of the object in the refresh group'
/
comment on column ALL_REFRESH_CHILDREN.NAME is
'Name of the object in the refresh group'
/
comment on column ALL_REFRESH_CHILDREN.TYPE is
'Type of the object in the refresh group'
/
comment on column ALL_REFRESH_CHILDREN.ROWNER is
'Name of the owner of the refresh group'
/
comment on column ALL_REFRESH_CHILDREN.RNAME is
'Name of the refresh group'
/
comment on column ALL_REFRESH_CHILDREN.REFGROUP is
'Internal identifier of refresh group'
/
comment on column ALL_REFRESH_CHILDREN.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column ALL_REFRESH_CHILDREN.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column ALL_REFRESH_CHILDREN.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column ALL_REFRESH_CHILDREN.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column ALL_REFRESH_CHILDREN.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column ALL_REFRESH_CHILDREN.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column ALL_REFRESH_CHILDREN.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column ALL_REFRESH_CHILDREN.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column ALL_REFRESH_CHILDREN.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column ALL_REFRESH_CHILDREN.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column ALL_REFRESH_CHILDREN.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym ALL_REFRESH_CHILDREN
/
create public synonym ALL_REFRESH_CHILDREN for ALL_REFRESH_CHILDREN
/
grant select on ALL_REFRESH_CHILDREN to public with grant option
/


create or replace view USER_REFRESH_CHILDREN
as select * from dba_refresh_children where rowner = user
/
comment on table USER_REFRESH_CHILDREN is
'All the objects in refresh groups, where the user owns the refresh group'
/
comment on column USER_REFRESH_CHILDREN.OWNER is
'Owner of the object in the refresh group'
/
comment on column USER_REFRESH_CHILDREN.NAME is
'Name of the object in the refresh group'
/
comment on column USER_REFRESH_CHILDREN.TYPE is
'Type of the object in the refresh group'
/
comment on column USER_REFRESH_CHILDREN.ROWNER is
'Name of the owner of the refresh group'
/
comment on column USER_REFRESH_CHILDREN.RNAME is
'Name of the refresh group'
/
comment on column USER_REFRESH_CHILDREN.REFGROUP is
'Internal identifier of refresh group'
/
comment on column USER_REFRESH_CHILDREN.IMPLICIT_DESTROY is
'Y or N, if Y then destroy the refresh group when its last item is subtracted'
/
comment on column USER_REFRESH_CHILDREN.PUSH_DEFERRED_RPC is
'Y or N, if Y then push changes from snapshot to master before refresh'
/
comment on column USER_REFRESH_CHILDREN.REFRESH_AFTER_ERRORS is
'If Y, proceed with refresh despite error when pushing deferred RPCs'
/
comment on column USER_REFRESH_CHILDREN.ROLLBACK_SEG is
'Name of the rollback segment to use while refreshing'
/
comment on column USER_REFRESH_CHILDREN.JOB is
'Identifier of job used to automatically refresh the group'
/
comment on column USER_REFRESH_CHILDREN.NEXT_DATE is
'Date that this job will next be automatically refreshed, if not broken'
/
comment on column USER_REFRESH_CHILDREN.INTERVAL is
'A date function used to compute the next NEXT_DATE'
/
comment on column USER_REFRESH_CHILDREN.BROKEN is
'Y or N, Y is the job is broken and will never be run'
/
comment on column USER_REFRESH_CHILDREN.PURGE_OPTION is
'The method for purging the transaction queue after each push'
/
comment on column USER_REFRESH_CHILDREN.PARALLELISM is
'The level of parallelism for transaction propagation'
/
comment on column USER_REFRESH_CHILDREN.HEAP_SIZE is
'The heap size used for transaction propagation'
/
drop public synonym USER_REFRESH_CHILDREN
/
create public synonym USER_REFRESH_CHILDREN for USER_REFRESH_CHILDREN
/
grant select on USER_REFRESH_CHILDREN to public with grant option
/
create or replace view DBA_REGISTERED_SNAPSHOTS 
  (OWNER, NAME, SNAPSHOT_SITE, CAN_USE_LOG, UPDATABLE, REFRESH_METHOD,
   SNAPSHOT_ID, VERSION, QUERY_TXT)
as select sowner, snapname, snapsite,
   decode(bitand(flag,1), 0 , 'NO', 'YES'),
   decode(bitand(flag,2), 0 , 'NO', 'YES'),
   decode(bitand(flag,32), 0 , 'ROWID', 'PRIMARY KEY'),
   snapshot_id, 
   decode(rep_type, 1, 'ORACLE 7 SNAPSHOT',
                    2, 'ORACLE 8 SNAPSHOT',
                    3, 'REPAPI SNAPSHOT',
                       'UNKNOWN'),
   query_txt
from sys.reg_snap$
/
drop public synonym DBA_REGISTERED_SNAPSHOTS
/
create public synonym DBA_REGISTERED_SNAPSHOTS for DBA_REGISTERED_SNAPSHOTS 
/
grant select on DBA_REGISTERED_SNAPSHOTS to select_catalog_role
/
comment on table DBA_REGISTERED_SNAPSHOTS is
'Remote snapshots of local tables'
/
comment on column DBA_REGISTERED_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column DBA_REGISTERED_SNAPSHOTS.NAME is
'The name of the snapshot'
/
comment on column DBA_REGISTERED_SNAPSHOTS.SNAPSHOT_SITE is
'Global name of the snapshot site'
/
comment on column DBA_REGISTERED_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and cannot fast refresh'
/
comment on column DBA_REGISTERED_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only'
/
comment on column DBA_REGISTERED_SNAPSHOTS.REFRESH_METHOD is
'Whether the snapshot uses rowids or primary key for fast refresh'
/
comment on column DBA_REGISTERED_SNAPSHOTS.SNAPSHOT_ID is
'Identifier for the snapshot used by the master for fast refresh'
/
comment on column DBA_REGISTERED_SNAPSHOTS.VERSION is
'Version of snapshot'
/
comment on column DBA_REGISTERED_SNAPSHOTS.QUERY_TXT is
'Query defining the snapshot'
/

create or replace view ALL_REGISTERED_SNAPSHOTS
as select  * from dba_registered_snapshots s
where exists (select a.snapshot_id from  all_snapshot_logs a
                             where  s.snapshot_id = a.snapshot_id)
or  userenv('SCHEMAID') = 1
or  exists (select null from v$enabledprivs
            where priv_number  in (-45 /* LOCK ANY TABLE */,
                                   -47 /* SELECT ANY TABLE */,
                                   -48 /* INSERT ANY TABLE  */,
                                   -49 /* UPDATE ANY TABLE */,
                                   -50 /* DELETE ANY TABLE */)
            )
/
comment on table ALL_REGISTERED_SNAPSHOTS is
'Remote snapshots of local tables that the user can see'
/
comment on column ALL_REGISTERED_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column ALL_REGISTERED_SNAPSHOTS.NAME is
'The name of the snapshot'
/
comment on column ALL_REGISTERED_SNAPSHOTS.SNAPSHOT_SITE is
'Global name of the snapshot site'
/
comment on column ALL_REGISTERED_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and cannot fast refresh'
/
comment on column ALL_REGISTERED_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only'
/
comment on column ALL_REGISTERED_SNAPSHOTS.REFRESH_METHOD is
'Whether the snapshot uses rowids or primary key for fast refresh'
/
comment on column ALL_REGISTERED_SNAPSHOTS.SNAPSHOT_ID is
'Identifier for the snapshot used by the master for fast refresh'
/
comment on column ALL_REGISTERED_SNAPSHOTS.VERSION is
'Version of snapshot'
/
comment on column ALL_REGISTERED_SNAPSHOTS.QUERY_TXT is
'Query defining the snapshot'
/
drop public synonym ALL_REGISTERED_SNAPSHOTS
/
create public synonym ALL_REGISTERED_SNAPSHOTS for ALL_REGISTERED_SNAPSHOTS
/
grant select on ALL_REGISTERED_SNAPSHOTS to public with grant option
/
create or replace view USER_REGISTERED_SNAPSHOTS
as select * from dba_registered_snapshots s
where exists (select snapshot_id from user_snapshot_logs u
                  where s.snapshot_id = u.snapshot_id)
/
comment on table USER_REGISTERED_SNAPSHOTS is
'Remote snapshots of local tables currently using logs owned by the user'
/
comment on column USER_REGISTERED_SNAPSHOTS.OWNER is
'Owner of the snapshot'
/
comment on column USER_REGISTERED_SNAPSHOTS.NAME is
'The name of the snapshot'
/
comment on column USER_REGISTERED_SNAPSHOTS.SNAPSHOT_SITE is
'Global name of the snapshot site'
/
comment on column USER_REGISTERED_SNAPSHOTS.CAN_USE_LOG is
'If NO, this snapshot is complex and cannot fast refresh'
/
comment on column USER_REGISTERED_SNAPSHOTS.UPDATABLE is
'If NO, the snapshot is read only'
/
comment on column USER_REGISTERED_SNAPSHOTS.REFRESH_METHOD is
'Whether the snapshot uses rowids or primary key for fast refresh'
/
comment on column USER_REGISTERED_SNAPSHOTS.SNAPSHOT_ID is
'Identifier for the snapshot used by the master for fast refresh'
/
comment on column USER_REGISTERED_SNAPSHOTS.VERSION is
'Version of snapshot'
/
comment on column USER_REGISTERED_SNAPSHOTS.QUERY_TXT is
'Query defining the snapshot'
/
drop public synonym USER_REGISTERED_SNAPSHOTS
/
create public synonym USER_REGISTERED_SNAPSHOTS for USER_REGISTERED_SNAPSHOTS
/
grant select on USER_REGISTERED_SNAPSHOTS to public with grant option
/
/*
 * This view is currently used only by the replication manager and 
 * is not made public.
 */
create or replace view DBA_SNAPSHOT_LOG_FILTER_COLS
(OWNER, NAME, COLUMN_NAME)
as
select mowner, master, colname from sys.mlog_refcol$
/
comment on table DBA_SNAPSHOT_LOG_FILTER_COLS is
'All filter columns (excluding PK cols) being logged in the snapshot logs'
/
comment on column DBA_SNAPSHOT_LOG_FILTER_COLS.OWNER is
'Owner of the master table being logged'
/
comment on column DBA_SNAPSHOT_LOG_FILTER_COLS.NAME is
'Name of the master table being logged'
/
comment on column DBA_SNAPSHOT_LOG_FILTER_COLS.COLUMN_NAME is
'Filter column being logged'
/
grant select on DBA_SNAPSHOT_LOG_FILTER_COLS to select_catalog_role
/

rem
rem  Family of MVIEW views
rem

rem  DBA_MVIEWS

/* Some of the column values come from summary meta-data.  This meta-data
 * can be missing for pre-8.1.5 MVs.  Missing information will be assigned 
 * a null value.  The columns that depend on a summary row are REWRITE_ENABLED, 
 * REWRITE_CAPABILITY, LAST_REFRESH_TYPE, LAST_REFRESH_DATE
 */

create or replace view DBA_MVIEWS
( OWNER,                    /* owner name                                   */
  MVIEW_NAME,               /* materialized view name                       */
  CONTAINER_NAME,           /* materialized view container table name       */
  QUERY,                    /* defining query                               */
  QUERY_LEN,                /* length of defining query (in bytes)          */
  UPDATABLE,                /* Y if updatable materialized view, else N     */
  UPDATE_LOG,               /* update log for updatable materialized views  */
  MASTER_ROLLBACK_SEG,      /* rollback segment for master site             */
  MASTER_LINK,              /* dblink for master site                       */
/* rewrite info:                                                            */
  REWRITE_ENABLED,          /* Y if enabled for rewrite, else N             */
  REWRITE_CAPABILITY,       /* NONE/TEXTMATCH/GENERAL                       */
                            /* (determined statically)                      */
/* refresh info:                                                            */
  REFRESH_MODE,             /* DEMAND/COMMIT/NEVER                          */
  REFRESH_METHOD,           /* COMPLETE/FORCE/FAST/NEVER                    */
  BUILD_MODE,               /* IMMEDIATE/DEFERRED/PREBUILT                  */
  FAST_REFRESHABLE,         /* NO/DIRLOAD/DML/DIRLOAD_DML/                  */
                            /* DIRLOAD_LIMITEDDML                           */
                            /* (determined statically)                      */
/* refresh execution:                                                       */
  LAST_REFRESH_TYPE,        /* COMPLETE/FAST/UNKNOWN                        */
  LAST_REFRESH_DATE,        /* date of last refresh                         */
/* staleness:                                                               */
  STALENESS,                /* FRESH/STALE/UNUSABLE/UNKNOWN/UNDEFINED       */
  AFTER_FAST_REFRESH,       /* FRESH/STALE/UNUSABLE/UNKNOWN/UNDEFINED/NA    */
/* meta-data info:                                                          */
  COMPILE_STATE             /* VALID/NEEDS_COMPILE/ERROR                    */
                            /* (state of the MV -- set by alter compile)    */
)
as
select s.sowner, s.vname, s.tname, s.query_txt, s.query_len, 
       decode(bitand(s.flag,2), 0, 'N', 'Y'),  /* updatable */
       s.uslog, s.mas_roll_seg, s.mlink,
       decode(w.mflags,
              '', '',  /* missing summary */
              decode(bitand(w.mflags, 4), 4, 'N', 'Y')),
       /* rewrite capability
        *   KKQS_NOGR_PFLAGS:
        *     QSMG_SUM_AGGREGATE_NOT_TOP + QSMG_SUM_OUT_MISSING_GRPCOL +
        *     QSMG_SUM_FROM_INLINE_VIEW  + QSMG_SUM_SELF_JOIN +
        *     QSMG_SUM_SELECTIONS        + QSMG_SUM_HAVING +
        *     QSMG_SUM_CONNECT_BY        + QSMG_SUM_RAW_OUTPUT +
        *     QSMG_SUM_SUBQUERY_HAVING   + QSMG_SUM_SUBQUERY_WHERE +
        *     QSMG_SUM_SET_OPERATOR      + QSMG_SUM_AGG_SELECTIONS +
        *     QSMG_SUM_NESTED_CURSOR     + QSMG_SUM_NON_AND_IN_WHERE +
        *     QSMG_SUM_GROUP_BY_ROLLUP   + QSMG_SUM_GROUP_BY_CUBE
        *
        *   QSMG_SUM_DATA_IGNORE - 2nd-class summary
        */
       decode(w.pflags,
              '', '',  /* missing summary */
              decode(bitand(w.pflags, 1073741824), /* 2nd-class summary */
                     1073741824, 'NONE',
                     /* 242425068 = 4+8+32+64+128+1024+2048+4096+65536+
                      *             131072+1048576+2097152+4194304+
                      *             33554432+67108864+134217728
                      */
                     decode(bitand(w.pflags, 242425068),
                            0, 'GENERAL',
                            'TEXTMATCH'))),
       decode(s.auto_fast,
              'N', 'NEVER',
              decode(bitand(s.flag, 32768), 0, 'DEMAND', 'COMMIT')),
       decode(s.auto_fast,   /* refresh method */
              'C',  'COMPLETE',
              'F',  'FAST',
              '?',  'FORCE',
              'N',  'NEVER',
              NULL, 'FORCE', 'ERROR'),
       decode(bitand(s.flag, 131072),  /* build mode */
              131072, 'PREBUILT',
              decode(bitand(s.flag, 524288), 0, 'IMMEDIATE', 'DEFERRED')),
       /* fast refreshable 
        *     rowid+primary key+subquery+complex+MAV+MJV+MAV1
        *     29104 = 16+32+128+256+4096+8192+16384
        */
       decode(bitand(s.flag, 29104),
              16,     'DML',  /* rowid */
              32,     'DML',  /* primary key */
  /*32+128*/  160,    'DML',  /* subquery - has both the primary key */
                              /*            bit and the subquery bit */
              256,    'NO',   /* complex */
              4096, decode(bitand(s.flag, 32832), /* MAV: on dem+on com */
                                                  /*      64+32768      */
                           64, 'DIRLOAD',               /* on demand */
                           32768, 'DIRLOAD_LIMITEDDML', /* on commit */
                           'ERROR'),
              8192,   'DIRLOAD_DML', /* MJV */
              16384,  'DIRLOAD_DML', /* MAV1 */
              'ERROR'),
       /* fixing bug 923186 */
       decode(w.mflags,                    /*last refresh type */
              '','',                       /*missing summary */
              decode(bitand(w.mflags,16384+32768), 
                     0, 'NA', 
                     16384, 'COMPLETE', 
                     32768, 'FAST',
                     'ERROR')),                  
       /* end fixing bug 923186 */
       decode(w.lastrefreshdate,  /* last refresh date */
              '', '',  /* missing summary */
              decode(to_char(w.lastrefreshdate,'DD-MON-YYYY'),'01-JAN-1950', '',w.lastrefreshdate)),
       /* staleness */
       decode(NVL(s.mlink,'null'),  /* not null implies remote */
              'null', decode(bitand(s.status, 4),  /* snapshot-invalid */
                             4, 'UNUSABLE',
                             decode(w.mflags,
                               '', '',  /* missing summary */
                               decode(bitand(w.mflags, 64),  /* wh-unusable */
                                      64, 'UNUSABLE',
                                      decode(bitand(w.mflags, 32),/* unknown */
                                             32, 'UNKNOWN',
                         /* known stale */   decode(bitand(w.mflags, 1),
                                                    0, 'FRESH',
                                                    'STALE'))))),
              'UNDEFINED'),  /* remote MV */
       /* after fast refresh */
       /* in the decode for after fast refresh, we only have to check
        * whether w.mflags is null once.  all of the other occurences 
        * fall under the first check.  if the summary information is not
        * null, we need to check for the warehouse unusable condition
        * before we check to see if the MV is complex.  if the summary
        * information is null, we still need to check whether the MV
        * is complex.  
        */
       decode(NVL(s.mlink,'null'),  /* remote */
              'null', decode(s.auto_fast,  /* never refresh */
                         'N', 'NA', 
                         decode(bitand(s.flag, 32768),  /* on commit */
                             32768, 'NA',
                             decode(bitand(s.status, 4),  /* snap-invalid */
                                4, 'NA',
                                decode(w.mflags,  /* missing summary */
                                   '', decode(bitand(s.flag, 256), /* complex */
                                              256, 'NA',
                                              ''),
         /* warehouse unusable */  decode(bitand(w.mflags, 64),
                                       64, 'NA',
                                       decode(bitand(s.flag, 256), /*complex*/
                                          256, 'NA',
                         /* unknown */    decode(bitand(w.mflags,32),
                                                 32, 'UNKNOWN',
                             /* known stale */   decode(bitand(w.mflags, 1),
                                                        0, 'FRESH',
                  /* stale states (on-demand only)
                   * (This decode is the default clause for the known-stale 
                   * decode statement.  It should be indented there, but there
                   * isn't enough room.) 
                   */
                  decode(bitand(s.flag, 4096),  /* MAV */
                     4096, decode(bitand(w.mflags, 3968),
                                  /* 3968 = 128+256+512+1024+2048 */
                                  128, 'FRESH',   /* si */
                                  256, 'UNKNOWN', /* su */
                                  512, 'STALE',   /* sf */
                                  1024, 'NA',     /* lsi */
                                  2048, 'NA',     /* lsu */
                 /* 128+1024 */   1152, 'NA',     /* si+lsi*/
                 /* 256+2048 */   2304, 'NA',     /* su+lsu*/
                                  'ERROR'),
                     decode(bitand(s.flag, 176), /* ri+pk+sq  */
                                                 /* 16+32+128 */
                            0, decode(bitand(s.flag, 24576), /* mjv+mav1   */
                                                             /* 8192+16384 */
                                      0, 'ERROR', /* no mv type */
                /* mjv or mav1 MV */  decode(bitand(w.mflags, 3968),
                                             /* 3968 = 128+256+512+1024+2048 */
                                             128, 'FRESH',     /* si */
                                             256, 'UNKNOWN',   /* su */
                                             512, 'STALE',     /* sf */
                                             1024, 'FRESH',    /* lsi */
                                             2048, 'UNKNOWN',  /* lsu */
                            /* 128+1024 */   1152, 'FRESH',    /* si+lsi*/
                            /* 256+2048 */   2304, 'UNKNOWN',  /* su+lsu*/
                                             'ERROR')),
   /* ri or pk or sq MV */  decode(bitand(w.mflags, 3968),
                                   /* 3968 = 128+256+512+1024+2048 */
                                   128, 'STALE',     /* si */
                                   256, 'STALE',     /* su */
                                   512, 'STALE',     /* sf */
                                   1024, 'FRESH',    /* lsi */
                                   2048, 'UNKNOWN',  /* lsu */
                  /* 128+1024 */   1152, 'STALE',    /* si+lsi*/
                  /* 256+2048 */   2304, 'STALE',    /* su+lsu*/
                                   'ERROR'))))))))))),
              'UNDEFINED'), /* remote mv */
       decode(o.status, 1, 'VALID', 5, 'NEEDS_COMPILE', 'ERROR')/* compile st*/
from sys.user$ u, sys.sum$ w, sys.obj$ o, sys.snap$ s
where w.containernam(+) = s.vname
  and o.obj#(+) = w.obj#
  and o.owner# = u.user#(+)
  and ((u.name = s.sowner) or (u.name IS NULL))
  and s.instsite = 0 
/
drop public synonym DBA_MVIEWS
/
create public synonym DBA_MVIEWS for DBA_MVIEWS
/
comment on table DBA_MVIEWS is
'All materialized views in the database'
/
comment on column DBA_MVIEWS.OWNER is
'Owner of the materialized view'
/
comment on column DBA_MVIEWS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column DBA_MVIEWS.CONTAINER_NAME is
'Name of the materialized view container table'
/
comment on column DBA_MVIEWS.QUERY is
'The defining query that the materialized view instantiates'
/
comment on column DBA_MVIEWS.QUERY_LEN is
'The number of bytes in the defining query (based on the server character set'
/
comment on column DBA_MVIEWS.UPDATABLE is
'Indicates whether the materialized view can be updated'
/
comment on column DBA_MVIEWS.UPDATE_LOG is
'Name of the table that logs changes to an updatable materialized view'
/
comment on column DBA_MVIEWS.MASTER_ROLLBACK_SEG is
'Name of the rollback segment to use at the master site'
/
comment on column DBA_MVIEWS.MASTER_LINK is
'Name of the database link to the master site'
/
comment on column DBA_MVIEWS.REWRITE_ENABLED is
'Indicates whether rewrite is enabled for the materialized view'
/
comment on column DBA_MVIEWS.REWRITE_CAPABILITY is
'Indicates the kind of rewrite that is enabled'
/
comment on column DBA_MVIEWS.REFRESH_MODE is
'Indicates how and when the materialized view will be refreshed'
/
comment on column DBA_MVIEWS.REFRESH_METHOD is
'The default refresh method for the materialized view (complete, fast, ...)'
/
comment on column DBA_MVIEWS.BUILD_MODE is
'How and when to initially build (load) the materialized view container'
/
comment on column DBA_MVIEWS.FAST_REFRESHABLE is
'Indicates the kinds of operations that can be fast refreshed for the MV'
/
comment on column DBA_MVIEWS.LAST_REFRESH_TYPE is
'Indicates the kind of refresh that was last performed on the MV'
/
comment on column DBA_MVIEWS.LAST_REFRESH_DATE is
'The date that the materialized view was last refreshed'
/
comment on column DBA_MVIEWS.STALENESS is
'Indicates the staleness state of the materialized view (fresh, stale, ...)'
/
comment on column DBA_MVIEWS.AFTER_FAST_REFRESH is
'Indicates the staleness state the MV will have after a fast refresh is done'
/
comment on column DBA_MVIEWS.COMPILE_STATE is
'Indicates the validity of the MV meta-data'
/
grant select on DBA_MVIEWS to select_catalog_role
/

rem  ALL_MVIEWS

create or replace view ALL_MVIEWS
as select m.* from dba_mviews m, sys.obj$ o, sys.user$ u
where o.owner#     = u.user#
  and m.mview_name = o.name
  and u.name       = m.owner
  and o.type#      = 2                     /* table */
  and ( u.user# in (userenv('SCHEMAID'), 1)
        or
        o.obj# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      )
                  )
        or /* user has system privileges */
        exists ( select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
               )
      )
/

drop public synonym ALL_MVIEWS
/
create public synonym ALL_MVIEWS for ALL_MVIEWS
/
comment on table ALL_MVIEWS is
'All materialized views in the database'
/
comment on column ALL_MVIEWS.OWNER is
'Owner of the materialized view'
/
comment on column ALL_MVIEWS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column ALL_MVIEWS.CONTAINER_NAME is
'Name of the materialized view container table'
/
comment on column ALL_MVIEWS.QUERY is
'The defining query that the materialized view instantiates'
/
comment on column ALL_MVIEWS.QUERY_LEN is
'The number of bytes in the defining query (based on the server character set'
/
comment on column ALL_MVIEWS.UPDATABLE is
'Indicates whether the materialized view can be updated'
/
comment on column ALL_MVIEWS.UPDATE_LOG is
'Name of the table that logs changes to an updatable materialized view'
/
comment on column ALL_MVIEWS.MASTER_ROLLBACK_SEG is
'Name of the rollback segment to use at the master site'
/
comment on column ALL_MVIEWS.MASTER_LINK is
'Name of the database link to the master site'
/
comment on column ALL_MVIEWS.REWRITE_ENABLED is
'Indicates whether rewrite is enabled for the materialized view'
/
comment on column ALL_MVIEWS.REWRITE_CAPABILITY is
'Indicates the kind of rewrite that is enabled'
/
comment on column ALL_MVIEWS.REFRESH_MODE is
'Indicates how and when the materialized view will be refreshed'
/
comment on column ALL_MVIEWS.REFRESH_METHOD is
'The default refresh method for the materialized view (complete, fast, ...)'
/
comment on column ALL_MVIEWS.BUILD_MODE is
'How and when to initially build (load) the materialized view container'
/
comment on column ALL_MVIEWS.FAST_REFRESHABLE is
'Indicates the kinds of operations that can be fast refreshed for the MV'
/
comment on column ALL_MVIEWS.LAST_REFRESH_TYPE is
'Indicates the kind of refresh that was last performed on the MV'
/
comment on column ALL_MVIEWS.LAST_REFRESH_DATE is
'The date that the materialized view was last refreshed'
/
comment on column ALL_MVIEWS.STALENESS is
'Indicates the staleness state of the materialized view (fresh, stale, ...)'
/
comment on column ALL_MVIEWS.AFTER_FAST_REFRESH is
'Indicates the staleness state the MV will have after a fast refresh is done'
/
comment on column ALL_MVIEWS.COMPILE_STATE is
'Indicates the validity of the MV meta-data'
/
grant select on ALL_MVIEWS to public with grant option
/

rem  USER_MVIEWS

create or replace view USER_MVIEWS
as select m.* from dba_mviews m, sys.user$ u
where u.user# = userenv('SCHEMAID')
  and m.owner = u.name
/

drop public synonym USER_MVIEWS
/
create public synonym USER_MVIEWS for USER_MVIEWS
/
comment on table USER_MVIEWS is
'All materialized views in the database'
/
comment on column USER_MVIEWS.OWNER is
'Owner of the materialized view'
/
comment on column USER_MVIEWS.MVIEW_NAME is
'Name of the materialized view'
/
comment on column USER_MVIEWS.CONTAINER_NAME is
'Name of the materialized view container table'
/
comment on column USER_MVIEWS.QUERY is
'The defining query that the materialized view instantiates'
/
comment on column USER_MVIEWS.QUERY_LEN is
'The number of bytes in the defining query (based on the server character set'
/
comment on column USER_MVIEWS.UPDATABLE is
'Indicates whether the materialized view can be updated'
/
comment on column USER_MVIEWS.UPDATE_LOG is
'Name of the table that logs changes to an updatable materialized view'
/
comment on column USER_MVIEWS.MASTER_ROLLBACK_SEG is
'Name of the rollback segment to use at the master site'
/
comment on column USER_MVIEWS.MASTER_LINK is
'Name of the database link to the master site'
/
comment on column USER_MVIEWS.REWRITE_ENABLED is
'Indicates whether rewrite is enabled for the materialized view'
/
comment on column USER_MVIEWS.REWRITE_CAPABILITY is
'Indicates the kind of rewrite that is enabled'
/
comment on column USER_MVIEWS.REFRESH_MODE is
'Indicates how and when the materialized view will be refreshed'
/
comment on column USER_MVIEWS.REFRESH_METHOD is
'The default refresh method for the materialized view (complete, fast, ...)'
/
comment on column USER_MVIEWS.BUILD_MODE is
'How and when to initially build (load) the materialized view container'
/
comment on column USER_MVIEWS.FAST_REFRESHABLE is
'Indicates the kinds of operations that can be fast refreshed for the MV'
/
comment on column USER_MVIEWS.LAST_REFRESH_TYPE is
'Indicates the kind of refresh that was last performed on the MV'
/
comment on column USER_MVIEWS.LAST_REFRESH_DATE is
'The date that the materialized view was last refreshed'
/
comment on column USER_MVIEWS.STALENESS is
'Indicates the staleness state of the materialized view (fresh, stale, ...)'
/
comment on column USER_MVIEWS.AFTER_FAST_REFRESH is
'Indicates the staleness state the MV will have after a fast refresh is done'
/
comment on column USER_MVIEWS.COMPILE_STATE is
'Indicates the validity of the MV meta-data'
/
grant select on USER_MVIEWS to public with grant option
/

