Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jesse.

Asked: June 28, 2002 - 7:07 am UTC

Last updated: August 02, 2013 - 5:34 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Thanks a lot for answering previous questions.
Appreciate if you could help in the following questions:

1. Is there a big difference between a global temporary table with on commit preserve rows and a global temporary table without on commit preserve rows in terms of performance wise?

2. Is it possible yo assign a specific rollback segment to an update statement?

3. If I use COPY command to copy a table from a db to another, it is copying the constraints of tables but with different name, why?

Thanks.


and Tom said...

1) nope, it is a matter of when the segment is truncated.

2) a transaction, not a statement -- yes. It is a horrible practice (and I love undo segments in 9i that just totally and 100% obsolete that statement forever)

3) the sqlplus copy command does not copy many constraints. Those that it does, it uses 'un-named' constraints (system generated). Why? I suppose because it would not otherwise be assured of not having a namespace conflct (consider what happens when you copy T to T2 in the same schema/database for example...)

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 28, 2002 - 9:27 am UTC

Thanks for the answer. But I have more doubts.

For Q1) from the manual,
a)default temporary table=transaction specific
b)on commit preserve rows=session specific

Does that mean the rows will be there if I don't log out for b) ?
What about a)? Do I still can see the rows after I insert into the table??

So will b) taken up more resources because the rows retain until the users log out?
Because we are facing problems in executing a procedure which is used by a lot of users in a 15 minutes interval.
It is usually very fast in executing this procedure but recently the execution just take very long time to complete....
What the procedure does is to insert into a few temporary tables and the front end program will read from this temporary tables to display the result on to the screen.



For Q3), does that mean export/import is still the best way to copy a schema into the other schema in different db?

Thanks.


Tom Kyte
June 28, 2002 - 10:06 am UTC

q1) it means the rows stay there for your session, when you log out, we truncate your segement.

If you NEED the rows to stay there, then you are not taking up more resources then necessary since you NEED THEM.

If you don't need the row to stay there for the session, but only for the transaction, then of course you would use on commit delete.

It is not a matter of resource utilization here, it is a simple matter of "what do you NEED"


You need to apply typical and traditional methods to diagnosing your issue. You are shooting in the dark trying to find cause and effect. That will never work.


You need to use tools such as tkprof, sql_trace, timed_statistics and statspack. If you have my book -- chapter 10 is for you. All of these tools are documented in the Oracle documentation as well.


for q3, it again depends on your needs. Need the constraints? exp/imp is one way. Having the CREATE SCRIPTS is yet another. And so on. You use the tool that best fits your needs. Me, I use both, sometimes I copy -- sometimes not.

global temporary tables

A reader, June 28, 2002 - 10:56 am UTC

Tom,

I am not sure how the oracle sessions are handled when there
is jdbc connection pools which are an open connections to the database which are shared among processes. what will happen to global temporary tables with on commit preserve rows in these case?

I am not even sure whether this is a valid question.

Thanks,

Tom Kyte
June 28, 2002 - 12:47 pm UTC

You could get hosed depending on how the connection pool actually handles connections.... You might not be able to use session based ones -- unless you grab the connection, keep it across calls and then "log off" -- physically end the session.


Global temp tables with connection pooling

Suubu, August 01, 2006 - 8:34 pm UTC

Tom,

I have a same question in regards to GTT with connection pooling.

Our application is calling a remote stored procedure (from different system via JDBC call. The interface maintaining this procedure is using Global temporary tables (session based, on commit preserve rows) to store the temporary results (some inserts/updates) before returning the status query resultset to us. connections are implemented using Connection Pools.

The issues are :

This interface is complaning that , the calling application (Ours) is not closing the connection in order to release the locks on that temp tables.

I went through many of your replies already, i want to clarify the following :

1. Our developers did check the code and verified that they close the connection/resultset etc. after this remote stored procedure call is completed, then why still these locks are not getting cleared on those temp tables (on downstream database ?

a) Is it because of the "session based GTT" approach where same session could be reused by "many sessions" ?

b) After "conn.close" issued from JDBC code, When does oracle release the locks on temp tables and release the temp space before serving the same connections for the next request ? Could you please explain.

c) Is "Conn.close" on Java side is equivalent to "exit" command on SQLPLUS on Oracle ?

d) Should i advise Downsteam system to use "on commit delete rows", i.e. transaction based GTT instead of session based due to "connection pool" ?

e) How do i find out whether GTT created was "session based or transaction based" querying from Data Dictionary ?

Tom Kyte
August 02, 2006 - 11:16 am UTC

connection pools likely, when you "close" your connection, you are not really closing anything, you are returning a connection to the pool for the next guy.

I cannot tell you if on commit delete rows will "work", but if you have a commit, there are not any "locks" on the gtt (they are private to a session in any case), so I'm wondering what locks they are talking about?

it is true they won't be able to do DDL on the gtt (there will be a lock to prevent that), but the gtt should be highly concurrent otherwise.

More info. on locking on gtt

Suubu, August 02, 2006 - 12:01 pm UTC

Tom,

Thanks for your response.
More locking info from Interfacing system OEM lock chart :

Following are the locks captured in OEM sent by downstream system :

User Name SID Lock Type Mode Held Mode Requested Object Name

<username> 80 TM ROW EXCLUSIVE NONE ISSUE_TEMP

<username> 80 TM ROW EXCLUSIVE NONE ORDER_STATUS_TEMP

<username> 80 TX EXCLUSIVE NONE


..... so on


Totally, OEM shows at that point 4 different sessions holding the row exclusive locks on those Global temporary tables (On commit preserve rows) and interfacing system is reporting that the resources on these temp tables (may be locks and their consumption) does not get released and asking our application to CLOSE the connection after each call of their stored procedure. But our JDBC client code does conn.close in our jdbc code.

As you also indicated that, "conn.close" just close the connection and return to the connection pool without releasing the temp segments (may be because of that, locks are persisting on that temp table).

Could you please advise how to advise the interfacing system to correct that. I do not see any issue on our end, other than closing the connection explicity (which is already there).


Another question :

In connection pool environment using "session based GTT", Say, if the very first session locks the GTT and finished the call, Will the lock gets released after some time or persistent locks will be there ? In case, the locks are persistent on the table, Can the next guy still able to reuse that session, remember, this session has already "row exclusive lock" on the temp table (but that old connection was returned to the connection pool).


Thank you.

Tom Kyte
August 02, 2006 - 12:42 pm UTC

those are ok, they would just prevent DDL.

why do they *care* if it is not causing any contention.


the only way to release this would be to use "transaction based global temporary tables" so when you commit or rollback they go "away"

THE GTT IS NOT LOCKED. You have a lock that protects the structure of the GTT, that is all.


Unless you clean out the contents of the table (using truncate or delete+commit) subsequent sessions might see the last sessions data which could be bad.

TKPROF

Elaine H, August 02, 2006 - 12:16 pm UTC

In this thread you refer the reviewer to Chapter 10 in Expert one on one. I only have Effective Oracle by Design and even in it, you refer to that same book.

Have you written about it in another book?

Tom Kyte
August 02, 2006 - 12:43 pm UTC

Using tkprof and reading raw trace files - not yet. My most comprehensive write up of that is in Expert One on One Oracle (which comes with Expert Oracle Database Architecture on CD-ROM)

A reader, August 02, 2006 - 6:14 pm UTC

<<quote>>
GTT IS NOT LOCKED. You have a lock that protects the structure of the GTT,
that is all.

Tom,

Does Oracle place this lock (for table protection) one per session ?

Tom Kyte
August 03, 2006 - 9:10 am UTC

it is based on open transactions - it would appear that you might well be missing a "commit" or "rollback" in your application

sid=80 has an open transaction. The TX lock shows that.

Internal view information for the temporary tables

A reader, July 23, 2008 - 6:07 pm UTC

Hi Tom,

What are the internal views that need to be used in order to be able to recreate the code to build a temporary table?
I checked the dba_tables and dba_objects but I can't find the internal view that has the details for options for the on commit part of the temp table configuration.

Thanks a lot for your help on this.
Tom Kyte
July 24, 2008 - 10:50 am UTC


ops$tkyte%ORA10GR2> drop table gtt;

Table dropped.

ops$tkyte%ORA10GR2> create global temporary table gtt ( x int );

Table created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'GTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT')
-------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "OPS$TKYTE"."GTT"
   (    "X" NUMBER(*,0)
   ) ON COMMIT DELETE ROWS



ops$tkyte%ORA10GR2> select duration from user_tables where table_name = 'GTT';

DURATION
---------------
SYS$TRANSACTION

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table gtt;

Table dropped.

ops$tkyte%ORA10GR2> create global temporary table gtt ( x int ) on commit preserve rows;

Table created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'GTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT')
-------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "OPS$TKYTE"."GTT"
   (    "X" NUMBER(*,0)
   ) ON COMMIT PRESERVE ROWS



ops$tkyte%ORA10GR2> select duration from user_tables where table_name = 'GTT';

DURATION
---------------
SYS$SESSION

One more Question with this

Sweta, June 03, 2010 - 3:15 am UTC

I have used global temp table structure in this flow
1) calling procedure
2)inserting data
3) commiting
4) End of procedure
5)displaying result on PHP page
but after commiting all the records has been deleted.
and zero records displayed on php page.
Can anybody give me some view for this?

Thanking you.
Tom Kyte
June 08, 2010 - 11:33 am UTC

do not do steps 2 and 3

Just return the result set, instead of inserting that data, just select it. I do not see any reason to use a temporary table here at all - I can only imagine you are a sqlserver developer by trade and are using Oracle for the first time and think "oracle = sqlserver, things are the same". They are not...

GTT in view,Index parts

Ramamoorthy.D, March 23, 2012 - 7:07 am UTC

Hi,
I am not cleared in GTT when used with view and index.If i created view for GTT,data's not appered in that view and also in index also.Then the purpose of GTT is select current session records only.?

what was the result of following code


create or replace procedure GTT_procedure
is

begin

CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
on commit preserver rows;


insert into gtt_zip1 values('6001','ram',sysdate);
insert into gtt_zip1 values('2001','ram',sysdate);

commit;

update gtt_zip1 set zip_code ='7001' ;

commit;

delete gtt_zip1 where zipcode ='2001';

commit;

end;
Tom Kyte
March 23, 2012 - 8:38 am UTC

a global temporary table is a session or transaction based thing, yes, that is exactly what it is designed and documented to be.


you do not create temporary tablespace in stored procedures.

Mechanism of Separation of Data

Snehasish Das, July 29, 2013 - 2:51 pm UTC

Hi Tom,

Can you please explain how the session specific records are protected from other sessions even though from same user.

i.e
User1 session 1 inserts 5 records in GTT TAB1.
User1 session 2 inserts 5 records in GTT TAB1.

But user1 session 1 sees its own data and session 2 sees its own data. How is this maintained by oracle.

Regards,
Snehasish Das
Tom Kyte
August 02, 2013 - 5:34 pm UTC

each has their own temporary segment. they do not know of the others segment (space allocated), they only see THEIR segment.