refresh information in dbms_space.asa_recommendations()

You need to analyze object you reorganized and then check parameters of this function to see that default it uses all (it means also old) runs of auto space advisor:

SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id

so use dbms_space.asa_recommendations(‘FALSE’,’FALSE’,’FALSE’)
to see latest.

you can also manually invoke the run of advisor by submitting of DB job (under a SYSDBA account):

Declare
v_Job Integer;
Begin
dbms_job.submit(v_Job,
‘Begin dbms_scheduler.run_job(”AUTO_SPACE_ADVISOR_JOB”); End;’ );
commit;
end;

SMON process consumes 100 % CPU

As soon as customer starts up the database, a lock is put on
SYS.SMON_SCN_TIME by SMON and it never go away.

Database Performance becomes slow.

SMON_SCN_TIME has huge no.of records.

SQL> select count(*) from sys.smon_scn_time;

COUNT(*)
———-
137545

1 row selected.

It is found that the object has been locked.

SQL> select object_id from dba_objects where object_name = ‘SMON_SCN_TIME’;

OBJECT_ID
———-
575

1 row selected.

SQL> select * from v$locked_object where object_id = 575;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID
———- ———- ———- ———- ———-
ORACLE_USERNAME OS_USER_NAME PROCESS
—————————— —————————— ————
LOCKED_MODE
———–
5 5 1494 575 164
dbadmin 4444350
3 conn / as sysdba

/* Set the event at system level */

SQL> alter system set events ‘12500 trace name context forever, level 10’;

/* Delete the records from SMON_SCN_TIME */

SQL> delete from smon_scn_time;

SQL> commit;

SQL> alter system set events ‘12500 trace name context off’;

Now restart the instance.