Search code examples
oracleexpdp

Oracle full EXPORT with exclude and NOT using par file

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.

Solution

  • 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