oracle-databaseexpdp

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