Import tables as sysdba

[Brian Baker]

It does not appear that the Import tables procedure (the one that utilizes imp.exe) is working correctly when you are connected as sysdba. Instead of proceeding with the import, I see this in the command prompt window:

IMP-00058: ORACLE error 28009 encountered
ORA-28009: connection to sys should be as sysdba or sysoperUsername:

 

[Marco Kalter]

Hmm, apparently the sysdba session is not passed to the imp command. We'll fix it.

 

[rsarwar]

It's the same issue with exp tool. I would suggest against taking exports/imports as SYS... it should be done as SYSTEM. But if you must then the workaround I use is to use Plsqldev to generate the parfile... which resides in ..\Local Settings\temp directory. Manually edit the parfile and add AS SYSDBA for the userid value. Make sure to enclose the whole connect string inside "". Then use the parfile and kick off exp/imp manually from DOS prompt.

Regards

 

[Brian Baker]

Originally posted by rsarwar:
It's the same issue with exp tool. I would suggest against taking exports/imports as SYS... it should be done as SYSTEM.

Hmm, didn't know that. Any particular reason?

I'm no Oracle expert... all I know is that our client sends us an export file for me to import and I have to do it as sysdba instead of the user. SYS was the first user that worked, so I've stuck with that.

 

[rsarwar]

My caution against using SYS as SYSBA for export was meant as "General good DBA practices". Both SYS and SYSTEM are granted DBA role and can do full exports. Since SYS owns the data dictionary, his objects are never exported... including any grants that SYS has granted to other users. SYSTEM's objects are however exported with full export.
In short... SYS is the super most user in the database and is generally used only for install, upgrade, shutdown/startup etc. You should not get into the habit of connecting as SYS as SYSDBA to perform routine administrative tasks like changing password, export/import etc... do it as SYSTEM. Maybe you should ask your client to export as system and then you can also import as system.

If you want detailed description of SYS and SYSTEM... then view online docs at http://tahiti.oracle.com

Regards

 

[Brian Baker]

Originally posted by Marco Kalter:
Hmm, apparently the sysdba session is not passed to the imp command. We'll fix it.

Marco,

Although the latest version (I have 6.0.5.926) is supposed to fix this, it does not. When I perform a table import, the following command is executed:

C:\oracle\ora81\bin\IMP.EXE log=C:\DOCUME~1\BBAKER\LOCALS~1\Temp\plsimp.log file=C:\EXPAPR20.1043.DMP userid="sys/password@mplqa.ateam.com as sysdba" buffer=30720 commit=yes full=yes grants=no ignore=yes indexes=yes rows=yes show=yes constraints=yes

No errors appear in the log tab, but if I run that command in a command prompt I get the following error returned:

LRM-00112: multiple values not allowed for parameter 'userid'

IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully

In order for it to work successfully, I must use single quotes instead of double quotes around the userid.

NOTE: although the path to my imp command says ora81, it is in fact the Oracle 9i client.

 

[Marco Kalter]

Hmm, it seems that you are right. We'll fix it.

 

[Brian Baker]

Originally posted by Marco Kalter:
Hmm, it seems that you are right. We'll fix it.

Great, thanks!