Re: Sys and System

ujangmz_at_gmail.com wrote:
> Hi everyone,
>
> What is the difference between sys and system? And which one is more
> powerful?

SYS owns dictionary objects. Dictionary objects are not exported because they are created as part of an install. You can see what other users are not exported by looking at
$ORACLE_HOME/rdbms/admin/catexp.sql and searching for the string "NOT IN" where you will see something like NOT IN ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
 'LBACSYS', 'XDB')
>
> 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?

In general, you don't want to use SYS (or its equivalent, "as sysdba") to do these things. There are some exceptions, which usually involve some sort of metadata. For example, for transportable tablespaces you export the metadata, copy the data file, and import the metadata. SYS is exempt from some security and access control, including virtual private databases. SYS is exempt from transactional control (which is why the CONSISTENT parameter cannot work).

A few more things are not done with exp/imp: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2192

So, "it depends."

If you are using the word "database" as some vendors other than Oracle do (that is, calling a schema your database), then perhaps you can use imp/exp as the user. One vendor I'm familiar with expects you to do that, but you have to also do some other things to prepare the new schema beforehand, and sometimes some post-imp operations too. Another makes a huge project out of it.

If you are asking as a DBA in preparation for backing up or cloning your database, the answer would be "use RMAN for backups/cloning, and you may also want to export some things as a logical backup." I find that user level direct exports of the schemata with production data in it, combined with a full export (by SYSTEM) with rows=N to get some objects that user level exports miss is usually the best balance. See the utilities manual for details, and don't use compress=Y.

It also depends on your version, platform and requirements!

jg

--
@home.com is bogus.
So what happens to training scammers?
http://www.signonsandiego.com/uniontrib/20060716/news_1n16allerca.html