[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select trim(oracle_username) oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
order by 1;
select count(obj#) into l_obj_cnt
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = x_owner)
and o.NAME = x_name
and o.type# = 13 --13 = TYPE,
and o.subname is not null; --only for evloved type
select count(obj#) into l_obj_cnt
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = x_owner)
and o.NAME = x_name
and o.type# = 13;
select count(obj#) into l_obj_cnt
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = x_owner)
and o.NAME = x_name
and o.type# = 14; -- 14: TYPE BODY
select count(service_id) into l_exists
from dba_services
where upper(name)=upper(c_patch_service);
select count(service_id) into l_exists
from sys.v_$active_services
where name=c_patch_service;
select distinct xmls.owner, xmls.schema_url
from dba_dependencies dep,
dba_xml_schemas xmls
where dep.type='TABLE'
and dep.owner in
(select oracle_username
from fnd_oracle_userid fu
where fu.read_only_flag in ('A','B', 'E', 'U', 'C') )
and dep.referenced_type = 'XML SCHEMA'
and dep.referenced_name = xmls.int_objname
-- XML schema owner either is SYSTEM or any e-biz user
and ((xmls.owner = 'SYSTEM') or
xmls.owner in
(select oracle_username
from fnd_oracle_userid fu
where fu.read_only_flag in ('A','B', 'E', 'U', 'C')));
select owner, schema_url
from dba_xml_schemas
where (
/* oracle seeded xml schemas in SYSTEM user */
(schema_url in (
'http://isetup.oracle.com/2006/diffresultdata.xsd' ,
'http://isetup.oracle.com/2006/selectionsets.xsd' ,
'http://isetup.oracle.com/2006/reporterdata.xsd' )
and owner='SYSTEM'
)
or
owner in
(
select oracle_username
from fnd_oracle_userid fu
where fu.read_only_flag in ('A','B', 'E', 'U', 'C')
)
);
delete from sys.xdb$moveSchemaTab;
insert into sys.xdb$moveSchemaTab(schema_url, schemaownerfrom, schemaownerto, schema)
values(xml_schema.schema_url, xml_schema.owner, g_apps_ne_schema, null);
log(l_module, 'STATEMENT', 'Inserted XML schema into sys.xdb$moveSchemaTab ('||
xml_schema.schema_url || ',' || xml_schema.owner ||
',' || g_apps_ne_schema ||', null)' );
select count(1) into l_count_pre
from dba_invalid_objects;
select count(1) into l_count_post
from dba_invalid_objects;
delete from sys.xdb$moveSchemaTab;
select aq.owner , aq.name
from dba_queues aq
where aq.owner = g_xla_schema
and (aq.name like 'XLA_%_DOC_Q' or aq.name like 'XLA_%_COMP_Q');
select referenced_owner, referenced_name
from dba_dependencies d
where d.type = 'TABLE'
and d.name = upper (name)
and d.referenced_type = 'TYPE'
and d.referenced_name = upper(d.referenced_name)
and (
-- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
d.referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
or
exists
( select 1
from dba_dependencies
where owner = d.referenced_owner
and name = d.referenced_name
and type = d.referenced_type
and referenced_type = 'TYPE'
and referenced_owner in
(
select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
)
/* filter out XDB types */
and not exists ( select null
from xdb.xdb$element e
where e.xmldata.property.sqltype = d.referenced_name )
and not exists (select null
from xdb.xdb$element e
where e.xmldata.property.sqlcolltype = d.referenced_name)
union all
select d.referenced_owner referenced_owner,
d.referenced_name referenced_name
from dba_dependencies d,
p
where d.referenced_type = 'TYPE'
and d.referenced_name = upper (d.referenced_name )
and d.name = p.referenced_name
and d.owner = p.referenced_owner
and d.referenced_owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
select distinct referenced_owner, referenced_name
from p where cyclemarker = 'N';
log(l_module, 'EVENT', 'Update TYPE reference from '||x_source_schema||' to '||x_target_schema);
sys.dbms_objects_utils.update_types
( schema1 => x_source_schema,
schema2 => x_target_schema,
typename => null,
check_update => true ) ;
select dep.owner, dep.name
from dba_dependencies dep
where dep.type = 'TABLE'
and dep.referenced_owner= type_owner
and dep.referenced_name = type_name
and dep.referenced_type = 'TYPE';
select ot.owner, ot.name
from dba_dependencies ot,
dba_types nt
where ot.type = 'TYPE'
and ot.referenced_owner= xx_type_owner
and ot.referenced_name = xx_type_name
and ot.referenced_type = 'TYPE'
and nt.owner = g_apps_ne_schema
and nt.type_name = ot.name;
select dep.owner, dep.name
from dba_dependencies dep
where dep.type = 'TABLE'
and dep.referenced_owner <> g_apps_ne_schema
start with
dep.referenced_owner = type_owner
and dep.referenced_name = type_name
and dep.referenced_type = 'TYPE'
connect by nocycle
prior dep.owner = dep.referenced_owner
and prior dep.name = dep.referenced_name
and prior dep.type = dep.referenced_type;
sys.dbms_objects_utils.update_types
( schema1 => x_type_owner,
schema2 => 'APPS_NE',
typename => x_type_name,
check_update => false ) ;
select 1 into l_exist
from dba_types
where owner = x_owner
and type_name = x_name;
select referenced_owner, referenced_name
from dba_dependencies d
where d.type = 'TABLE'
and d.name = upper (name)
and d.referenced_type = 'TYPE'
and d.referenced_name = upper(d.referenced_name)
and (
-- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
d.referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
OR
exists
( select 1
from dba_dependencies
where owner = d.referenced_owner
and name = d.referenced_name
and type = d.referenced_type
and referenced_type = 'TYPE'
and referenced_owner in
(
select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
)
-- /* filter out XDB types */
and not exists ( select null
from xdb.xdb$element e
where e.xmldata.property.sqltype = d.referenced_name )
and not exists (select null
from xdb.xdb$element e
where e.xmldata.property.sqlcolltype = d.referenced_name)
union all
select d.referenced_owner referenced_owner,
d.referenced_name referenced_name
from dba_dependencies d,
p
where d.referenced_type = 'TYPE'
and d.referenced_name = upper (d.referenced_name )
and d.name = p.referenced_name
and d.owner = p.referenced_owner
and d.referenced_owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
select distinct referenced_owner, referenced_name
from p where cyclemarker = 'N';
select referenced_owner, referenced_name
from dba_dependencies d
where type = 'TABLE'
and name = upper ( name )
and referenced_type = 'TYPE'
and referenced_name = upper (referenced_name)
and (
-- either referenced owner is registred SYSTEM.fnd_oracle_userids owner
referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U','C')
)
or
exists
( select 1
from dba_dependencies
where owner = d.referenced_owner
and name = d.referenced_name
and type = d.referenced_type
and referenced_type = 'TYPE'
and referenced_owner in
(
select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
)
-- Only take care evolved types i.e. exclude normal UDT
--
and exists
( select 1
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = referenced_owner)
and o.NAME = referenced_name
and o.type# = 13
and o.subname is not null
)
/* filter out XDB types */
and not exists ( select null
from xdb.xdb$element e
where e.xmldata.property.sqltype = d.referenced_name )
and not exists (select null
from xdb.xdb$element e
where e.xmldata.property.sqlcolltype = d.referenced_name)
union all
select d.referenced_owner referenced_owner,
d.referenced_name referenced_name
from dba_dependencies d,
p
where d.referenced_type = 'TYPE'
and d.referenced_name = upper(d.referenced_name )
and d.name = p.referenced_name
and d.owner = p.referenced_owner
and d.referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
and exists
( select 1
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = d.referenced_owner)
and o.name = d.referenced_name
and o.type# = 13 --13 = TYPE,
and o.subname is not null -- sstomar:
-- This predicate is important becuase for evolved type more than
-- one rows will be exist,
-- one with "subname= null" and other one as "subname= "
)
)
cycle referenced_owner, referenced_name set cyclemarker to 'Y' default 'N'
select distinct referenced_owner, referenced_name
from p where cyclemarker = 'N';
select count(1) into l_count
from dba_dependencies d
where d.type = 'TABLE'
and d.name = upper(name)
and d.referenced_type = 'TYPE'
and d.referenced_name = upper(d.referenced_name)
and (
d.referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U','C')
)
OR
exists
( select 1
from dba_dependencies
where owner = d.referenced_owner
and name = d.referenced_name
and type = d.referenced_type
and referenced_type = 'TYPE'
and referenced_owner in
(
select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
)
and exists
( select 1
from sys.obj$ o
where owner# =(select user# from sys.user$ where name = referenced_owner)
and o.NAME = referenced_name
and o.type# = 13
and o.subname is not null
)
and not exists ( select null
from xdb.xdb$element e
where e.xmldata.property.sqltype = d.referenced_name )
and not exists (select null
from xdb.xdb$element e
where e.xmldata.property.sqlcolltype = d.referenced_name);
select col.data_type_owner, col.data_type
into l_type_owner, l_type_name
from dba_tab_columns col
where col.owner = x_owner
and col.table_name = x_table
and col.column_name = x_column;
l_flag := true; -- not in already updated list
select '"'||aq.owner || '"."' || aq.name ||'"',
aq.queue_type
from dba_queues aq
where aq.owner = x_owner
and aq.queue_table = x_table
and not ( aq.name like 'XLA_%_COMP_Q'
OR aq.name like 'XLA_%_DOC_Q' );
select aq.name,
aq.queue_type
from dba_queues aq
where aq.owner = x_owner
and aq.queue_table = x_table;
select flags into l_qt_flags
from system.aq$_queue_tables
where schema = x_owner
and name = x_table;
select
atab.owner table_owner
, atab.table_name table_name
, acol.column_name column_name
from
dba_tables atab
, dba_tab_columns acol
where atab.owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
and acol.owner = atab.owner
and acol.table_name = atab.table_name
and acol.data_type_owner in -- User defined data type (UDT)
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
order by 1, 2, 3;
select
atab.owner table_owner
, atab.table_name table_name
, acol.column_name column_name
from
dba_tables atab
, dba_tab_columns acol
where acol.owner = atab.owner
and acol.table_name = atab.table_name
and atab.owner IN -- table owner
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
and acol.data_type_owner='SYSTEM'
and exists
(
select 1
from dba_dependencies dep
where dep.type = 'TYPE'
start with dep.owner = acol.data_type_owner
and dep.name = acol.DATA_TYPE
and dep.type = 'TYPE'
and dep.referenced_type= 'TYPE'
and dep.referenced_owner <> 'SYSTEM'
connect by
prior dep.referenced_name = dep.name
and prior dep.referenced_type = dep.type
and prior dep.referenced_type = 'TYPE'
and dep.referenced_owner in (
select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
)
order by 1,2,3;
select count(name) into l_queue_count
from dba_queues
where owner = l_owner
and queue_table = l_table;
select count(name) into l_queue_count
from dba_queues
where owner = l_owner
and queue_table = l_table;
select syn.synonym_name ,
syn.table_owner ,
syn.table_name ,
syn.db_link
from dba_synonyms syn
where syn.owner='PUBLIC'
and syn.table_owner in
(select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'C', 'U'))
and (syn.table_owner, syn.table_name) in
(select obj.owner, obj.object_name
from dba_objects obj
where obj.owner = syn.table_owner -- To avoid GSCC error
and obj.object_type in ('TYPE', 'PACKAGE', 'VIEW' ,
'SYNONYM','PROCEDURE',
'TRIGGER','FUNCTION'));
select distinct owner
from dba_dependencies
where referenced_owner = 'PUBLIC'
and referenced_type = 'SYNONYM'
and referenced_name = x_synonym_name
and owner in ( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'C', 'U'));
select 1 into l_exist
from dba_synonyms
where owner = l_users.owner
and synonym_name = x_synonym_name
and table_owner = x_table_owner
and table_name = x_table_name
and ((db_link is null
and x_db_link is null
)
OR
(db_link is not null
and x_db_link is not null
and db_link = x_db_link
)
) ;
select object_type into l_object_type
from dba_objects
where owner = l_users.owner
and object_name = x_synonym_name
and namespace in /* not hard-coding namespace=1 */
( select namespace
from dba_objects
where object_type = 'SYNONYM'
and owner = 'PUBLIC'
and object_name = x_synonym_name
);
select distinct name
from dba_dependencies
where owner='CTXSYS'
and TYPE='PACKAGE BODY'
and referenced_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
and referenced_type in
( 'TYPE',
'PACKAGE',
'VIEW' ,
'SYNONYM',
'PROCEDURE',
'TRIGGER',
'FUNCTION'
);
select aps.synonym_name
from dba_synonyms aps
where aps.owner = 'CTXSYS'
and aps.table_owner in
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U','C')
)
order by 1;
select dep.d_obj# d_obj, do.name d_name, do.type# d_type,
dep.p_obj# p_obj, po.name p_name, po.type# p_type
from sys.dependency$ dep,
sys.obj$ do,
sys.obj$ po
where do.obj# = dep.d_obj#
and po.obj# = dep.p_obj#
-- Only Non-Editionable objects
and do.type# not in (4,5,7,8,9,10,11,12,13,14,22,55,87)
start with dep.p_obj# in (select o.obj#
from sys.obj$ o,
sys.user$ u
where u.name = P_USER
and u.user# = o.owner#
and o.type# in (4,5,7,8,9,10,11,12,13,14,22,55,87) )
connect by nocycle prior dep.d_obj# = dep.p_obj#;
select
tab.owner table_owner
, tab.table_name table_name
, col.column_name column_name
, col.data_type type_name
, col.data_type_owner type_owner
from
dba_tables tab
, dba_tab_columns col
where tab.owner = x_user
and col.owner = tab.owner
and col.table_name = tab.table_name
and col.data_type_owner in -- User defined data type (UDT)
( select oracle_username
from SYSTEM.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C')
)
order by 1, 2, 3;
select syn.synonym_name ,
syn.table_owner ,
syn.table_name ,
syn.db_link
from dba_synonyms syn
where syn.owner='PUBLIC'
and syn.table_owner = x_user
and (syn.table_owner, syn.table_name) in
(select obj.owner, obj.object_name
from dba_objects obj
where obj.owner = syn.table_owner -- To avoid GSCC error
and obj.object_type in ('TYPE', 'PACKAGE', 'VIEW' ,
'SYNONYM','PROCEDURE',
'TRIGGER','FUNCTION'));
select owner, schema_url
from dba_xml_schemas
where owner =x_user;
select m.owner owner,
m.mview_name name
from dba_mviews m
where m.owner =x_user
and not exists
( select 'X'
from dba_views v
where v.owner = m.owner
and v.view_name = ad_zd_mview.get_mvq_name(m.mview_name) )
union
select v.owner owner,
ad_zd_mview.get_mv_name(v.view_name) name
from dba_views v
where v.view_name like '%'||'#'
and v.editioning_view = 'N'
and v.owner =x_user
and not exists
( select 'X' from dba_objects m
where m.owner = v.owner
and m.object_name = ad_zd_mview.get_mv_name(v.view_name)
and m.object_type = 'MATERIALIZED VIEW'
and m.status = 'VALID' );
select
tab.owner table_owner
, tab.table_name table_name
from dba_tables tab
where tab.owner = x_user
and tab.temporary = 'N'
and tab.secondary = 'N'
and not exists /* not a queue table */
( select qt.owner, qt.queue_table
from dba_queue_tables qt
where qt.owner = tab.owner
and qt.queue_table = tab.table_name )
and not exists /* not an MV container table */
( select mv.owner, mv.container_name
from dba_mviews mv
where mv.owner = tab.owner
and mv.container_name = tab.table_name )
order by tab.owner, tab.table_name;
select fou.oracle_username username
from system.fnd_oracle_userid fou
, dba_users du
where fou.read_only_flag in ('A','B', 'E', 'U', 'C')
and du.editions_enabled = 'N'
and du.username = fou.oracle_username
and not exists
(select u.name
from sys.registry$ r,
sys.user$ u
where r.status in (1,3,5)
and r.namespace = 'SERVER'
and r.schema# = u.user#
and u.name = du.username
union
select u.name
from sys.registry$ r,
sys.registry$schemas s,
sys.user$ u
where r.status in (1,3,5)
and r.namespace = 'SERVER'
and r.cid = s.cid
and s.schema# = u.user#
and u.name = du.username
);