mahjag
asked on
convert a temp table in oracle to global temporary table
Hi
I have a temp table created in my oracle package (version 11) that takes the date parameter and then I delete some tables in my schema based on those dates from temp table and also do a immediate insert records the same tables with new set of data, it is always fine in a single user session running the process but I find it is a problem when 2 users try use this process to run a request, the temp table does not work in that scenario, how woulod I convert my temp table to global temp table, it is a oracle pacakge code with procedues and functions for insert and delete data.
I have a temp table created in my oracle package (version 11) that takes the date parameter and then I delete some tables in my schema based on those dates from temp table and also do a immediate insert records the same tables with new set of data, it is always fine in a single user session running the process but I find it is a problem when 2 users try use this process to run a request, the temp table does not work in that scenario, how woulod I convert my temp table to global temp table, it is a oracle pacakge code with procedues and functions for insert and delete data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes. Only the person inserting the data can see it. When the session ends, the data is deleted.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi alexpert
We make use of global temporary table when the process populates and do some operations within same session.
can you elaborate
We make use of global temporary table when the process populates and do some operations within same session.
can you elaborate
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2 uses can use a global temp table at the same time
ASKER
Hi Akexpert - I awarded points for the response - I wanted to find more details as to why we cannot use gloabl temp table for above case - I want to understand what I can use so that one process cannot insert the data without delete happen to the same session or diff session by actually locking the transaction - let me know..
ASKER
how to control transaction of not deleting one user session data with another?
This is separate question. You may ask new question.
Might be seperate but close enough.
You don't need to delete from a GTT. Once the session ends, Oracle does it for you.
I suggest the online docs for how they work.
You don't need to delete from a GTT. Once the session ends, Oracle does it for you.
I suggest the online docs for how they work.
Hi Sean,
Could you please explain with example?
"2 uses can use a global temp table at the same time"
Since 2 users will have separate sessions how is it possible?
Could you please explain with example?
"2 uses can use a global temp table at the same time"
Since 2 users will have separate sessions how is it possible?
Here are the online docs for GTT
In breif:
http://www.oracle-base.com/articles/8i/TemporaryTables.php
Detailed explaination:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm
In breif:
http://www.oracle-base.com/articles/8i/TemporaryTables.php
Detailed explaination:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm
"Since 2 users will have separate sessions how is it possible?"
That's exactly how it's possible.
each session has its own version of the table.
If you and I are both logged in to the same database we can both insert/update/delete/selec t from our own temp table. We can't share data from our respective temp tables, but that's the whole point. We each get our own private workspace that lasts only for the duration of our session or transaction if configured to "ON COMMIT DELETE ROWS" instead of PRESERVE ROWS
That's exactly how it's possible.
each session has its own version of the table.
If you and I are both logged in to the same database we can both insert/update/delete/selec
Yes Sean, I concur with you.
I some how got confused and thought your reply may be pointing to sharing of data between two users in GTT
Thanks Sean
ASKER