Rem
Rem $Header: rdbms/admin/catiasc.sql /main/7 2008/12/25 17:24:23 yurxu Exp $
Rem
Rem catiasc.sql
Rem
Rem Copyright (c) 1900, 2008, Oracle and/or its affiliates.
Rem 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 12/10/04 - lrg_1803304
Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
Rem htseng 04/12/01 - eliminate execute twice (remove ;).
Rem celsbern 03/20/01 - moved export views to this file from dbmsiast.
Rem jingliu 12/11/00 - add index repcat$_template_objects_n2
Rem celsbern 11/16/00 - fixed views to use new IAS tables
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
create index system.repcat$_template_objects_n2 on
system.repcat$_template_objects (refresh_template_id,
object_name,schema_name,object_type );
-- 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,
system.repcat$_template_types tt
where rt.template_type_id = tt.template_type_id
and bitand(rawtohex(tt.flags),2) = 2
/
create or replace 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.schema_name,
ro.object_name,
ro.object_type object_type_id,
ot.object_type_name object_type,
ro.derived_from_sname,
ro.derived_from_oname
from system.repcat$_refresh_templates rt,
system.repcat$_template_objects ro,
system.repcat$_object_types ot,
system.repcat$_template_types tt
where rt.refresh_template_id = ro.refresh_template_id
and ro.object_type = ot.object_type_id
and rt.template_type_id = tt.template_type_id
and bitand(rawtohex(tt.flags),2) = 2
/
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.schema_name,
ot.object_type_name object_type
from system.repcat$_template_objects ro,
system.repcat$_refresh_templates rt,
system.repcat$_template_types tt,
system.repcat$_object_types ot
where ro.refresh_template_id = rt.refresh_template_id
and ro.object_type = ot.object_type_id
and rt.template_type_id = tt.template_type_id
and bitand(rawtohex(tt.flags),2) = 2
/
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
/
create or replace 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? */
/
create or replace 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
/
--IAS generated statements view
create or replace view dba_ias_gen_stmts as
select rt.refresh_template_name ias_template_name,
decode(ro.object_type, -1017, to_number(ro.object_name), 0) lineno, ddl_text
from system.repcat$_refresh_templates rt,
system.repcat$_template_objects ro,
system.repcat$_template_types tt
where rt.template_type_id = tt.template_type_id
and bitand(rawtohex(tt.flags),2) = 2
and rt.refresh_template_id = ro.refresh_template_id
and ro.object_type = -1017 -- object_type = dbms_ias_template.generated_ddl
/
create or replace public synonym dba_ias_gen_stmts for dba_ias_gen_stmts
/
grant select on dba_ias_gen_stmts to select_catalog_role
/
-- IAS generated statements view for export
create or replace view dba_ias_gen_stmts_exp as
select ro.refresh_template_id ias_template_id,
decode(ro.object_type, -1017, to_number(ro.object_name), 0) lineno, ddl_text
from system.repcat$_template_objects ro
where ro.object_type = -1017
/
grant select on dba_ias_gen_stmts_exp to select_catalog_role
/
-- IAS pre-generated statements view.
-- pre-gen stmts are exported after table export
create or replace view dba_ias_pregen_stmts as
select * from sys.dba_ias_gen_stmts_exp gs
where gs.lineno < (select lineno from sys.dba_ias_gen_stmts_exp f
where dbms_lob.substr(f.ddl_text,1,1)='0'
and dbms_lob.getlength(f.ddl_text) = 1
and f.ias_template_id = gs.ias_template_id)
/
grant select on dba_ias_pregen_stmts to select_catalog_role
/
-- IAS post generated statment.
-- post-gen stmts are exported at the end of database objects export.
create or replace view dba_ias_postgen_stmts as
select * from sys.dba_ias_gen_stmts_exp gs
where gs.lineno > (select lineno from sys.dba_ias_gen_stmts_exp f
where dbms_lob.substr(f.ddl_text,1,1)='0'
and dbms_lob.getlength(f.ddl_text) = 1
and f.ias_template_id = gs.ias_template_id)
/
grant select on dba_ias_postgen_stmts to select_catalog_role
/