Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2812245
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-12-27 10:40:02

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.

阅读(1728) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~