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