【Oracle】Oracle创建用户以及备份还原数据库操作

编辑小王

1. -- Create the user
   
2. create user XX
   
3.   identified by ""
   
4.   default tablespace USERS
   
5.   temporary tablespace TEMP
   
6.   profile DEFAULT
   
7.   password expire;
   
8. -- Grant/Revoke role privileges
   
9. grant connect to XX<span style="font-family: Arial, Helvetica, sans-serif;">;</span>
   
10. grant ctxapp to XX;
    
11. grant dba to XX;
    
12. grant exp_full_database to XX;
    
13. -- Grant/Revoke system privileges
    
14. grant alter any index to XX;
    
15. grant alter any table to XX;
    
16. grant create any index to XX;
    
17. grant create any table to XX;
    
18. grant create session to XX;
    
19. grant drop any index to XX;
    
20. grant drop any table to XX;
    
21. grant select any table to XX;
    
22. grant unlimited tablespace to XX;
    
23. 
    
24. ---删除用户时，提示无法删除当前用户链接用户
    
25. 
    
26. select 'alter system kill session'''|| to_char(a.sid)||','||to_char(serial#)||'''' from v$session a
    
27. where a.username=XX;
    
28. --执行查询结果，然后再删除用户
    
29. 
    
30. ---计划任务
    
31. 
    
32. select job,broken,what,interval,t.* from user_jobs t;
    
33.   begin
    
34. dbms_job.remove(63);
    
35.   end;
    
36. 
    
37. --导出（排除表T_ATTACHMENT）
    
38. expdp switch40/switch40@orcl dumpfile = switch40_0606_1907.dmp schemas=switch40 EXCLUDE=TABLE:"IN ('T_XX')"
    
39. --导入，从刚刚备份的用户switch40到新用户SWITCH52
    
40. impdp SWITCH52/SWITCH52@orcl dumpfile = switch40_0606_1907.dmp remap_schema=switch40:SWITCH52

复制代码