導讀:1建站知識清理TEMP臨時表空間、清理UNDO表空間、清理TEMPTABS表空間等等，有類似需求的朋友可以參考下哈網(wǎng)站建設企業(yè)網(wǎng)站建設。

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臨時表空間：(在無用戶連接的狀況下操作,最好在清理之前重啟一下數(shù)據(jù)庫)

復制代碼 代碼如下:

#重啟數(shù)據(jù)庫 sqlplus '/as sysdba' SQL>shutdown immediate SQL>startup #創(chuàng)建一個臨時表空間temp02，用作臨時替換 SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M; #將系統(tǒng)臨時表空間指向temp02 SQL>alter database default temporary tablespace temp02; #刪除原來的臨時表空間temp SQL>drop tablespace temp including contents and datafiles; #創(chuàng)建新的臨時表空間temp SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M; #將系統(tǒng)臨時表空間指回temp SQL>alter database default temporary tablespace temp; #刪除臨時表空間tem網(wǎng)seo優(yōu)化趨勢p02 SQL>drop tablespace temp02 including contents and datafiles;

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

復制代碼 代碼如下:

#重啟數(shù)據(jù)庫 sqlplus '/as sysdba' SQL>如何seo優(yōu)化推廣網(wǎng)站shutdown immediate SQL>startup #創(chuàng)建一個UNDO表空間undotbs2，用作臨時替換 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; #刪除原來的UNDO表空間undotbs1 SQL>drop tablespace undotbs1 including contents and datafiles; #創(chuàng)建新的臨時表空間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;

關鍵詞標簽: 代碼 常用