create table as select

大家都知道create table a as select * from b可以创建一个与b表结构一样的表，但是在实际应用中最好不要这么创建表。原因是这样只创建表的结构，而不会将原表的默认值一起创建。

说白了，表结构出来了，默认值没有。

另外，但是有一个我对一个大表执行create table a as select * from b时候报了一个temp表空间不足，不知道是什么原因，记录一下。下次发现在处理吧。

 

转载http://space.itpub.net/9252210/viewspace-660173

----------------------------------------

 

一、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.

一、Temporary Segments Concept
        Oracle often requires temporary work space for intermediate stages of database processing. There are different kinds of temporary segments in the database.
        Some of them are created explicitly by the users. The others are created and accessed for the user by the system.
       There are SQL operations containing a sorting step which require temporary segments. However, segments used for sorting are not the only segments having SEGMENT_TYPE=TEMPORARY. Temporary segments can also exist for permanent segments creation.

      Temporary segments for sorting are created in the default temporary tablespace of the user. This tablespace may be of type TEMPORARY or PERMANENT.
      (1)  A TEMPORARY tablespace (Locally Managed Tablespace) is recommended for sort operations.
      (2)  Temporary segments for permanent segments creation are created in the tablespace specified in the create statement or in the user’s default tablespace.

a.  Temporary Tables
     only exists during a transaction or session.
     DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated.
   
b.  Temporary LOBs
     The goal of temporary LOBs is to develop an interface to support the creation and deletion of lobs that act like local variables.
   
c.  Temporary Segments as work area for sorting
     When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement execution.
     The sort area is allocated in memory. If the sort operation needs additional memory (above the value specified by the SORT_AREA_SIZE parameter), the sorted rows are written to disk to free up the sort area so that it can be re-used for the remaining sort.
    Oracle automatically allocates this disk space called a temporary segment.
    The following statements may require the use of a temporary segment for sorting:
   CREATE INDEX/SELECT ... ORDER BY/SELECT DISTINCT/SELECT ... GROUP BY/SELECT ... UNION/SELECT ... INTERSECT/SELECT ... MINUS/ANALYZE TABLE/Unindexed joins/Correlated subqueries

d.  Temporary Segments for permanent segments creation
     Besides sort operations, there are other SQL operations, which also require temporary segments:
   CREATE PRIMARY/UNIQUE KEY CONSTRAINT
    ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
    CREATE TABLE STORAGE (MINEXTENTS>1)
    CREATE TABLE AS SELECT
           The CTAS creates a data segment in the target tablespace and marks this segment as temporary in dictionary.
           On completion, the dictionary type is changed from temporary to table. In addition, if the SELECT performs a SORT operation, temporary space may be used as for a standard select.
   CREATE PARTITION TABLE
    ALTER TABLE ... SPLIT PARTITION
    CREATE SNAPSHOT
    CREATE INDEX
           The CREATE INDEX statement, after sorting the index values, builds a temporary segment in the INDEX tablespace;
           once the index is completely built, the segment type is changed to INDEX.
    DROP TABLE  
   
e.  Temporary Tablespaces

二、Introduction to Direct-Path INSERT
        1. Conventional insert operations：
            Oracle reuses free space in the table, interleaving newly inserted data with existing data. During such operations, Oracle also maintains referential integrity constraints.

        2. Direct-path INSERT operations：
           Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.
            a. During direct-path INSERT, you can disable the logging of redo and undo entries
            b.CREATE TABLE ... AS SELECT statement, does not have any indexes defined on it and not null constraint; you must define them later.
  
Note:If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging or nologging setting, such as STANDBY database.

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

转载于:http://blog.itpub.net/9524377/viewspace-660041/