• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Shrinking a tablespace - datafile

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.
1 Solution
What database are you using? Access, SQL, mySQL?

adjdarAuthor Commented:
this is oracle...sorry for not mentioning that.

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now