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:

In which, username is a user who has been granted SYSDBA system privilege, it is usually but not necessarily SYS.

Or without password:

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.