Oracle如何生成导出账号的DDL脚本

之前我写了一篇博客ORACLE如何用一个脚本找出一个用户的授权信息？,有些场景,这个脚本够用了,但是有些场景,例如,你需要将一个账号的权限从开发环境平移到UAT环境时,我想通过一个脚本生成账户(test)的ddl脚本,自己写了一个脚本,后面发现网上有一个脚本更好/更全面,分享于此:

1. set long 20000
2. set longchunksize 20000
3. set pagesize 0
4. set linesize 1000
5. set trimspool on
6. set feedback off
7. set verify off
8. column ddl format a1000
10. --Add a semicolon at the end of each statement
11. execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
12. --Generate the DDL for User you enter
13. select dbms_metadata.get_ddl('USER', u.username) AS ddl
14. from dba_users u
15. where u.username = upper(trim('&&v_username'))
16. union all
17. select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
18. from dba_ts_quotas tq
19. where tq.username = upper(trim('&&v_username')) and rownum = 1
20. union all
21. select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
22. from dba_role_privs rp
23. where rp.grantee = upper(trim('&&v_username'))
24. and rownum = 1
25. union all
26. select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
27. from dba_sys_privs sp
28. where sp.grantee = upper(trim('&&v_username'))
29. and rownum = 1
30. union all
31. select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
32. from dba_tab_privs tp
33. where tp.grantee = upper(trim('&&v_username'))
34. and rownum = 1
35. union all
36. select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
37. from dba_role_privs rp
38. where rp.grantee = upper(trim('&&v_username'))
39. and rp.default_role = 'YES'
40. and rownum = 1
41. union all
42. select to_clob('/* Start profile creation script in case they are missing') AS ddl
43. from dba_users u
44. where u.username = upper(trim('&&v_username'))
45. and u.profile='DEFAULT'
46. and rownum = 1
47. union all
48. select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
49. from dba_users u
50. where u.username = upper(trim('&&v_username'))
51. and u.profile='DEFAULT'
52. union all
53. select to_clob('End profile creation script */') AS ddl
54. from dba_users u
55. where u.username = upper(trim('&&v_username'))
56. and u.profile='DEFAULT'
57. and rownum = 1
58. /

复制代码

此脚本不是原始脚本,原始脚本请见下文链接.  此脚本做了些许变化, 对输入变量v_username进行了去除空格与转换大写处理,让脚本更健壮一点.
测试如下所示, 生成用户test的ddl脚本,如下所示

1. SQL> @gen_user_create_script.sql
2. Enter value for v_username: test
4.    CREATE USER "TEST" IDENTIFIED BY VALUES 'T:3F0DD3EE56D86868D4C97E562247BFFFD8EC4D8C60BDE2D720D406B46A4BE300C0BCD1BFF90EFD40D8843D872698FCFC62FF64F589E6B6102350CD3C762E22B955F52FF0E0EC64BF96F3B60799FFAE5B'
5.       DEFAULT TABLESPACE "USERS"
6.       TEMPORARY TABLESPACE "TEMP";
9.    GRANT "CONNECT" TO "TEST";
12.   GRANT CREATE TABLE TO "TEST";
13.   GRANT CREATE VIEW TO "TEST";
16.    ALTER USER "TEST" DEFAULT ROLE ALL;
18. /* Start profile creation script in case they are missing
20.    ALTER PROFILE "DEFAULT"
21.     LIMIT
22.          COMPOSITE_LIMIT UNLIMITED
23.          SESSIONS_PER_USER UNLIMITED
24.          CPU_PER_SESSION UNLIMITED
25.          CPU_PER_CALL UNLIMITED
26.          LOGICAL_READS_PER_SESSION UNLIMITED
27.          LOGICAL_READS_PER_CALL UNLIMITED
28.          IDLE_TIME UNLIMITED
29.          CONNECT_TIME UNLIMITED
30.          PRIVATE_SGA UNLIMITED
31.          FAILED_LOGIN_ATTEMPTS 10
32.          PASSWORD_LIFE_TIME 15552000/86400
33.          PASSWORD_REUSE_TIME UNLIMITED
34.          PASSWORD_REUSE_MAX UNLIMITED
35.          PASSWORD_VERIFY_FUNCTION NULL
36.          PASSWORD_LOCK_TIME 86400/86400
37.          PASSWORD_GRACE_TIME 604800/86400
38.          INACTIVE_ACCOUNT_TIME UNLIMITED
39.          PASSWORD_ROLLOVER_TIME -1/86400 ;
41. End profile creation script */

复制代码

参考资料

https://smarttechways.com/2021/02/04/generate-ddl-for-the-user-including-grants-in-oracle/
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！