dbms_session.unique_session_id for other sessions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Volker Hetzer - 30 Aug 2005 15:26 GMT

Hi!
We've got an application which runs concurrently,
So each app identifies its data by having the session id in one column.
(We can't use temp tables because we use oracle spatial.)
Now, sometimes that app dies and leaves data in the table.
It doesn't really matter result wise since each new instance cleans out
the tables using its own session id as key but nevertheless, I'd like
to avoid accumulations of data because session it's won't get reused
that frequently.

What I'd like is to get rid of that data. What I had in mind was a
cronjob which once per weekend figures out all current session ids and
throws out the rest of the data.

Can I do that? How could I find out the unique session ids for all currently
active sessions?

Lots of Greetings!
Volker

Reply to this Message

Mark D Powell - 30 Aug 2005 15:57 GMT

Volker, where is your application getting the session id from? Are you
using dbms_session.unique_session_id or v$session.sid?

There is no dynamic performance view that directly contains a list of
the unique_session_id in use for all existing sessions.

View v$session does on the other hand have a list of all current sid's.
However in theory sid's can be duplicated and it is the combination of
sid, serial# that provides uniqueness.

I have discovered that the unique session identified provied via
dbms_session can actually be converted to the sid and serial# in
v$session but the last portion of the number is a sequence and as far
as I know does not exist anywhere.

Nevertheless if you are using dbms_session.unique_session_id to create
your key then you can probably join to v$session on the sid, serial#
and not worry about the sequence portion.

But it might be easier to add a date/time column to the table and
remove data that is older than a session can be and run this task
daily.

HTH -- Mark D Powell --

Reply to this Message

Volker Hetzer - 30 Aug 2005 16:17 GMT

> Volker, where is your application getting the session id from? Are you
> using dbms_session.unique_session_id or v$session.sid?

dbms_session.unique_session_id
But I just solved it. The same package also has a function that takes such
an id and checks whether the session is alive. So I can use that.

Since there are never more than five or so sessions, this is feasible.

Lots of Greetings and Thanks nevertheless!
Volker

Reply to this Message

Mark D Powell - 30 Aug 2005 21:31 GMT

Volker, thanks for the reply. I never noticed the is_session_alive
procedure and I agree that is definitely the way you want to go since
you expect to find only a few entries to check at any one time.

-- Mark D Powell --

Reply to this Message