admin管理员组文章数量:1642448
--在数据库迁移过程中，担心新库中担心存储不够，统计下数据量大小。
缺发现有空间需要shrink, 但是报错了， 表示默认表空间不允许shink.
SQL> select 6, tablespace_name,bytes/1024/1024/1024 from dba_data_files
2 where tablespace_name='TBDATA_DEFAULT'
3 union all
4 select 6, tablespace_name,sum(bytes)/1024/1024/1024 from user_segments
5 where tablespace_name='TBDATA_DEFAULT'
6 group by tablespace_name
7 ;
6 TABLESPACE_NAME BYTES/1024/1024/1024
---------- ------------------------------ --------------------
6 TBDATA_DEFAULT 2361.125
6 TBDATA_DEFAULT 90
SQL> alter tablespace TBDATA_DEFAULT shrink space;
alter tablespace TBDATA_DEFAULT shrink space
ORA-12916: cannot shrink permanent or dictionary managed tablespace
SQL> --那么我们改变默认表空间
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 selected
SQL>
SQL> select username,default_tablespace,temporary_tablespace
2 from dba_users
3 where username = 'UNICOMIDMP'
4 ;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
UNICOMIDMP TBDATA_DEFAULT TEMP
SQL> alter user unicomidmp default tablespace users;
User altered
SQL>
SQL> select username,default_tablespace,temporary_tablespace
2 from dba_users
3 where username = 'UNICOMIDMP'
4 ;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
UNICOMIDMP USERS TEMP
SQL>
--执行shrink
SQL> alter tablespace TBDATA_DEFAULT shrink space;
alter tablespace TBDATA_DEFAULT shrink space
ORA-12916: cannot shrink permanent or dictionary managed tablespace--还是报错。
说明不是默认表空间的问题。
而是字典表空间无法shrink。，并且要求自动增长的。 system表空间也是无法shrink
本文标签： shrinkORApermanentTablespacemanaged
版权声明：本文标题：ORA-12916 cannot shrink permanent or dictionary managed tablespace 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://m.elefans.com/dongtai/1729334467a1196800.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论