0

I have a tablespace has size 307,200 MB and "Used (MB)" is 103 229,81. This tablespace was created with "BIG" Datafile. For recent time "Used (MB)" value increase insignificantly. So I want to shrink the tablespace.

alter tablespace MYTABLESPACE shrink space keep 130G

raises

ORA-12916: cannot shrink permanent or dictionary managed tablespace

Yes. This tablespace is "permanent", but its size is bigger in three times than the used size is, and this fact is a problem for me. How can I shrink it?

2

The error message is quite straightforward. You are trying to shrink a permanent tablespace using a command to shrink temporary tablespace.

12916, 00000, "cannot shrink permanent or dictionary managed tablespace"
// *Cause: An attempt was made to shrink a permanent tablespace or a
//         dictionary managed tablespace.
// *Action: Check the tablespace type and issue the statement only on
//          locally managed temporary tablespaces.

Example to resize BIGFILE Tablespace.

ALTER TABLESPACE bigtbs RESIZE 80G;

Refer to Altering and Maintaining Tablespaces

  • Thank you. Unfotunately, I was able to shrink only to 205G by an unсlear reason. – 4per May 21 '18 at 5:07
  • 2
    Your "unclear" reason is only "unclear" because you failed to post the very clear error message. Most likely it failed because the high-water mark was at 205 G. To resize a file, it is not just a matter of how much unused space it has, but WHERE that unused space is located. – EdStevens May 21 '18 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.