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:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

日历

« 2008-07-26  
  12345
6789101112
13141516171819
20212223242526
2728293031  

数据统计

  • 访问量: 7261
  • 日志数: 647
  • 文件数: 5
  • 建立时间: 2007-12-29
  • 更新时间: 2008-07-17

RSS订阅

Open Toolbar