Solved

convert a temp table in oracle to global temporary table

Posted on 2010-11-23
16
524 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:mahjag
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 63 total points
ID: 34201477
simply drop your old table and then recreate it as a global temp with the same columns, triggers, contstraints, etc.

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 62 total points
ID: 34201479
Take the create table syntax out of the pacakge and from a sql prompt add create gloobal temporary table syntax to it.

Then it's just like any other table.  Only choice is on commit to preserve rows or not.

Syntax is in the online docs.
0
 

Author Comment

by:mahjag
ID: 34201543
Thanks for the postings - does it work for multiple sessions of user accsessing same data - currently I get unique constraint error on a table that boith users tyring to insert same data, how is that will work for global temp table -  I want to understand genreally how gt table works on multi user application and each user can submit same request to delete and insert recrods to same table.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34201557
Yes.  Only the person inserting the data can see it.  When the session ends, the data is deleted.
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 125 total points
ID: 34201571
temp table is user and session specific.

We make use of global temporary table when the process populates and do some operations within same session.

Make sure you are aware of the global tempoary table concepts before you proceed
0
 

Author Comment

by:mahjag
ID: 34205400
hi alexpert

We make use of global temporary table when the process populates and do some operations within same session.

can you elaborate
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 125 total points
ID: 34206029
You can make use of global temporaray table within same session for the logged on user.

But you cannot make use of global temporaty table when

 "2 users try use this process to run a request, the temp table does not work in  that scenario"

HTH
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34213102
2 uses can use a  global temp table at the same time
0
 

Author Comment

by:mahjag
ID: 34214870
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..
0
 

Author Comment

by:mahjag
ID: 34214873
how to control transaction of not deleting one user session data with another?
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34215232
This is separate question.  You may ask new question.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34215239
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.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34233632
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?

0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34233639
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34233700
"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/select 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
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34233732


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
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Distributed Transaction Lock Error ORA-01591 8 66
Row_number in SQL 6 45
how to double quote a string for an inline sql statement. 8 88
Read XML values 8 53
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question