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

Andrew picture Andrew · Oct 24, 2008 · Viewed 587.5k times · Source

I know the statement:

create table xyz_new as select * from xyz;

Which copies the structure and the data, but what if I just want the structure?

Answer

Jim Hudson picture Jim Hudson · Oct 24, 2008

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