ORACLE audit tablespace 空間清除

［ORACLE audit tablespace 空間清除筆記］-2015/10/13

兩小時前， 

TIPTOP 系統突然無法使用..

原來是 AUDIT 空間滿了.. 

待會就要前往機場的我..

都快要褲子一包了..

ORA-01654: 

unable to extend index

 <name of the index> 

by 128 in tablespace <name of the Index tablespace>

幸好問了 GOOGLE 大神 跟前輩，

才得以順利解鎖...

記錄語法：

當程式發生無法開啟且資料庫回報

AUDIT空間已滿的問題解決步驟：

錯誤訊息：

ORA-00604: 

error occurred at recursive SQL level 1

ORA-01654: 

unable to extend index SYS.I_AUD2 

by 8192 in tablespace AUDIT

解法有兩種：

1是清除audit tablespace空間，

2是增加 audit tablespace

這裏採用清除的方法：

使用指令：

1.登入oracle主機下：

  sqlplus '/as sysdba'

2. 清空表空間：

  truncate table sys.aud$;

</u2/oracle/oradata/toptest> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 10:24:39 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> truncate table sys.aud$;

Table truncated.

SQL>

打完收工 ! 不用換褲子了⋯

有用TT的人客，

後續記得加入排程清除。

另一個常發生的問題是 DBS1 空間滿，

需要放大多少，自己加上去即可：

下面還有 ds_pos1空間、rpt 空間，都可參考加上：

#看datafile編號oraq q_datafilecd  $ORACLE_BASE/oradata/topprod/

alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-06.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-07.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-08.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-09.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-10.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-11.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-12.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-13.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-14.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-15.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-16.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-17.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-18.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-19.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-20.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-21.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-22.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-23.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-24.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-25.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-26.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topprod/dbs1-27.dbf' size 4096M;
alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-04.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-05.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-06.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-07.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-08.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-09.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-10.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-11.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-12.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-13.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-14.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-15.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-16.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-17.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-18.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-19.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-20.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-21.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-22.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-23.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-24.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-25.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-26.dbf' size 2000M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/toptest/dbs1-27.dbf' size 2000M;
alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topstd/dbs1-03.dbf' size 4096M;alter tablespace dbs1 add datafile '$ORACLE_BASE/oradata/topstd/dbs1-04.dbf' size 4096M;

#為了ds_pos1空間增加posdbs1的tablespacealter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-02.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-03.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-04.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-05.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-06.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-07.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-08.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-09.dbf' size 4096M;alter tablespace posdbs1 add datafile '$ORACLE_BASE/oradata/topprod/posdbs1-10.dbf' size 4096M;

alter database datafile '/u2/oradb/oradata/toptest/rptdbs1-02.dbf' offline drop;