How to execute datapump commands as sysdba from remote machine

data-pumporacle

I would like to know the syntax to call datapump commands (expdp/impdp) logged as 'sys as sysdba' from a remote machine.

I know that when logged on the machine which runs the database, I can use :

expdp \"/ as sysdba\"

However, I cannot find how to do this from a remote machine, for example, these does not work :

expdp 'SYS@SID AS SYSDBA'
expdp "SYS AS SYSDBA"@SID

In both case, the error message is :

LRM-00108: invalid positional parameter value [...]

Best Answer

expdp \"SYS@service AS SYSDBA\"

This works for me (10.2 and 11.1), but you need either to define service in your tnsnames.ora or to use proper SCAN. Generally, ORACLE_SID is a different identifier than TNS service, but for simplicity they often are administratively set to the same value.

Related Solutions

How to give access to execute packages and select from table for another schema

I answered a very similar question on stackoverflow.

Basically, it's this:

BEGIN
  FOR Rec IN (SELECT object_name, object_type FROM all_objects WHERE owner='SCHEMA1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    IF Rec.object_type IN ('TABLE','VIEW') THEN
      EXECUTE IMMEDIATE 'GRANT SELECT ON SCHEMA1.'||Rec.object_name||' TO SCHEMA2';
    ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON SCHEMA1.'||Rec.object_name||' TO SCHEMA2';
    END IF;
  END LOOP;
END;

Powershell – Executing Oracle SQLPlus in a Powershell Invoke-Command statement against a remote machine

This smells to me like an environment issue. Why can't you install the client properly on the remote system?

Is there any way you can dump your environment from the powershell script just prior to invoking SQL*Plus? If so, compare that with your environment when you're logged in and it works. Perhaps something like this:

Invoke-Command -comp remote.machine.host -ScriptBlock { $env:ORACLE_HOME= "C:\temp\oracle"; $env:PATH = "$env:ORACLE_HOME"; set > c:\temp\oracle\set.txt  }

Note: there appears to be a missing double quote in your PATH statement that I added in. I assume that was a transcription error.

Best Answer

Related Solutions

How to give access to execute packages and select from table for another schema

Powershell – Executing Oracle SQLPlus in a Powershell Invoke-Command statement against a remote machine

Related Topic