GTTs

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”.