Wednesday, May 8, 2024

syntax for dropping a temporary file from a temporary tablespace

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