SYSAUX reorganization [message #496076] |
Thu, 24 February 2011 04:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
SYSAUX is about 35G, but the space occupied by segments is about 2.5G. I need to resize the datafiles down, but I can't because they all have extents allocated almost at the top. I have checked for dropped objects, and for objects that shouldn't be there: nothing significant. So I think I need to reorganize the tablespace to relocate all the segments, then I should be able to resize the datafiles.
I cold try using the move procedures for the SYSAUX occupants, and hope that would relocate the segments - but not all occupants have a move procedure. Or I could try moving individual tables and indexes, starting with the ones that have extents at the top of the files. But before I do that, any advice on reorganizing SYSAUX? I should mention that this is Standard Edition, so I can't use (for example) dbms_redefinition.
Thanks for any suggestions.
|
|
|
|
Re: SYSAUX reorganization [message #496184 is a reply to message #496155] |
Fri, 25 February 2011 02:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Those articles refer to objects within sysaux becoming excessively large, my problem is that there is a vast amount (about 90%) of unused space, which is a bit different. At some point, there must have been a load of rubbish in there, since dropped. I can bring the file size down by repeated use of move on tables, rebuild on indexes, coalesce and purge on the tablespace, and resize on the datafiles - but it is not a precise process with predictable results.
Thanks anyway.
|
|
|
Re: SYSAUX reorganization [message #496274 is a reply to message #496184] |
Fri, 25 February 2011 11:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/85219.jpg) |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
The one note mentions supported methods of moving objects out of the tablespace. If one of these objects is the one with segments at the end of the datafile, then these could allow you to shrink the datafile. In addition, you could use OEM to see what is located at the end of the datafile and this could allow you to move specific partitions of a table in order to shrink the file.
|
|
|