30 июл. 2014 г.

on Leave a Comment

Rebuild TEMP tablespace

If you need to rebuild your temp tablespace, eg the existing one is corrupt or the wrong size, the trick is to temporarily give oracle a new one to use as its default temp tablespace while you replace the real one, then switch back.

1. Create new temporary tablespace:

 create temporary tablespace TEMP_TMP
 tempfile '/oracle/oradata/ORCL/temp02.dbf'
 size 1G reuse
 autoextend on next 100M maxsize unlimited;

2. Say to Oracle to use  new temporary tablespace:

 alter database default temporary tablespace TEMP_TMP;

3. Delete old temporary tablespace:

 drop tablespace TEMP including contents and datafiles;

4. Recreate old temporary tablespace

 create temporary tablespace TEMP
 tempfile '/oracle/oradata/ORCL/temp01.dbf'
 size 1G reuse
 autoextend on next 100M maxsize unlimited;

5. Say to Oracle to use a new temporary tablespace(old one):

alter database default temporary tablespace TEMP;


6. Delete unneeded tablespace:

drop tablespace TEMP_TMP including contents and datafiles;


7. Look results, whether all is as expected:

select * from dba_temp_files;

select * from dba_tablespaces;

0 коммент.:

Отправить комментарий

Технологии Blogger.