SYSAUX Tablespace growth because of DB stat history

View current history level

select dbms_stats.get_stats_history_availability from dual;

Applying purge stat from 20 days to 200 days.

begin
for i in reverse 20..200
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/

Confirm recent analyzed date

select dbms_stats.get_stats_history_availability from dual;

        to reclaim the space,
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY deallocate unused;

al    alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY enable row movement;
alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY shrink space;
alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORYdeallocate unused;

 
 
Reclaim index stat

alter index SYS.I_WRI$_OPTSTAT_H_ST INDEX shrink space;
alter index SYS.I_WRI$_OPTSTAT_H_ST INDEX deallocate unused;

      alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX shrink space;
alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX deallocate unused;

Then do

Gathering statistics for all objects in the database:

exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1' );

 

Recovering Database after manual delete of DBF files

Issue after deleting

When we try to shutdown the DB

ORA-01116: error in opening database file 41
ORA-01110: data file 41: '/data2/dbfname.dbf'
ORA-27041: unable to open file


 Solution

Shutdown abort


exit

sqlplus sys/xxx as sysdba

Alter database mount

alter database datafile '' offline drop;

Alter database open