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
How to duplicate a table with all its constrains in SQL*Plus?
提问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 table
statement 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 table
for语句TAB1
(在模式< SCHEMANAME>)。然后您可以复制该语句并将标识符 TAB1 更改为 TAB2。您应该确保还更改了所有约束的名称,因为它们在 Oracle 中必须是唯一的。
Finally, you'll want to do a insert into TAB2 select * from TAB1
最后,你会想做一个 insert into TAB2 select * from TAB1