Professional Web Applications Themes

Temporary tables visible to all sessions - Oracle Server

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. ...

  1. #1

    Default Temporary tables visible to all sessions

    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
    Konstantin Kudin Guest

  2. #2

    Default Re: Temporary tables visible to all sessions

    On 9 Jul 2003 13:18:57 -0700, [email]konstantin_kudin[/email] (Konstantin
    Kudin) wrote:
    > I am curious if it is possible to create a temporary table (in Oracle
    >8.1.7) that would be visible to all sessions.
    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
    Sybrand Bakker Guest

  3. #3

    Default Re: Temporary tables visible to all sessions

    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: [email]kennedy-down_with_spammersattbi.com[/email]
    with family. Remove the negative part, keep the minus sign. You can figure
    it out.
    "Konstantin Kudin" <konstantin_kudin> wrote in message
    news:ff88eb34.0307091218.373b70a3posting.google.c om...
    > 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

    Jim Kennedy Guest

  4. #4

    Default Re: Temporary tables visible to all sessions

    see notes below... your best bet is a redesign of your proposed process.
    "Konstantin Kudin" <konstantin_kudin> wrote in message
    news:ff88eb34.0307091218.373b70a3posting.google.c om...
    > 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.
    >
    how often are you going to be shutting down the database? How hard is it to
    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.
    > 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?
    >
    well this is a session level authentication right? why does it need to be
    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.
    > 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?
    put the tablespace in read only mode. put the tables you want to be writable
    in another tablespace that is not in read only mode.
    >
    > Thanks!
    >
    > Konstantin
    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.




    Ryan Guest

Similar Threads

  1. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  2. set integrity with temporary exception tables?
    By alederer in forum IBM DB2
    Replies: 3
    Last Post: September 5th, 08:42 PM
  3. Handling temporary tables in Informix
    By Suket in forum Informix
    Replies: 4
    Last Post: September 5th, 07:17 AM
  4. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  5. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139