Hi,

I am curious if it is possible to create a temporary table (in Oracle
8.1.7) that would be visible to all sessions. Then its data would go
away when the database is shutdown. Hopefully, due to its temporary
nature such a table would also be writable in read-only mode.

A possible usage would be to keep track of web users when each user
is issued an ID on web logon. The ID is passed back and forth as a
cookie or a hidden form field to authenticate the user. Stored
procedures would validate legitimacy of requests by the ID. The IDs
would be stored in an all session visible temporary table.
Hypothetically if the database is opened in read-only mode, the users
can still login from the web, get IDs, and do at least some things
since the table with IDs is hopefully writable. Is this possible or is
there a better way?

I know that Oracle has "global temporary" tables, which are either
session-specific or transaction-specific. Are these writable in
read-only mode? There is a way to make sure that Oracle can sort in
read-only mode and thus perform writes to a temporary tablespace.
Would the same mechanism work with "global temporary" tables thus
making them writable, or not?

Thanks!

Konstantin

On 9 Jul 2003 13:18:57 -0700, konstantin_kudin (AT) yahoo (DOT) com (Konstantin
Kudin) wrote:

It isn't. Please read up the definition of globally temporary table.
I'm sure additional Oracle packages have mechanisms to handle cookies,
just visit otn.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Not sure how something can be writable in read only mode. Seems like a
contradition. v$session already tracks each user, you could do that. Read
up on application context.
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Konstantin Kudin" <konstantin_kudin (AT) yahoo (DOT) com> wrote

see notes below... your best bet is a redesign of your proposed process.
"Konstantin Kudin" <konstantin_kudin (AT) yahoo (DOT) com> wrote

just drop the tables when the database shutsdown? just create a script that
drops the tables and then shuts the database down???? do you mean the data
gets deleted the USER logs off, but is visible to everyone? if so just use a
table and use a logoff trigger to delete the data.

visible to all sessions? If its authentication dont you want it to be
secure.

now by 'validate legitimacy' if its for queries you can use the Virtual
Private database. It just appends a where clause to your sql statements...
very simple to use. no overhead. no bother.

if you mean only certain users can perform DML, then use roles. Alot of this
'validation' sounds like it should be application layer logic. are you using
html? not sure how hard it is to implement business logic using that, but if
its a more robust front end, it should be easy.

in another tablespace that is not in read only mode.

you really should drop this and redesign your process. sounds like you did
something similiar to this with another tool and want to twist oracle to do
it that way. bad idea. each tool has its own pros and cons.

Sounds like a simple validation model right? ie, certain users can do
certain things right? use VPD and roles.
its pretty easy. now are you saying that users can 'enter passwords' to gain
access to certain things... lots of ways to do that, and it sounds like you
only want a limited number of accounts.