[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select oracle_username into l_schema
from system.fnd_oracle_userid
where read_only_flag ='U';
select oracle_username into l_schema
from system.fnd_oracle_userid
where read_only_flag ='E';
** Update LOGON trigger status.
X_STATUS - 'ENABLE' or 'DISABLE' the trigger
*/
procedure ALTER_LOGON_TRIGGER(X_STATUS varchar2)
is
C_MODULE varchar2(80) := c_package||'alter_logon_trigger';
select status into l_status
from dba_triggers
where owner='SYSTEM'
and trigger_name='EBS_LOGON';
select du.editions_enabled
into l_editions_enabled
from system.fnd_oracle_userid fou, dba_users du
where fou.read_only_flag = 'U'
and du.username = fou.oracle_username;
select property_value into l_default
from database_properties
where property_name = 'DEFAULT_EDITION';
select aed.edition_name into l_edition
from all_editions AED
where aed.parent_edition_name = l_default;
select aed.parent_edition_name into l_edition
from all_editions AED
where aed.edition_name = l_default;
select df.bytes allocated_bytes,
sum(fs.bytes) free_bytes
from dba_free_space fs,
(select sum(bytes) bytes
from dba_data_files
where tablespace_name = p_tablespace ) df
where fs.tablespace_name = p_tablespace
group by df.bytes;
select count(*) into v_count
from dba_data_files
where tablespace_name = ts_names(i)
and autoextensible = 'yes';
select
oe.created edition_date
, oe.object_name edition_name
, decode(co.object_type,
'TRIGGER', 1,
'PACKAGE BODY', 2,
'TYPE BODY', 3,
'PROCEDURE', 4,
'FUNCTION', 5,
'PACKAGE', 6,
'VIEW', 7,
'SYNONYM', 8,
'TYPE', 9,
/*other*/ 99 ) drop_order
, co.owner
, co.object_type
, co.object_name
from
( select
eusr.edition_name
, eusr.user_name owner
, obj.name object_name
, obj.type#
, decode(obj.type#,
4, 'VIEW',
5, 'SYNONYM',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
10, 'NON-EXISTENT',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY', 'ERROR') object_type
, obj.namespace namespace
, obj.obj# object_id
from
sys.obj$ obj
, ( select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$
where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
union
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type#=1 or user#=1) busr
, (select * from sys.obj$ where owner#=0 and type#=57) ed
where ed.name = 'ORA$BASE' ) eusr
where obj.owner# = eusr.user#
and obj.type# not in (10, 0, 88)
and obj.remoteowner is null ) co
, dba_objects_ae oe /* old edition */
, dba_objects_ae re /* run edition */
, database_properties run /* run edition name */
where run.property_name = 'DEFAULT_EDITION'
/* run edition */
and re.owner = 'SYS'
and re.object_type = 'EDITION'
and re.object_name = run.property_value
/* old edition */
and oe.owner = 'SYS'
and oe.object_type = 'EDITION'
and oe.created < re.created
/* covered object */
and co.edition_name = oe.object_name
and co.object_type in
('SYNONYM', 'VIEW',
'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY',
'PROCEDURE', 'FUNCTION', 'TRIGGER') /* editioned type */
and exists
( select null
from dba_objects_ae ro /* replacement object */
, dba_objects_ae ne /* newer edition */
where ro.owner = co.owner
and ro.object_type in (co.object_type, 'NON-EXISTENT')
and ro.object_name = co.object_name
and ro.namespace = co.namespace
and ro.edition_name = ne.object_name
and ne.owner = 'SYS'
and ne.object_type = 'EDITION'
and ne.created > oe.created
and ne.created <= re.created )
order by edition_date DESC, drop_order, co.owner, co.object_name;
select count(*)
into l_priv_count
from dba_tab_privs
where privilege = 'USE'
and owner = l_owner /* Added for GSCC error */
and table_name in ( select parent_edition_name from dba_editions);
select grantor into l_grantor
from dba_tab_privs
where privilege = 'USE'
and owner = l_owner /* Added for GSCC error */
and table_name = l_edition
and grantee = 'PUBLIC';
select oe.edition_name
from dba_editions oe, database_properties re
where re.property_name = 'DEFAULT_EDITION'
and oe.edition_name < re.property_value
order by oe.edition_name;
select 'N' into l_empty
from dba_objects_ae obj
where obj.edition_name = erec.edition_name
and obj.object_type <> 'NON-EXISTENT'
and rownum = 1;
select
'alter system kill session '||''''||s.sid||','||s.serial#||'''' kill
from
v$session s
, v$process p
, dba_objects_ae e
where s.type <> 'BACKGROUND'
and p.addr = s.paddr
and e.object_id = s.session_edition_id
and e.object_name = ad_zd.get_edition(x_edition_type)
and e.object_type = 'EDITION';