Problem solve Get help with specific problems with your technologies, process and projects.

Copy the structure of a table

How to CREATE a table with exactly the same structure as an existing table but without any records.

Here is a very simple tip for Oracle users. Many times in my various projects I have come across cases where I needed to CREATE a table with exactly the same structure as an existing table but without any records. Here's how to do it.

Suppose we have a table TESTTABLE:

SQL> Desc TESTTABLE; 
COL1   VARCHAR2(2), 
col2   NUMBER(8,2), 
.... 
.... 
COL200  varchar2(2000) 

Suppose the table has 20 million records. This is what I used to do:

CREATE TABLE NEWTABLE 
tablespace ... 
UNRECOVERABLE 
AS SELECT * FROM TESTTABLE; 

...and then truncate NEWTABLE. This was time-consuming even when using the UNRECOVERABLE option.

You can create a table with exactly the same columns as an existing table like this:

SQL> create table NEWTABLE 
     tablespace ... Storage .... 
     UNRECOVERABLE 
     as select * from TESTTABLE 
     where 1=2 

This is fast!

Reader Feedback

Alex V. writes: Starting from version 8.0.x, Oracle does NOT recommend the use of RECOVERABLE/UNRECOVERABLE: "These keywords are deprecated and have been replaced with LOGGING and NOLOGGING, respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle Corporation strongly recommends that you use the LOGGING and NOLOGGING keywords."

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close