9i oracle数据库迁移到11G（exp） (6)

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
BOSS CREATE SESSION NO
BOSS UNLIMITED TABLESPACE NO
DBSNMP SELECT ANY DICTIONARY NO
MONITOR UNLIMITED TABLESPACE NO
PERFSTAT ALTER SESSION NO
PERFSTAT CREATE PROCEDURE NO
PERFSTAT CREATE PUBLIC SYNONYM NO
PERFSTAT CREATE SEQUENCE NO
PERFSTAT CREATE SESSION NO
PERFSTAT CREATE TABLE NO
PERFSTAT DROP PUBLIC SYNONYM NO
SMART UNLIMITED TABLESPACE NO
SMART_CP CREATE SESSION NO
SMART_CP CREATE SNAPSHOT NO
SMART_CP CREATE TABLE NO
SMART_CP ON COMMIT REFRESH NO
SMART_CP UNLIMITED TABLESPACE NO
SMART_RO UNLIMITED TABLESPACE NO
SRDQ UNLIMITED TABLESPACE NO

19 rows selected.

 

 

已选择8行。

select * from dba_role_privs where grantee in ('SMART','BOSS','DBSNMP','SMART_CP','MD_QZY','SRDQ','SMART_RO','PERFSTAT','MONITOR','ZKDB','MD_Q')order by 1;

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSS CONNECT NO YES
BOSS RESOURCE NO YES
DBSNMP CONNECT NO YES
MD_QZY CONNECT NO YES
MD_QZY MD_Q NO YES
MONITOR CONNECT NO YES
MONITOR RESOURCE NO YES
MONITOR SELECT_CATALOG_ROLE NO YES
PERFSTAT SELECT_CATALOG_ROLE NO YES
SMART CONNECT NO YES
SMART DBA NO YES
SMART MD_Q YES YES
SMART RESOURCE NO YES
SMART_CP CONNECT NO YES
SMART_RO CONNECT NO YES
SMART_RO RESOURCE NO YES
SRDQ CONNECT NO YES
SRDQ RESOURCE NO YES
ZKDB CONNECT NO YES
ZKDB DBA NO YES
ZKDB RESOURCE NO YES

21 rows selected.

新建用户：

create user SMART identified by lzxMZD135468;
create user BOSS identified by BOSS;
create user DBSNMP identified by csmd2018;
create user SMART_CP identified by oracle;
create user MD_QZY identified by oracle;
create user SRDQ identified by oracle;
create user SMART_RO identified by oracle;
create user PERFSTAT identified by oracle;
create user MONITOR identified by oracle;
create user ZKDB identified by oracle;
create user rims identified by rims2019csmd;

alter user DBSNMP identified by values 'A0103B9F133B2E22';
alter user SMART_CP identified by values '88C0958CC2570C55';
alter user MD_QZY identified by values 'E7AD6E6562822EBE';
alter user SRDQ identified by values '6F1E875DA235F4F2';
alter user SMART_RO identified by values '1DEE3859FDA8CA41';
alter user PERFSTAT identified by values 'AC98877DE1297365';
alter user MONITOR identified by values '9AFC7F2344F99FF6';
alter user BOSS identified by values '2127DD06CE51E181';
alter user ZKDB identified by values '965E06A46BC6B0AC';
alter user SMART identified by values '5586BF85377BE4F2';

 

dba用户执行：

grant UNLIMITED TABLESPACE to smart;
grant CONNECT,DBA,MD_Q,RESOURCE to smart;
grant dba to SMART;
grant md_q to SMART with admin option;
grant CONNECT,RESOURCE to boss;
grant CREATE SESSION,UNLIMITED TABLESPACE to boss;
grant CONNECT,resource to dbsnmp;
grant SELECT ANY DICTIONARY to dbsnmp;
grant CREATE SESSION,CREATE SNAPSHOT,CREATE TABLE,ON COMMIT REFRESH,UNLIMITED TABLESPACE to SMART_CP;
grant CONNECT to SMART_CP;
grant CONNECT,MD_Q to MD_QZY;
grant UNLIMITED TABLESPACE to SRDQ;
grant CONNECT,RESOURCE to SRDQ;
grant UNLIMITED TABLESPACE to SMART_RO;
grant CONNECT,RESOURCE to SMART_RO;
grant ALTER SESSION,CREATE PROCEDURE,CREATE PUBLIC SYNONYM,CREATE SEQUENCE,CREATE SESSION,CREATE TABLE,DROP PUBLIC SYNONYM to PERFSTAT;
grant SELECT_CATALOG_ROLE to PERFSTAT;
grant UNLIMITED TABLESPACE to MONITOR;
grant CONNECT,RESOURCE,SELECT_CATALOG_ROLE to MONITOR;
grant CONNECT,RESOURCE,DBA to ZKDB;

grant connect to RIMS;
grant resource to RIMS;
grant create table to RIMS;
grant unlimited tablespace to RIMS;

-- smart Create the role
create role MD_Q;
-- Grant/Revoke object privileges

smart用户执行(导入表后在授权)：
grant select on S_MOBACKUPQUEUE to MD_Q;
grant select on S_MOQUEUE to MD_Q;
grant select on S_OPERATELOG_TONGJI to MD_Q;
grant select on S_REALINFO to MD_Q;
grant select on S_REGISTRYINFO to MD_Q;
grant select on S_T_RETURN_REPORT_TJ to MD_Q;
grant select on S_T_SEND_REPORT_TJ to MD_Q;
grant md_q to MD_QZY;
grant md_q to SMART with admin option;

 

--revoke DBA from SMART ;
--revoke DBA from BOSS
--revoke DBA from DBSNMP ;
--revoke DBA from SMART_CP;
--revoke DBA from MD_QZY ;
--revoke DBA from SRDQ ;
--revoke DBA from SMART_RO ;
--revoke DBA from PERFSTAT;
--revoke DBA from MONITOR ;
revoke DBA from ZKDB ;

设置字符集（expdp不用设置）

 

查看字符集：

SQL>select userenv('language') from dual;

export nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

4、检查对象下表的具体行数