Skip to content

How to expdp AS SYSDBA without Password

expdp AS SYSDBA

Normally, we use system to migrate data from one to another, who has pretty enough privileges for all kinds of manipulation at content-level. Whereas SYSDBA is the highest privilege which usually does maintenance and critical jobs like STARTUP or SHUTDOWN. Whenever we are using to connect to the database with SYSDBA privilege, we should add a preposition AS. For example, expdp AS SYSDBA.

Of course, you may also use SYSDBA to manipulate data like expdp AS SYSDBA. Legacy exp / imp and data pumps expdp / impdp all allow to use SYSDBA in the command line to do data migrations. It's convenient and privileged. Just remember, whenever using Oracle utilities that need authentication like expdp AS SYSDBA, please take the credential string as a whole to pass it into the database.

Before doing that, you should make sure ORACLE_HOME and ORACLE_SID have been set in your profile, e.g. ~/.bash_profile.

Double Quotations

For example, we double quoted the whole credentials like this:

[oracle@test ~]$ exp "/ as sysdba" parfile=table_list.par
Mon Nov 10 18:25:18 TAIST 2014
LRM-00108: invalid positional parameter value 'as'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
Mon Nov 10 18:25:18 TAIST 2014

Whoops! We got errors in the above.

Escape Double Quotations

In some OS, double quoting the whole credentials may be not enough, we should escape the double quote (or single quote) expdp AS SYSDBA, which symbol is like this:

[oracle@test ~]$ expdp \"/ as sysdba\" parfile=table_list.par
...

If the target database that you want to export is a Pluggable Database (PDB), you may refer to: How to Export a PDB without Password.

Shell Scripts

If you'd like make a shell script for daily routines, it's safer to set all related environment variables in your script. A simple sourcing command can do it.

. ~/.bash_profile

Or set them separately.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$PATH:$ORACLE_HOME/bin

Then put the line(s) on top of your expdp command line in the script.

19c Problem

For release 19c, we saw that the console still prompts for password in the command due to an issue about OS authentication (Bug 28707931) in 19c database server.

Although we can just press Enter to ignore it, the command line cannot be used in shell scripts for daily routines.

To overcome it, you need to patch your database 19c to 19.7 or higher in order to run it without password prompting.

Beside connections through OS authentication like the above, we can also connect remote databases via local naming (TNSNAMES).

TNS connection

For local TNS naming, a full qualified connection string using SYSDBA should be formatted as this:

username/password@connect_identifier as sysdba
In which, username is a user who has been granted SYSDBA system privilege, it is usually but not necessarily SYS.

Or without password:

username@connect_identifier as sysdba
In this way, the utility will prompt you for password.

Let's see an example of TNS connection.

[oracle@test ~]$ expdp \"sys/password@orcl as sysdba\" parfile=table_list.par
...

In fact, some utilities provided by Oracle need authentication in order to proceed their jobs, but some others don't. More examples about expdp AS SYSDBA can be found at Oracle: Invoking Export and Import

Once again, SYSDBA is a system privilege, not a role. A role is a group of privileges that you can grant it to users or other roles in a quicker way.

There're more about importing dump files without password, just like we use impdp AS SYSDBA by OS authentication.

8 thoughts on “How to expdp AS SYSDBA without Password”

  1. Hello,

    How can do an export with a tns connection but without username and password !
    expdp \”/@orcl as sysdba\” is that correct ?

    1. No, you can’t, OS authentication is basically a local connection without passing through listener. Any external connections through listener need credentials to log into the database.

  2. Hi Ed

    for some reason this is not working for me inside a Bash shell script if i run the script either on the command line or in cron it will prompt for password what am I doing wrong?

    expdp \”/ as sysdba\” directory=BACKUP_DIR dumpfile=exp_PDWND12_$DATE.dmp logfile=exppdwnd12_$DATE.log EXCLUDE=STATISTICS full=y

    Export: Release 19.0.0.0.0 – Production on Sat Jun 3 15:44:05 2023
    Version 19.3.0.0.0

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

    1. To have environment variables also been set in a shell script, it’s safer to source user’s profile at the first line.

      . ~/.bash_profile

  3. Ed
    I added the shell script below for your review

    #!/bin/bash

    set -x
    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi

    ORACLE_HOME=/oracle/orabin/

    ORACLE_SID=PDWND12

    PATH=$PATH:$ORACLE_HOME/bin

    DATE=` date +%m%d%y`

    PASS=/home/oracle/scripts/.pass

    export ORACLE_HOME ORACLE_SID PATH DATE PASS
    # remove old exports

    find /oracle/oradata/Export_Dumps/ -name ‘exp*’ -mtime +6 -exec rm -f {} \;
    find /oracle/archive/logfiles/ -name ‘*dbf*’ -mtime +2 -exec rm -f {} \;

    cd /oracle/oradata/Export_Dumps/

    expdp \”/ as sysdba\” directory=BACKUP_DIR dumpfile=exp_PDWND12_$DATE.dmp logfile=exppdwnd12_$DATE.log EXCLUDE=STATISTICS full=y

    gzip /oracle/oradata/Export_Dumps/exp*.dmp
    gzip /oracle/oradata/Export_Dumps/exp*.log

    echo ‘finished the export’ >> /home/oracle/scripts/log/exp_pdwnd12.txt

    echo ‘finished the exp script’ >> /home/oracle/scripts/log/exp_pdwnd12.txt

    Thank You

    Joe

Leave a Reply

Your email address will not be published. Required fields are marked *