Oracle ORA-10917: TABLESPACE GROUP cannot be specified
Oracle Cannot be group specified Tablespace ORA
2023-09-11 14:18:40 时间
参考文档 https://blog.csdn.net/qq_27917209/article/details/80059629
检查alert日志发现近期大量ORA-1652报错
截止上午10:00近2天报错次数为19次
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Tue Feb 16 23:45:20 2016
2.问题点分析
1.> 默认表空间问题:
[sql] view plain copy
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL>
经查询默认临时表空间是TEMP
但是erp库中根本没有temp表空间
[sql] view plain copy
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP1
TEMP2
PROD2_IAS_TEMP
PROD1_IAS_TEMP
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- ------------------------------
+DATA_ERP/prod/tempfile/temp1.302.835913167 3 TEMP1
+DATA_ERP/prod/tempfile/temp2.303.835913167 4 TEMP2
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743 1 PROD1_IAS_TEMP
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877 2 PROD2_IAS_TEMP
SQL>
2.> TEMP1和TEMP2报空间不足的问题
[sql] view plain copy
SQL> SELECT A.tablespace_name tablespace,
2 D.mb_total,
3 SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
4 D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
5 FROM v$sort_segment A,
6 (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
7 FROM v$tablespace B, v$tempfile C
8 WHERE B.ts# = C.ts#
9 GROUP BY B.name, C.block_size) D
10 WHERE A.tablespace_name = D.name
11 GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1 4000 28.75 3971.25
TEMP2 4000 147 3853
SQL>
3.解决方案
1.> 针对默认临时表空间问题,建议添加默认临时表空间temp
但是出现了奇怪的问题,居然报错TEMP已存在
[sql] view plain copy
SQL> create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M ;
create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M
ORA-01543: tablespace 'TEMP' already exists
SQL> alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M;
alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M
ORA-10917: TABLESPACE GROUP cannot be specified
这是临时表空间组,好吧,没想到
[sql] view plain copy
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
SQL>
2.> 针对短时间内报错问题
临时表空间4G太小了,改为自动扩展
[sql] view plain copy
SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp1.302.835913167' autoextend on next 100M;
Database altered
SQL>
SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp2.303.835913167' autoextend on next 100M;
Database altered
SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, INCREMENT_BY from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES AUTOEXTENSIBLE INCREMENT_BY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------
+DATA_ERP/prod/tempfile/temp1.302.835913167 TEMP1 4194304000 YES 12800
+DATA_ERP/prod/tempfile/temp2.303.835913167 TEMP2 4194304000 YES 12800
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743 PROD1_IAS_TEMP 104857600 YES 6400
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877 PROD2_IAS_TEMP 104857600 YES 6400
SQL>
截止上午10:00近2天报错次数为19次
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP1
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Tue Feb 16 23:45:20 2016
2.问题点分析
1.> 默认表空间问题:
[sql] view plain copy
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL>
经查询默认临时表空间是TEMP
但是erp库中根本没有temp表空间
[sql] view plain copy
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP1
TEMP2
PROD2_IAS_TEMP
PROD1_IAS_TEMP
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- ------------------------------
+DATA_ERP/prod/tempfile/temp1.302.835913167 3 TEMP1
+DATA_ERP/prod/tempfile/temp2.303.835913167 4 TEMP2
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743 1 PROD1_IAS_TEMP
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877 2 PROD2_IAS_TEMP
SQL>
2.> TEMP1和TEMP2报空间不足的问题
[sql] view plain copy
SQL> SELECT A.tablespace_name tablespace,
2 D.mb_total,
3 SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
4 D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
5 FROM v$sort_segment A,
6 (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
7 FROM v$tablespace B, v$tempfile C
8 WHERE B.ts# = C.ts#
9 GROUP BY B.name, C.block_size) D
10 WHERE A.tablespace_name = D.name
11 GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1 4000 28.75 3971.25
TEMP2 4000 147 3853
SQL>
3.解决方案
1.> 针对默认临时表空间问题,建议添加默认临时表空间temp
但是出现了奇怪的问题,居然报错TEMP已存在
[sql] view plain copy
SQL> create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M ;
create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M
ORA-01543: tablespace 'TEMP' already exists
SQL> alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M;
alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M
ORA-10917: TABLESPACE GROUP cannot be specified
这是临时表空间组,好吧,没想到
[sql] view plain copy
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
SQL>
2.> 针对短时间内报错问题
临时表空间4G太小了,改为自动扩展
[sql] view plain copy
SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp1.302.835913167' autoextend on next 100M;
Database altered
SQL>
SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp2.303.835913167' autoextend on next 100M;
Database altered
SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, INCREMENT_BY from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES AUTOEXTENSIBLE INCREMENT_BY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------
+DATA_ERP/prod/tempfile/temp1.302.835913167 TEMP1 4194304000 YES 12800
+DATA_ERP/prod/tempfile/temp2.303.835913167 TEMP2 4194304000 YES 12800
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743 PROD1_IAS_TEMP 104857600 YES 6400
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877 PROD2_IAS_TEMP 104857600 YES 6400
SQL>
相关文章
- Oracle数据库使用出现错误-状态: 失败 ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
- Oracle-查看oracle是否有表被锁
- 【Oracle】并行等待之PX Deq Credit: need buffer
- 【Oracle】利用在线重定义的方式改变普通表为分区表
- 【oracle】静默安装oracle软件和数据库之三
- 《oracle每日一练》Oracle DBLink连接数过多的问题(Ora-02020)
- 《oracle每天一练》Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Oracle EXPDP导出数据
- php oracle数据库clob和nclob字段
- Oracle导入excel数据快速方法
- oracle 11g在linux下的安装指引-tina
- Oracle手边常用70则脚本知识汇总
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之集群概念介绍(一)
- Step by step Install Oracle 11gR2(11.2.0.4.0) RAC+Active DataGuard on Oracle Enterprise Linux(OEL6.3X86_64)
- Oracle 强制审计
- Oracle 11g AMM与ASMM切换
- 64位Oracle用pl/sql导入表
- ORACLE 官方文档
- C# 连接Oracle数据库,免安装oracle客户端
- ORACLE中的Net Configuration Assistant 点击后无反应, sqlplus登录数据库提示Oracle11g ORA-12560: TNS: 协议适配器错误
- 从DML角度看ADS和ORACLE的区别
- Ubuntu Linux下安装Oracle JDK
- oracle动态游标
- ORACLE 中查看CDB、PDB数据文件
- Oracle对临时表空间管理以及UNDO表空间的管理
- Oracle 11g 053 课程 使用 RMAN 创建备份
- Oracle 12c中SQLPlus操作使用(包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释)