Oracle完整EXPORT，带有exclude和NOT，使用par文件

Question

我需要完整导出12.2数据库。最近，我们在其中放置了2张表，其中有超过400万条记录将保持不变。我想将它们从每日EXPDP中删除，因为它们已离线存档。

此EXPDP通过计划任务启动，并调用一系列批处理文件，这些文件具有从批处理文件传递到批处理文件的定义变量。这会生成一系列日志和归档文件，这些文件在更大的方案中很重要。

我这样做没有一个.标准杆数文件，因为.标准杆数文件似乎不喜欢批处理文件中定义的任何变量名称。

我可以在命令提示符下运行它而不会出现问题，但如果我通过批处理调用它，我会得到一个错误

**LRM-00111：值表没有结束引号："；LIK**

EXPDP *******/********@%dbname% FULL=Y exclude=statistics exclude=table:"LIKE'%_80'" DUMPFILE=%bckupdate%.dmp LOGFILE=%bckupdate%.log reuse_dumpfiles=yes

关于如何在标准杆数文件中使用变量名(如%DBNAME%(或批处理文件的正确格式的任何有用提示都将不胜感激。

Answer 1

您可以尝试这个脚本expdp_powershell.ps1

例如

E:upworkstackoverflowexpdp_powershell>powershell ./expdp_powershell.ps1   -user_name system -user_password manager -connect_string test -exclude table:"LIKE'%_80'"

或

E:upworkstackoverflowexpdp_powershell>powershell  ./expdp_powershell.ps1

脚本expdp_powershell.ps1

param(
[string]$user_name = "system"
, 
[string]$user_password = "manager"
,
[string]$connect_string = "TEST"
, 
[string]$export_mode = "FULL=Y" 
,
[string]$exclude = "table:""LIKE '%_80'""" 
)
$date_time_log = Get-Date -Format "yyyyMMddHHmmss" 

$DUMPFILE = "backup" + $date_time_log + ".dmp"
$LOGFILE = "backup_log" + $date_time_log + ".log"
$reuse_dumpfiles = "yes"
$DIRECTORY="DATA_PUMP_DIR"
echo $exclude
EXPDP $user_name/$user_password@$connect_string $export_mode exclude=statistics exclude=$exclude DIRECTORY=$DIRECTORY DUMPFILE=$DUMPFILE LOGFILE=$LOGFILE reuse_dumpfiles=$reuse_dumpfiles

例如输出

E:upworkstackoverflowexpdp_powershell>powershell ./expdp_powershell.ps1   -user_name system -user_password manager -connect_string test -exclude table:"LIKE'%_80'"
table:"LIKE '%_80'"
Export: Release 11.2.0.4.0 - Production on Sat Jan 9 12:44:10 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@TEST FULL=Y exclude=statistics exclude=table:"LIKE '%_80'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=backup20210109124410.dmp LOGFILE=ba
ckup_log20210109124410.log reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 363.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE