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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-660172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9252210/viewspace-660172/