temporary table duration
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: temporary table duration

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    175

    temporary table duration

    Hi,

    i use oracle 8.1.5 .

    i have a temporary table with the scope of session.
    (created with on commit preserve rows clause)

    from oracle's documentation the explanation
    for user_tables.duration is

    If temporary table, then duration is sys$session or
    sys$transaction, else NULL.

    i understand sys$transaction means all the rows
    will be deleted after a commit. that is the
    duration is just for a transaction.

    but in my case the rows still exist.

    below is the actual screen shot.

    is my understanding wrong? can anyone explain.



    SQL>
    SQL> select table_name, duration
    from user_tables
    where table_name='TEMP__INVOICES';

    TABLE_NAME DURATION
    ------------------------------ ---------------
    TEMP__INVOICES SYS$TRANSACTION



    SQL> SELECT * FROM TEMP__INVOICES;

    no rows selected



    SQL> insert into temp__invoices (invoicenumber) values (10);

    1 row created.



    SQL> SELECT INVOICENUMBER FROM TEMP__INVOICES;

    INVOICENUMBER
    -------------
    10



    SQL> COMMIT;

    Commit complete.



    SQL> SELECT INVOICENUMBER FROM TEMP__INVOICES;

    INVOICENUMBER
    -------------
    10

    SQL>


    with thanks,

    -Raja

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "on commit preserve rows" keeps them during the session.
    "on commit delete rows" keeps them only during a transaction.

    You asked for "on commit preserve rows" - that's what you got! They were still there after commit.

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    from oracle doc,
    If temporary table, then duration is sys$session or
    sys$transaction, else NULL.

    can you clarify,

    what is meant by sys$session?

    what is meant by sys$transaction.

    thank you,

    -Raja

  4. #4
    Join Date
    Aug 2002
    Posts
    115
    imh

    create global temporary table a1
    ( a2 number);-->session specific tables

    create global temporary table a1
    ( a2 number) on commit preserver/delete rows; -->transaction specific

    and the duration as NULL ones..are the permenant tables.

  5. #5
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    aspdba,
    i think you are incorrect.

    this is from oracle doc.

    DELETE ROWS
    specifies that the temporary table is transaction
    specific (this is the default). Oracle will truncate
    the table (delete all its rows) after each commit.


    PRESERVE ROWS
    specifies that the temporary table is session specific.
    Oracle will truncate the table (delete all its rows)
    when you terminate the session.

    -Raja

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by rajabalachandra
    from oracle doc,
    If temporary table, then duration is sys$session or
    sys$transaction, else NULL.
    This doesn't sound like Oracle doc - where did you find it?

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    oracle8i documentation -> static data dictionary views
    ->all_tables


    http://download-west.oracle.com/docs...52.htm#1303953

  9. #9
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    my question is :

    my table is behaving as session specific.
    (see my first post - screen shot)

    but my data dictionary view (user_tables.duration)
    show as sys$transaction.

    why such an inconsistency? or am i wrong?

    -Raja

  10. #10
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,

    another evidence. actual screen shot.

    1 create global temporary table test__temp1
    2* (name varchar2(10))
    SQL> /

    Table created.


    1 create global temporary table test__temp2
    2 (name varchar2(10))
    3* on commit preserve rows
    4 /

    Table created.


    SQL> select table_name, duration from user_tables where table_name like '%TEMP%';

    TABLE_NAME DURATION
    ------------------------------ ---------------
    TEST__TEMP1 SYS$TRANSACTION
    TEST__TEMP2 SYS$TRANSACTION


    first is transaction specific.

    second is session specific.

    but my view shows both as transaction specific.

    what went wrong?

    -Raja

Posting Permissions

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


Click Here to Expand Forum to Full Width