CREATE TABLE AS SELECT(CAST)(一)-ORA-1652

 

一、Symptoms
        During CTAS (CREATE TABLE AS SELECT), be reported:
        ORA-01652: unable to extend temp segment by 1024 in tablespace

二、Cause
        The tablespace where the object is being created doesnt have sufficient space to extend for the CTAS command to succeed.

三、Solution
        Modify the datafile associated for the tablespace to AUTOEXTEND ON till the CTAS command gets executed successfully.
        During the CTAS , it creates a data segment in the target tablespace and marks this segment as temporary in dictionary.
       Once the table created successfully , the dictionary type is changed from TEMPRORAY to TABLE. In addition, if the SELECT performs a SORT operation,temporary space may be used as for the same.

四、TEST CASE
       1. 建立表空間
           SQL> CREATE TABLESPACE TEST DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\TEST.DBF' SIZE 10M AUTOEXTEND OFF;
           Tablespace created
        2. 為客戶分配預設表空間
           SQL> ALTER USER TESTER DEFAULT TABLESPACE TEST;
           User altered.
        3. 查詢物件實際大小
          SQL>SELECT BYTES/1024/1024,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME='DUMMY';
                     11       system    --Size of the DUMMY object is 11 M
       4. CTAS建立新物件
           SQL> CONN TESTER/TESTER
                      Connected.
           SQL> CREATE TABLE DUMMY_123 AS SELECT * FROM SCOTT.DUMMY;
           CREATE TABLE DUMMY_123 AS SELECT * FROM SCOTT.DUMMY
           *
           ERROR at line 1:
           ORA-01652: unable to extend temp segment by 128 in tablespace TEST
      The above error message reported above is because the Tablespace TEST is of 10 M size and AUTOEXTEND OFF. The object about to be created "DUMMY_123 " , requires 11 M size and as it doesn't have enough space to extend , it has failed with the ORA-1652 error message in "TEST" tablespace.

 

參考文獻：
1. DURING CTAS (CREATE TABLE AS SELECT) ORA-1652 REPORTED ON THE DATA TABLESPACE [ID 577643.1]
2. http://space.itpub.net/?uid-9252210-action-viewspace-itemid-660173