#!/bin/bash

# author by ray

# v6

source ~/.bash_profile

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

oraname=****

#定義獲取ddl的函式

getOracleTableDDL(){

userpass=$1

tname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${tname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

select DBMS_METADATA.GET_DDL('TABLE',upper('${tname}')) from dual;

select 'comment on table '||TABLE_NAME||' is '||chr(39)||COMMENTS||chr(39)||';' from user_tab_comments where table_name=upper('${tname}');

SELECT 'comment on column ' ||table_name||'.'||column_name|| ' ' || 'is' ||' ' || '''' || comments || ''''||';' FROM USER_col_COMMENTS where table_name=upper('${tname}');

select DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME) from user_indexes where TABLE_NAME=upper('${tname}');

select DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME) from USER_CONSTRAINTS where TABLE_NAME=upper('${tname}');

spool off;

exit;

RAY

sed -i "s/\”${oraname}\"\.//g" $3/${tname}.sql

sed -i "s/\"//g" $3/${tname}.sql

}

getOracleIndexDDL(){

userpass=$1

Iname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${tname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

select DBMS_METADATA.GET_DDL('INDEX',upper('${Iname}')) from dual;

spool off;

exit;

RAY

sed -i "s/\"${oraname}\"\.//g" $3/${Iname}.sql

sed -i "s/\"//g" $3/${Iname}.sql

}

getOracleViewDDL(){

userpass=$1

Vname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${tname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

SELECT DBMS_METADATA.GET_DDL('VIEW',upper('${Vname}')) FROM DUAL;

spool off;

exit;

RAY

sed -i "s/\”\${oraname}\"\.//g" $3/${Iname}.sql

sed -i "s/\"//g" $3/${Iname}.sql

}

getOracleUserDDL(){

userpass=$1

Uname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${Uname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

SELECT DBMS_METADATA.GET_DDL('USER',upper('${Uname}')) FROM DUAL;

SELECT 'grant ' || tt.granted_role || ' to ' || tt.grantee || ';' AS SQL_text FROM dba_role_privs tt WHERE tt.grantee = (UPPER('${oraname}'))

UNION ALL

SELECT 'grant ' || tt. PRIVILEGE || ' to ' || tt.grantee || ';' FROM dba_sys_privs tt WHERE tt.grantee = (UPPER('kcpt'))

UNION ALL

SELECT 'grant ' || tt. PRIVILEGE || ' on ' || OWNER || '.' || table_name || ' to ' || tt.grantee || ';' FROM dba_tab_privs tt WHERE tt.grantee = (UPPER(‘\${oraname}'));

spool off;

exit;

RAY

}

getOracleTablespaceDDL(){

userpass=$1

Tname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${Tname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','${Tname}') FROM DUAL;

spool off;

exit;

RAY

}

getOracleSequenceDDL(){

userpass=$1

Sname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${Sname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('SEQUENCE','${Sname}') FROM DUAL;

spool off;

exit;

RAY

sed -i "s/\"${oraname}\"\.//g" $3/${Iname}.sql

sed -i "s/\"//g" $3/${Iname}.sql

}

getOracleFunctionDDL(){

userpass=$1

Fname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${Fname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('FUNCTION','${Fname}') FROM DUAL;

spool off;

exit;

RAY

sed -i "s/\"${oraname}\"\.//g" $3/${Iname}.sql

sed -i "s/\"//g" $3/${Iname}.sql

}

getOracleProcedureDDL(){

userpass=$1

Pname=$2

sqlplus -s /nolog <<-RAY

conn $userpass

spool $3/${Pname}.sql

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('PROCEDURE','${Pname}') FROM DUAL;

spool off;

exit;

RAY

sed -i "s/\"${oraname}\"\.//g" $3/${Iname}.sql

sed -i "s/\"//g" $3/${Iname}.sql

}

getDDL(){

objectname=$2

if [ -e ${objectname} ];then

for line in `cat $2`

do

$5 $1 ${line} $3

done

ls $3/*.sql | xargs cat >> $3/$4

rm -rf $3/*.sql

else

arr=(${objectname//,/ })

for line in ${arr[@]}

do

$5 $1 ${line} $3

ls $3/*.sql | xargs cat >> $3/$4

rm -rf $3/*.sql

done

fi

}

#迴圈獲取引數

argvs=($@)

for i in ${argvs[@]}

do

case `echo $i | awk -F '=' '{print $1}' | awk -F '--' '{print $2}'| tr [a-z] [A-Z]` in

USERPASS)

up=`echo $i | awk -F '=' '{print $2}'`

;;

OBJECT)

obj=`echo $i | awk -F '=' '{print $2}'`

;;

SAVEPATH)

sp=`echo $i | awk -F '=' '{print $2}'`

;;

SAVEFILE)

sf=`echo $i | awk -F '=' '{print $2}'`

;;

TYPE)

tp=`echo $i | awk -F '=' '{print $2}'`

;;

esac

done

#判斷匯出型別的個數

num=(${tp//,/ })

if [[ ${#num[@]} -gt 1 ]];then

echo "No more than one type of parameters"

exit 1

fi

#指令碼的入口，呼叫函式獲取DDL語句

case `echo ${tp} | tr [a-z] [A-Z]` in

TABLE)

getDDL ${up} ${obj} ${sp} ${sf} getOracleTableDDL

;;

INDEX)

getDDL ${up} ${obj} ${sp} ${sf} getOracleIndexDDL

;;

VIEW)

getDDL ${up} ${obj} ${sp} ${sf} getOracleViewDDL

;;

USER)

getDDL ${up} ${obj} ${sp} ${sf} getOracleUserDDL

;;

TABLESPACE)

getDDL ${up} ${obj} ${sp} ${sf} getOracleTablespaceDDL

;;

SEQUENCE)

getDDL ${up} ${obj} ${sp} ${sf} getOracleSequenceDDL

;;

FUNCTION)

getDDL ${up} ${obj} ${sp} ${sf} getOracleFunctionDDL

;;

PROCEDURE)

getDDL ${up} ${obj} ${sp} ${sf} getOracleProcedureDDL

;;

esac

##使用方法的例子

#./getddl.sh --userpass=ora_name/ora_pass --type=table --savepath=/home/oracle/shell --savefile=aa.txt --object=/home/oracle/shell/tablename.txt

#--type可以選擇TABLE，INDEX，VIEW，USER，TABLESPACE，SEQUENCE，FUNCTION，PROCEDURE

#--savepath 不用/結束

#--object可以用多個，可以單個，也可以用檔案

#./getddl.sh ora_name/ora_pass /home/oracle/shell/tablename.txt /home/oracle/shell sqlfile.txt #引數1使用者名稱密碼，引數2存放表名的檔案，引數3存放匯出ddl的目錄不已/結束，引數4最後形成的sql檔案

#./getddl.sh ora_name/ora_pass LY_ADVANCE_MONEY ~/sql/LY_ADVANCE_MONEY.sql #引數1使用者名稱密碼，引數2檢索的關鍵字,引數3最後形成的檔案

#ls sql/*.sql | xargs cat >> sql/tmp.txt

oracle獲取ddl指令碼

相關文章