oracle表空單清理常用程式碼片段整理

1.查詢資料表空間使用方式:
sqlplus system/manager@topprod 複製代碼 代碼如下:SQL>@q_tbsFREE

2.查詢temp使用方法:
sqlplus system/manager@topprod 複製代碼 代碼如下: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暫存資料表空間：(在無使用者串連的狀況下操作,最好在清理之前重啟一下資料庫) 複製代碼 代碼如下:#重啟資料庫
sqlplus '/as sysdba'
SQL>shutdown immediate
SQL>startup
#建立一個暫存資料表空間temp02，用作臨時替換
SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M;
#將系統暫存資料表空間指向temp02
SQL>alter database default temporary tablespace temp02;
#刪除原來的暫存資料表空間temp
SQL>drop tablespace temp including contents and datafiles;
#建立新的暫存資料表空間temp
SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M;
#將系統暫存資料表空間指回temp
SQL>alter database default temporary tablespace temp;
#刪除暫存資料表空間temp02
SQL>drop tablespace temp02 including contents and datafiles;

3.清理UNDO資料表空間：(在無使用者串連的狀況下操作,最好在清理之前重啟一下資料庫) 複製代碼 代碼如下:#重啟資料庫
sqlplus '/as sysdba'
SQL>shutdown immediate
SQL>startup
#建立一個UNDO資料表空間undotbs2，用作臨時替換
SQL>create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10M autoextend on next 10M;
#將系統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;
#刪除原來的UNDO資料表空間undotbs1
SQL>drop tablespace undotbs1 including contents and datafiles;
#建立新的暫存資料表空間undotbs1
SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M;
#將系統UNDO資料表空間指回undotbs1
SQL>alter system set undo_tablespace=undotbs1 scope=both;
#刪除UNDO資料表空間undotbs2
SQL>drop tablespace undotbs2 including contents and datafiles;

3.清理TEMPTABS資料表空間： 複製代碼 代碼如下:#刪除TEMPTABS資料表空間
SQL>drop tablespace temptabs including contents and datafiles;
#建立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.增加系統資料表空間： 複製代碼 代碼如下: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;