现场人员需要将USRDB用户复制出来,弄个USRDB1用户,在这个用户上导入数据做测试。
复制方案的思路:
1.查USRDB的默认表空间,PROFILE,系统权限,对象权限,角色,
2.建新用户,授权,
3.导出USRDB方案
4.导入方案
步骤如下:
- -- 1.查USRDB用户的信息
- SQL> select default_tablespace, temporary_tablespace,profile from dba_users where username='USRDB';
- DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
- ------------------------------ ------------------------------ ------------------------------
- CBS_USER_DAT TEMP DEFAULT
- -- 2.建USRDB1用户
- SQL> create user USRDB1 identified by USRDB1 default tablespace CBS_USER_DAT;
- -- 3.查出USRDB的系统权限,对象权限,角色
- SQL> select 'grant '||privilege||' to USRDB1;' from dba_sys_privs where grantee='USRDB'; -->系统权限
- 'GRANT'||PRIVILEGE||'TOUSRDB1;'
- ----------------------------------------------------------
- grant CREATE ANY SEQUENCE to USRDB1;
- grant CREATE SEQUENCE to USRDB1;
- grant CREATE VIEW to USRDB1;
- grant UNLIMITED TABLESPACE to USRDB1;
- grant CREATE ANY TRIGGER to USRDB1;
- grant CREATE PROCEDURE to USRDB1;
- grant CREATE ANY PROCEDURE to USRDB1;
- grant CREATE ANY TABLE to USRDB1;
- 8 rows selected.
- SQL> select 'grant '||privilege||' on '||table_name||' to USRDB1;' from dba_tab_privs where grantee='USRDB'; -->对象权限
- no rows selected
- SQL> select 'grant '||granted_role||' to USRDB1;' from dba_role_privs where grantee='USRDB'; -->角色
- 'GRANT'||GRANTED_ROLE||'TOUSRDB1;'
- ------------------------------------------------
- grant RESOURCE to USRDB1;
- grant CONNECT to USRDB1;
- grant DBA to USRDB1;
- -- 4.给USRDB1用户制授权
- 运行上一步骤执行得到的结果
- grant CREATE ANY SEQUENCE to USRDB1;
- grant CREATE SEQUENCE to USRDB1;
- grant CREATE VIEW to USRDB1;
- grant UNLIMITED TABLESPACE to USRDB1;
- grant CREATE ANY TRIGGER to USRDB1;
- grant CREATE PROCEDURE to USRDB1;
- grant CREATE ANY PROCEDURE to USRDB1;
- grant CREATE ANY TABLE to USRDB1;
- grant RESOURCE to USRDB1;
- grant CONNECT to USRDB1;
- grant DBA to USRDB1;
- -- 5.导出USRDB方案
- exp "'sys/oracle as sysdba'" owner=USRDB file=USRDBl.dmp rows=n -->因为不需要导数据,所以加了rows=n
- -- 6.导入方案
- imp "'sys/oracle as sysdba'" fromuser=USRDB touser=USRDB1 file=USRDBll.dmp