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;