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.
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,
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 ?
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.
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?
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 ?
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.
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.
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;
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
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.