ablog

不器用で落着きのない技術者のメモ

楽々 import シェルスクリプトを作った

Oracle bash

来る日も来る日も、Oracle Database の export ユーティリティーでとった dump ファイルを別の環境に import。。。
ユーザを drop して create して、import。ただ、気をつけないと drop した後に、あれ？このユーザはどんなシステム権限を持ってたっけ？オブジェクト権限は？ロールは？大丈夫。svn に DDL がある。いや、その DDL 本当に最新ですか？

とそんな私のために作ったのが、このシェルスクリプト(bash)。
これからは快適な import life が待ってる。

ディレクトリ構成

$ tree -p
.
|-- gen_create_user_script.sql <-- imp_schema.sh から呼ばれる SQL スクリプト
|-- imp_schema.sh <-- bash スクリプト
`-- scott.dmp <-- これから import する dump

実行例

$ ./imp_schema.sh scott.dmp
-- drop user.                                                                   
DROP USER SCOTT CASCADE;                                                        
                                                                                
-- create user.                                                                 
CREATE USER SCOTT                                                               
IDENTIFIED BY tiger                                                             
DEFAULT TABLESPACE USERS                                                        
TEMPORARY TABLESPACE TEMP                                                       
ACCOUNT UNLOCK                                                                  
PROFILE DEFAULT                                                                 
;

...

Grant succeeded.


Grant succeeded.

[2010-02-05 15:33:32] Importing scott.dmp to scott schema ...
[2010-02-05 15:33:32] Please check log/imp_schema_20100205-153324.log for more details.
[2010-02-05 15:33:32] Import proccess -> oracle   15542 15540  0 15:33 pts/0    00:00:00 imp                fromuser=scott touser=scott buffer=51380224 commit=y ignore=y file=scott.dmp

$ tail -f log/imp_schema_20100205-152935.log 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path
import done in JA16SJISTILDE character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
Import terminated successfully without warnings.

ソースコード

yoheia/imp_schema.sh at master · yoheia/yoheia · GitHub

#!/bin/bash
export LANG=C
export NLS_LANG=American_America.JA16SJISTILDE
BASE_DIR=$(cd $(dirname $0);pwd)
BASE_NAME=$(basename $0)

cd ${BASE_DIR}
test "$1" || { echo "Usage: ${BASE_NAME} dump_file"; exit 1; }
test -r "$1" || { echo "Can't read specified dump file: $1"; exit 1; } ; DMP_FILE=$1
test -d log || mkdir log

IMP_USER=system
IMP_PASS=manager

FROM_USER=scott
TO_USER=scott
TO_USER_PASS=tiger

TS=`date '+%Y%m%d-%H%M%S'`
CREATE_USER_SCRIPT="log/create_user_${TO_USER}_${TS}.sql"
LOG_FILE="log/${BASE_NAME%%.*}_${TS}.log"


# generate the sql script which drop and create specified user
${ORACLE_HOME}/bin/sqlplus -s / as sysdba @gen_create_user_script.sql ${TO_USER} ${TO_USER_PASS} ${CREATE_USER_SCRIPT} \
	|| { echo 'Failed to create a sql script.' ; exit 1; }

# drop and create specified user
${ORACLE_HOME}/bin/sqlplus -s / as sysdba @${CREATE_USER_SCRIPT} \
	|| { echo "Failed to execute ${CREATE_USER_SCRIPT}." ; exit 1; }

# import dump
nohup imp ${IMP_USER}/${IMP_PASS} fromuser=${FROM_USER} touser=${TO_USER} \
	buffer=51380224 commit=y ignore=y file=${DMP_FILE} > ${LOG_FILE} 2>&1 \
	|| { echo "Failed to import ${DMP_FILE} to ${TO_USER} schema." ; exit 1; } &

# show info
echo "[`date '+%Y-%m-%d %H:%M:%S'`] Importing ${DMP_FILE} to ${TO_USER} schema ..."
echo "[`date '+%Y-%m-%d %H:%M:%S'`] Please check ${LOG_FILE} for more details."
sleep 1
echo "[`date '+%Y-%m-%d %H:%M:%S'`] Import proccess -> `ps -f|egrep \"[i]mp \"`"


exit 0

yoheia/gen_create_user_script.sql at master · yoheia/yoheia · GitHub

define user_name = &1
define user_passwd = &2
define spool_file = &3

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 lower('&&spool_file') 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 'exit' from dual;

spool off
exit