Oracle Scratchpad

February 6, 2011

GTTs

Filed under: Infrastructure — Jonathan Lewis @ 6:51 pm BST Feb 6,2011

That’s Global Temporary Table, of course.

A request appeared on the OTN database forum recently asking if it was possible to allocate a global temporary table to a particular temporary tablespace because the programmer didn’t want typical users to be at risk of running out of temporary space when a particular global temporary table suddenly got loaded with millions of rows.

If you’re running 11.1 or above, go straight to the footnote, otherwise read on.

At first sight the answer seems to be no – but an important point in the question was the comment: “I have a procedure which …”. This may make it possible to isolate this table in the required way.

When someone inserts data into a global temporary table the tablespace used to instantiate their version of the table is the temporary tablespace of the schema that is inserting the data. But remember, if you call a procedure to insert the data it’s (usually) the schema that owns the procedure that is doing the insert. (I say “usually” because you could, of course, have a procedure defined as “authid current_user”)

Consider the following (running on 11.1.0.6):

select 
	username, temporary_tablespace 
from 
	dba_users 
where 
	username in ('U1','TEST_USER')
;

USERNAME        TEMPORARY_TABLESPACE
--------------- --------------------
U1              TEMP
TEST_USER       TEMP2

I have two users with different temporary tablespaces. In schema test_user we do the following:

create global temporary table t 
as 
select	* 
from	all_objects 
where	rownum = 0;

grant all on t to u1;

create or replace procedure pt
as
begin
	insert into t select * from all_objects where rownum <= 1000;
end;
/

grant execute on pt to u1;

Now we connect as user u1 and run the following:


SQL> insert into test_user.t select * from all_objects where rownum < = 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> execute test_user.pt

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

The first commit it very important, by the way. In another session logged in through a suitably privileged account, execute the following before each commit:

select username, tablespace from v$sort_usage;

The results after the direct call to insert into the GTT will be:

USERNAME TABLESPACE
-------- ----------------
U1       TEMP

The results from the call to the procedure will be:

USERNAME TABLESPACE
-------- ----------------
U1       TEMP2

The direct insert has used the temporary tablespace of schema U1, but the insert from the procedure call has used the temporary tablespace of schema test_user. So there are ways to allocate a global temporary table to a particularly temporary tablespace if you always use a procedure to populate it. I did point out, though, that the first commit in the demonstration was important. If you don’t do this commit the global temporary table will have been instantiated in the “wrong” temporary tablespace at the point you call the procedure, so the data from the procedure call would be added to the existing data – i.e. where you didn’t want it to go.

Footnote: Everything changes – you’ll see in a response to the thread from Uwe Hesse that from 11g onwards you can specify a tablespace for a global temporary tablespace, and that’s where the data will go. (But I’d drafted this note before I got back to the thread – and it’s still valid for earlier versions of Oracle.)

Footnote 2: I had to create a spare temporary tablespace for this demo. It’s funny little detail that although you: “create TEMPORARY tablespace … TEMPfile…”, when it comes to dropping it’s: “drop tablespace … including … datafiles”.

4 Comments »

  1. Decent workarounds. But the lack of alter session set temporary tablespace
    to any of n temporary tablespaces to which you have quota remains one the most frustrating (and one would think easy) circa 1990 enhancement requests unfulfilled.

    Comment by Mark W. Farnham — February 6, 2011 @ 9:44 pm BST Feb 6,2011 | Reply

    • Mark,

      Now you mention it, it does seem like an obvious (and deceptively simple) idea.

      Another defect I find with the temporary stuff that seems easy to fix is that GTTs have a default pctfree of 10% – and you can’t change it.

      Comment by Jonathan Lewis — February 7, 2011 @ 9:28 pm BST Feb 7,2011 | Reply

  2. [blockquote]because the programmer didn’t want typical users to be at risk of running out of temporary space when a particular global temporary table suddenly got loaded with millions of rows[/blockquote]
    Resumable space allocation can help.

    Comment by Timur Akhmadeev — February 7, 2011 @ 8:10 am BST Feb 7,2011 | Reply

  3. Timur,

    Nice idea – and worth looking at; there are two thoughts I’d want to chase up.

    Although it might stop other sessions from failing (for the resumable_timeout, at least) it will cause those sessions to suspend, possibly for an unacceptable amoint of time.
    The other thought (which I expressed in 2004 http://www.freelists.org/post/oracle-l/Why-not-set-all-sessions-to-resumable,2 and may not have looked at since) is that there may be a single point on contention introduced by the sessions that have to keep updating their registration in the resumable operaton dynamic performance view.

    Comment by Jonathan Lewis — February 7, 2011 @ 9:20 pm BST Feb 7,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.