Oracle 11g ORA-12916: cannot shrink permanent or dictionary managed tablespace; Oracle Tablespace Shrink Command Failed

Error Description:
Oracle tablespace shrink command failed with following error.

SQL> ALTER tablespace data01 shrink space keep 31g;
ALTER tablespace data01 shrink space keep 31g
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace

Cause of error:

You can only shrink locally managed temporary tablespaces. If you try to shrink a data tablespace with content type as permanent, you will get this error. The same is applicable for the UNDO or dictionary managed tablespaces also.

SQL>  ALTER tablespace UNDOTBS1 shrink space keep 20g;
 ALTER tablespace UNDOTBS1 shrink space keep 20g
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace

You can check this by following query. Only you can shrink the tablespace marked in red color below.
SQL> select tablespace_name, contents, EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         PERMANENT LOCAL
SYSAUX                         PERMANENT LOCAL
UNDOTBS1                       UNDO      LOCAL
TEMP                           TEMPORARY LOCAL
USERS                          PERMANENT LOCAL
UNDOTBS2                       UNDO      LOCAL
DATA01                         PERMANENT LOCAL
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google