清理SYSAUX（其一）

1:

add SYSAUX tablespace

或者如下脚本:

if test -f /var/opt/oracle/oratab
then
 voratab=/var/opt/oracle/oratab
else
 voratab=/etc/oratab
fi
 export voratab
echo ""voratab=$voratab"" > AL.sh
ps -ef | grep pmon_ | grep -v ""grep pmon"" | \
awk -F '_' '\
{print ""export ORACLE_SID=""$3"" && export ORACLE_HOME=`grep ^""$3"": $voratab | cut -f 2 -d :` && echo ""$3"" && \
$ORACLE_HOME/bin/sqlplus / as sysdba @AL.sql 2>&1 | tee -a AL.sh.out""}' >> AL.sh

chmod 750 AL.sh
rm AL.out


echo ""set lines 200 pages 32000 trims on
col dbid format a20
spool AL.out append
select name, dbid from v\$database;
CREATE TABLE WRI\$_ADV_OBJECTS_NEW AS 
SELECT * FROM WRI\$_ADV_OBJECTS 
WHERE TASK_ID !=
(SELECT DISTINCT ID 
FROM WRI\$_ADV_TASKS 
WHERE NAME='AUTO_STATS_ADVISOR_TASK');
TRUNCATE TABLE WRI\$_ADV_OBJECTS;
INSERT /*+ APPEND */ INTO WRI\$_ADV_OBJECTS 
SELECT * FROM WRI\$_ADV_OBJECTS_NEW;
COMMIT;
ALTER INDEX WRI\$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI\$_ADV_OBJECTS_PK REBUILD;

drop table WRI\$_ADV_OBJECTS_NEW;

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/


exit
spool off
"">AL.sql

./AL.sh