Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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 100+ 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




More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database