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!
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 firstname.lastname@example.org 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.