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

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?

In other way you can get ddl of table creation from command listed below, and execute the creation.

SELECT DBMS_METADATA.GET_DDL('TYPE','OBJECT_NAME','DATA_BASE_USER') TEXT FROM DUAL 

• TYPE is TABLE,PROCEDURE etc.

With this command you can get majority of ddl from database objects.

WHERE 1 = 0 or similar false conditions work, but I dislike how they look. Marginally cleaner code for Oracle 12c+ IMHO is

CREATE TABLE bar AS SELECT * FROM foo FETCH FIRST 0 ROWS ONLY;

Same limitations apply: only column definitions and their nullability are copied into a new table.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

Create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

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

    DECLARE
    l_ddl   VARCHAR2 (32767);
BEGIN
    l_ddl      := REPLACE (
                      REPLACE (
                          DBMS_LOB.SUBSTR (DBMS_METADATA.get_ddl ('TABLE', 'ACTIVITY_LOG', 'OLDSCHEMA'))
                        , q'["OLDSCHEMA"]'
                        , q'["NEWSCHEMA"]'
                      )
                    , q'["OLDTABLSPACE"]'
                    , q'["NEWTABLESPACE"]'
                  );

    EXECUTE IMMEDIATE l_ddl;
END; 

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

Create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

copy without table data

create table <target_table> as select * from <source_table> where 1=2;

copy with table data

create table <target_table> as select * from <source_table>;

Simply write a query like:

create table new_table as select * from old_table where 1=2;

where new_table is the name of the new table that you want to create and old_table is the name of the existing table whose structure you want to copy, this will copy only structure.

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;

you can also do a

create table abc_new as select * from abc; 

then truncate the table abc_new. Hope this will suffice your requirement.

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;

Create table target_table 
As
Select * 
from source_table 
where 1=2;

Source_table is the table u wanna copy the structure of.

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.

copy without table data

create table <target_table> as select * from <source_table> where 1=2;

copy with table data

create table <target_table> as select * from <source_table>;

Create table target_table 
As
Select * 
from source_table 
where 1=2;

Source_table is the table u wanna copy the structure of.

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?

The task above can be completed in two simple steps.

STEP 1:

CREATE table new_table_name AS(Select * from old_table_name);

The query above creates a duplicate of a table (with contents as well).

To get the structure, delete the contents of the table using.

STEP 2:

DELETE * FROM new_table_name.

Hope this solves your problem. And thanks to the earlier posts. Gave me a lot of insight.

In other way you can get ddl of table creation from command listed below, and execute the creation.

SELECT DBMS_METADATA.GET_DDL('TYPE','OBJECT_NAME','DATA_BASE_USER') TEXT FROM DUAL 

• TYPE is TABLE,PROCEDURE etc.

With this command you can get majority of ddl from database objects.

you can also do a

create table abc_new as select * from abc; 

then truncate the table abc_new. Hope this will suffice your requirement.

WHERE 1 = 0 or similar false conditions work, but I dislike how they look. Marginally cleaner code for Oracle 12c+ IMHO is

CREATE TABLE bar AS SELECT * FROM foo FETCH FIRST 0 ROWS ONLY;

Same limitations apply: only column definitions and their nullability are copied into a new table.

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.

Simply write a query like:

create table new_table as select * from old_table where 1=2;

where new_table is the name of the new table that you want to create and old_table is the name of the existing table whose structure you want to copy, this will copy only structure.

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

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.

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;

    DECLARE
    l_ddl   VARCHAR2 (32767);
BEGIN
    l_ddl      := REPLACE (
                      REPLACE (
                          DBMS_LOB.SUBSTR (DBMS_METADATA.get_ddl ('TABLE', 'ACTIVITY_LOG', 'OLDSCHEMA'))
                        , q'["OLDSCHEMA"]'
                        , q'["NEWSCHEMA"]'
                      )
                    , q'["OLDTABLSPACE"]'
                    , q'["NEWTABLESPACE"]'
                  );

    EXECUTE IMMEDIATE l_ddl;
END; 

Using pl/sql developer you can right click on the table_name either in the sql workspace or in the object explorer, than click on "view" and than click "view sql" which generates the sql script to create the table along with all the constraints, indexes, partitions etc..

Next you run the script using the new_table_name

The task above can be completed in two simple steps.

STEP 1:

CREATE table new_table_name AS(Select * from old_table_name);

The query above creates a duplicate of a table (with contents as well).

To get the structure, delete the contents of the table using.

STEP 2:

DELETE * FROM new_table_name.

Hope this solves your problem. And thanks to the earlier posts. Gave me a lot of insight.

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?

Using pl/sql developer you can right click on the table_name either in the sql workspace or in the object explorer, than click on "view" and than click "view sql" which generates the sql script to create the table along with all the constraints, indexes, partitions etc..

Next you run the script using the new_table_name

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...

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.

You can do this Create table New_table as select * from Old_table where 1=2 ; but be careful The table you create does not have any Index, PK and so on like the old_table.

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...

You can do this Create table New_table as select * from Old_table where 1=2 ; but be careful The table you create does not have any Index, PK and so on like the old_table.