Rem
Rem $Header: catiasc.sql 03-aug-2000.17:50:06 jingliu Exp $
Rem
Rem catiasc.sql
Rem
Rem  Copyright (c) Oracle Corporation 1900, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      catiasc.sql  IAS views and catalog
Rem
Rem    DESCRIPTION
Rem      views required to support IAS
Rem
Rem    NOTES
Rem      requires catrepc.sql to have already been executed
Rem      execute as sys
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jingliu     07/27/00 - add view dba_ias_objects_exp
Rem    jingliu     05/15/00 - add internal view dba_ias_objects_base
Rem    masubram    04/20/00 - add temporary tables
Rem    masubram    04/18/00 - remove user synonym object types
Rem    masubram    04/12/00 - add sequence and remove trigger types
Rem    celsbern    04/07/00 - added alter of template tables/objects at start.
Rem    masubram    04/05/00 - add new types for IAS template objects
Rem    masubram    03/29/00 - add new ias object types
Rem    celsbern    03/29/00 - even more site changes
Rem    celsbern    03/29/00 - fixed dba_ias_templates synonym
Rem    celsbern    03/28/00 - created.
Rem
 
-- changes to repcat$_template_objects table
alter table system.repcat$_template_objects
drop constraint repcat$_template_objects_c1;

alter table system.repcat$_template_objects
add  constraint repcat$_template_objects_c1 check
   ((object_type in (-1, 1, 2, 4, 5, 6, 7, 8, 9, 10, 12, -5)) or 
    (object_type in (-1001,-1002,-1003,-1004, -1005, -1006, -1007,-1008,
     -1009, -1010, -1011, -1012, -1013, -1014, -1015, -1016, -1017)));

alter table system.repcat$_template_objects
drop constraint repcat$_template_objects_u1;

create index system.repcat$_template_objects_n3 on 
system.repcat$_template_objects (refresh_template_id,
  object_name,master_rollback_seg,object_type );

-- changes to repcat$_refresh_templates table
alter table system.repcat$_refresh_templates
drop constraint refresh_templates_c1;

alter table system.repcat$_refresh_templates
add  constraint refresh_templates_c1 check
(public_template in ('Y','N','I') or public_template is NULL);

-- changes to repcat$_template_sites table
alter table system.repcat$_template_sites
drop constraint repcat$_template_sites_c1;

alter table system.repcat$_template_sites
add constraint repcat$_template_sites_c1 check
(status in (-100,-1,0,1));


-- IAS templates view
create or replace view dba_ias_templates as
select owner, refresh_group_name,
refresh_template_name ias_template_name,
refresh_template_id   ias_template_id,
template_comment
from system.repcat$_refresh_templates rt
where rt.public_template = 'I'
/
drop public synonym dba_ias_templates;
/
create public synonym dba_ias_templates for dba_ias_templates
/
grant select on dba_ias_templates to select_catalog_role
/

-- IAS internal object view
create or replace view dba_ias_objects_base as 
select rt.refresh_template_name ias_template_name,
  ro.master_rollback_seg schema_name,
  ro.object_name,
  ro.object_type object_type_id,
  decode(ro.object_type,
    -1001,'IAS USER',
    -1002,'IAS SITEOWNER',
    -1003,'READ ONLY TABLE',
    -1004,'ASYNC UPDATABLE TABLE',
    -1005,'SYNC UPDATABLE TABLE',
    -1006,'PLSQL PACKAGE',
    -1007,'PLSQL PROCEDURE',
    -1008,'PLSQL FUNCTION',
    -1009,'USER SEQUENCE',
    -1010,'USER VIEW',
    -1011,'TEMP TABLE',
    -1012,'ASYNCHRONOUS MASTER REPGROUP',
    -1013,'SYNCHRONOUS MASTER REPGROUP',
    -1014,'REFRESH GROUP',
    -1015,'UPDATABLE MV LOG',
    -1016,'DUMMY SNAPSHOT',
    -1017,'GENERATED DDL',
          'UNDEFINED') object_type,
  ro.derived_from_sname,
  ro.derived_from_oname
from system.repcat$_refresh_templates rt,
  system.repcat$_template_objects ro
where rt.public_template = 'I'
and rt.refresh_template_id = ro.refresh_template_id
/
grant select on dba_ias_objects_base to select_catalog_role
/

-- IAS internal object view for export
create or replace view dba_ias_objects_exp as
select ro.refresh_template_id template_id,
       ro.object_name,
       ro.master_rollback_seg schema_name,
  decode(ro.object_type,
    -1001,'IAS USER',
    -1002,'IAS SITEOWNER',
    -1003,'READ ONLY TABLE',
    -1004,'ASYNC UPDATABLE TABLE',
    -1005,'SYNC UPDATABLE TABLE',
    -1006,'PLSQL PACKAGE',
    -1007,'PLSQL PROCEDURE',
    -1008,'PLSQL FUNCTION',
    -1009,'USER SEQUENCE',
    -1010,'USER VIEW',
    -1011,'TEMP TABLE',
    -1012,'ASYNCHRONOUS MASTER REPGROUP',
    -1013,'SYNCHRONOUS MASTER REPGROUP',
    -1014,'REFRESH GROUP',
    -1015,'UPDATABLE MV LOG',
    -1016,'DUMMY SNAPSHOT',
    -1017,'GENERATED DDL',
          'UNDEFINED') object_type
from system.repcat$_template_objects ro
/
grant select on dba_ias_objects_exp to select_catalog_role
/

--IAS objects view
create or replace view dba_ias_objects as 
select ro.ias_template_name,
  ro.schema_name,
  ro.object_name,
  ro.object_type,
  ro.derived_from_sname,
  ro.derived_from_oname
from sys.dba_ias_objects_base ro 
/
drop public synonym dba_ias_objects
/
create public synonym dba_ias_objects for  dba_ias_objects
/
grant select on dba_ias_objects to select_catalog_role
/

create or replace view dba_ias_sites 
(ias_template_name,user_name,site_name) as
select refresh_template_name, user_name, site_name
from system.repcat$_template_sites
where status = -100 /*secret code for IAS template sites? */
/
drop public synonym dba_ias_sites
/
create public synonym dba_ias_sites for dba_ias_sites
/
grant select on dba_ias_sites to select_catalog_role
/

create or replace view dba_ias_constraint_exp as
select 1 pkexists, cd.obj# from sys.cdef$ cd
where cd.type# = 2
/
grant select on dba_ias_constraint_exp to select_catalog_role
/

-- The dba_ias_gen_stmts view is defined later after the package
-- dbms_ias_template is loaded as it uses package constants 
