Reorganizing tablespace files

In case you are working with HD Space limit, for instance running Oracle XE, you should be aware of following procedure to reduce tablespace files. You may have tried to reduce datafile space with command:

And obtained error:

In my case, I had an Oracle Xe 11g with big tablespaces (7 GB’s of reserved data) but used less than 1% (I only used 80MB!!). So, why the hell I cannot retrieve my empty space back? This happened because at a certain moment, schemas generated a lot of info which was deleted afterwards. Even though data isn’t already there, tablespace had grown so you have a big gap between deletion and the following inserted data.

First of all, you should discover file ID of your tablespace, you can use following query which detects free segments:

Then you can run this query which indicates which objects you should move (drop and create again is the best option) in order to be able to shrink datafile:

(source http://nikolayivankin.wordpress.com/2012/04/27/useful-scripts-get-a-list-of-objects-to-be-moved-for-a-data-file-resize/)

Then you can proceed to move data, but before dropping and creating objects, which is quite dangerous, you can try to shrink object used space instead. Since I’m quite lazy, I’ve generated a script which outputs the commands I have to execute to shrink data from all my schema tables:

1) Enabling row movement in order to shrink tables. Execute, copy / paste output and execute output again:

2) Shrinking cascade all tables (which means shrinking associated indexes as well)

3) Disabling row movement again:

So after that, I could successfully execute:

For further info, following blogs helped me to understand what I had to do:

Update 2012/11/19:

Thanks to danruca, I implemented an script including two steps to best shrink data:

Tags: , , , , ,

No comments yet.

Leave a Reply

Reorganizing tablespace files

In case you are working with HD Space limit, for instance running Oracle XE, you should be aware of following procedure to reduce tablespace files. You may have tried to reduce datafile space with command:

And obtained error:

In my case, I had an Oracle Xe 11g with big tablespaces (7 GB’s of reserved data) but used less than 1% (I only used 80MB!!). So, why the hell I cannot retrieve my empty space back? This happened because at a certain moment, schemas generated a lot of info which was deleted afterwards. Even though data isn’t already there, tablespace had grown so you have a big gap between deletion and the following inserted data.

First of all, you should discover file ID of your tablespace, you can use following query which detects free segments:

Then you can run this query which indicates which objects you should move (drop and create again is the best option) in order to be able to shrink datafile:

(source http://nikolayivankin.wordpress.com/2012/04/27/useful-scripts-get-a-list-of-objects-to-be-moved-for-a-data-file-resize/)

Then you can proceed to move data, but before dropping and creating objects, which is quite dangerous, you can try to shrink object used space instead. Since I’m quite lazy, I’ve generated a script which outputs the commands I have to execute to shrink data from all my schema tables:

1) Enabling row movement in order to shrink tables. Execute, copy / paste output and execute output again:

2) Shrinking cascade all tables (which means shrinking associated indexes as well)

3) Disabling row movement again:

So after that, I could successfully execute:

For further info, following blogs helped me to understand what I had to do:

Update 2012/11/19:

Thanks to danruca, I implemented an script including two steps to best shrink data:

No comments yet.

Leave a Reply

Reorganizing tablespace files

In case you are working with HD Space limit, for instance running Oracle XE, you should be aware of following procedure to reduce tablespace files. You may have tried to reduce datafile space with command:

And obtained error:

In my case, I had an Oracle Xe 11g with big tablespaces (7 GB’s of reserved data) but used less than 1% (I only used 80MB!!). So, why the hell I cannot retrieve my empty space back? This happened because at a certain moment, schemas generated a lot of info which was deleted afterwards. Even though data isn’t already there, tablespace had grown so you have a big gap between deletion and the following inserted data.

First of all, you should discover file ID of your tablespace, you can use following query which detects free segments:

Then you can run this query which indicates which objects you should move (drop and create again is the best option) in order to be able to shrink datafile:

(source http://nikolayivankin.wordpress.com/2012/04/27/useful-scripts-get-a-list-of-objects-to-be-moved-for-a-data-file-resize/)

Then you can proceed to move data, but before dropping and creating objects, which is quite dangerous, you can try to shrink object used space instead. Since I’m quite lazy, I’ve generated a script which outputs the commands I have to execute to shrink data from all my schema tables:

1) Enabling row movement in order to shrink tables. Execute, copy / paste output and execute output again:

2) Shrinking cascade all tables (which means shrinking associated indexes as well)

3) Disabling row movement again:

So after that, I could successfully execute:

For further info, following blogs helped me to understand what I had to do:

Update 2012/11/19:

Thanks to danruca, I implemented an script including two steps to best shrink data:

No comments yet.

Leave a Reply