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

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

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

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.

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

Quote:

---

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?

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

oracle8i documentation -> static data dictionary views
->all_tables


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

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

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

Raja, I do appologise - I totally mis-read your first post. (Having trouble doing three things at once.)

It's a bug - it looks OK to me in 8.1.7:
http://metalink.oracle.com/metalink/...UG&p_id=932236

it's alright.

it seems to be a bug in data dictionary views scripts.

thank you DaPi.

-Raja