Oracle Database Discussions

Hi all

After doing an deep performance tuning session (AWR: Snapshot interval = 30min, retention time = 30 days, Collection Level = Full), I set AWR back to "normal" mode (interval 1h, retention time = 10 days, collection level = standard).

This "normal" mode results in less used space (about 2.5GB) , but SYSAUX still allocated 28 GB. My aim is therefore to shrink the allocated space to somewhere like 3 GB.

I ran segment adviser to shrink some tables, but this had no effects on the allocated space, of course. Then I tried to resize the datafile with

"ALTER DATABASE DATAFILE ... RESIZE ...;

without success, because there are Segments at the end of the datafile.




In Grid Control, I then tried to reorganize SYSAUX without success, getting an error message:

"The selected tablespace cannot be reorganized. Oracle does not support reorganizing the following tablespaces: SYSTEM, SYSAUX (10g or later), temporary, undo, offline or read only."

I use Oracle 10.2.0.3.0.




An possible way seems to

1) identify the segments at the end of the datafile (in Grid Controller: "Show Tablespace Contents" on SYSAUX and eventually extend map)

2) Move the identified segments to another tablespace

3) resize the datafile

4) move back the previously moved segments to SYSAUX


For me, this seems to be a so-called "open heart" operation. Can you think of another way or the impact of the suggested solution?



Kind regards and thanks for your answers!

Casi