Hi
I want to Shrink a datafile in SYSAUX tablespace but getting "file contains used data beyond requested RESIZE value". The reason is the file has grown too large i.e. about 10GB whereas 9GB is free. I am using Oracle XE 11g R2 therefore space is a concern for me.
I run following query
select * from dba_extents where tablespace_name='SYSAUX' order by block_id desc;
And found that SYS.WRH$_SEG_STAT_PK (INDEX PARTITION) is on the last block ID (1217568) consuming only 65536 bytes. Where is the next segment is at Block ID 978560.
I feel if SYS.WRH$_SEG_STAT_PK can be rebuild it will move to some early block and give me ample space to shrink.
But I don't know how to shrink INDEX PARTITION and when an object is of SYS user I feel scary.
I will be glad if someone can help.
Regards,
Habib