Saturday, June 23, 2018

How to resize temporary tablespace

How to resize temporary tablespace


step.1

Following query will give you tablespace name and datafile name along with path of that data file.
sql> select file_name, tablespace_name from dba_temp_files;

step.2

Following query will create temp tablespace named: 'temp2' with 800 MB size along with auto-extend and maxsize unlimited.
sql> create temporary tablespace temp2 tempfile  '/u01/oradata/temp_02.dbf' size 800m autoextend on next 10m maxsize unlimited;

step.3

Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )
sql> alter database default temporary tablespace temp2;

step.4

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.
sql> select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#, a.username,a.osuser, a.status from v$session a,v$sort_usage b WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.
sql> alter system kill session 'sid, serial#';
For example:
sql> alter system kill session '59,57391';

step.5

Now, we can drop old temporary tablespace without any trouble with following:
sql> drop tablespace temp2 including contents and datafiles;

If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

No comments:

Post a Comment