Shrinking a tablespace - datafile

Posted on 2003-03-05
Medium Priority
Last Modified: 2012-06-27
Is there an easy way to shrink the sizse of a tablespace datafile if, for example, the size of the file is 1.5gig but there's only .5 gig of that file currently being used.
Question by:adjdar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8075563
What database are you using? Access, SQL, mySQL?


Author Comment

ID: 8075611
this is oracle...sorry for not mentioning that.

Accepted Solution

Danielzt earned 150 total points
ID: 8075879

1: use this sql to show the block allocation in the tablespace.

select file_name,a.tablespace_name,owner, segment_name, segment_type,a.block_id
from dba_extents a, dba_data_files b
where a.file_id=b.file_id
     and a.tablespace_name='?????'

2: you can not shrink the datafile, that's because your high water mark is big(the biggest block_id used in your datafile)
  if you find some tables which use a block_id, you need to copy these tables to another tablespace, then drop these tables , shrink your datafile, and copy them back.

3: you can also export all these tables, drop them, shrink your datafile, and import all the tables back.

try it.


Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question