ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to use Oracle utility EXPORT/IMPORT

How to use Oracle utility EXPORT/IMPORT

原创 Linux操作系统 作者:licheng79 时间:2011-09-09 11:10:59 0 删除 编辑

How to use Oracle utility EXPORT/IMPORT

In this case, we will use Oracle export/import to refresh database schema from production to DEV. Oracle data pump can enable parallel processing for higher speed. Data pump is recommanded. But, Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility. Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later. For this scenario, we use oracle 8, 9i and 10g version for different system combined.

 

For the performance consideration, we drop the schema owner and rebuild before importing. Following steps are applied:

 

1) Export the LOY schema from MA_PRD on CTMSUN09s /db/exp/.

2) Drop tthe LOY schema in MA_DEV on CTMSUN14.

3) Create the LOY user in MA_DEV on CTMSUN14.

4) Import the LOY schema to MA_DEV from CTMSUN09 remotely, i.e. imp system@MA_DEV on CTMSUN09.

 

Before drop a user, we need to log off or kill connected session with corresponding users:

 

Scripts:

 

exp username/password wner=LOY file=maace_20100805.dmp log=maace_20100805.log buffer=50000 feedback=10000 direct=y

 

drop user LOY cascade;

Create user LOY:

CREATE USER LOY

  IDENTIFIED BY VALUES '5F9B00F845914BF1'

  DEFAULT TABLESPACE DATA

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

  -- 4 Roles for LOY

  GRANT DBA TO LOY;

  GRANT LOY_ARRAY_TYPE TO LOY WITH ADMIN OPTION;

  GRANT RESOURCE TO LOY;

  GRANT CONNECT TO LOY;

  ALTER USER LOY DEFAULT ROLE ALL;

  -- 8 System Privileges for LOY

  GRANT CREATE ROLE TO LOY;

  GRANT SELECT ANY TABLE TO LOY;

  GRANT UNLIMITED TABLESPACE TO LOY;

  GRANT SELECT ANY DICTIONARY TO LOY;

  GRANT SELECT ANY SEQUENCE TO LOY;

  GRANT SELECT ANY TRANSACTION TO LOY;

  GRANT CREATE VIEW TO LOY;

  GRANT CREATE ANY SYNONYM TO LOY;

  -- 1 Object Privilege for LOY

    GRANT SELECT ON  SYS.AUD$ TO LOY;

 

 

imp username/password file=maace_20100805.dmp log=imp_maace_schema.log buffer=50000 fromuser=LOY touser=LOY commit=y grants=y indexes=y rows=y ignore=y compile=y

 

 

After restoration:

 

SQL> exec dbms_java.grant_permission( 'LOY', 'SYS:java.io.FilePermission', '/bin/sh', 'execute' );

PL/SQL procedure successfully completed.

--Grant permisison for Java external job execution

SQL> grant dba to loy;

Grant succeeded.

Img318742325.jpg

Img318749348.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-707018/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    69799