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

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?

哪些拷贝了结构和数据，但是如果我只想要结构呢?

15 个解决方案

#1

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

这也不处理分区

---

#2

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

#3

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有时也会为您生成它们。你只需要注意你输入的顺序，知道什么时候打开或关闭触发器。

#4

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的条件下不插入任何内容

#5

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

您可以从Old_table(1=2)中选择*来创建表New_table。但是要小心，您创建的表不包含任何索引、Pk等等，比如old_table

#6

    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; 

#7

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.

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

#8

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子句，使查询不返回任何数据:

#9

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

在1 = 0或类似的错误条件下工作，但我不喜欢它们的样子。Oracle 12c+ IMHO的代码稍微干净一点

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

仅从foo获取前0行，创建表条SELECT *;

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

同样的限制也适用:只将列定义及其可空性复制到新表中。

#10

you can also do a

你也可以做a

create table abc_new as select * from abc; 

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

然后截断表abc_new。希望这能满足你的要求。

#11

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

Source_table is the table u wanna copy the structure of.

Source_table是要复制其结构的表。

#12

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

通过使用pl/sql developer，您可以在sql工作区或对象浏览器中右键单击table_name，而不是单击“查看”和单击“视图sql”，后者生成sql脚本，创建表以及所有的约束、索引、分区等。

Next you run the script using the new_table_name

接下来，使用new_table_name运行脚本。

#13

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 

TYPE is ('TABLE','PROCEDURE', etc...)

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

通过这个命令，您可以从数据库对象中获得大部分ddl。

#14

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

#15

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.

希望这能解决你的问题。感谢之前的文章。给了我很多洞察力。