Global Temporary Taable

  • Permalink
  • submit to reddit
  • Email
  • Follow


I am using a global temporary table with "on commit preserve rows" in
order to pass a ref cursor back to the calling program.  The data is
returning fine.  However, I want to drop the table after the cursor is
returned.  If I insert a drop table statment inside of the procedure I
am getting the following error:

ORA-14452: attempt to create, alter or drop an index on temporary
table already in use.  If I close the session and go through TOAD I
can see the table but still can't drop it.  I get the same error.

I've researched via the internet and understand in a perfect
environment a "real" table is the way to go.  However, receiving
strong pushback from the customer.

Thanks For your help
0
Reply neil2391 (1) 6/25/2004 5:47:08 PM

See related articles to this posting


On 25 Jun 2004 10:47:08 -0700, neil@blueslate.net (Neil) wrote:

>I've researched via the internet and understand in a perfect
>environment a "real" table is the way to go

In a 'perfect' environment you don't need permanent temporary tables.
Fix your database design and/or your queries.


--
Sybrand Bakker, Senior Oracle DBA
0
Reply gooiditweg2 (295) 6/25/2004 6:30:17 PM

Neil wrote:

> However, I want to drop the table after the cursor is
> returned.

Please explain WHY you want to DROP the table.  

If I understand Oracle's intent properly, GTTs are intended to have a
permanent definition, supplied by the DBA ideally ONCE in the life of the
database).  The term TEMP is relative to the data, not the definition.

Without understanding your requirements, to me it appears that your design
is fundementally in conflict with the intent and implementation of temp
tables.

/Hans
0
Reply forbrich (430) 6/25/2004 6:46:51 PM

Neil wrote:
> 
> I am using a global temporary table with "on commit preserve rows" in
> order to pass a ref cursor back to the calling program.  The data is
> returning fine.  However, I want to drop the table after the cursor is
> returned.  If I insert a drop table statment inside of the procedure I
> am getting the following error:
> 
> ORA-14452: attempt to create, alter or drop an index on temporary
> table already in use.  If I close the session and go through TOAD I
> can see the table but still can't drop it.  I get the same error.
> 
> I've researched via the internet and understand in a perfect
> environment a "real" table is the way to go.  However, receiving
> strong pushback from the customer.
> 
> Thanks For your help

The term "global temporary table" is used because 

a) the table is permanently defined (hence the term "temporary")
b) the data is local to your session (hence the term "local")

:-)

Seriously though, a couple of things 

i) you don't need a temp table to pass a ref cursor around.  ref cursor
point to a resultset which does not need to "exist" as a temporary table

ii) if you are heading down the gtt route - you just populate it, use
the data, and then forget about the data.  It vanishes as soon as you
exit the session.  

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------
0
Reply connor_mcdonald (351) 6/26/2004 2:30:56 AM

On 25 Jun 2004, neil@blueslate.net wrote:
> I am using a global temporary table with "on commit preserve
> rows" in order to pass a ref cursor back to the calling
> program.  The data is returning fine.  However, I want to drop
> the table after the cursor is returned.  If I insert a drop
> table statment inside of the procedure I am getting the
> following error:
> 
> ORA-14452: attempt to create, alter or drop an index on
> temporary table already in use.  If I close the session and go
> through TOAD I can see the table but still can't drop it.  I
> get the same error.
> 
> I've researched via the internet and understand in a perfect
> environment a "real" table is the way to go.  However,
> receiving strong pushback from the customer.

Just stop dropping the table.  Leave it there.  GTTs are used for
session-based data but they aren't to be constantly dropped.
They are tables, but their data is temporary.

If you have a connection pool, then you will need to clear it out
every call.

-- 
Galen Boyer
0
Reply galenboyer (300) 6/26/2004 4:17:04 AM
comp.databases.oracle.server 21684 articles. 10 followers. Post

4 Replies
190 Views

Similar Articles

[PageSpeed] 26


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

global or $GLOBALS
Hi! Just two short questions..: 1) In a function I want to get the value of a global variable declared outside the function. Should I use "global $variable" or $GLOBALS[variable]? Are there any differences in performance or other aspects which should make me choose the one thing or the other? 2) Generally, is it better to pass arguments to the function instead? Ole J ojorus wrote: > Hi! > Just two short questions..: Hi, > > 1) In a function I want to get the value of a global variable declared > outside the function. Should I use "global $variable&qu...

Global Temporary Table
Hello Oraclegurus, I would like to know if i am doing the right thing or not?? I've created a global temporary table "tempcomp". "create global temporary table tempcomp (col1 number,col2 char,col3varchar2)" . I looked into these groups and it was mentioned somewhere that default is "on commit delete rows"... I am assuming that the rows are being deleted after commit and not stored . We have a web application which would be calling a stored procedure which inserts into a global temporary some values we nee...

Global Temporary Tables
Each, Am wanting to create a temporary table within a PLSQL package. Have used EXECUTE IMMEDIATE 'create global temporary table temp_status........' Problem now is that package wont compile due to insert statements for this table. Compiler has no idea about this temp table or its structure. I take it temporary tables arent designed for this use are am i missing something here? Cheers Roy Munson wrote: > Each, > > Am wanting to create a temporary table within a PLSQL package. Have > used > > EXECUTE IMMEDIATE 'create global temporary table temp_status.........

Global temporary table
Hi all! I dont know how to declare and use a global temporary table in my java stored procedure.... I used these statements: sql="declare global temporary table session.temp(recordinteger,eventtype character(20)) not logged"; Statement st=con.createStatement(); st.execute(sql); For inserting I'm trying to do.. sql="insert into session.temp values(2003,'fatal error'); st.executeUpdate(sql); But its flagging an error.With just the declare statement there is no error.But i read somewhere that a temporary table space needs to be created.I dont kn...

global temporary tables?
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record when the session ends Update activity on this table is going to be pretty intense, and the transient nature of the data makes it a good candidate for a temporary and/or in-memory table. Unfortunately, different network connections may be updating the same session record at different times (connection pool), so the CREATE TABLE TEMPORARY doesn't seem to fit the bill, since i...

Global Temporary Tables
Hi, I am inserting records to a global temporary table(say A) in which a trigger is fired and im getting results from other global tempory tables(say B, C, D,..)... This is my scenario... I am able to fetch the results from the global temporary tables B,C,D,...when inserting directly in the table A from the front end. But I am not able to fetch the results from the global temporary tables B,C,D,...when inserting thru a web service (in dotnet)in the table A from the front end. Can somebody explain the reson for the above and give a solution to fetch the records from the global ...

global a b != global a; global b in this circumstance
I had to quickly hack a script and came across a "gotcha" that wasn't obvious because of a well-placed catch. Here is a script that shows the problem: #---------------------------- set x 1 set y1 "y1" proc ok {} { global x global y$x puts "$x [set y$x]" } proc fail {} { global x y$x puts "$x [set y$x]" } ok fail #---------------------------- Regards E Wilson ewilsonmail@gmail.com wrote: > I had to quickly hack a script and came across a "gotcha" that wasn't > obvious because of a well-placed catch. > > Here...

global, globals(), _global ?
Using global variables in Python often raises chaos. Other languages use a clear prefix for globals. * you forget to declare a global * or you declare a global too much or in conflict * you have a local identical variable name and want to save/load it to/from the global with same name * while you add code, the definition of globals moves more and more apart from their use cases -> weirdness; programmers thinking is fragmented * using globals()['xy'] is 'stringy non-program-code' Thus, since long time at the head of my bigger modules I often put... _global = sys.modul...

Global temporary tables performance
Hello, I'm trying to use a global temporary tables with a bulk insert, and the performance for the temporary table is about 5 times LESS than those of a real table - shouldent it be the opposite way? Here is my example: -- Create real table and a temporary one create table realTable( id number(15) ); create global temporary table tmpTable(id number(15)) ON COMMIT DELETE ROWS; -- Insert 50000 records with bulk operation -- into the rea table and the temporary one, and getting the time it took declare tableSize constant number := 50000; type ty_numTable is varray (50000) of number(15)...

Global variable is not so global
For ivestigations of the "global behaviors" of the so called "global variable" , I tried the follwoing codes in ARC xlisp-stat. > (defvar *t* 100) *T* > (let ((*t* 10))) NIL > *t* 100 > You have already seen that the global variable *t* didn't change value after the binding by the let form. So , global ? I'm afraid not so gloabal ! newser.bbs@bbs.ee.ncu.edu.tw wrote: > For ivestigations of the "global behaviors" of the so called "global > variable" , I tried the follwoing codes in ARC xlisp-stat. You know, Lisp isn't s...

globals and super globals
Hi, I'm looking for the best way to deal with globals in PHP. As a 'C' software developer, I would normally avoid all globals and not have any at all, but use structs and pass everything in the function parameters... However, being realistic, I can see that globals can (and do ?) have a place in PHP web scripts. As I see it, there are two approaches: 1) go with globals, 2) avoid globals. Looking at (1) ============== Sure, I can just put a 'global $a' at the start of every function that requires the use of the global '$a', but its an easy mistake to forge...

Global temporary table and SP
Hello all, I'm using SS2K on W2K. Brieffing: Many months ago, I created a stored procedure only used by those with admin rights in SS. Now, someone else (without admin rights) has to run it. I gave him rigth to execute the SP but, at the second and more execution, he got a error message concerning a temp table already existing (see further). The SP: ------------------------------------------------------ CREATE PROCEDURE MySP @Type INT DECLARE @strSQL AS VARCHAR(4000) IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE table_name = '##MyTmpTable&#...

Global Temporary Table #2
Hello, We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0) I'm facing a problem with a global temporary table (on commit preserve rows) : it is going through the loop : - delete all - insert - update At the beginning of the procedure everything is going fine. I can see how the process is going on in v$session. After half an hour, the speed begins to decrease a lot and there are lot of "db file scattered read" events pointing to my temporary table. It is true that there are lots of FTS on this temporary table, but its content is very small (about 20 rows at each step of t...

Load global temporary table
Hi all, I have a fixedwidth .DAT file and a DB2 table, I have to create a flat file by pulling data from both file and DB2 table. I tried using IMPORT/LOAD utilities to load to a global temporary table and thought of joining it and extracting, but IMPORT/LOAD are not allowed on temporary table. Can you please suggest me how to go about it... thanks in advance, kore Can you Import it to a real TABLE, and then export with a join? B. As per my requirements I am not supposed to use a real table. Please suggest me..... vijay_dwi@yahoo.com wrote: > As per my requ...

Problem with Global Temporary Table
I am creating a procedure A which is creating a global temporary table DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2), CustomerServiceTypeId INTEGER) WITH REPLACE ON COMMIT PRESERVE ROWS; I am able to compile the proceudre. But when I try to compile procedure B which is referencing the temporary table in procedure A, I get the error. "SESSION.TEMP" is an undefined name. In procedure B all I am trying to do is INSERT INTO session.temp VALUES ('MS', 0); Can some one please help chettiar wrote: > I am creating a procedure A which ...

EXPLAIN and Global Temporary Tables
I have my developers explaining the stored procedures that they write using visual studio. These stored procedures are for DB2 os390. In one case, one of the developers has defined a global temporary table that he uses to put the input parms into and then uses it to join against to create a dataset. When he runs explain (both visual explain, and CA's explain) it chokes on the SESSION.TABLE saying that it doesn't exist. Is there anyway I can have them explain these when they have global temporary tables defined? ...

GLOBAL TEMPORARY TABLE Efficiency ?
If I create one of these using DECLARE (not cataloged) and insert and then delete no more than 20 256-byte rows again and again, and if this would replace several overlapping legacy SELECTs, will I see run-time improvement ? I have heard from others that GTTs do not perform well. Here, I would expect a net reduction on physical I/O. But might there be an offsetting inefficiency otherwise ? Thanks, Walter Rue WalterR wrote: > If I create one of these using DECLARE (not cataloged) and insert and > then delete no more than 20 256-byte rows again and again, and if this...

global temporary table disappears??
HP-UX 11.11 Ingres 2.6/0305 I'm using a script that creates several global temporary tables. Among other temporary tables created, part of the process creates one table and then creates a second table from some of the data from the previous one. I then do an update to the second one from the first with some additional data; an exercise in getting a unique set of data :-(. The update to the second table complains with the "tuple at tid already deleted" error. And then the next statement that tries to update a different table from the second table complains th...

creating global temporary tables
Can you please advice on how to create global temporary tables in informix 9.1, so that it can be accessed across procedures? Rishi wrote: > Can you please advice on how to create global temporary tables in > informix 9.1, so that it can be accessed across procedures? You should have abandoned IDS 9.1x prior to 2000-01-01. What do you mean by 'across procedures'? Did you read the SQL manual on CREATE TABLE - the section on CREATE TEMP TABLE? The name 'global temporary table' is SQL standard speak and doesn't have a direct counter-part in IDS. I...

Global temporary tables surprise
I created a global temporary table, using the following syntax: [mgogala@medo tmp]$ psql scott Timing is on. psql (8.4.4) Type "help" for help. scott=# create global temporary table t_emp scott-# on commit preserve rows scott-# as select * from emp; SELECT Time: 127.086 ms scott=# commit; WARNING: there is no transaction in progress COMMIT Time: 0.353 ms scott=# select * from t_emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+---------------------+------+------ +-------- 7369 | SMITH |...

global temporary table and connection pooling
The environment I'm running is apache/tomcat and oracle 816. I'm using global temporary tablespaces in certain cases( they were created using on commit preserve rows option as it is a multipart query), and then the table is truncated after the last query has executed. I've noticed that occasionally if I run two independent queries using 2 different sessions, and the second query accesses the temp table before the first session has truncated it, I'm seeing results from the first session. I thought that the global temporary tables were supposed to be restricted to the session. Do...

output global optimization temporary solution
Dear all, I am using the global optimization toolbox. And I have been waiting the output for two days. Would it be any method that I can ask the toolbox to output the temporary best solution? (Or hourly report) ** It does not need for the current run, I am asking whether I can write a couple lines of code to do this next time. Thanks On 10/5/2011 7:36 PM, Marco Wu wrote: > Dear all, > > I am using the global optimization toolbox. And I have been waiting the > output for two days. Would it be any method that I can ask the toolbox > to output the temporary best solution?...

DECLARE GLOBAL TEMPORARY TABLE problem
A bit longwinded; apologies in advance. My client has two large iSeries machines, both at V5R2 (they may be logical partitions of the same physical machine; I'm not sure.) The DECLARE GLOBAL TEMPORARY TABLE statement in SQL behaves a little differently on the two machines, causing me problems. I am issuing the statement in the Run SQL Scripts window of the navigator; *SQL naming is in effect (this matters.) On the development machine, the table created is owned by the group profile to which my user profile belongs; there is no *GROUP authority generated, and *PUBLIC authority ...

Global Temporary Table performance with inserts.
I am using an Oracle Temporary table to generate a tree of data dependencies. I noticed that Inserts are significantly longer into the Temporary table than if I replaced the Global Temporary Table with a permanent table (just as a test case), almost by a factor of 50. The number of rows being inserted are approx. 100. Has any one experienced this issue and what are the workarounds? I am using Oracle 8.1.7 and have created the Global Temporary Table with "on commit preserve rows" clause. Thank you for your help in advance. Hector wrote: > I am using an Oracle Temporary table to g...