Oracle Database Discussions

Good Morning,

I need some help. I need someone to explain to me what I am doing wrong. What I am trying to do is run a performance test for my supervisor that will explain the benefits of using the group temporary tablespaces as compared to one temp tablespace.

I did the following

I created a new temp table temp_test2 and assigned a user to it.

alter user ABC temporary tablespace temp_test2;

Using this command tells me that all other users on the system will still use the temp tablespace.

Next:

set timing on;

Now, wanting to use the least amount of memory for processing, I want to sort everything on disk, I did the following:

alter session set sort_area_size = 0;

alter session set sort_area_retained_size = 0;


To check to see if my user is assigned the temp_test2 tablespace I used the following:

select username, temporary_tablespace from dba_users where username = ‘ABC’;

It returns the right answer.

I also check for the default temp tablespace assigned assigned to the database.

select * from database_properties;

This returns the default temp tablespace.

You would think that everything is set up just right. But it doesn’t work judging from the following query when I run the 6 queries from 6 different sessions all set to the same above.

When I run this query to see how the sorts are doing I get the following returned. This query was run when I know I was the only one on the system. I checked with the appropriate query which is too long to type here.

select tablespace_name, current_user, used_extents, max_sort_size from v$sort_segment;

tablespace_name current_user used_extents, max_sort_size

temp 4 205 16559

tempgrp 0 0 0

This response tells me my queries are still using the temp tablespace even though I assigned user ABC that was in the query to temp_test2/

What happened to the temp_test2 temporary tablespace and why is it not being used?

Is there a command I didn’t use?

Can anyone help me out here?

Thanks in advance.

Confused.