Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ALTER temp TABLESPACE SHRINK keeps failing with "contains used data beyond requested RESIZE value"

1007321Oct 14 2014 — edited Oct 14 2014

Hello,

I'm in 11gR2, and saw that we -eventually- have a command supposed to let you resize temporary tbsp files.

I've been trying to use this new feature, with:

SQL> alter tablespace temp shrink space keep 200G;

alter tablespace temp shrink space keep 200G

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

but I'm always returned this error message; yet I checked the HWM in the file (this is a bigfile tbsp) by

taking the max(SEGBLK#)+BLOCKS from V$TEMPSEG_USAGE/V$SORT_USAGE, which gives something

around 30Gb, so I expected that keeping 200Gb would pass...

Someone knows how to effectively check how many segments remain and up to which location in the

file - so that I can specify a proper KEEP clause ?

Thanks a lot...

Regards,

Seb

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 11 2014

Added on Oct 14 2014

#general-database-discussions

7 comments

973 views