Thanks for the question, Paul .
Asked: August 04, 2022 - 5:00 pm UTC
Last updated: August 05, 2022 - 12:07 am UTC
Version: 19.2
Viewed 1000+ times
You Asked
I use the process below to shrink datafiles and reclaim operating system space. It works fine.
My question is there any downside to ALWAYS leaving row movement enabled instead of disabling it when the process is completed?
Below is a sample of what I'm currently doing.
create table t as
select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000;
/
-- ALTER TABLE t SHRINK SPACE CHECK;
select bytes from user_segments
where segment_name = 'T';
/
delete t where x <= 9900;
/
select bytes from user_segments
where segment_name = 'T';
/
alter table t enable row movement;
/
alter table t shrink space CASCADE;
/
select bytes from user_segments
where segment_name = 'T';
and Connor said...
It is only a risk if you have applications that might depend on the ROWID for the duration of their operation.
For example, some old Forms apps would do:
select rowid, [rest of data]
to populate a screen, and then later when you make changes and save, it would do:
update mytable
set ...
where rowid = [selected rowid]
If you're not doing that, then row movement on tables is unlikely to cause any harm.
Note however that on *autonomous*, having row movement enabled is your permission for us to move data around automatically to improve performance. I'd strongly recommend a look at this video
Is this answer out of date? If it is, please let us know via a Comment