Re: Sys and System

Mark D Powell wrote:
> marcus.rangel_at_gmail.com wrote:
> > Basically, sys owns the core database objects and system has almost
> > every available privilege. Sys is more powerful. You should use system
> > for full exports and imports.
> >
> > []īs
> >
> >
> > ujangmz_at_gmail.com wrote:
> > > Hi everyone,
> > >
> > > What is the difference between sys and system? And which one is more
> > > powerful?
> > >
> > > If I want to use EXP to create a dump file and then use the dump file
> > > with IMP to recreate the database in another machine, which id should I
> > > use to do the EXP?
> > >
> > > Regards
> > > Ujang
>
> For the export any id with the export_full_database privilege will work
> equally well.
>
> For the import any username with the corresponding import_full_database
> privilege will work.
>
> HTH -- Mark D Powell --

I ran across this information while trying to track down errors thrown by expdp in 10g R2:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1005615 "CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both."

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:21545894805637  (see response for November 06, 2004 post): "Ahh -- they tricked you -- read only transactions are NOT SUPPORTED for "sys" (as sysdba or otherwise) and they are using a read only transaction. the documentation has this to say:<quote> CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both. </quote>"

It appears that CONSISTENT=y cannot be used reliably in an export with the SYS user, but may be used with the SYSTEM user.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Jul 16 2006 - 19:08:04 CDT