On a database housing an Oracle Text index there is a user LOADMSST who runs an application that executes ctx_ddl.sync_index, there is another user EDMAPPS who is used by applications that pose Text queries.
The table column on which the Oracle Text index is built is a clob.
The Oracle Text index has an indextype of ctxsys.context.
user LOADMSST has the database default temporary tablespace S_SORT as his temporary tablespace
user CTXSYS also has the database default temporary tablespace S_SORT as his temporary tablespace
user EDMAPPS has a different temporary tablespace of S_USERSORT
When the sync runs, I can see the S_SORT space being used by LOADMSST, as expected, in v$sort_usage.
When a user issues a query through EDMAPPS that involves a "contains" clause, the EDMAPPS user is shown accessing S_SORT rather than S_USERSORT in v$sort_usage.
Is there some functionality involving the Oracle Text index or the "contains" clause or the clob data that causes the Oracle database to force usage of the database default temporary tablespace S_SORT for EDMAPPS queries rather than the S_USERSORT temporary tablespace assigned to EDMAPPS?
I tried to replicate this but could not. Here's my test case
SQL> create user LOADMSST identified by LOADMSST quota unlimited on users;
User created.
SQL> grant create session, create table to LOADMSST;
Grant succeeded.
SQL> create user EDMAPPS identified by EDMAPPS temporary tablespace TEMP_OTHER quota unlimited on users;
User created.
SQL> grant create session, create table, select any table to EDMAPPS;
Grant succeeded.
SQL> grant alter session, select any dictionary to EDMAPPS;
Grant succeeded.
SQL> drop table LOADMSST.t purge;
Table dropped.
SQL> create table LOADMSST.t ( x int, y clob , z char(200));
Table created.
SQL> select username, temporary_tablespace
2 from dba_users
3 where username in ('CTXSYS','LOADMSST','EDMAPPS');
USERNAME TEMPORARY_TABLESPACE
-------------------- ------------------------------
CTXSYS TEMP
LOADMSST TEMP
EDMAPPS TEMP_OTHER
SQL> declare
2 c clob;
3 b bfile := bfilename('TEMP','asktom.demo');
4 begin
5
6 dbms_lob.fileopen( b );
7
8 for i in 1 .. 20000 loop
9 insert into LOADMSST.t values (i,empty_clob(), dbms_lob.substr(b,100,1))
10 returning y into c;
11 dbms_lob.loadfromfile( c, b, dbms_lob.getlength(b));
12
13 if mod(i,100) = 0 then commit; end if;
14 end loop;
15 dbms_lob.fileclose( b );
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> create index LOADMSST.t_ix on LOADMSST.t(y) indextype is ctxsys.context;
Index created.
SQL> conn EDMAPPS/EDMAPPS
Connected.
SQL> @smallsort
kilobytes 256
Setting to 262144
Session altered.
Session altered.
SQL> set autotrace traceonly stat
SQL> SELECT SCORE(1), x , y, z
2 from LOADMSST.t
3 WHERE CONTAINS(y, 'java', 1) > 0
4 ORDER BY 1,4,2;
20000 rows selected.
Statistics
----------------------------------------------------------
20698 recursive calls
44 db block gets
42728 consistent gets
41438 physical reads
3836 redo size
12250783 bytes sent via SQL*Net to client
5360552 bytes received via SQL*Net from client
40002 SQL*Net roundtrips to/from client
39 sorts (memory)
1 sorts (disk)
20000 rows processed
and whilst the above query was running, I ran this in another session
SQL> select username, tablespace, blocks from v$sort_usage where username is not null;
USERNAME TABLESPACE BLOCKS
-------------------- ------------------------------ ----------
EDMAPPS TEMP_OTHER 1024
So it seems to do what it's meant to be doing. Can you try the test case above on your db ?