How to expdp AS SYSDBA

  • by


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.

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.

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.

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

For 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 ~]$ impdp \"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 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.

Leave a Reply

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