I need to do a full export of a 12.2 database. Recently we placed 2 tables in it with over 4 million records that will remain static. I'd like to eliminate them from the daily EXPDP as they have been archived offline.
This EXPDP is launched via a scheduled task and calls a series of batch files that have defined variables that are passed from batch file to batch file. This produces a series of log and archive files important in the larger scheme of things.
I do this without a .PAR file as the .PAR files does not seem to like any VARIABLE names defined in the batch files.
I can run this at the command prompt without issue, but if I call it via a batch I get an error
** LRM-00111: no closing quote for value 'table:"LIK' **
EXPDP *******/********@%dbname% FULL=Y exclude=statistics exclude=table:\"LIKE\'%_80\'\" DUMPFILE=%bckupdate%.dmp LOGFILE=%bckupdate%.log reuse_dumpfiles=yes
Any helpful hints on how to either use a variable name (as in %DBNAME%) in the PAR file or proper formatting for the batch file would be appreciated.
You can try this script expdp_powershell.ps1
For example
E:\upwork\stackoverflow\expdp_powershell>powershell ./expdp_powershell.ps1 -user_name system -user_password manager -connect_string test -exclude table:\"LIKE\'%_80\'\"
or
E:\upwork\stackoverflow\expdp_powershell>powershell ./expdp_powershell.ps1
Script 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
For example output
E:\upwork\stackoverflow\expdp_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