You cannot export any schema that contains dictionary metadata. This is a
restriction of export process.
Schemas that exclusively contain
dictionary objects like SYS, CTXSYS, MDSYS and ORDSYS are never exported nor
imported. This is because these schemas are created when you install the related
database option or when you create your database. Thus it is not necessary for
export to generate create statements for their objects.
We are sure that this metadata
always will exists in a database if the corresponding option has been installed.
Rows in dictionary metadata objects will be created while export process is
creating the different user objects in the database.
For an example
scenario:
Suppose that we have a database with Spatial option installed,
and we want to create a new identical database via exp-imp. We will follow
steps:
1. Create a new database with Spatial Option. After this, we will
have a new database with schemas SYS and MDSYS and all dictionary metadata for
those schemas has been created.
2. Export with FULL=Y of our old
database. We will realize, although no error will be raised, that schemas SYS
and MDSYS won't be exported.
3. Import with FULL=Y into new database. All
our user schemas and user data will be created in the new database. Notice that
while all create statements are going to be executed against our new database,
all corresponding rows are being inserted in the dictionary metadata
schemas.
Eg: If we issue
CREATE TABLE, a row is inserted in DBA_TABLES.
If we create a spatial
index, the corresponding rows are inserted in the MDSTS schema.
If you check $ORACLE_HOME/rdbms/admin/catexp.sql you see will the users that are
excluded from export , in the definition of view exu8usr
REM
REM all users
REM
CREATE OR REPLACE VIEW exu8usr (
name, userid, passwd, defrole, datats, tempts, profile#,
profname, astatus, ext_username) AS
SELECT u.name, u.user#, DECODE(u.password, 'N', '', u.password),
DECODE(u.defrole, 0, 'N', 1, 'A', 2, 'L', 3, 'E', 'X'),
ts1.name, DECODE(BITAND(ts2.flags,2048),2048,'SYSTEM',ts2.name),
u.resource$, p.name, u.astatus,
u.ext_username
FROM sys.user$ u, sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
WHERE u.datats# = ts1.ts# AND
u.tempts# = ts2.ts# AND
u.type# = 1 AND
u.resource$ = p.profile# AND
u.name NOT IN ( 'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA',
'DIP', 'DMSYS', 'DBSNMP', 'EXFSYS',
'WMSYS','ORACLE_OCM')
/
GRANT SELECT ON sys.exu8usr TO SELECT_CATALOG_ROLE;
Be aware that you must
not create any objects in
these schemas, because you will not be able to export or import them.
阅读(1804) | 评论(0) | 转发(0) |
