動態

詳情

Oracle如何生成導出賬號的DDL腳本 - 動態詳情

03:38 下午 · 11月 08 ,2025

之前我寫了一篇博客ORACLE如何用一個腳本找出一個用户的授權信息？,有些場景,這個腳本夠用了,但是有些場景,例如,你需要將一個賬號的權限從開發環境平移到UAT環境時,我想通過一個腳本生成賬户(test)的ddl腳本,自己寫了一個腳本,後面發現網上有一個腳本更好/更全面,分享於此:

set long 20000
set longchunksize 20000
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
set verify off
column ddl format a1000

--Add a semicolon at the end of each statement
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
--Generate the DDL for User you enter
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = upper(trim('&&v_username')) and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = upper(trim('&&v_username'))
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
and rownum = 1
/

此腳本不是原始腳本,原始腳本請見下文鏈接. 此腳本做了些許變化, 對輸入變量v_username進行了去除空格與轉換大寫處理,讓腳本更健壯一點.

測試如下所示, 生成用户test的ddl腳本,如下所示

SQL> @gen_user_create_script.sql
Enter value for v_username: test

   CREATE USER "TEST" IDENTIFIED BY VALUES 'T:3F0DD3EE56D86868D4C97E562247BFFFD8EC4D8C60BDE2D720D406B46A4BE300C0BCD1BFF90EFD40D8843D872698FCFC62FF64F589E6B6102350CD3C762E22B955F52FF0E0EC64BF96F3B60799FFAE5B'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";


   GRANT "CONNECT" TO "TEST";


  GRANT CREATE TABLE TO "TEST";
  GRANT CREATE VIEW TO "TEST";


   ALTER USER "TEST" DEFAULT ROLE ALL;

/* Start profile creation script in case they are missing

   ALTER PROFILE "DEFAULT"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400
         INACTIVE_ACCOUNT_TIME UNLIMITED
         PASSWORD_ROLLOVER_TIME -1/86400 ;

End profile creation script */