oracle表空單清理常用代碼段整理

更新時(shí)間：2013年06月03日 15:20:10 作者：

清理TEMP臨時(shí)表空間、清理UNDO表空間、清理TEMPTABS表空間等等，有類似需求的朋友可以參考下哈

1.查詢表空間使用情況:
sqlplus system/manager@topprod

復(fù)制代碼代碼如下:

SQL>@q_tbsFREE

2.查詢temp使用方法:
sqlplus system/manager@topprod

復(fù)制代碼代碼如下:

 
SQL>SELECT 
d.tablespace_name tablespace_name 
, d.status tablespace_status 
, NVL(a.bytes, 0) tablespace_size 
, NVL(t.bytes, 0) used 
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct 
, NVL(s.current_users, 0) current_users 
FROM 
sys.dba_tablespaces d 
, ( select tablespace_name, sum(bytes) bytes 
from dba_temp_files 
group by tablespace_name 
) a 
, ( select tablespace_name, sum(bytes_cached) bytes 
from v$temp_extent_pool 
group by tablespace_name 
) t 
, v$sort_segment s 
WHERE 
d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.tablespace_name = s.tablespace_name(+) 
AND d.extent_management like 'LOCAL' 
AND d.contents like 'TEMPORARY'; 

2.清理TEMP臨時(shí)表空間：(在無(wú)用戶連接的狀況下操作,最好在清理之前重啟一下數(shù)據(jù)庫(kù))

復(fù)制代碼代碼如下:

 
#重啟數(shù)據(jù)庫(kù) 
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
#創(chuàng)建一個(gè)臨時(shí)表空間temp02，用作臨時(shí)替換 
SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M; 
#將系統(tǒng)臨時(shí)表空間指向temp02 
SQL>alter database default temporary tablespace temp02; 
#刪除原來(lái)的臨時(shí)表空間temp 
SQL>drop tablespace temp including contents and datafiles; 
#創(chuàng)建新的臨時(shí)表空間temp 
SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M; 
#將系統(tǒng)臨時(shí)表空間指回temp 
SQL>alter database default temporary tablespace temp; 
#刪除臨時(shí)表空間temp02 
SQL>drop tablespace temp02 including contents and datafiles; 

3.清理UNDO表空間：(在無(wú)用戶連接的狀況下操作,最好在清理之前重啟一下數(shù)據(jù)庫(kù))

復(fù)制代碼代碼如下:

 
#重啟數(shù)據(jù)庫(kù) 
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
#創(chuàng)建一個(gè)UNDO表空間undotbs2，用作臨時(shí)替換 
SQL>create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10M autoextend on next 10M; 
#將系統(tǒng)UNDO表空間指向undotbs2 
SQL>alter system set undo_tablespace=undotbs2 scope=both; 
#確保所有在UNDOTBS1的undo segment都已offline 
SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs; 
#刪除原來(lái)的UNDO表空間undotbs1 
SQL>drop tablespace undotbs1 including contents and datafiles; 
#創(chuàng)建新的臨時(shí)表空間undotbs1 
SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M; 
#將系統(tǒng)UNDO表空間指回undotbs1 
SQL>alter system set undo_tablespace=undotbs1 scope=both; 
#刪除UNDO表空間undotbs2 
SQL>drop tablespace undotbs2 including contents and datafiles; 

3.清理TEMPTABS表空間：

復(fù)制代碼代碼如下:

 
#刪除TEMPTABS表空間 
SQL>drop tablespace temptabs including contents and datafiles; 
#創(chuàng)建TEMPTABS表空間 
SQL>create tablespace temptabs datafile '/u2/oradb/oradata/topprod/temptabs.dbf' size 4096M autoextend on next 100M; 
或者刪除表 
[code] 
select 'drop table '||segment_name ||';' from dba_segments where tablespace_name='TEMPTABS' and segment_name like 'TT%' and segment_name not like '%_FILE'; 

4.增加系統(tǒng)表空間：

復(fù)制代碼代碼如下:

 
alter tablespace SYSTEM add datafile '/u2/oradb/oradata/topprod/system02.dbf' size 2000M autoextend on next 10M; 
alter tablespace SYSAUX add datafile '/u2/oradb/oradata/topprod/sysaux02.dbf' size 2000M autoextend on next 10M; 

您可能感興趣的文章: