How to move the data of SYSAUX Tablespace temporary

[former_member185031]

Hello Community,

I have a SAP BW Sysem which is running on Oracle 11.2.0.4. This system contains the SM/OPTSTATS data since 2014. These were not investigated earlier and the size of SYSAUX grown to 2.5 TB.

So we have started working on cleaning of these tables associated with SM/OPTSTAT tables. So we are able to gain the space on Table space SYSAUX by deleting and reorg tables & indexes.

As 2.5 TB is too much space we started to work to release it on File System. We read so many Metalink notes and found a way that we can only resize the data file once objects have been moved from that data file

This note talks about that.

How to Resize a Datafile (Doc ID 1029252.6)

But the script @SHRINK_DATAFILE.SQL attached to this note only works with Datafile level. I have 100 datafile on each systems and there are total 6 Systems so it is a very time consuming task.

So the question i would like to ask here is do you know any other method which can help me to move these objects of SYSAUX table space to a temporary table space so that I can shrink the data file and move the objects back to Sysaux.

Thanks & Regards,

Subhash

[former_member185031]

Just for the information to everyone, I have received a response from Oracle support and they said there is no other way to achieve what I am trying to achieve here as all the objects can't be moved from SYSAUX table space.

Regards,

Subhash

[former_member185031]

Hi Reagan,

Thanks for the response. I have moved only OPTSTAT related table,index, & index partition from SYSAUX to SYSAUX_TEMP, which takes 99% of space. But Database does not allow to shrink the data file because of 1% of objects are still there. So my only problem is i am not able to identify that 1% objects on table space level.

@SHRINK_DATAFILE.SQL script gives me only option to identify based on data file level and move it one by one. So i was just checking if anyone encounter this kind of weird issue and there is any other work around to reduce some time and effort.

Thanks again

Regards,

Subhash

[former_member185031]

Hi Reagan,

Thanks for the responding to my question. Yes, after performing the deletes and reorg I moved those SM/OPTSTAT related table and Indexes to the temporary table space as per the note you listed.

We have tried everything, Space has been released back to SYSAUX, only thing i am not able to do here release the space to file system as i need to resize the datafile, which oracle does not allow, so as per the note 1029252.6 it gives me option to identify and move the objects which are necessary to resize the data file but it does not gives me option to identify the objects on table space level. So i am just looking for any options to find the objects on SYSAUX level which i will have to move to resize the datafile and release the space to file system.

Thanks again

Regards,

Subhash

[Reagan]

Hi Subhash,

If you have moved all the items from the original SYSAUX to an auxiliary tablespace SYSAUX_TEMP, do you have any items left in the original SYSAUX tablespace. If there aren't any objects in the old tablespace then technically you should be able to resize the datafiles. Use this script and see what the output for the SYSAUX datafiles is.

https://github.com/geekcomputers/Oracle-SQL/blob/master/maxshrink.sql

It will list the usage info and also generates the datafile resize SQL statements as well

[Reagan]

Subhash,

As you have access to Oracle Metalink, have you checked the below Oracle note?

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)