Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news-han1.dfn.de!news-stu1.dfn.de!news.germany.com!news.space.net!news.m-online.net!not-for-mail
From: Volker Hetzer <volker.hetzer@gmx.de>
Newsgroups: comp.databases.oracle.server
Subject: Re: ORA-00920 when using function returning boolean...
Date: Fri, 09 Sep 2005 23:47:00 +0200
Organization: (posted via) Mnet Telekommunikations GmbH
Lines: 28
Message-ID: <dfsvsj$ob8$1@svr12.m-online.net>
References: <dfsfra$65t$1@nntp.fujitsu-siemens.com> <1126291660.674221.300420@g14g2000cwa.googlegroups.com>
Reply-To:  volker.hetzer@ieee.org
NNTP-Posting-Host: 210.17-dial.augustakom.net
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: svr12.m-online.net 1126302419 24936 80.81.17.210 (9 Sep 2005 21:46:59 GMT)
X-Complaints-To: abuse@m-online.net
NNTP-Posting-Date: Fri, 9 Sep 2005 21:46:59 +0000 (UTC)
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
In-Reply-To: <1126291660.674221.300420@g14g2000cwa.googlegroups.com>
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251211

jkstill@gmail.com wrote:
> If you get this to work, it would be nice if you could post the
> solution here.
> 
> The DBMS_SESSION.IS_SESSION_ALIVE function requires an input of the
> form returned by the UNIQUE_SESSION_ID function.
> 
> UNIQUE_SESSION_ID does not take any arguments - it returns a value for
> the current session only.  The value returned does not appear to be
> available from v$session.
> 
> The IS_SESSION_ALIVE function appears to be somewhat useless, at least
> from the documentation I have been able to find.
Ok, point is this:
We use oracle spatial. That, in turn, uses objects.
Which the application cannot put into temporary tables because temporary
tables can't have spatial columns and indexes.
So, to make this application multi-user-able we use normal tables
and include one column with the session-id. That, as you have correctly
assumed is the one UNIQUE_SESSION_ID returned for each session.
Now, sometimes applications crash, leaving data in the spatial
tables. So, I'm going to do a little cronjob which cleans out
those tables once per day, probably midnight.
That job first gets all session id's and then deletes the data of
all non-active sessions.

Lots of Greetings!
Volker, hoping that one day temporary tables will be up to that job.