我想在不更改任何内容的情况下将现有表从一个模式复制到另一个模式

Question

我正试图使用DBVizualizer或SQL Developer将一个模式中的一个表复制到不存在的表中的另一个模式。此外，如果可能的话，仅出于测试目的，还需要示例如何将一个表复制到同一模式中的另一个(不存在的表(中。

如果有任何帮助，我将不胜感激。

尝试过这个：

SELECT * 
INTO new_table 
FROM old_table;

ALTER TABLE old_table 
RENAME TO new_table;

在DBVizuallizer和SQL Developer中都不起作用，我真的不确定我做了什么，因为它是从w3school复制粘贴的。

Answer 1

CTAS(Create Table As Select(是一个简单的选项：

SQL> show user
USER is "SCOTT"
SQL> create table dept_new as select * From dept;
Table created.
SQL> select * from dept_new;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL>

---

但是，如果一个新表应该位于另一个架构中，那么您可能会遇到问题，因为除非您有权限，否则您无法在其他架构中创建对象。通常情况下，我们不会这样做，因为这需要create any table权限，而这不是您(或您的DBA(应该轻易授予的权限。

SQL> connect scott/tiger
Connected.
SQL> create table mike.dept_new as select * From dept;
create table mike.dept_new as select * From dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create any table to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> create table mike.dept_new as select * From dept;
Table created.
SQL>

---

假设另一个模式已经包含这样一个表(但它是空的(，您只需要复制数据。但是，这也不容易，因为-你猜怎么着？-缺少权限。我们不应该破坏其他用户的数据。

SQL> insert into mike.dept_new select * From dept;
insert into mike.dept_new select * From dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

这一次，我们不需要DBA来授予特权；其他用户(mike(可以这样做：

SQL> connect mike/lion
Connected.
SQL> grant insert on dept_new to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> insert into mike.dept_new select * From dept;
4 rows created.
SQL>

---

还有一个选项是从源模式导出数据并将其导入目标模式。首先，将其从mike中删除(正如您所说，它不应该已经存在(：

SQL> connect mike/lion
Connected.
SQL> drop table dept_new;
Table dropped.
SQL>

出口：

SQL> $exp scott/tiger file=dept.dmp tables=dept
Export: Release 11.2.0.2.0 - Production on Pon Pro 20 21:41:36 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

导入：

SQL> $imp mike/lion file=dept.dmp
Import: Release 11.2.0.2.0 - Production on Pon Pro 20 21:41:50 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into MIKE
Import terminated successfully without warnings.
SQL>