Global temp table

[dynamicjourney2001]

Hi,

I see I can create a global temporary table in Oracle.
After using the global temp table, do i have to drop it or it automatically drops as soon as Im done with it? pls enlighten me on this one.(Im planning to use it just like the temp table in MSSQL7, to be called within the stored proc.)
THanks in advance and have fun...

 

[sem]

Temporary table contains temporary data, but its structure is permanent. You probably need a variable of some collection type.

Regards, Dima

 

[dsanchez2]

As sem said, the global temporary table itself is permanent, but the data store in it is not. The data in the table can either be transaction or session persistent. That is, the data is automatically deleted after either a commit/rollback or when a session ends. You specified which type of persistency you want when you create the table.

Also data inserted into the table is only available to the session that inserted it. Thus you can have multiple users/sessions using the table without getting in each others way.

 

[sybaseguru]

dynamicjourney2001,

Unfortunately you cannot use #tables as available in Sybase or MSSQL. In Sybase /MSSQL these #tables are created in tempdb and dropped automatically when you drop them yourself or when your sesion ends. Oracle provides a work around by allowing you to create a "global temporary table". Table will be permanent but the data is temporary (i.e. you will lose it on commit or exit). And as the guys pointed out mutiple users can use the same temp table. However, it is only a workaround by Oracle to mimic Sybase/MSSQL behaviour

 

[dbalearner]

sybaseGuru,

What do you mean by workaround? Are you implying that the Oracle aproach is not exactly to the point?

 

[sybaseguru]

dbalearner,

I do not want to start another turf war in here however every RDBMS has a number of indigenous strong points and some others created as add ons because other RDBMS vendors brought them up and they were good things. Temporary tables come into those categories. Sybase introduced them and handles them neatly. On the other hand ability to shrink unused tablespaces was introduced by Oracle and followed by others. It is to some extend fruitless for people like you and I to argue about one or other or who is better in the RDBMS wars. These days RDBMSs are more and less like soaps. The Enterprise Class RDBMSs are all good and reasonably fast and deliver the goods. What customer (as opposed to practitioners like you and I) wants is the add-ons and other bits and pieces that the customer gets for free when they purchase the database. I make a living in the Financial sector both out of Oracle and Sybase. Do I really care which one I use? Not really. I just have to accept that certain things can be done better in one than the other. Do I like them? Yes I like both because I feel comfortable with either of them. These days customers want a consultant who know from A-Z, excellent in both Oracle and Sybase and may be MSSQL. Knows UNIX inside out and feels comfortable with Windows Data centre etc besides one thousand and one other stuff like JAVA, Perl, networking..

 

[dbalearner]

sybaseguru,

Thanks for your enlightened answer. I know it may be off the topic but what is interesting is how you managed to learn both products and be good at them. I am a mmember of Sybase forum as well and I can see that you are the highest scorer there like both in Oracle 8 & 9 forums. Any tips for a learner like me? What is the secret.

regards

 

[sybaseguru]

dbalearner,

It is nearly midnight in London and I have to start early tomorrow in the City (City is the Finacial centre of London where all the investment banks are, assuming that you are not from Great Britain). I will try to give you a better answer tomorrow if I get a chance from work/meetings etc.

 

[sybaseguru]

Hi dbalearner,

To be honest with you I do not think stars matter. There are a lot of people in these three forums that I wished I knew as much as half of them. Anyway to be successful as an RDBMS consultant (DBA, developer, MIS whatever hat), you need to understand the way databases work and you need to know about the Relational Model. Coming from a developer background also helps. Knowing about your environment where Oracle running will be a must. For example understanding UNIX etc and good scripting skills. There is no substitute for experience and since an individual cannot experience everything in his/her lifetime, we have forums like this to share knowledge and discuss. So understanding and hearing about problems/bottlenecks will be very helpful. Try to understand know why as opposed to know how in solving problems. Also you need to develop a systematic way of going about solving problems.

Hope this helps and good luck with your learning curve!

 

[dbalearner]

Thanks sybaseGuru. I much appreciate your advice.

Regards,

Jim

 

[dynamicjourney2001]

Hey, thanks for the advise.
More power to all of you.
have fun!