ANALYZED

Analyzed failed with following error out of 2700 tables 2200 odd got analyzed
ERROR at line 1:
ORA-12801: error signaled in parallel query server P052
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10564
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

This is known issue in earlier version of oracle 9i when estimate was used instead of compute while gathering statistics using GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS command. Although it was claimed to be fixed in 9.2.0.20 i faced problem in 9.2.0.6.0 while estimating whereas compute worked great.



Analyze query:



Select OWNER,TABLE_NAME,
to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') "last_analyzed"
from dba_tab_columns
where OWNER not in ('SYS','SYSTEM')
and LAST_ANALYZED is not null
and COLUMN_ID=1
and (SYSDATE-LAST_ANALYZED) <>



Simple analyze job script :



#!/bin/sh# Script:

analyze_only.sh

#

# Description: Analyze Database.
# Set the variables

ORACLE_SID=

ORACLE_BASE=

ORACLE_HOME=

LD_LIBRARY_PATH=$ORACLE_HOME/lib

PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH


date


# Analyze Databasesqlplus -s "/ as sysdba" <<>

set echo off

set feedback off

set heading off

set pagesi 0

set linesize 125

spool analyze_all_output.lst

execute DBMS_STATS.GATHER_SCHEMA_STATS ownname=>'',cascade=>TRUE);

select '-----------------------------------------------------------' from dual;

select ' TABLE_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select table_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where OWNER='' ORDER by table_name;

select '---------------------End of List--------------------------------------' from dual;

select '-----------------------------------------------------------' from dual;

select ' INDEX_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select index_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_indexes where OWNER='' ORDER by index_name;

select '---------------------End of List--------------------------------------' from dual;

exit

EOF
ANALYZELOG="analyze_all_output.lst"

oraerr='cat $ANALYZELOGgrep ORA-wc -l'

if [ $oraerr -gt 0 ]
then echo "Status as on 'date' : Analyze Failed" >> "analyze_all_output.lst"

sqlplus /nolog <

conn aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB FAILED');

commit;
EOF
exit;

else

echo "Status as on 'date' : Analyze Successfull" >> "analyze_all_output.lst"
sqlplus /nolog <<>aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB SUCCESSFUL');

commit;
EOF
exit;

fi

mailx -s "$ORACLE_SID Analyze is complete." "xx@xx.com,yy@yy.com" < $ date