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 5000
SELECT 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 add If the table you are after is in a different schema:

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

Using sql developer select the table and click on the DDL tab

You can use that code to create a new table with no data when you run it in a sql worksheet

sqldeveloper is a free to use app from oracle.

If the table has sequences or triggers the ddl will sometimes generate those for you too. You just have to be careful what order you make them in and know when to turn the triggers on or off.

If one needs to create a table (with an empty structure) just to EXCHANGE PARTITION, it is best to use the "..FOR EXCHANGE.." clause. It's available only from Oracle version 12.2 onwards though.

CREATE TABLE t1_temp FOR EXCHANGE WITH TABLE t1;

This addresses 'ORA-14097' during the 'exchange partition' seamlessly if table structures are not exactly copied by normal CTAS operation. I have seen Oracle missing some of the "DEFAULT" column and "HIDDEN" columns definitions from the original table.

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

See this for further read...

1. create table xyz_new as select * from xyz;

-- This will create table and copy all data.

2. delete from xyz_new;

-- This will have same table structure but all data copied will be deleted.

If you want to overcome the limitations specified by answer: How can I create a copy of an Oracle table without copying the data?