claim space

dba88

Hi

SYSAUX tablespace in our database has around 400GB of free space. We want to reduce the size of tablespace to reclaim the free space as it is not much used.

What are the step need to follow to do it.

From another tablespace, we have delete around 50% of data and want to claim the space release by deletion.

let me know

thanks

CDS

Masiuddin Ahmed-Oracle

Hi

You have not mentioned the version of Oracle database currently being used.

To shrink the tablespace and claim the space we can use the command from SQL prompt as follows

ALTER TABLESPACE TABLESPACENAME SHRINK;

If required you can do enable row moment before executing this command / statement.

Thanks

Masiuddin Ahmed

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16827864#Comment_16827864

There is no such statement. Shirnk space/shrink tempfile is related to temporary tablespace only.

Mustafa_KALAYCI

https://community.oracle.com/tech/developers/discussion/4495501/claim-space

What is the size of the data in SYSAUX? please check  support document 1563921.1

basically, move objects into a new tablespace, shrink sysaux datafiles and then move them back.

Jonathan Lewis

@Mustafa_KALAYCI

I'm going to email an Oracle management figure about that note. It's showing "last updated" as March 22 but (a) doesn't mention v$sysaux_occupants, and (b) only warns about the dangers of moving XDB objects. There are a number of other groups of tables that should only be moved through one of the 13 supplied procedures:

DBMS_AMD.Move_OLAP_Catalog
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
DBMS_AW.MOVE_AWMETA
DBMS_WM.move_proc
DBMS_XSOQ.OlapiMoveProc
DRI_MOVE_CTXSYS
MDSYS.MOVE_SDO
MOVE_WK
SYS.DBMS_LOGMNR_D.SET_TABLESPACE
SYS.DBMS_LOGSTDBY.SET_TABLESPACE
XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
emd_maintenance.move_em_tblspc
ordsys.ord_admin.move_ordim_tblspc

Regards

Jonathan Lewis

PS I've just published a little note about the relevant view: https://jonathanlewis.wordpress.com/2022/03/07/sysaux-occupants/

Mustafa_KALAYCI

https://community.oracle.com/tech/developers/discussion/comment/16828098#Comment_16828098

and I forgot about those :) thanks Jonathan. I mostly check dba_extents and try to figure out where are the big empty spaces and move objects which are at the end of the file to be able to reach empty spaces and run alter database datafile resize command. of course I do this for user created tablespaces, I never move sysaux before. thanks for warning.

Jonathan Lewis

You're welcome.

The intent in that note, though, is to increase the free space in the tablespace by moving objects that might take less space after being rebuilt. It evens says at one point "you don't need to worry about small objects" while we know that it might be a small object which is blocking the "resize datafile" command. (For the same reason it doesn't mention the possibility that the recyclebin may block a "resize datafile" command.)

For the OP - there are a couple of notes on my blog on the topic, though none of them cover the detail of when it is, or is not, safe to do a simple move / rebuild, starting at: https://jonathanlewis.wordpress.com/2014/11/14/shrink-tablespace-4/

Regards

Jonathan Lewis