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

advertisements

_____________________________________________________________________________________________________________________

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

Shrink Command Sql Syntax

Oracle 11g New Feature : Advantage of Shrink Temp Tablespace

_____________________________________________________________________________________________________________________

Website Stats