Re: Sys and System
- From: "joel garry" <joel-garry@xxxxxxxx>
- Date: 17 Jul 2006 15:38:39 -0700
ujangmz@xxxxxxxxx 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
.
- References:
- Sys and System
- From: ujangmz
- Sys and System
- Prev by Date: Re: RAC Clusterware Question
- Next by Date: Partition Table and Index
- Previous by thread: Re: Sys and System
- Next by thread: Oracle XMLType
- Index(es):
Relevant Pages
|
