temp空间使用情况：

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
ADAPTER_TEMPORARY                       750      96000           688        91.73            62
SMC_TEMPORARY                           150      19200             4         2.67           146
TEMP                                  63487    8126336         63395        99.86            92

temp使用情况2：

USERNAME             SQL_ID        B.BYTES_USED/1024/1024/1024 B.BYTES_FREE/1024/1024/1024
-------------------- ------------- --------------------------- ---------------------------
                     aykvshm7zsabd                  31.9091797                   .08984375
                     aykvshm7zsabd                          30                           0
                     aykvshm7zsabd                  31.9091797                   .08984375
                     aykvshm7zsabd                          30                           0
SYS                  65ppdfwmj3krr                  31.9091797                   .08984375
SYS                  65ppdfwmj3krr                          30                           0
SYS                  65ppdfwmj3krr                  31.9091797                   .08984375
SYS                  65ppdfwmj3krr                          30                           0
OPO                  9wsmt55q4hsng                    .0078125                  .138671875
OPO                  bunvx480ynf57                    .0078125                  .138671875
CAS                  9pkhj5xrhw7fr                    .6640625                  .068359375

sql信息：

SQL> select * from table(dbms_xplan.display_cursor('aykvshm7zsabd',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  aykvshm7zsabd, child number 0
-------------------------------------
select size_for_estimate,                      size_factor * 100 f,
               estd_physical_read_time,
estd_physical_reads              from v$db_cache_advice where id = '3'

Plan hash value: 1829585422

--------------------------------------------------------------------------------------
| Id  | Operation           | Name               | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |        |       |       |          |
|   1 |  VIEW               | GV$DB_CACHE_ADVICE |     20 |       |       |          |
|   2 |   SORT ORDER BY     |                    |     20 |  2048 |  2048 | 2048  (0)|
|*  3 |    HASH JOIN        |                    |     20 |  2078K|  2078K|  617K (0)|
|*  4 |     FIXED TABLE FULL| X$KCBWBPD          |      1 |       |       |          |
|*  5 |     FIXED TABLE FULL| X$KCBSC            |     21 |       |       |          |
--------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   3 - access("A"."BPID"="B"."BP_ID" AND "A"."INST_ID"="B"."INST_ID")
   4 - filter(("B"."BP_ID"=3 AND "B"."INST_ID"=USERENV('INSTANCE')))
   5 - filter(("A"."BPID"=3 AND "A"."INST_ID"=USERENV('INSTANCE')))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level