DEALLOCATE UNUSED

[Sameer]

Hi,

If I execute

ALTER TABLE tab_name DEALLOCATE UNUSED; to recover the unused blocks.. will it invalid procedures or triggers written on tab_name table?

I tried this by executing test statements... It worked well and didn't invalid any procedure or triggres written on tab_name.

I just want to make sure by asking experts before running it on production database.

Please answer.

Thanks

Sameer

[SANJAY_G]

I don't see any reason that deallocating unused will invalid procedurs/packages.

Sanjay

[Sameer]

Yes, true...

But I am bit scared because of "ALTER TABLE" words

Because if you try to modify a column having trigger on it , then it INVALIDS the trigger.

So I just wanted to make sure

Thanks

Sameer

[SANJAY_G]

I would go for it without thinking twice..
Good Luck!!

Sanjay

[denevge]

Be aware that DEALLOCATE UNUSED does not work if the segment has only 1 extent.
The initial extent can not be deallocated.

Regards
Gert

[SANJAY_G]

Be aware that DEALLOCATE UNUSED does not work if the segment has only 1 extent.
The initial extent can not be deallocated.

In that case Oracle will return an error..

Sanjay

[aarroyob]

Deallocate unused only works (make it's function) with this space above the HWM (high water mark).

Cheers

Angel

[aarroyob]

Ops ...

If the space is below the HWM and you are in 8i, you could make

alter table XXXXX move tablespace YYYYY storage (initial ...)

Cheers

Angel