如何使用expdp导出oracle未确定的模式

Question

我想导出Oracle数据库，但不是完整且未确定的模式；例如，其名称类似'IAS%'的模式或类似'YS%'的模式

我写了这个命令：

Expdp admin/admin@orcl schemas like 'IAS%'  file=my_data.dmp directory=exp_dir 

但我面临错误：

无效的位置参数值"like">

Answer 1

您将无法使用该命令运行导出。数据泵导出实用程序(EXPDP(不使用常规SQL。不能在子句中使用缩写SQL命令——必须根据Oracle文档定义子句。根据文档，EXPDP模式子句的示例如下所示：

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

它们将模式列为逗号分隔的值，您必须执行这样的操作才能执行导出。您可以创建一个查询，以逗号分隔的格式返回所有这些模式，然后简单地将该列表复制并粘贴到命令中，而不是尝试在命令本身中使用查询。

要获得该列表，您可以尝试运行以下查询：

select listagg(username,', ') within group(order by username) csv
from all_users
where username like 'IAS%';

此查询使用listagg函数。如果您使用的Oracle版本不支持listagg，则可以使用以下查询来完成同样的操作。我在一个单独的StackOverflow问题上发现了这个查询，但它应该可以正常工作：

SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
COUNT (*) OVER () cnt
FROM all_users where username like 'IAS%')
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

无论使用哪种查询，现在都会有一个以字符串"IAS"开头的逗号分隔的架构列表。对于这个例子，假设这个列表看起来像IAS_1, IAS_2, IAS_3，等等。然后，您的命令将从以下内容更改为：

Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir

更像：

Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir

如果经常在此数据库中创建和删除架构，则可以运行查询来更新受影响的架构列表，并手动复制和粘贴新列表。如果这种情况经常发生，您还可以创建一个动态SQL脚本来生成和执行EXPDP命令。

希望这能有所帮助！

数据泵文件：https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200

listagg函数文档：https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030