CODEWITHSUNDEEP
oracle-database
dang
dang

Reputation: 2412

Oracle - Move all tables and reclaim free space

I have this query:

select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
from dba_extents
where tablespace_name = 'USERS'
union all
select file_id, block_id, block_id+blocks-1, 'free'
from dba_free_space
where tablespace_name = 'USERS'
order by file_id, first_block DESC;

It shows lot of "free" segments in between. There are lot of tables which are coming in between.

I move the tables using:

Alter table table_name move;

I have 2000 such tables. Is there a way on how I can move it altogether so I can reclaim all the free space from the tablespace?

Upvotes: 1

Views: 4975

Answers (1)

Giova
Giova

Reputation: 1127

To achieve your goal you have to move all objects in the tablespace, not just tables. At least you have to move tables and then rebuild all their indexes because when you move a table all indexes built on this table are invalidated. You can not move all tables all together but you can obtain all commands in the following way:

 select 'alter table ' ||table_name ||' move;'
 from dba_tables where tablespace_name = 'YOURTABLESPACENAME';

To rebuild indexes:

select 'alter index ' ||index_name ||' rebuild;'
 from dba_indexes where tablespace_name = 'YOURTABLESPACENAME' and status <>'VALID';

Be careful: this procedure is not complete, you can have indexes of different kind.

Note: to obtain best results you should move objects in a different tablespace.

A simpler approach can be the one explained here: this article describe how to use the shrink command against Oracle database tables to reclaim wated space.

If you want a detailed procedure give me the result of the following query:

select distinct segment_type from  dba_segments where tablespace_name='YOURTABLESPACENAME';

Upvotes: 2

Related Questions