The client complain the usage of TEMP tablespace is high.

1. Check the utilization of temp tablespace.

Current temp space usage as below,

----total usage of the temp file for all users

SQL> select sum(BLOCKS)*8/1024 from gv$sort_usage;  

SUM(BLOCKS)*8/1024

-------------------------------

             62855 (M)

----total size of the temp tablespace

SQL> select sum(BLOCKS)*8/1024 from V$tempfile;

SUM(BLOCKS)*8/1024

----------------------------------

             63780 (M)

2. Do you know the SQl which used for checking top consuming TEMP space?
Find out which sql is consuming TEMP space ?

set lines 300
set pages 80
col sql_text for a80
select
sort.inst_id,sess.SID,sort.segtype, sort.blocks*8/1000 "MB" ,sql.sql_id,sql.sql_text
from
gv$sort_usage sort, gv$session sess ,gv$sql sql
where
sort.SESSION_ADDR = sess.SADDR
and sort.inst_id=sess.inst_id
and sess.inst_id=sql.inst_id
and sql.sql_id = sess.sql_id
and sort.blocks*8/1000>100
order by blocks asc;

INST_ID        SID SEGTYPE           MB SQL_ID        SQL_TEXT
---------- ---------- --------- ---------- ------------- -----------------------------------------------------------------------------
  1        710 SORT          199.68 0c5hnfs6w0pqx      SELECT NULL CASH_DIV_SELECTOR_LEG_SEQNO , CASE WHEN VTOS.CA_CANCEL_REF_SEQNO IS
                                                         NOT NULL THEN VTOS.CA_CANCEL_REF_SEQNO ELSE VTOS.CASH_DIV_INCME_DATA_SEQNO

3. explain plan for the SQL.

SQL> SELECT * FROM table(dbms_xplan.display_cursor('cs5qrfvrwmqd4',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cs5qrfvrwmqd4, child number 0
-------------------------------------
select count(*) from (select * from CLIENT where 1=1 AND  client_seqno IN (SELECT
client_seqno  FROM   (SELECT client_seqno,client_short_nm,client_id,
SUM(not_there) not_there,COUNT(*) c  FROM
(SELECT c.client_seqno, c.client_short_nm,c.client_id,nvl2(ampd.custodian_acct_id, 0, 1) not_there,

Plan hash value: 966048578

------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |       |       |   211 (100)|          |
|   1 |  SORT AGGREGATE                        |             |     1 |       |            |          |
|   2 |   VIEW                                 |             |     1 |       |   211   (3)| 00:00:03 |
|*  3 |    FILTER                              |             |       |       |            |          |
|   4 |     HASH GROUP BY                      |             |     1 |    99 |   211   (3)| 00:00:03 |
|   5 |      NESTED LOOPS                      |             |     1 |    99 |   210   (2)| 00:00:03 |
|   6 |       VIEW                             |             |     1 |    53 |   209   (2)| 00:00:03 |
|   7 |        HASH UNIQUE                     |             |     1 |   130 |   209   (2)| 00:00:03 |
|*  8 |         TABLE ACCESS BY INDEX ROWID    | FUND        |     1 |    11 |     3   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                  |             |     1 |   130 |   208   (2)| 00:00:03 |
|* 10 |           HASH JOIN OUTER              |             |     1 |   119 |   205   (2)| 00:00:03 |
|  11 |            NESTED LOOPS                |             |     1 |    88 |   202   (1)| 00:00:03 |
|  12 |             MERGE JOIN CARTESIAN       |             |     1 |    42 |   201   (1)| 00:00:03 |
|  13 |              TABLE ACCESS FULL         | FUND_PRVLGS |     1 |    19 |     2   (0)| 00:00:01 |
|  14 |              BUFFER SORT               |             | 26574 |   596K|   199   (2)| 00:00:03 |
|  15 |               TABLE ACCESS FULL        | FUND        | 26574 |   596K|   199   (2)| 00:00:03 |
|  16 |             TABLE ACCESS BY INDEX ROWID| CLIENT      |     1 |    46 |     1   (0)| 00:00:01 |
|* 17 |              INDEX UNIQUE SCAN         | CLIENT_PK   |     1 |       |     0   (0)|          |
|  18 |            TABLE ACCESS FULL           | FUND_PRVLGS |     1 |    31 |     2   (0)| 00:00:01 |
|* 19 |           INDEX RANGE SCAN             | FUND_IX3    |     2 |       |     1   (0)| 00:00:01 |
|  20 |       TABLE ACCESS BY INDEX ROWID      | CLIENT      |     1 |    46 |     1   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN               | CLIENT_PK   |     1 |       |     0   (0)|          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter((SUM(NVL2("$vm_col_1",0,1))=0 AND COUNT(*)>0))
   8 - filter("C"."CLIENT_SEQNO"="CLIENT_SEQNO")
  10 - access("F"."CUSTODIAN_ACCT_ID"="AMPD"."CUSTODIAN_ACCT_ID")
  17 - access("F"."CLIENT_SEQNO"="C"."CLIENT_SEQNO")
  19 - access("F"."CUSTODIAN_ACCT_ID"="AMPD"."CUSTODIAN_ACCT_ID")
  21 - access("CLIENT_SEQNO"="$vm_col_3")

Note
-----
   - dynamic sampling used for this statement

57 rows selected.