SQL 如何在不复制数据的情况下创建 Oracle 表的副本？

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

提问by Andrew

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?

哪个复制结构和数据，但如果我只想要结构呢？

回答by Jim Hudson

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

只需使用不会选择任何行的 where 子句：

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

这也不处理分区

---

---

回答by Dave Costa

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

我使用了您经常接受的方法，但正如有人指出的那样，它不会重复约束（我认为 NOT NULL 除外）。

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.

这将为您提供完整的 create 语句文本，您可以根据需要对其进行修改以创建新表。当然，您必须更改表的名称和所有约束。

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

（您也可以使用 EXP/IMP 在旧版本中执行此操作，但现在要容易得多。）

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;

回答by branchgabriel

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

使用 sql developer 选择表并单击 DDL 选项卡

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

当您在 sql 工作表中运行时，您可以使用该代码创建一个没有数据的新表

sqldeveloper is a free to use app from oracle.

sqldeveloper 是一个免费使用的 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.

如果表有序列或触发器，ddl 有时也会为您生成这些。您只需要注意制作它们的顺序，并知道何时打开或关闭触发器。

回答by sunleo

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

为了避免一次又一次地迭代，并且根据 1=2 的条件不插入任何内容

回答by Brian Leach

    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; 

回答by Mohsen Molaei

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

你可以这样做， Create table New_table as select * from Old_table where 1=2 ; 但要小心你创建的表没有任何 Index ， Pk 等像 old_table

回答by guesswho

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:

使用另一个的架构创建一个新的空表。只需添加一个 WHERE 子句，使查询不返回任何数据：

回答by DKroot

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

WHERE 1 = 0或类似的错误条件有效，但我不喜欢它们的外观。Oracle 12c+ 恕我直言，稍微干净的代码是

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

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.

同样的限制适用：只有列定义及其可为空性被复制到新表中。

回答by Diogo Maschio

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

以其他方式，您可以从下面列出的命令中获取表创建的 ddl，并执行创建。

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

• TYPEis TABLE,PROCEDUREetc.

• TYPE是TABLE，PROCEDURE等等。

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

使用此命令，您可以从数据库对象中获取大部分 ddl。

回答by user3284249

Simply write a query like:

只需编写如下查询：

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

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

其中new_table是要创建的新表old_table的名称，是要复制其结构的现有表的名称，这将仅复制结构。