有没有一种方法可以在Oracle 10g中的数据库之间复制BLOB记录? [英] Is there a way to copy BLOB records between databases in Oracle 10g?

问题描述

我们有一个生产表，其中包含数百万行，并且包含一个BLOB字段，我想将这些记录的较小选择复制到我们的开发数据库中，如果可能的话，不涉及DBA.我尝试了以下COPY命令，但收到了CPY-0012: Datatype cannot be copied

We have a production table that has millions of rows in it and contains a BLOB field, I would like to copy a smaller selection of these records into our development database without getting a DBA involved if possible. I tried the following COPY command but received a CPY-0012: Datatype cannot be copied

COPY FROM user/password@prod_db TO user/password@dev_db -
INSERT TABLE_A (COL1, COL2, COL3, BLOB_COL) USING -
SELECT COL1, COL2, COL3, BLOB_COL -
FROM TABLE_A WHERE COL1='KEY' 

是否可以通过SQL在数据库之间复制带有BLOB字段的记录?

Is there a way to copy records with a BLOB field between databases via SQL?

推荐答案

很遗憾，您无法使用COPY命令复制BLOB值.

Unfortunately you cannot copy BLOB values using the COPY command.

另一种方法是在源数据库上建立数据库链接，并执行SQL INSERT语句:

An alternative is to set up a DB link on the source database, and execute a SQL INSERT statement:

CREATE DATABASE LINK link_to_prod CONNECT TO prod_user IDENTIFIED BY prod_password USING 'prod_db';

INSERT INTO TABLE_A@link_to_prod (COL1, COL2, COL3, BLOB_COL) SELECT COL1, COL2, COL3, BLOB_COL FROM TABLE_A

这篇关于有没有一种方法可以在Oracle 10g中的数据库之间复制BLOB记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！