实验环境一
SQL> create table FACT_XX01
(
MAX VARCHAR2(10) not null,
POLNO VARCHAR2(30),
MAINPOLYEAR VARCHAR2(100)
)
tablespace test
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 4M
minextents 1
maxextents unlimited
pctincrease 0
);
Table created.
SQL> insert into FACT_XX01 values ('zhaoxiaobi','zhaoxiaobing','adnfuafnweguwergq');
1 row created.
SQL> commit;
Commit complete.
32768 rows created.
SQL> commit;
表空间名称 EXTENT_MAN SEGMEN 表空间大小(M) 已使用空间(M) 使用比
------------------------------ ---------- ------ ------------- ------------- -------
SYSTEM LOCAL MANUAL 250 249 99.60
UNDOTBS1 LOCAL MANUAL 960 953.19 99.29
SYSAUX LOCAL AUTO 370 343.81 92.92
TEST LOCAL AUTO 5 4.06 81.20
USERS LOCAL AUTO 288.75 154.44 53.49
SQL> create table n_wzl_0930 tablespace test nologging parallel 5 as
SELECT /*+parallel(a, 8)*/
MAX,
POLNO,
MAINPOLYEAR
FROM FACT_XX01 a 2 3 4 5 6
7 ;
create table n_wzl_0930 tablespace test nologging parallel 5 as
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P004
ORA-01652: unable to extend temp segment by 8 in tablespace TEST
数据库alert日志
ORCLPDB1(3):ORA-1652: unable to extend temp segment by 8 in tablespace TEST [ORCLPDB1]
2019-09-30T14:09:55.170847+08:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_vktm_6676.trc
[oracle@alonly trace]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
注意这里的FACT_XX01是test所在表空间,并非temp表空间,所以这里的ORA-1652并非是group by引起,而且在进行insert into ... select ... 时需要在insert into的表空间中产生一个临时段用于存储select查询产生的结果集,待语句执行结束后,这个临时段会变成永久段,就是insert into的表段。会产生这种临时段的操作还很多,例如:create index、create pk constraint、enable constraint、CATS等。 所以,要解决这个错误,可以整理表空间碎片,也可以为表空间增加新的空间。
实验环境二
SQL> create table FACT_XX01
(
MAX VARCHAR2(10) not null,
POLNO VARCHAR2(30),
MAINPOLYEAR VARCHAR2(100)
)
tablespace test;
SQL> insert into FACT_XX01 select * from FACT_XX01;
SQL> create table n_wzl_0930 tablespace test nologging parallel 5 as
SELECT /*+parallel(a, 8)*/
MAX,
POLNO,
MAINPOLYEAR
FROM FACT_XX01 a 2 3 4 5 6
7 ;
create table n_wzl_0930 tablespace test nologging parallel 5 as
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-01658: unable to create INITIAL extent for segment in tablespace TEST