使用 PL/SQL 过程在 oracle 10g 中转储表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1886531/
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
Taking dump of tables in oracle 10g using PL/SQL procedure
提问by P Sharma
Hi Required immediate response,
您好 需要立即回复，
I want to take dump of some selected tables from schema, can any body tell me is it possible? Can anybody provide procedure by executing that we can take dump.
我想从模式中转储一些选定的表，任何人都可以告诉我这可能吗？任何人都可以通过执行我们可以转储来提供程序。
e.g. I have schema, testuser, and tables (T1,T2,T3,T5,T9), i want to take dump of T1 & T5 only.
例如，我有模式、testuser 和表（T1、T2、T3、T5、T9），我只想转储 T1 和 T5。
Thanks in advance
提前致谢
回答by APC
As you are on 10g you can do this with the Data Pump API. You need to have read and write access on a directory object which maps to the destination OS directory.
当您使用 10g 时，您可以使用Data Pump API执行此操作。您需要对映射到目标操作系统目录的目录对象具有读写访问权限。
In the following example I am exporting two tables, EMP and DEPT, to a file called EMP.DMP in a directory identified by DATA_PUMP_DIR.
在以下示例中，我将两个表 EMP 和 DEPT 导出到由 DATA_PUMP_DIR 标识的目录中名为 EMP.DMP 的文件。
SQL> declare
2 dp_handle number;
3 begin
4 dp_handle := dbms_datapump.open(
5 operation => 'EXPORT',
6 job_mode => 'TABLE');
7
8 dbms_datapump.add_file(
9 handle => dp_handle,
10 filename => 'emp.dmp',
11 directory => 'DATA_PUMP_DIR');
12
13 dbms_datapump.add_file(
14 handle => dp_handle,
15 filename => 'emp.log',
16 directory => 'DATA_PUMP_DIR',
17 filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
18
19 dbms_datapump.metadata_filter(
20 handle => dp_handle,
21 name => 'NAME_LIST',
22 value => '''EMP'',''DEPT''');
23
24 dbms_datapump.start_job(dp_handle);
25
26 dbms_datapump.detach(dp_handle);
27 end;
28 /
PL/SQL procedure successfully completed.
SQL>
@DerekMahar asks:
@DerekMahar 问：
"Is there a similar data pump tool or API available for execution from the client side"
“是否有类似的数据泵工具或 API 可用于从客户端执行”
DataPump, both the PL/SQL API and the OS utility, write to Oracle directories. An Oracle directory must represent an OS directory which is visible to the database. Usually that is a directory on the server, although I suppose it is theoretically possible to map a PC drive to the network. You'd have to persuade your network admin that this is a good idea, it is a tough sell, because it isn't...
DataPump（PL/SQL API 和 OS 实用程序）写入 Oracle 目录。Oracle 目录必须代表对数据库可见的操作系统目录。通常这是服务器上的一个目录，尽管我认为理论上可以将 PC 驱动器映射到网络。您必须说服您的网络管理员，这是一个好主意，但很难推销，因为它不是...
The older IMP and EXP utilities read and wrote from client directories, so it is theoretically possible possible to IMP a local dump file into a remote database. But I don't think this is a practical approach. By their nature dump files tend to be big, so importing across a network is slow and prone to failure. It is a much better solution to zip the dump file, copy it to the server and import it from there.
较旧的 IMP 和 EXP 实用程序从客户端目录读取和写入，因此理论上可以将本地转储文件 IMP 到远程数据库中。但我不认为这是一种实用的方法。由于其性质，转储文件往往很大，因此通过网络导入很慢并且容易失败。压缩转储文件，将其复制到服务器并从那里导入它是一个更好的解决方案。
回答by Dinesh Bhat
You should try using the DATAPUMP api's (EXPDP/IMPDP). It has a lot more capabilities and has PLP/SQL APIs. DATAPUMP is a replacement for exp and imp and is supported in 10g.
您应该尝试使用 DATAPUMP api (EXPDP/IMPDP)。它具有更多功能并具有 PLP/SQL API。DATAPUMP 是 exp 和 imp 的替代品，在 10g 中得到支持。
回答by kth
With this command you'll get a binary Oracle dump: exp scott/tiger file=mydump.dmp tables=(T1,T5)
使用此命令，您将获得二进制 Oracle 转储：exp scott/tiger file=mydump.dmp tables=(T1,T5)
I recommend this link: http://www.orafaq.com/wiki/Import_Export_FAQ
回答by Jim Hudson
If you must use PL/SQL, and you're trying to create a file, then you'll need to have a directory defined with write access granted to your user. That's something your DBA can do. See the "create directory" command.
如果您必须使用 PL/SQL，并且您正在尝试创建一个文件，那么您需要定义一个目录，并授予您的用户写访问权限。这是您的 DBA 可以做到的。请参阅“创建目录”命令。
At that point, you can (1) call UTL_FILE to open a file and write rows to it or (2) create an "EXTERNAL TABLE" and copy the information to it or (3) use DBMS_XMLGEN or (4) use any of several other ways to actually write the data from the database to the file. All of these are in the Oracle docs. The PL/SQL Packages and Typesmanual is your friend for things like this.
此时，您可以 (1) 调用 UTL_FILE 打开文件并向其中写入行或 (2) 创建“外部表”并将信息复制到其中或 (3) 使用 DBMS_XMLGEN 或 (4) 使用多个将数据从数据库实际写入文件的其他方法。所有这些都在 Oracle 文档中。在PL / SQL程序包和类型手册是你的朋友，这样的事情。
Note that the actual file system directory has to be on the server where the database is located. So you may need to get access to that server to copy your file, or have somebody set up a mount or whatever.
请注意，实际的文件系统目录必须位于数据库所在的服务器上。因此，您可能需要访问该服务器以复制您的文件，或者让某人设置安装或其他任何内容。
Alternatively, you could set up a plsql web service that you could call to get your data.
或者，您可以设置一个 plsql Web 服务，您可以调用该服务来获取您的数据。
But, personally, I'd just use exp. Or, if that's not available, Toad or some other front end tool (even SQL*Plus) where you can just write a simple SQL script and save the results.
但是，就个人而言，我只会使用 exp。或者，如果这不可用，Toad 或其他一些前端工具（甚至 SQL*Plus），您可以在其中编写一个简单的 SQL 脚本并保存结果。
If you're doing this for a homework assignment, my guess is they'll want a UTL_FILE solution.
如果你是为了家庭作业而这样做，我猜他们会想要一个 UTL_FILE 解决方案。
