Home » RDBMS Server » Server Administration » How do I reclaim unused space? (11.2.0.4, Windows 2012)
How do I reclaim unused space? [message #671972] Wed, 26 September 2018 09:23 Go to next message
juniordbanewbie
Messages: 232
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 messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Wed, 26 September 2018 07:23
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
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 #671978 is a reply to message #671973] Wed, 26 September 2018 09:49 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Unless this is an archival operation, consensus is to add more disk than shrink data files. A database, generally, grows.
Re: How do I reclaim unused space? [message #671991 is a reply to message #671972] Thu, 27 September 2018 06:40 Go to previous messageGo to next message
EdStevens
Messages: 1077
Registered: September 2013
Senior Member
juniordbanewbie wrote on Wed, 26 September 2018 09:23
Dear all,



but still not enough as my customer want it to be smaller....

Customer expectations may very well exceed the laws of physics.
Re: How do I reclaim unused space? [message #671994 is a reply to message #671972] Thu, 27 September 2018 07:16 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
but still not enough as my customer want it to be smaller....
I missed this, thanks Ed.

@OP, how "small" does your customer want it? Databases grow.
Re: How do I reclaim unused space? [message #671996 is a reply to message #671972] Thu, 27 September 2018 07:20 Go to previous messageGo to next message
John Watson
Messages: 7933
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 #672082 is a reply to message #671994] Tue, 02 October 2018 01:25 Go to previous messageGo to next message
juniordbanewbie
Messages: 232
Registered: April 2014
Senior Member
Dear gazzag

probably less than 80 GB. We need to create a developing database from a production database. So effectively we need to delete a lot of data through the application. I realize that it took weeks to delete the data.

My customer don't have a enteprise lession.

Thanks a lot for all your responses.
Re: How do I reclaim unused space? [message #672085 is a reply to message #672082] Tue, 02 October 2018 03:30 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
So you need to reclaim over 400GB of space? That's a big ask. You might be better off recreating a dev box from scratch.
Re: How do I reclaim unused space? [message #672094 is a reply to message #672085] Tue, 02 October 2018 14:26 Go to previous messageGo to next message
JPBoileau
Messages: 59
Registered: September 2017
Member
I do not suggest reducing the database to such a small size (80GB vs original of 400GB). The 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.

Storage is cheap these days, I highly suggest that you make your dev/test/pre-production as large as the production database. This will prevent lots of headaches in the future.

JP
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
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
JPBoileau wrote on Tue, 02 October 2018 20:26
The 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 Smile
Previous Topic: Reading Hard disk file details through plsql
Next Topic: Migrations and replications
Goto Forum:
  


Current Time: Sat Jun 15 19:55:35 CDT 2019