tempfile operations

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select username,TEMPORARY_TABLESPACE from dba_users order by 1

select file_name,bytes from dba_temp_files;

ALTER USER scott TEMPORARY TABLESPACE temp;


Monitoring tempfile
====================


select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER; 

Adding tempfile
======================
ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;



Dropping tempfile
==================
ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

Various other operations on tempfile
========================================
CREATE TEMPORARY TABLESPACE temp; --for OMF

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;