ORA-12916 cannot shrink permanent or dictionary managed tablespace

本文主要是介绍ORA-12916 cannot shrink permanent or dictionary managed tablespace，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

--在数据库迁移过程中，担心新库中担心存储不够，统计下数据量大小。

缺发现有空间需要shrink,  但是报错了， 表示默认表空间不允许shink.

SQL> select 6, tablespace_name,bytes/1024/1024/1024 from dba_data_files2  where tablespace_name='TBDATA_DEFAULT'3  union all4  select 6, tablespace_name,sum(bytes)/1024/1024/1024 from user_segments5  where tablespace_name='TBDATA_DEFAULT'6  group by tablespace_name7  ;6 TABLESPACE_NAME                BYTES/1024/1024/1024
---------- ------------------------------ --------------------6 TBDATA_DEFAULT                             2361.1256 TBDATA_DEFAULT                                   90SQL> alter tablespace TBDATA_DEFAULT shrink space;alter tablespace TBDATA_DEFAULT shrink spaceORA-12916: cannot shrink permanent or dictionary managed tablespaceSQL> 

--那么我们改变默认表空间

SQL> col file_name format a50;
SQL> select * from dba_data_files;FILE_NAME                                             FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
+DG01DATA/unicom/datafile/system.263.889796403              1 SYSTEM                         1555038208     474560 AVAILABLE         1024 YES            1407374882 4294967293          320 1554933350      474528 SYSTEM
+DG01DATA/unicom/datafile/sysaux.264.889796407              2 SYSAUX                         2967470080      90560 AVAILABLE         1024 YES            1407374882 4294967293          320 2966421504       90528 ONLINE
+DG01DATA/unicom/datafile/undotbs1.265.889796409            3 UNDOTBS1                       1974992896     602720 AVAILABLE         1024 YES            1407374882 4294967293          160 1974888038      602688 ONLINE
+DG01DATA/unicom/datafile/undotbs2.267.889796431            4 UNDOTBS2                       4482662400     136800 AVAILABLE         1024 YES            1407374882 4294967293          160 4481613824      136768 ONLINE
+DG01DATA/unicom/datafile/users.268.889796431               5 USERS                          1938554880      59160 AVAILABLE         1024 YES            1407374882 4294967293           40 1937506304       59128 ONLINE
+DG01DATA/unicom/datafile/tbdata_default.272.88985          6 TBDATA_DEFAULT                 2535238664   77369344 AVAILABLE         1024 YES            1407374882 4294967293         8192 2535234469    77369216 ONLINE
7763                                                                                                                                                                                                               6 rows selectedSQL> 
SQL> select username,default_tablespace,temporary_tablespace2  from dba_users3  where username = 'UNICOMIDMP'4  ;USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
UNICOMIDMP                     TBDATA_DEFAULT                 TEMPSQL>  alter user unicomidmp default tablespace users;User alteredSQL> 
SQL>  select username,default_tablespace,temporary_tablespace2  from dba_users3  where username = 'UNICOMIDMP'4  ;USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
UNICOMIDMP                     USERS                          TEMPSQL> 

--执行shrink

SQL> alter tablespace TBDATA_DEFAULT shrink space;alter tablespace TBDATA_DEFAULT shrink spaceORA-12916: cannot shrink permanent or dictionary managed tablespace

--还是报错。

说明不是默认表空间的问题。

而是字典表空间无法shrink。，并且要求自动增长的。 system表空间也是无法shrink

这篇关于ORA-12916 cannot shrink permanent or dictionary managed tablespace的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---