Utilizing Separate Temp Tablespace

The Challenge: You want one process in your application to use a separate temp tablespace created on faster “ram disks” even though it executes the same packages on the same objects as the other applications.

The complication in the challenge is that TEMP is not like other resources such as default tablespace. Even though you might create a new user with a separate default temp tablespace, the database will allocate temp by the default tablespace of the owner of the package/objects involved. The object’s owner temp tablespace will then be used for all temp tables, hashing and sorting.

Now I’ve created a new user to utilize a new temp tablespace group I’ve striped across my new ram disks:

create user new_fast_user identified by “f4st_f0rwrd”
default tablespace users
temporary tablespace ram_temp;

To work around the package owner issue, you can still utilize the new user with the separate temp tablespace, but you will need to use invokers rights in the packages involved, (and if more than one package or other code)and add the authorization to use the current users defaults.

to perform this, you would add the following to the code involved:

CREATE OR REPLACE PACKAGE “INVOKER_PKG”
authid current_user <–This command invokes the current user’s default settings
AS
TYPE RefCurs IS REF CURSOR;
BEGIN
…
CREATE TABLE INVOKER_TEST_TAB AS
…

This command only needs to be before the declaration for the package, not in each individual procedure or function called in a package, btw.
Once you have all synonyms and grants in place for the new user, this is a simple way of allowing a specific user to have specific rights to a unique, performance enhancing temp tablespace.

You can test this by executing the pkg for the new user:

select a.username, a.sid, a.serial#, a.osuser, b.tablespace, round((b.blocks*32768)/(1024*1024*1024)) gigs, c.sql_text
from v$session a, v$tempseg_usage b, v$sqlarea c
where a.saddr = b.session_addr
and c.address(+)= a.sql_address
and c.hash_value(+) = a.sql_hash_value
order by b.tablespace, b.blocks desc;

before “authid current_user” is added:

NEW_FAST_USER 821 1907 oracle TEMP 27 create table invoker_test_tab

and then after with “authid current_user” added and recompiled in the package:

NEW_FAST_USER 821 1907 oracle RAM_TEMP 30 create table invoker_test_tab

We’re now using the NEW_FAST_USER’s temp tablespace and not the owner of the package’s temp tablespace!