Data Pump

How to expdp AS SYSDBA

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.

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
...

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
By this way, you have to provide password at run-time.

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

Please note that, if you put the connection string into a PARFILE, then you don’t have to escape any double or single quotes.

In fact, some utilities provided by Oracle need authentication in order to proceed, 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 *