我的导出文件中缺少Oracle，Blob数据 [英] Oracle, Blob data are missing in my export file

问题描述

我使用Oracle 11g，我正在尝试导出数据(仅导出数据，而不导出表创建脚本等). 这样它就可以由客户导入到他们的数据库中

I use Oracle 11g, I am trying to export my data(only the data, not table create scripts etc.) So it can be imported by customer to their database

当我使用Oracle Sql开发人员的导出数据库时，它仅导出数据，但是我的BLOB丢失了！我的一张表中也有很多图像文件，我也想导出.

When I use Oracle Sql developer's export database, it only exports the data but my BLOB's are missing! There are a lot of image files in one of my tables which I want to export as well.

我想知道我是否真的需要使用oracle exp imp工具；

I wonder If I really need to use oracle exp imp tool;

http://docs.oracle.com/cd/B28359_01/server.111/b28319/exp_imp.htm#i1004777

有什么主意吗?

推荐答案

来自文档:

格式:为要卸载的数据选择所需的输出格式.根据所选格式，可能会出现其他选项.例如，对于xls(Microsoft Excel文件)，可以为数据和SELECT语句指定工作表名称.

Format: Select the desired output format for the data to be unloaded. Depending on the selected format, other options may appear. For example, for xls (Microsoft Excel file), you can specify worksheet names for the data and the SELECT statement.

对于CLOB数据，仅当格式为loader(SQL * Loader)或pdf(PDF)时才支持导出.某些导出类型仅导出字符串的子集，后跟省略号(...).

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

它没有明确地引用BLOB，但是如果CLOB只能导出为loader或pdf，则BLOB也将具有该限制是有道理的.如果要在另一个架构或数据库中重新创建此数据，SQL * Loader格式似乎是一个不错的选择.

It doesn't explicitly refer to BLOBs, but if CLOBs can only be exported as loader or pdf, it makes sense that BLOBs would also have that restriction. If you want to recreate this data in another schema or database, SQL*Loader format seems like a good choice.

您希望insert语句看起来如何?您必须具有包含二进制值的文本文字，这本身就是一个问题，但是您还必须限制为4k，这可能会超出许多图像文件.对于CLOB，它可能会给您前4903个字符，然后在字符串文字中加上省略号，但是我不确定.对于BLOB来说，即使没有任何意义.

What would you expect the insert statement to look like? You'd have to have a text literal containing the binary value, which is a problem in itself, but you'd also be limited to 4k - which many image files might exceed. For a CLOB it might give you the first 4903 chars followed by an ellipsis in the string literal, but I'm not sure; for a BLOB even that wouldn't make any sense.

如果要在数据库之间传输数据，则应考虑数据泵导出/导入，或者如果您(或您的客户端)受到服务器访问的限制，则可以使用

If you want to transfer data between databases you should consider data pump export/import, or if you (or your client) are restricted by server access then you could fall back to legacy export/import. Both support LOBs. Data pump is superior and should be used in preference if at all possible. The only downside really is that the dump files are written to the database server and getting access to them (or permissions on a directory object to write to can be problematic in some organisations.

这篇关于我的导出文件中缺少Oracle，Blob数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！