实验目的:将原有数据库的用户进行重建
1.将该用户下数据导出备份
exp userid=pcms/pcms@ncbsdbhis buffer=1024000000
file=pcms.dmp log=pcms.log compress=N consistent=Y owner=PCMS;
2.查询该用户的权限(赋予新建用户同样的权限)
SQL> select * from dba_role_privs where grantee='PCMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
PCMS SELECT_CATALOG_ROLE NO YES
PCMS CONNECT NO YES
PCMS RESOURCE NO YES
3.删除该用户下的表及其表空间
drop user pcms cascade;
4.删除该用户所占用表空间
drop tablespace TS_PCMS including contents;
drop tablespace IDX_PCMS including contents;
5.重建该用户并赋予原有权限
create user pcms identified by ylw2015;
grant connect to pcms;
grant resource to pcms;
grant SELECT_CATALOG_ROLE to pcms;
6.重建数据库表空间
CREATE TABLESPACE TS_PCMS
LOGGING
DATAFILE '/app/oracle/oradata/NCBSDBHIS/TS_PCMS.dbf'
SIZE 2048M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE IDX_PCMS
LOGGING
DATAFILE '/app/oracle/oradata/NCBSDBHIS/IDX_PCMS.dbf'
SIZE 512M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL;