For future reference, here is how to drop and recreate a temporary file in an existing temporary tablespace, but with a changed path:
If applicable, set the container:
alter session set container=pdb1;
Drop the current tempfile, and recreate it in the desired location. Make it autoextensible:
alter database tempfile '/data/oradata/db01/temp1.dbf' drop including datafiles;
alter tablespace TEMP1 add tempfile '/data/oradata/db02/temp1.dbf' size 1G;
alter database tempfile '/data/oradata/db02/temp1.dbf' autoextend on next 1G maxsize unlimited;
A good query for temporary tablespaces:
select t.ts#, t.name,t.bigfile, t.con_id, f.name "file_name", tg.group_name
from v$tempfile f join v$tablespace t
on (t.TS# = f.TS#)
and t.con_id = f.CON_ID join DBA_TABLESPACE_GROUPS tg on (tg.tablespace_name = t.name)
TS# NAME BIG CON_ID file_name GROUP_NAME
---------- ------------------------------ --- ---------- ---------------------------------------- ------------------------------
3 TEMP1 YES 3 /u02/oradata/pdb1/temp1.dbf TEMP
4 TEMP2 YES 3 /u02/oradata/pdb1/temp2.dbf TEMP
No comments:
Post a Comment