Month: February 2010

Next sysdba credentials syntax for impdp with Windows PowerShell

there are a lot of posts around concerning the different credentials syntax for sysdba logins between sqlplus and impdp/expdp. in most cases people get stuck upon the fact, that the usual double quote syntax, they know from sqlplus:

sqlplus "/ as sysdba"

ceases to work for impdp/expdp like so:

expdp "/ as sysdba" directory=data_pump_dir ...
LRM-00108: Unzul├Ássiger Wert des Positionsparameters 'sysdba'

ok, we all learned that impdp/expdp prefer a single quote syntax for tea time:

expdp '/ as sysdba' directory=data_pump_dir ...

on unix system shells even escaped by \.

expdp \'/ as sysdba\'  directory=data_pump_dir ...

however, the introdution of windows powershell:

PS C:\Users> cmd
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. Alle Rechte vorbehalten.

found me learning another variant of the syntax game. because windows powershell uses a diffenrent approach of interpreting empty spaces on command lines, you have to enclose all the unnamed (credentials) and named (all the rest) switches of impdp/expdp into additional double quotes.

impdp "'/ as sysdba' directory=data_pump_dir ..."

that’s it.

last but not least, do not forget that employing sys for impdp/expdp may deliver inconsistent data over a multiple of transactions, since sys cannot and shall not do a transaction level consisent read (read-only transaction). this is especially important for partitioned or nested tables.