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.
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.
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.
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."