我正在嘗試使用 DBVizualizer 或 SQL Developer 將 1 個表從 1 個模式復制到非現有表中的另一個模式。此外，如果可能僅出于測驗目的，將需要示例如何將 1 個表復制到同一架構中的另一個（不存在的表）中。
我真的很感激任何幫助。
試過這個：
SELECT *
INTO new_table
FROM old_table;
ALTER TABLE old_table
RENAME TO new_table;
在 DBVizuallizer 和 SQL Developer 中都不起作用，我不確定我做了什么，因為它是從 w3school 復制粘貼的。
uj5u.com熱心網友回復：
CTAS（按選擇創建表）是一個簡單的選項：
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/388834.html
標籤：sql 甲骨文 dbvisualizer
下一篇：Oracle提取幾個xpath值