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;
30 июл. 2014 г.
Rebuild TEMP tablespace
Технологии Blogger.
0 коммент.:
Отправить комментарий