Re: Session ID

From: Karlis Ogsts <ogsts_at_cclu.lv>
Date: 1997/11/12
Message-ID: <3469d1e4.15836936@news.latnet.lv>#1/1

On Tue, 11 Nov 1997 16:09:31 +0000, Pepe Galindo <ppgg_at_lcc.uma.es> wrote:

>Hello people!
>
>I have some questions. I hope somebody answer me. I will be grateful if he
>answerd me by e-mail too. Thanks.
>
>1. What number/s identify a Oracle session.
>2. How can a normal user (not dba) know that numbers?
> If it is impossible then:
> How can I construct a PL/SQL procedure to do that?
>
>I think that to identify a session are necessary 2 numbers: SID and
>SERIAL# from table V$SESSION.
>
>How can a user know that numbers?
>V$SESSION is a dba table. Then I can construct a PL/SQL procedure with SYS
>as owner:
>
>PROCEDURE SESSIONID (SID OUT NUMBER,
> SERIAL# OUT NUMBER) IS
> Usuario ALL_TABLES.OWNER%TYPE;
>BEGIN
> SELECT USER INTO Usuario FROM DUAL;
> SELECT SID,SERIAL# INTO SID,SERIAL# FROM V$SESSION
> WHERE USERNAME=Usuario AND STATUS='ACTIVE';
>END SESSIONID;
>
>One problem arise when exist two 'ACTIVE' sessions with the same USERNAME.
>Then the oracle error is:
>ORA-01422: exact fetch returns more than requested number of rows
>
>How can I access to V$SESSION table to read this two values for a only one
>session??
>
>The last question: Do somebody know a oracle news in spanish language?
>
>Thank you very much.
>ppgg_at_lcc.uma.es
>

function GetSID(lsid in out number,lserial in out number) return boolean is

	source_cursor INTEGER;
	rows_processed INTEGER;

begin

---

source_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,'select sid

        from v$mystat where rownum=1',dbms_sql.v7); dbms_sql.define_column(source_cursor,1,lsid); rows_processed:=dbms_sql.execute(source_cursor); if dbms_sql.fetch_rows(source_cursor)<=0 then

	dbms_sql.close_cursor(source_cursor);
	return FALSE;

end if;
dbms_sql.column_value(source_cursor,1,lsid); dbms_sql.close_cursor(source_cursor);

---

source_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,'select serial#

        from v$session where sid = :lsid',dbms_sql.v7);

dbms_sql.bind_variable(source_cursor,'lsid',lsid);
dbms_sql.define_column(source_cursor,1,lserial);
rows_processed:=dbms_sql.execute(source_cursor);

if dbms_sql.fetch_rows(source_cursor)<=0 then
	dbms_sql.close_cursor(source_cursor);
	return FALSE;

end if;
dbms_sql.column_value(source_cursor,1,lserial); dbms_sql.close_cursor(source_cursor);

---

return TRUE;
exception when others then

	if dbms_sql.is_open(source_cursor) then
		dbms_sql.close_cursor(source_cursor);
	end if;
	return FALSE;

end; Received on Wed Nov 12 1997 - 00:00:00 CST