1、停止所有对数据库访问的应用程序以及相关其它方式对数据库的访问
2、在plsql中执行下面脚本删除已有的各个系统用户及用户下对象
DROP USER CRMACCESS cascade;
DROP USER CRMTPM03 cascade;
DROP USER CRMTBM03 cascade;
DROP USER CRMTCM03 cascade;
DROP USER CRMTCMHIS03 cascade;
DROP USER CRMTBMHIS03 cascade;
DROP USER PUBUSER03 cascade;
DROP USER PUBUSERHIS03 cascade;
DROP USER CRMSTATIC03 cascade;
DROP USER PUBMETA03 cascade;
DROP USER CRMPROG03 cascade;
DROP USER CSR cascade;
DROP USER PUBMETA CASCADE;
DROP USER PUBUSER CASCADE;
DROP USER PUBUSERHIS CASCADE;
DROP USER SPSACCESS CASCADE;
DROP USER SPSPROG CASCADE;
DROP USER SPSSTATIC CASCADE;
DROP USER SPSTRM CASCADE;
DROP USER SPSTRMHIS CASCADE;
DROP USER SPSTSP CASCADE;
DROP USER SPSTSPHIS CASCADE;
DROP USER TSAPPROG CASCADE;
3、在plsql中执行下面脚本重新创建各个系统用户
create user CRMPROG03 identified by crmprog123 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMPROG03;
grant create any outline to CRMPROG03;
grant create any procedure to CRMPROG03;
grant debug connect session to CRMPROG03;
grant unlimited tablespace to CRMPROG03;
create user PUBMETA03 identified by PUBMETA default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBMETA03;
grant unlimited tablespace to PUBMETA03;
create user CRMSTATIC03 identified by CRMSTATIC default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMSTATIC03;
grant unlimited tablespace to CRMSTATIC03;
create user PUBUSERHIS03 identified by PUBUSERHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBUSERHIS03;
grant unlimited tablespace to PUBUSERHIS03;
create user PUBUSER03 identified by PUBUSER03 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBUSER03;
grant unlimited tablespace to PUBUSER03;
create user CRMTBMHIS03 identified by CRMTBMHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMTBMHIS03;
grant unlimited tablespace to CRMTBMHIS03;
create user CRMTCMHIS03 identified by CRMTCMHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMTCMHIS03;
grant unlimited tablespace to CRMTCMHIS03;
create user CRMTCM03 identified by CRMTCM default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMTCM03;
grant unlimited tablespace to CRMTCM03;
create user CRMTBM03 identified by CRMTBM default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMTBM03;
grant unlimited tablespace to CRMTBM03;
create user CRMTPM03 identified by CRMTPM default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMTPM03;
grant unlimited tablespace to CRMTPM03;
create user CRMACCESS identified by crmaccess123 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CRMACCESS;
grant alter any procedure to CRMACCESS;
grant unlimited tablespace to CRMACCESS;
create user CSR identified by csr123 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to CSR;
grant alter any procedure to CSR;
grant unlimited tablespace to CSR;
grant create any outline to CSR;
grant debug connect session to CSR;
create user SPSPROG identified by spsprog123 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSPROG;
grant create any outline to SPSPROG;
grant create any procedure to SPSPROG;
grant debug connect session to SPSPROG;
grant unlimited tablespace to SPSPROG;
grant dba to SPSPROG;
create user PUBMETA identified by PUBMETA default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBMETA;
grant unlimited tablespace to PUBMETA;
create user SPSSTATIC identified by SPSSTATIC default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSSTATIC;
grant unlimited tablespace to SPSSTATIC;
create user PUBUSERHIS identified by PUBUSERHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBUSERHIS;
grant unlimited tablespace to PUBUSERHIS;
create user PUBUSER identified by PUBUSER default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to PUBUSER;
grant unlimited tablespace to PUBUSER;
create user SPSTRM identified by SPSTRM default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSTRM;
grant unlimited tablespace to SPSTRM;
create user SPSTRMHIS identified by SPSTRMHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSTRMHIS;
grant unlimited tablespace to SPSTRMHIS;
create user SPSTSP identified by SPSTSP default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSTSP;
grant unlimited tablespace to SPSTSP;
create user SPSTSPHIS identified by SPSTSPHIS default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSTSPHIS;
grant unlimited tablespace to SPSTSPHIS;
create user SPSACCESS identified by spsaccess123 default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource to SPSACCESS;
grant alter any procedure to SPSACCESS;
grant unlimited tablespace to SPSACCESS;
grant dba to SPSACCESS;
create user TSAPPROG identified by tsapprog default tablespace SYSTEM temporary tablespace TEMP;
grant connect,resource,create view to TSAPPROG;
grant alter any procedure to TSAPPROG;
grant unlimited tablespace to TSAPPROG;
grant create any outline to TSAPPROG;
grant debug connect session to TSAPPROG;
4、在plsql中执行脚本从数据库查询到字符集格式select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET'
5、使用数据库安装用户oracle登陆数据库操作系统,执行命令env查看系统环境变量NLS_LANG值
检查导入客户端操作系统环境变量NLS_LANG值是否跟数据库字符集一致,如果不一致,请使用下面命令进行更改,export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
6、在操作系统中执行下面命令根据用户导入各个系统对象
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBMETA03 TOUSER=PUBMETA03 ignore=y commit=y log=PUBMETA03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMSTATIC03 TOUSER=CRMSTATIC03 ignore=y commit=y log=CRMSTATIC03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBUSER03 TOUSER=PUBUSER03 ignore=y commit=y log=PUBUSER03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBUSERHIS03 TOUSER=PUBUSERHIS03 ignore=y commit=y log=PUBUSERHIS03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMTBM03 TOUSER=CRMTBM03 ignore=y commit=y log=CRMTBM03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMTBMHIS03 TOUSER=CRMTBMHIS03 ignore=y commit=y log=CRMTBMHIS03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMTCM03 TOUSER=CRMTCM03 ignore=y commit=y log=CRMTCM03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMTCMHIS03 TOUSER=CRMTCMHIS03 ignore=y commit=y log=CRMTCMHIS03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMTPM03 TOUSER=CRMTPM03 ignore=y commit=y log=CRMTPM03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMPROG03 TOUSER=CRMPROG03 ignore=y commit=y log=CRMPROG03.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=CRMACCESS TOUSER=CRMACCESS ignore=y commit=y log=CRMACCESS.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBMETA TOUSER=PUBMETA ignore=y commit=y log=PUBMETA.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBUSER TOUSER=PUBUSER ignore=y commit=y log=PUBUSER.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=PUBUSERHIS TOUSER=PUBUSERHIS ignore=y commit=y log=PUBUSERHIS.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSSTATIC TOUSER=SPSSTATIC ignore=y commit=y log=SPSSTATIC.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSTRM TOUSER=SPSTRM ignore=y commit=y log=SPSTRM.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSTRMHIS TOUSER=SPSTRMHIS ignore=y commit=y log=SPSTRMHIS.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSTSP TOUSER=SPSTSP ignore=y commit=y log=SPSTSP.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSTSPHIS TOUSER=SPSTSPHIS ignore=y commit=y log=SPSTSPHIS.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSPROG TOUSER=SPSPROG ignore=y commit=y log=SPSPROG.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/crmdb.dmp FROMUSER=SPSACCESS TOUSER=SPSACCESS ignore=y commit=y log=SPSACCESS.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/prvdb.dmp FROMUSER=TSAPPROG TOUSER=TSAPPROG ignore=y commit=y log=TSAPPROG.log
imp "'sys/oracle as sysdba'" file=/data/ora11/mddmp/prvdb.dmp FROMUSER=CSR TOUSER=CSR ignore=y commit=y log=CSR.log
7、导入完成后在plsql中执行如下脚本
grant select on crmtpm03.product to CSR;
grant select on CRMTCM03.PARTY to CSR;
grant select on CRMTPM03.PRODUCT_OFFER to CSR;
grant select on CRMTBM03.PRODUCT_OFFER_INSTANCE to CSR;
grant select on CRMTBM03.PRODUCT_OFFER_INSTANCE_DETAIL to CSR;
grant select on PUBMETA03.PUB_COLUMN_REFERENCE to CSR;
grant select on CRMTBM03.SERV to CSR;
grant select on CRMTBM03.TBM_ADDRESS_INSTANCE to CSR;
grant select on CRMTBM03.TBM_BROADBAND_SERVICE_ASK to CSR;
grant select on CRMTBMHIS03.TBM_BROADBAND_SERVICE_HIS to CSR;
grant select on CRMTBM03.TBM_OTHER_ASK to CSR;
grant select on CRMTBMHIS03.TBM_OTHER_HIS to CSR;
grant select on CRMTBMHIS03.TBM_VOIP_HIS to CSR;
grant select on CRMTBM03.TBM_VOIP_INSTANCE to CSR;
grant select on CRMTCM03.TCM_CUSTOMER to CSR;
create or replace synonym CSR.PARTY for crmtpm03.product;
create or replace synonym CSR.PARTY for CRMTCM03.PARTY;
create or replace synonym CSR.PRODUCT_OFFER for CRMTPM03.PRODUCT_OFFER;
create or replace synonym CSR.PRODUCT_OFFER_INSTANCE for CRMTBM03.PRODUCT_OFFER_INSTANCE;
create or replace synonym CSR.PRODUCT_OFFER_INSTANCE_DETAIL for CRMTBM03.PRODUCT_OFFER_INSTANCE_DETAIL;
create or replace synonym CSR.PUB_COLUMN_REFERENCE for PUBMETA03.PUB_COLUMN_REFERENCE;
create or replace synonym CSR.SERV for CRMTBM03.SERV;
create or replace synonym CSR.TBM_ADDRESS_INSTANCE for CRMTBM03.TBM_ADDRESS_INSTANCE;
create or replace synonym CSR.TBM_BROADBAND_SERVICE_ASK for CRMTBM03.TBM_BROADBAND_SERVICE_ASK;
create or replace synonym CSR.TBM_BROADBAND_SERVICE_HIS for CRMTBMHIS03.TBM_BROADBAND_SERVICE_HIS;
create or replace synonym CSR.TBM_OTHER_ASK for CRMTBM03.TBM_OTHER_ASK;
create or replace synonym CSR.TBM_OTHER_HIS for CRMTBMHIS03.TBM_OTHER_HIS;
create or replace synonym CSR.TBM_VOIP_HIS for CRMTBMHIS03.TBM_VOIP_HIS;
create or replace synonym CSR.TBM_VOIP_INSTANCE for CRMTBM03.TBM_VOIP_INSTANCE;
create or replace synonym CSR.TCM_CUSTOMER for CRMTCM03.TCM_CUSTOMER;