如何清除_oracle_占据的硬碟空间_V2.doc

會影響硬碟空間大小的 oracle 相關檔案項次檔案名稱用途1$ORACLE_HOME/network/log/listener.log記錄所有連線到 oracle server 的連線資料,有些上線比較久系統,此檔案有可能會有幾十MB或幾百MB,此檔過大,會造成 login 時間增加.處理方式與執行步驟1. 用 oracle user login 2. 先將該檔案複製到其他目錄,或是直接刪除3. 執行 $ORACLE_HOME/network/log/listener.log可以直接將檔案清成空的及產生新檔.2$ORACLE_BASE/oradata/topprod/temptabs.dbfTEMPTABS tablespace 所使用的資料檔,專供 BDL & Genero 做 temp table 資料儲存地方處理方式1 droptemp可以依照產品處發的相關”清除oracle TT 開頭的table”技術文件直接清空,但此法不會將檔案空間縮小 處理方式2將TEMPTABS刪除重建,執行時 User必須要離線TIPTOP 6.5 以前1. oracle user login 2. 用 system 身份登入 sqlplus 3. drop tablespace temptabs including contents and datafiles4. create tablespace temptabs datafile /u2/oradb/oradata/topdb/temptabs.dbf size 1024M autoextend on next 10MGenero 1. oracle user login2. 用 system 身份登入 sqlplus3. drop tablespace temptabs including contents and datafiles 4. create tablespace temptabs datafile 5. /u2/oradb/oradata/topprod/temptabs.dbf size 1024M autoextend on next 10M1.create tablespace temp02 datefile $ORACLE_BASE/oradata/topprod/temp02.dbf size 100M autoextend on next 10M;2.alter database default temporary tablespace temp02;3.drop tablespace temptabs including contents and datafiles;4.create tablespace temptabs datafile $ORACLE_BASE/oradata/topprod/temptabs.dbf size 1024M autoextend on next 100M;5.alter database default temporary tablespace temptabs;6.drop tablespace temp02 including contents and datafiles;7.alter database default temporary tablespace temptabscreate temporary tablespace temp02 datafile $ORACLE_BASE/oradata/topprod/temp02.dbf size 100M autoextend on next 10M;create temporary tablespace temp02 tempfile /u2/oradb/oradata/topprod/temp02.dbf size 100M autoextend on next 10M;alter database default temporary tablespace temp02;drop tablespace temptabs including contents and datafiles;create temporary tablespace temptabs tempfile /u2/oradb/oradata/topprod/temptabs.dbf size 4096M autoextend on next 100M;alter database default temporary tablespace temptabs;drop tablespace temp02 including contents and datafiles;3$ORACLE_BASE/oradata/topprod/undo01.dbfundotbs1 tablespace 所使用的資料檔,專供 oracle system 放置 before image 地方處理方式1將UNDOTBS1ResizeUser 必須要離線TIPTOP 6.5 1. oracle user login2. 用 system 身份登入 sqlplus3. alter database datafile /u2/oradb/oradata/topdb/undo01.dbf resize 500MGenero1. oracle user login2. 用 system 身份登入 sqlplus3. alter database datafile /u2/oradb/oradata/topprod/undo01.dbf resize 500M處理方式2若 resize方式不能成功,將 undotbs1刪除重建User 必須要離線TIPTOP 6.51. oracle user login2. 用 system 身份登入 sqlplus3. create undo tablespace undotbs2 datafile /u2/oradb/oradata/topdb/undo02.dbf size 50M4. alter system set undo_tablespace=undotbs25. drop tablespace undotbs1 including contents and datafiles6. create undo tablespace undotbs1 datafile /u2/oradb/oradata/topdb/undo01.dbf size 500M autoextend on next 10M7. alter system set undo_tablespace=undotbs18. drop tablespace undotbs2 including contents and datafilesGenero1. oracle user login2. 用 system 身份登入 sqlplus3. create undo tablespace undotbs2 datafile /u2/oradb/oradata/topprod/undo02.dbf size 50M4. alter system set undo_tablespace=undotbs25. drop tablespace undotbs1 including contents and datafiles6. create undo tablespace undotbs1 datafile /u2/oradb/oradata/topprod/undo01.dbf size 500M autoextend on next 10M7. alter system set undo_tablespace=undotbs18. drop tablespace undotbs2 including contents and datafiles4$ORACLE_BASE/oradata/topprod/temp.dbftemp tablespace 所使用的資料檔,專供 oracle system 放置 暫存資料的地方處理方式1將 temp刪除重建User 必須要離線TIPTOP 6.51. oracle user login2. 用 system 身份登入至 sqlplus3. CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE /u2/oradb/oradata/topdb/topprod/temp02.dbf SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10240M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;4. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;5. DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;6. CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u2/oradb/oradata/topdb/topprod/temp01.dbf SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10240M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;7. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;8. DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;Genero9. oracle user login10. 用 system 身份登入至 sqlplus11. CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE /u2/oradb/oradata/topprod/temp02.dbf SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10240M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;12. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;13. DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;14. CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u2/oradb/oradata/topprod/temp01.dbf SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10240M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;15. ALTER DATABASE DEF