impdp数据库dump的脚本

import-dpdump.sh

#!/bin/bash
if [[ -z $1 || -z $2 || -z $3 || -z $4 || -z $5 || -z $6 ]]; then
	echo 'usage: import-dpdump.sh <dba> <dba password> <user> <user password> <directory> <dump>'
	echo 'example: import-dpdump.sh admin 123456 user 123456 dir_object user20141010.dpdmp'
	exit
fi

dba=$1
dbapsswd=$2
user=$3
userpasswd=$4
dir=$5
file=$6

# 删除用户、新建用户
sqlplus $dba/$dbapsswd <<sql
drop user $user cascade;
create user $user identified by "$userpasswd";
grant connect,resource,dba to $user;
grant read,write on directory $dir to $user;
GRANT UNLIMITED TABLESPACE TO $user;
exit;
sql

# 仅导入数据结构
impdp $user/$userpasswd DIRECTORY=$dir dumpfile=$file LOGFILE=$file.log remap_tablespace=$user:USERS content=metadata_only

# 修正字符串数据结构
sqlplus $user/$userpasswd <<sql
-- 查找外键
drop table test_constraint;

CREATE TABLE test_constraint AS
SELECT constraint_name,table_name,owner
FROM all_constraints WHERE owner='$user' AND constraint_type='R'
AND status='ENABLED';

-- 禁用外键
DECLARE
  v_sql VARCHAR2(2000);
BEGIN
  FOR x IN(SELECT constraint_name,table_name,owner FROM test_constraint) LOOP
    v_sql:='alter table '||x.owner||'.'||x.table_name||' disable constraint '||x.constraint_name;
    EXECUTE IMMEDIATE (v_sql);
    END LOOP;
END;
/

# -- 加长字符类型长度
DECLARE
v_sql VARCHAR2(2000);
BEGIN
  FOR x IN(
    SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,DECODE(SIGN(A.data_length*2-4000),1,4000,data_length*2) data_length
    FROM all_tab_cols A
    INNER JOIN all_tables b ON a.TABLE_NAME=b.TABLE_NAME AND a.owner=b.OWNER
    WHERE a.owner='$user' AND a.data_type LIKE'%CHAR%'  AND a.CHAR_USED='B'
    AND b.PARTITIONED<>'YES' AND  (B.TABLE_NAME,B.OWNER)
    NOT IN (SELECT TABLE_NAME,OWNER FROM ALL_EXTERNAL_TABLES)
  )LOOP
    v_sql:='ALTER TABLE '||x.owner||'.'||x.table_name||' MODIFY '||x.column_name||' VARCHAR2('||x.data_length||')';
    EXECUTE IMMEDIATE(v_sql);
  END LOOP;
END;
/

commit;
exit;
sql

# 导入数据
impdp $user/$userpasswd DIRECTORY=$dir dumpfile=$file LOGFILE=$file.log remap_tablespace=$user:USERS content=data_only

启用外键
sqlplus $user/$userpasswd <<sql
DECLARE
v_sql VARCHAR2(2000);
BEGIN
  FOR x IN(SELECT constraint_name,table_name,owner FROM test_constraint) LOOP
    v_sql:='alter table '||x.owner||'.'||x.table_name||' enable constraint '||x.constraint_name;
    EXECUTE IMMEDIATE (v_sql);
    END LOOP;
END;
/

drop table test_constraint;
exit;
sql

# 把用户密码设置为1
sqlplus $user/$userpasswd <<sql
update se_users set password=md5(1);
update se_users set name='admin' where id=1;
commit;
exit;
sql

# 收回用户的dba权限
sqlplus $user/$userpasswd <<sql
revoke dba from $user;
grant unlimited tablespace to $user;
exit;
sql