oracle表空間清理

1、 oracle表空單清理分類(lèi)： oracle tiptop2013-06-03 14:28 279人閱讀 評(píng)論(0) 收藏 舉報(bào)1.查詢(xún)表空間使用情況:sqlplus system/managertopprodSQL>q_tbsFREE2.查詢(xún)temp使用方法:sqlplus system/managertopprodSQL>SELECT    d.tablespace_name       &

2、#160;              tablespace_name  , d.status                               tab

3、lespace_status  , NVL(a.bytes, 0)                        tablespace_size  , NVL(t.bytes, 0)              

4、          used  , TRUNC(NVL(t.bytes / a.bytes * 100, 0) used_pct  , NVL(s.current_users, 0)                current_usersFROM    sys.dba_tablespaces d 

5、 , ( 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  

6、0;   group by tablespace_name    ) t  , v$sort_segment  sWHERE      d.tablespace_name = a.tablespace_name(+)  AND d.tablespace_name = t.tablespace_name(+)  AND d.tablespace_name = s.tablespace_name(+)  AND d.extent_management

7、 like 'LOCAL'  AND d.contents like 'TEMPORARY'2.清理TEMP臨時(shí)表空間：(在無(wú)用戶(hù)連接的狀況下操作,最好在清理之前重啟一下數(shù)據(jù)庫(kù))#重啟數(shù)據(jù)庫(kù)sqlplus '/as sysdba'SQL>shutdown immediateSQL>startup#創(chuàng)建一個(gè)臨時(shí)表空間temp02，用作臨時(shí)替換SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf&#

8、39; size 10M autoextend on next 10M; #將系統(tǒng)臨時(shí)表空間指向temp02SQL>alter database default temporary tablespace temp02;#刪除原來(lái)的臨時(shí)表空間tempSQL>drop tablespace temp including contents and datafiles;#創(chuàng)建新的臨時(shí)表空間tempSQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf

9、9; size 4096M autoextend on next 100M;#將系統(tǒng)臨時(shí)表空間指回tempSQL>alter database default temporary tablespace temp;#刪除臨時(shí)表空間temp02SQL>drop tablespace temp02 including contents and datafiles;3.清理UNDO表空間：(在無(wú)用戶(hù)連接的狀況下操作,最好在清理之前重啟一下數(shù)據(jù)庫(kù))#重啟數(shù)據(jù)庫(kù)sqlplus '/as sysdba'SQL>shutdown immediateSQL>startup

10、#創(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表空間指向undotbs2SQL>alter system set undo_tablespace=undotbs2 scope=both;#確保所有在UNDOTBS1的undo segment都已offlineSQL> select SEGME

11、NT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;#刪除原來(lái)的UNDO表空間undotbs1SQL>drop tablespace undotbs1 including contents and datafiles;#創(chuàng)建新的臨時(shí)表空間undotbs1SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M;#將系統(tǒng)UNDO表空間指回undotbs1SQL>alt

12、er system set undo_tablespace=undotbs1 scope=both;#刪除UNDO表空間undotbs2SQL>drop tablespace undotbs2 including contents and datafiles;3.清理TEMPTABS表空間：#刪除TEMPTABS表空間SQL>drop tablespace temptabs including contents and datafiles;#創(chuàng)建TEMPTABS表空間  SQL>create tablespace temptabs datafile 

13、9;/u2/oradb/oradata/topprod/temptabs.dbf' size 4096M autoextend on next 100M;或者刪除表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)表空間：alter tablespace SYSTEM add datafi