How do I reclaim unused space? [message #671972] |
Wed, 26 September 2018 09:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/82920145e40e0c65c671e4d0b8212ab7?s=64&d=mm&r=g) |
juniordbanewbie
Messages: 250 Registered: April 2014
|
Senior Member |
|
|
Dear all,
How do I reclaim unused space?
Scenario,
Initially I have a tablespace with 24 datafile each already extended up to 32767M.
through the export and import dump operation I was able to reduce from 24 datafiles to 16 datafiles.
but still not enough as my customer want it to be smaller....
SELECT owner, tablespace_name, count(0), sum(bytes)/1024/1024 FROM dba_segments GROUP BY cube(owner,tablespace_name)
2 ORDER BY owner, tablespace_name;
BIGDATA BIGDATA 293 498,742.750
BIGDATA 293 498,742.750
after next round of deletion of rows from the tables, there actually an increase in segments size
after the deletion of rows from the tables, there should be any decrease in segment size right since segment size will only decrease after truncate or drop operation?
I can only know the actual segment size used after reimporting the schema again right?
thanks a lot of your help
|
|
|
Re: How do I reclaim unused space? [message #671973 is a reply to message #671972] |
Wed, 26 September 2018 09:27 ![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) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
juniordbanewbie wrote on Wed, 26 September 2018 07:23Dear all,
How do I reclaim unused space?
Scenario,
Initially I have a tablespace with 24 datafile each already extended up to 32767M.
through the export and import dump operation I was able to reduce from 24 datafiles to 16 datafiles.
but still not enough as my customer want it to be smaller....
SELECT owner, tablespace_name, count(0), sum(bytes)/1024/1024 FROM dba_segments GROUP BY cube(owner,tablespace_name)
2 ORDER BY owner, tablespace_name;
BIGDATA BIGDATA 293 498,742.750
BIGDATA 293 498,742.750
after next round of deletion of rows from the tables, there actually an increase in segments size
after the deletion of rows from the tables, there should be any decrease in segment size right since segment size will only decrease after truncate or drop operation?
I can only know the actual segment size used after reimporting the schema again right?
thanks a lot of your help
DELETE statement has ZERO impact on table size or tablespace size.
Correct
>I can only know the actual segment size used after reimporting the schema again right?
|
|
|
|
|
|
Re: How do I reclaim unused space? [message #671996 is a reply to message #671972] |
Thu, 27 September 2018 07:20 ![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: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Do you have Enterprise Edition licences? If so, you could use basic compression which can achieve impressive compression ratios. If you can handle possible problems later on caused by DML against the compressed objects.
|
|
|
|
|
|
Re: How do I reclaim unused space? [message #672100 is a reply to message #672094] |
Wed, 03 October 2018 02:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
JPBoileau wrote on Tue, 02 October 2018 20:26The reason is that developers will be writing code against a smaller subset of data, which will give them faster results than in the production database.
This is 100% correct. Now you just have to persuade your client. I refer you to Ed Stevens' earlier post
|
|
|