Oracle Shrink Space, don’t do it just once

I’ve noticed interesting results with shrinking Oracle tables. The strangest is that sometimes the tables actually get bigger, rather than smaller. For a long time I’ve had a habit of doing the shrink twice because often I see the space actually go down on the second shrink. In this case the table actually grew bigger the first time.
Please note that this is a SYS table and I was doing this based on My Oracle Support articles for freeing up space in SYSAUX. Please do not use this example on your database unless you don’t care too much what happens to it.
Here are the commands I was running:

alter table WRH$_SYSMETRIC_HISTORY enable row movement;
alter table WRH$_SYSMETRIC_HISTORY shrink space cascade;
alter table WRH$_SYSMETRIC_HISTORY disable row movement;
 
Here are the results from the runs.

SQL> @tblidx WRH$_SYSMETRIC_HISTORY
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS BYTES
-------------------------------- ------------------ -------------------- -------- -------------
WRH$_SYSMETRIC_HISTORY TABLE SYSAUX 291 4476370944
WRH$_SYSMETRIC_HISTORY_INDEX INDEX SYSAUX 308 5096079360

-- Here is where I ran the first shrink. You can see below that the table ended up with more extents and more bytes of data. I don’t know, it is theoretically possible that a lot of data got added to the table while this was running, but given the shrink didn’t report an error, this seems very odd.

WRH$_SYSMETRIC_HISTORY TABLE SYSAUX 297 4631298048
WRH$_SYSMETRIC_HISTORY_INDEX INDEX SYSAUX 275 3740139520

-- Here I ran the shrink again, per my habit, and this time the size of the table dropped, not significantly, but by a bit, and the index got a bit smaller again.

WRH$_SYSMETRIC_HISTORY TABLE SYSAUX 290 4339990528
WRH$_SYSMETRIC_HISTORY_INDEX INDEX SYSAUX 268 3492020224

I would say this is just coincidence and a bunch of data got dumped in, but I see often where a shrink results in little change, usually for the good, and then on the second shrink I’ll get a much bigger drop in space.
Have fun, and shrink twice!

One response to “Oracle Shrink Space, don’t do it just once

  1. Pingback: Oracle Shrink Space, don’t do it just once | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s