In this section you will find how can we CREATE and DROP tempfiles from ORACLE database

For an example , what we can do if a TEMPFILE was deleted wrongly from the operating system level ?
In this situation we can easly create a new TEMPFILE on the database level like shown below ;

- Connect to SQLPLUS via SYSDBA authorization.

- RUN following syntax for creating new TEMPFILE with value of 1000MB

SQL> alter tablespace temptablespace add tempfile ‘<Disk location of TEMPFILE >’ size 1000M autoextend on;

example ;

SQL> alter tablespace PSAPTEMP add tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ size 1000M autoextend on;

Drop TEMPFILE procedure

- Connect to SQLPLUS via SYSDBA authorization.

- RUN following syntax for first getting OFFLINE to releated TEMPFILE and then DROP with releated Datafiles on OS level

SQL> alter database tempfile ‘<disk location of TEMPFILE >’ offline ;

SQL> alter database tempfile ‘<disk location of TEMPFILE >’ drop including datafiles;

example ;

SQL> alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ offline;

SQL> alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ drop including datafiles;