pl/sql,dba-ebs常用

 g_sid v$session.SID%type := '-1';
 g_serial# v$session.SERIAL#%type;
 g_terminal v$session.TERMINAL%type;
 g_username v$session.username%type;

 function get_object_by_id(p_object_id in number) return varchar2 is
 l_object_name varchar2(1000);
 begin
 select owner || '.' || object_name
 into l_object_name
 from dba_objects
 where object_id = p_object_id;
 
 return l_object_name;
 exception
 when others then
 return p_object_id;
 end;

 function get_objects_locked_by_session(p_sid in number) return varchar2 is
 l_result varchar2(1000) := '';
 cursor locks is
 select get_object_by_id(id1) locked_object
 from v$lock
 where type = 'TM'
 and sid = p_sid;
 begin
 for c in locks loop
 if l_result is not null then
 l_result := l_result || chr(10);
 end if;
 l_result := l_result || c.locked_object;
 end loop;
 return l_result;
 end;

 procedure get_session_info(p_sid in number) is
 begin
 select serial#, terminal, username
 into g_serial#, g_terminal, g_username
 from v$session
 where sid = p_sid;
 end get_session_info;

 function get_session_serial#(p_sid in number) return v$session.SERIAL#%type is
 begin
 if p_sid != g_sid then
 get_session_info(p_sid);
 end if;
 return g_serial#;
 end get_session_serial#;

 function get_session_terminal(p_sid in number)
 return v$session.terminal%type is
 begin
 if p_sid != g_sid then
 get_session_info(p_sid);
 end if;
 return g_terminal;
 end get_session_terminal;

 function get_session_username(p_sid in number)
 return v$session.username%type is
 begin
 if p_sid != g_sid then
 get_session_info(p_sid);
 end if;
 return g_username;
 end get_session_username;

 function get_sids(p_process in varchar2) return varchar2 is
 cursor users_sids is
 select sid, module, program
 from v$session s
 where s.process = p_process
 or s.process like p_process || ':%';
 
 result varchar2(1000);
 begin
 result := '';
 for c in users_sids loop
 if c.program = 'JDBC Thin Client' then
 result := 'Web sessions';
 else
 if result is not null then
 result := result || chr(10);
 end if;
 result := result || to_char(c.sid) || ' ' ||
 nvl(c.module, 'Main Menu');
 end if;
 end loop;
 return result;
 end;
--還有很多 見附件 。。。