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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

create table xyz new as select   from xyz       -- This will create table and copy all data         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

User · Answer

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

User · Answer

If one needs to create a table  with an empty structure  just to EXCHANGE PARTITION  it is best to use the  quot   FOR EXCHANGE   quot  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  quot DEFAULT quot  column and  quot HIDDEN quot  columns definitions from the original table   ORA-14097  column type or size mismatch in ALTER TABLE EXCHANGE PARTITION  See this for further read

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Create table target table  As Select    from source table  where 1 2    Source table is the table u wanna copy the structure of

User · Answer

create table xyz new as select   from xyz       -- This will create table and copy all data         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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

copy without table data  create table  lt target table gt  as select   from  lt source table gt  where 1 2    copy with table data  create table  lt target table gt  as select   from  lt source table gt

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

copy without table data  create table  lt target table gt  as select   from  lt source table gt  where 1 2    copy with table data  create table  lt target table gt  as select   from  lt source table gt

User · Answer

If one needs to create a table  with an empty structure  just to EXCHANGE PARTITION  it is best to use the  quot   FOR EXCHANGE   quot  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  quot DEFAULT quot  column and  quot HIDDEN quot  columns definitions from the original table   ORA-14097  column type or size mismatch in ALTER TABLE EXCHANGE PARTITION  See this for further read

User · Answer

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

User · Answer

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

User · Answer

Create table target table  As Select    from source table  where 1 2    Source table is the table u wanna copy the structure of

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

Examples related to sql

Examples related to oracle

Examples related to copy

Examples related to database-table