oracle 如何在 SQL*Plus 中复制包含所有约束的表?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/3725535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 21:31:39  来源:igfitidea点击:

How to duplicate a table with all its constrains in SQL*Plus?

databaseoraclesqlplus

提问by Moeb

Using create table tab2 as select * from tab1;, I am able to copy the data but not the primary key constraint :

使用create table tab2 as select * from tab1;,我可以复制数据但不能复制主键约束:

SQL> desc tab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20)

SQL> select * from tab1;

        ID NAME
---------- --------------------
         1 A

SQL> create table tab2 as select * from tab1;

Table created.

SQL> desc tab2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)

SQL> select * from tab2;

        ID NAME
---------- --------------------
         1 A

SQL> 

How can I copy the table with all its constraints as well?

如何复制包含所有约束的表?

回答by René Nyffenegger

I'd start with something like

我会从类似的东西开始

set long 100000
select dbms_metadata.get_ddl('TABLE', 'TAB1', '<schemaname'>) from dual

This returns a create tablestatement for TAB1(in schema <schemaname>). You can then copy that statement and change the identfier TAB1 to TAB2. You should make sure that you also change the names of all constraints since they must be unique in Oracle.

这将返回一个create tablefor语句TAB1(在模式< SCHEMANAME>)。然后您可以复制该语句并将标识符 TAB1 更改为 TAB2。您应该确保还更改了所有约束的名称,因为它们在 Oracle 中必须是唯一的。

Finally, you'll want to do a insert into TAB2 select * from TAB1

最后,你会想做一个 insert into TAB2 select * from TAB1