ORACLE TEMP表空間大小改小
EMP表空間重做方法是(做兩步的動作主要目的是為了相關命名和之前沒有變化),
1)先產生TMP臨時表空間,再刪除TEMP表空間
2)再重新做TEMP表空間,後再刪除TMP表空間
---------------------------------------------------------
--kstest 20110802 test ok
--create TMP---------------------------------------------
step 1)
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE
'/ora/orakstest/kstest/tmp01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 32M MAXSIZE 5120M;
step 2)
alter database default temporary tablespace TMP;
step 3)
drop tablespace TEMP including contents and datafiles ;
--ReCreate TEMP-----------------------------------------
step 1)
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE
'/ora/orakstest/kstest/temp01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 32M MAXSIZE 5120M;
step 2)
alter database default temporary tablespace TEMP;
step 3)
drop tablespace TMP including contents and datafiles ;
----------------------------------------------------------
--ks2k4 20110803 test ok
----------------------------------------------------------
1) 若先限制用戶login系統,再做kill光進程,這樣比較順利,五分鐘可以完成。
(昨天在測試庫做測試時,沒有做以上動作,今天早上都沒有做完)
2) KS2K4的TEMP表空間采1G(初始大小)至5G(最大)自動增長方式,每次增長32M;
PS: 表空間大小修改小時,Oracle已經正常,Unix可能需要時間才能全部釋放,若重啟資料庫會完全釋放(我剛在測試庫有測試)。
--create TMP---------------------------------------------
step 1)
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE
'/ora/ardataks/ks2k4/tmp01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 32M MAXSIZE 5120M;
step 2)
alter database default temporary tablespace TMP;
step 3)
drop tablespace TEMP including contents and datafiles ;
--ReCreate TEMP-----------------------------------------
step 1)
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE
'/ora/ardataks/ks2k4/temp01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 32M MAXSIZE 5120M;
step 2)
alter database default temporary tablespace TEMP;
step 3)
drop tablespace TMP including contents and datafiles ;
----------------------------------------------------------
----------------------------------------------------------
other:
ALTER DATABASE TEMPFILE '/ora/orakstest/kstest/tmp01.dbf' RESIZE 1024M;
ALTER DATABASE TEMPFILE '/ora/orakstest/kstest/tmp01.dbf' AUTOEXTEND ON NEXT 32M MAXSIZE 5120M;
1)create temporary tablespace temp2 TEMPFILE '/ora/erp/zstest_temp/temp02.dbf' size 1000m AUTOEXTEND off;
2)alter database default temporary tablespace temp2;
3)drop tablespace temp including contents and datafiles ;
----------------------------------------------------------
by 宇宙老人 20110803
心有多大,宇宙就有多大。
http://blog.csdn.net/foreveryday007