CODEWITHSUNDEEP
bashoracle-databasedatapumpexpdp
zappee
zappee

Reputation: 22668

Oracle Data Pump: special characters in schema name + bash variable substitution

I have a bash function that does a schema export with expdp. That works great in the 90% of the cases but if the schema name contains minus (-) character then it fails with the following oracle error:

Export: Release 21.0.0.0.0 - Production on Tue Oct 10 09:43:50 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'abc-def' does not correspond to any schemas.

According to docs the minus character is considered as a special characters and I need to escape them on the following way: '\"abc-def\"'.

Unfortunately this pattern does not work for me. Another problem is that I use a bash variable to pass the schema name to expdp and if I escape everything then the bash variable substitution does not work.

What is the proper way to escape special chars in the DB schema name for expdp and keep the bash variable substitution ability too?

This is my bash function:

# ------------------------------------------------------------------------------
# execute a datapump export
# ------------------------------------------------------------------------------
function exportSchema {
    local schema oracleHost oraclePort dumpFile
    schema="$1"
    oracleHost="localhost"
    oraclePort=1521
    dumpFile="$schema"-$(date '+%Y-%m-%d.%Hh.%Mm.%Ss')

    printf "\nexporting to a dump file...\n"
    printf "    database host:         %s\n" "$oracleHost"
    printf "    database port:         %s\n" "$oraclePort"
    printf "    pluggable database:    %s\n" "$ORACLE_PDB"
    printf "    datapump user:         %s\n" "$DATAPUMP_USER"
    printf "    datapump password:     %s\n" "$DATAPUMP_PASSWORD"
    printf "    schema to be exported: %s\n" "$schema"
    printf "    dump file name:        %s.dmp\n" "$dumpFile"

    expdp "$DATAPUMP_USER"/"$DATAPUMP_PASSWORD"@"$oracleHost":"$oraclePort"/"$ORACLE_PDB" schemas="$schema" directory=DATAPUMP_WORKSPACE dumpfile="$dumpFile.dmp" logfile="$dumpFile.log"
    exitCode=$?
    if [ $exitCode -ne 0 ]; then
        printf "ERROR: An unexpected error has occurred while exporting the '%s' schema.\n" "$schema"
    else
        printf "OK: The '%s' schema has been successfully exported.\n" "$schema"
    fi
}

I have tried the followings, but non of them work fine:

This command works properly:

sqlplus abc-def/[email protected]:1521/ORCLPDB1 

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Oct 10 12:15:28 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 10 2023 12:14:58 +00:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>

I have no more idea why expdp does not like my schema name but sqlplus can connect to the schema.

-- UPDATE --

I am calling the function this way:

exportSchema  "xxx_yyy"
exportSchema  "abc-def"
exportSchema  '\"abc-def\"'
exportSchema  '\\"abc-def\\"'

The 1st call runs properly:

$ export.sh

exporting schema to a dump file...
    database host:         localhost
    database port:         1521
    pluggable database:    ORCLPDB1
    datapump user:         datapump
    datapump password:     password
    schema to be exported: xxx_yyy
    dump file name:        xxx_yyy-2023-10-10.13h.44m.14s.dmp

Export: Release 21.0.0.0.0 - Production on Tue Oct 10 13:44:14 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "DATAPUMP"."SYS_EXPORT_SCHEMA_01":  datapump/********@localhost:1521/ORCLPDB1 schemas=xxx_yyy directory=DATAPUMP_WORKSPACE dumpfile=xxx_yyy-2023-10-10.13h.44m.14s.dmp logfile=xxx_yyy-2023-10-10.13h.44m.14s.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
...

Unfortunately the rest are failing:

exporting schema to a dump file...
    database host:         localhost
    database port:         1521
    pluggable database:    ORCLPDB1
    datapump user:         datapump
    datapump password:     password
    schema to be exported: abc-def
    dump file name:        abc-def-2023-10-10.13h.53m.54s.dmp

Export: Release 21.0.0.0.0 - Production on Tue Oct 10 13:53:54 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'abc-def' does not correspond to any schemas.

ERROR: An unexpected error has occurred while exporting the 'abc-def' schema.

exporting schema to a dump file...
    database host:         localhost
    database port:         1521
    pluggable database:    ORCLPDB1
    datapump user:         datapump
    datapump password:     password
    schema to be exported: \"abc-def\"
    dump file name:        \"abc-def\"-2023-10-10.13h.54m.01s.dmp

Export: Release 21.0.0.0.0 - Production on Tue Oct 10 13:54:01 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'abc-def' does not correspond to any schemas.

exporting schema to a dump file...
    database host:         localhost
    database port:         1521
    pluggable database:    ORCLPDB1
    datapump user:         datapump
    datapump password:     password
    schema to be exported: \\"abc-def\\"
    dump file name:        \\"abc-def\\"-2023-10-10.13h.59m.17s.dmp

Export: Release 21.0.0.0.0 - Production on Tue Oct 10 13:59:17 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39170: Schema expression '\"abc-def\"' does not correspond to any schemas.

This makes me crazy...

Upvotes: 0

Views: 571

Answers (0)

Related Questions