- 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;
- --该片段来自于http://www.codesnippet.cn/detail/060120131340.html
来源: http://www.codesnippet.cn/detail/060120131340.html