CREATE USER 文を生成する SQL スクリプト

Oracle で imp コマンドでスキーマの定義もデータもまるまる入れ替るときのために作った SQL スクリプト。
最初、オブジェクトを全部削除する SQL スクリプトを作ろうかと思ったけど、ぐぐってみると、id:niwanos さんが

FKとか張ってたりするとDROP TABLEするにも順序があったりするので先に制約を消すとよいです
ただ上記のものだけだとマテビューやLOBなどが消えなかったりするので
できるなら、DROP USER CASCADE でユーザごと一度消して再度作るのが良いかもしれません

ユーザのオブジェクトをすべて削除したいとき - niwaのoracle日記

と書かれていたので、ユーザごと消して再度作ることにした。
そこで、現状と同じ設定でユーザを作成する SQL スクリプトを生成する SQL スクリプトを作成してみた。
ちゃんとテストしてません。。。

yoheia/create_user_script.sql at master · yoheia/yoheia · GitHub
github にもおきました。

• create_user_script.sql

prompt specify user name which you want to create;
define user_name = &user_name
prompt specify user's passord which you want to create;
define user_passwd = &user_passwd

set echo off
set pagesize 0
set head off
set feed off
set verify off

col spool_file_name new_value spool_file_name format a100
select 'create_user_'||lower('&&user_name')||'.sql' spool_file_name from dual;
spool &spool_file_name

select '-- drop user.' from dual;
select '--DROP USER '||upper('&&user_name') ||' CASCADE;' from dual;
select '' from dual;

select '-- create user.' from dual;
select 'CREATE USER '|| upper('&&user_name') || CHR(10) ||
                'IDENTIFIED BY &&user_passwd '|| CHR(10) ||
                DECODE(DEFAULT_TABLESPACE, NULL, '', 'DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||CHR(10)) ||
                DECODE(TEMPORARY_TABLESPACE, NULL, '', 'TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||CHR(10)) ||
                DECODE(ACCOUNT_STATUS, 'OPEN', 'ACCOUNT UNLOCK', 'ACCOUNT LOCK') || CHR(10) ||
                DECODE(PROFILE, NULL, '', 'PROFILE '||PROFILE||CHR(10)) || ';'
        from dba_users where username = upper('&&user_name');
select '' from dual;

select '-- quota.' from dual;
select 'ALTER USER '|| upper('&&user_name') ||' QUOTA '|| DECODE(MAX_BYTES, -1, 'UNLIMITED', MAX_BYTES) ||' ON '|| TABLESPACE_NAME ||';'
        from dba_ts_quotas where username = upper('&&user_name');
select '' from dual;

select '-- grant system privilege to user.' from dual;
select 'GRANT '||PRIVILEGE||' TO '|| upper('&&user_name') || DECODE(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';'
        from dba_sys_privs where grantee = upper('&&user_name');
select '' from dual;

select '--grant object privilege to user.' from dual;
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '|| upper('&&user_name') || DECODE(GRANTABLE,'YES',' WITH GRANT OPTION','')||';'
        from dba_tab_privs where grantee = upper('&&user_name');
select '' from dual;

select '--grant role to user.' from dual;
select 'GRANT '||GRANTED_ROLE||' TO '|| upper('&&user_name') || DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', '')||';'
        from DBA_ROLE_PRIVS where GRANTEE = upper('&&user_name');
select '' from dual;

spool off
exit

実行してみるとこんな感じ。

$ sqlplus -s / as sysdba @create_user_script.sql
specify user name which you want to create
Enter value for user_name: scott
specify user's passord which you want to create
Enter value for user_passwd: tiger

$ head -10 create_user_scott.sql 
-- drop user.                                                                   
--DROP USER SCOTT CASCADE;                                                      
                                                                                
-- create user.                                                                 
CREATE USER SCOTT                                                               
IDENTIFIED BY tiger                                                             
DEFAULT TABLESPACE USERS                                                        
TEMPORARY TABLESPACE TEMP                                                       
ACCOUNT UNLOCK                                                                  
PROFILE DEFAULT                   

$ perl -i -ple 's/^--(DROP USER SCOTT.*)$/$1/' create_user_scott.sql

$ sqlplus / as sysdba @create_user_scott.sql

...

User dropped.


User created.


Grant succeeded.
...