关于tempory tablespace

--ts.sql
set linesize 200 pagesize 50000;
col tablespace_name format a50;
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc 

set pages 999
select 'alter database datafile '||file_id||' resize 16G;' from dba_data_files
union
select 'alter database datafile '||file_id||' resize 16G;' from dba_data_files where tablespace_name like '%UNDO%'
union
select 'alter database tempfile '||file_id||' resize 16G;' from dba_temp_files;

set pages 999
select 'alter database datafile '||file_id||' autoextend off;' from dba_data_files
union
select 'alter database datafile '||file_id||' autoextend off;' from dba_data_files where tablespace_name like '%UNDO%'
union
select 'alter database tempfile '||file_id||' autoextend off;' from dba_temp_files;

set lines 200 pages 50000
col file_name for a90
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1;
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1;

set lines 200 pages 9999 
select name from v$datafile 
union 
select member from v$logfile 
union 
select name from v$controlfile 
union 
select name from v$tempfile; 

set pages 999
select 'alter database tempfile '||file_id||' resize 6G;' from dba_temp_files;

*** 查看正在使用TEMP表空间的会话
SQL> set line 132
set pagesize 49999
set wrap off
col USERNAME for a8
col tablespace for a10
Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text
  from v$sort_usage su,v$parameter p,v$session se,v$sql s
   where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash
   and s.address=su.sqladdr
   and tablespace in ('TEMP','TEMP1','TEMP2')
  order by se.username,se.sid;

v$sort_usage 查看的是哪个session正在以什么方式使用临时表空间，且使用了多少块;是当前正在使用的临时表空间信息

sql> select (sum (blocks))*8/1024 "mb" from v$sort_usage;
sql> select username,user, tablespace,blocks from  v$sort_usage;

SELECT
    dt.tablespace_name AS "Tablespace",
    dt.total_space_mb AS "Total MB",
    (dt.total_space_mb - df.free_space_mb) AS "Used MB",
    df.free_space_mb AS "Free MB",
    ROUND((dt.total_space_mb - df.free_space_mb) / dt.total_space_mb * 100, 2) AS "Used Percentage"
FROM
    (SELECT
         tablespace_name,
         SUM(bytes) / 1024 / 1024 AS total_space_mb
     FROM
         dba_temp_files
     GROUP BY
         tablespace_name) dt,
    (SELECT
         tablespace_name,
         SUM(bytes_cached) / 1024 / 1024 AS free_space_mb
     FROM
         v$temp_extent_pool
     GROUP BY
         tablespace_name) df
WHERE
    dt.tablespace_name = df.tablespace_name;

CREATE TEMPORARY TABLESPACE "TEMP1"
TEMPFILE
        '+DATA' SIZE 16384M ,
        '+DATA' SIZE 16384M ;

CREATE TEMPORARY TABLESPACE "TEMP2"
TEMPFILE
        '+DATA' SIZE 16384M ,
        '+DATA' SIZE 16384M ;

ALTER TABLESPACE TEMP1 ADD TEMPFILE '+DATA' SIZE 16G;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '+DATA' SIZE 16G;

ALTER TABLESPACE TEMP1 TABLESPACE GROUP '';
ALTER TABLESPACE TEMP2 TABLESPACE GROUP '';

ALTER TABLESPACE "TEMP1" TABLESPACE GROUP "TEMP";
ALTER TABLESPACE "TEMP2" TABLESPACE GROUP "TEMP";

ALTER TABLESPACE TEMP1 SHRINK SPACE KEEP 15G;
ALTER TABLESPACE TEMP2 SHRINK SPACE KEEP 15G;

ALTER TABLESPACE TEMP3 TABLESPACE GROUP TEMP;
ALTER TABLESPACE TEMP3 TABLESPACE GROUP '';

alter tablespace TEMP3 add tempfile '+DATA' size 16G;

修改默认TEMP表空间
sqlplus / as sysdba
SQL> alter tablespace TEMP1 tablespace group TEMP;
SQL> alter tablespace TEMP2 tablespace group TEMP;
SQL> alter database default temporary tablespace TEMP;
SQL> shutdown immediate;
SQL> alter pluggable database "PROD" open ;
SQL> drop tablespace TEMP3 including contents and datafiles;
SQL> select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

修改PDB默认Undo表空间
With Local Undo enabled, How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node (Doc ID 2673826.1)

SOLUTION
As this database is enabled with LOCAL UNDO, assign specific Undo tablespace to each PDB on node-1 & node-2 by executing below commands.

On node-1:
alter session set container=PROD;
alter pluggable database "PROD" open ;
alter system set undo_tablespace=APPS_UNDOTS1 container=current sid='PRODCDB1' scope=both;             >>>> sid=First Instance
On Node-2
alter session set container=PROD;
alter pluggable database "PROD" open ;
alter system set undo_tablespace=APPS_UNDOTS2 container=current sid='PRODCDB2' scope=both;             >>>> sid=Second Instance