recreate temporary tablespace on datagurd
上一篇 /
下一篇 2008-05-15 10:10:40
/ 个人分类:oracle
recreate temporary tablespace on datagurd
the primary database temporary tablespace is at 32GB,so i feel it too big wu must to reduce it.
but temporary tablespace can't be reduce automatic !so i think i must recreate that.
procedure for recreate temprorary tablespace
1.startup
2.create temporary tablespace TEMP1 TEMPFILE SIZE 512M AUTOEXTEND ON NEXT 640K
--create a temp temporary tablespace first.
3.alter database default temporary tablespace temp1; --to change tablespace temp1 as default temporary tablespace
4.drop tablespace temp including contents and datafiles;--drop original tablespace temp
but But in this step then We have encountered a problem,wu issue the command but database hang for a long time and no response . so i issue "select *from V$sort_usage;" find some one has using the temp tablespace. and shutdown database and restart
it . then "select *from V$sort_usage;" find nothing. so wu can drop this tablespace .
5. create temporary tablespace TEMP TEMPFILE SIZE 8G AUTOEXTEND ON MAXSIZE 32G;
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which
can contain up to approximately 4 million (2^22) blocks.
so max datafile or tempfile size<=(^22*8k=32G) ,oh wu must create tempfile set maxsize less than 32 G or unlimited
create temporary tablespace TEMP TEMPFILE SIZE 8G MAXSIZE UNLIMITED; --this step is recreate temp tablespace
6.alter database default temporary tablespace temp; --reset new temp tablespace as default temporary tablespace
7.drop tablespace temp1 including contents and datafiles;--Delete transit use temporary tablespace temp1
wu complete in primary database recreate temp tablespace
but in standby database i found
select name from V$tempfile
return no row.
so i must add the tempfile manually on the standby database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancal.
alter database open read only .
alter tablespace temp add tempfile size 8G AUTOEXTEND ON MAXSIZE UNLIMITED;
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG: