使用 SQL 在 Oracle 模式之间复制数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/869390/
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
Copying data between Oracle schemas using SQL
提问by chabzjo
I'm trying to copy data from one Oracle schema (CORE_DATA) into another (MY_DATA) using an INSERT INTO (...)SQL statement.
我正在尝试使用SQL 语句将数据从一个 Oracle 模式 ( CORE_DATA)复制到另一个 ( MY_DATA) INSERT INTO (...)。
What would the SQL statement look like?
SQL 语句会是什么样的？
回答by Sliff
Prefix your table names with the schema names when logged in as a user with access to both:
当以具有访问权限的用户身份登录时，使用模式名称作为表名的前缀：
insert into MY_DATA.table_name select * from CORE_DATA.table_name;
Assuming that the tables are defined identically in both schemas, the above will copy all records from the table named table_name in CORE_DATA to the table named table_name in MY_DATA.
假设表在两个模式中的定义相同，上面将把所有记录从 CORE_DATA 中名为 table_name 的表复制到 MY_DATA 中名为 table_name 的表。
回答by funny_irony
usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel]
[db] : database schema string, e.g., grprass/grprass@grprass, pplan/pplan@prassm1
[opt] : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
[table]: name of the destination table
[cols] : a comma-separated list of destination column aliases ( optional )
[sel] : any valid SQL SELECT statement
SQL> COPY FROM scott/tiger@schema1 TO scott/tiger@schema2 insert mytable using select * from mytable;
回答by Diana
your schema must have grant create any table privilege for this
您的架构必须为此授予创建任何表权限
