Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-1652 on CTAS

sql_coderFeb 19 2018 — edited Feb 22 2018

Hello,

using 11.2.0.4 Enterprise Edition, no archive mode, I have a problem with a Create Table As Select statement, cause I am running out of Temp Tablespace with Ora-1652. I check it twice, it is not a normal data tablespace, but the temp one. The statement is like that:

CREATE TABLE t1

...

PARTITION BY RANGE (date_Column)

(PARTITION Part_Name VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))

...

LOGGING

NOPARALLEL

AS

SELECT *

FROM tab_source

WHERE date_Column > sysdate - 1800

;

Inside that statement is no index or constraint creation like primary/Unique, only storage parameter and the partition part. My question is, why this statement is filling my temp tabelspace. Would I would expect is, that I could run out of normal data space, so I would add more data files to that tablespace. But I have no clue why it is our temp tablespace....

sql_coder

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 22 2018
Added on Feb 19 2018
45 comments
2,708 views