-
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
-
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?
-
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|