set pages 999set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024INCREMENT_BY_MBfrom dba_temp_files d,
v$tempfile v
where d.FILE_ID= v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;
Please Note: For pluggable databases, please ensure you are in the right PDB as temp datafiles can be at container level and/or pluggable database level.
Add Temp Datafile to Temp Tablespace
To add a temp datafile to ‘TEMP‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:
Query to see Current Temp Datafiles State
To see the current state of the temp datafiles:
set pages 999 set lines 400 col FILE_NAME format a75 select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB from dba_temp_files d, v$tempfile v where d.FILE_ID = v.FILE# order by d.TABLESPACE_NAME, d.FILE_NAME;Please Note: For pluggable databases, please ensure you are in the right PDB as temp datafiles can be at container level and/or pluggable database level.
Add Temp Datafile to Temp Tablespace
To add a temp datafile to ‘TEMP‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;