Skip to Main Content
  • Questions
  • ONLINE datafile defragmentation Oracle 11.2g

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Dominik.

Asked: December 13, 2016 - 12:01 pm UTC

Last updated: December 15, 2016 - 2:10 am UTC

Version: 11.2g

Viewed 10K+ times! This question is

You Asked

Hello Guys,

In past few days I was trying to figure it how to defragmentate datafile.
Tablespace has even more that one datafile.

I thought that with method I will be able to move extents from the end of datafile to the begging.
ALTER TABLE <table_name> ENABLE ROW MOVEMENT;
ALTER TABLE <table_name> SHRINK SPACE CASCADE;
ALTER TABLE <table_name> DISABLE ROW MOVEMENT;

But his only shrinks extents of table "together". But datafile remains fragmentate.

Also I was trying to "move" tables with DBMS_REORGANIZATION package but this package is not able to move table without PK. Since there are tables also without PK, this method I cannot use.

Please notice that it is single instance Oracle 11g on AIX.
And operation must by ONLINE.

Any ideas? thanks in advance.

and Chris said...

Why are you trying to defrag the datafile? What problem are you trying to solve exactly?

Depending on your situation and what you want at the end a combination of shrink and move should be able to help. Jonathan Lewis has a nice analogy explaining the difference between these at:

https://jonathanlewis.wordpress.com/category/oracle/infrastructure/fragmentation/

You can read more about shrink and move at:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527343800346989243

And reclaiming space at:

https://oracle-base.com/articles/misc/reclaiming-unused-space

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not exactly what I'm trying to figure it out

Dominik Chovan, December 14, 2016 - 9:47 am UTC

Hello Chris,

Thank you for you quick response.
What I want to achieve is to freed up to 100GB free space from 6 datafiles of one tablespace.
Filesystem is criticaly running out of space and unfortunately I wasn't aware that it isnot easy to deal with client abount new space. ( let say new disks)
Thats why I'm trying to lower HWM of datafiles (not tables) so I will be able to shrink them as temporary solution while new disks will come.

The links you've posted me are very useful but unfortunateli not for my situation.
As ready mentioned, this datafile reorganization must be online operation and I run of my knowledge what else I can try.

Thank you.
Chris Saxon
December 14, 2016 - 10:20 am UTC

Do you actually have 100Gb of free space in your datafiles? Have you tried lowering the table's HWMs and seeing how that helps?

Dominik Chovan, December 14, 2016 - 1:50 pm UTC

Hi Chris,

Yes of course I've tryied to shrink tables, but not for all tables it is helpfull. Sice tables which caused fragmentation are somewhere in the middle of datafiles. Tables which have extents on the end of datafiles they cannot profit from shrinking.
Shrinking of tables which are profitable from shrink space (also they are recommended by SEGMENT SPACE ADVISOR ), gaves me free space, but as I said, in the middle of datafiles, so I'm not able to release free space alocated for datafiles.

Hope that my situation is more clear now.

What do you think Chris, is it possible to defragment such kind of datafiles in ONLINE mode?

Thanks for your help.
Chris Saxon
December 14, 2016 - 2:51 pm UTC

You could use dbms_redefinition to move the tables online. But you will need enough space to store the whole table + its copy. So if you're struggling for space this may be a non-starter...

Something else to consider:

Do your tables have large number of indexes (in this tablespace)? If so, it may be worth an audit to see if you can remove some. Or look into index compression:

https://richardfoote.wordpress.com/2008/02/17/index-compression-part-i-low/

Dominik Chovan, December 14, 2016 - 4:27 pm UTC

You could use dbms_redefinition to move the tables online. But you will need enough space to store the whole table + its copy. So if you're struggling for space this may be a non-starter...

Unfortunatelly, as I stated in problem description DBMS_REDEFINITION cannot be used on these tables because they don't have PK.

Something else to consider:
Do your tables have large number of indexes (in this tablespace)? If so, it may be worth an audit to see if you can remove some. Or look into index compression:


in this production environment there are severel TABLESPACES, so compressing indexes will save se space only for this particullar tablespace which already have enough free space - 100GB.
Or you want me to reduce space with compriming INDEXES also in other TABLESPACES? If do, it is particular solution ONLY and not solving the question I'm asking.
I'm trying to release this space for other TABLESPACES, by simple resizing affected datafiles. But first I need to reorganize space in them so I will be able to resize them.

From what we have already mentioned in this thread, for t looks like that it is not possible to reoganize space on datafiles with this specific setup we have?
Thanks.
Connor McDonald
December 15, 2016 - 2:10 am UTC

"Unfortunatelly, as I stated in problem description DBMS_REDEFINITION cannot be used on these tables because they don't have PK. "

Having a PK is not mandatory.

"options_flag
Indicates the type of redefinition method to use:
If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys.
If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids."


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library