How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

• sequences
• triggers
• indexes
• some constraints may not be copied
• materialized view logs

This also does not handle partitions

I used the method that you accepted a lot, but as someone pointed out it doesn't duplicate constraints (except for NOT NULL, I think).

A more advanced method if you want to duplicate the full structure is:

SET LONG 5000SELECT dbms_metadata.get_ddl( 'TABLE', 'MY_TABLE_NAME' ) FROM DUAL;

This will give you the full create statement text which you can modify as you wish for creating the new table. You would have to change the names of the table and all constraints of course.

(You could also do this in older versions using EXP/IMP, but it's much easier now.)

Edited to addIf the table you are after is in a different schema:

SELECT dbms_metadata.get_ddl( 'TABLE', 'MY_TABLE_NAME', 'OTHER_SCHEMA_NAME' ) FROM DUAL;

create table xyz_new as select * from xyz where rownum = -1;

To avoid iterate again and again and insert nothing based on the condition where 1=2