#!/bin/bash
# Desc: 该脚本用于导出某属主用户的所有表结构： 表明、字段名、字段类型
## declare variables
sid_profile="/home/oracle/xxxprofile"
table_owner="xxx"
table_list_file="${table_owner}_tables.list"
get_table_structures_sql="get_${table_owner}_table_structures.sql"
table_structures_file="${table_owner}_table_structures.xls"
base_dir="/home/oracle/dw_standbys"
## env init
. ${sid_profile}
cd ${base_dir}
## get table list
sqlplus -S / as sysdba <<EOF
set linesize 200
set trimspool on feedback off pagesize 0
spool ${table_list_file}
select table_name from dba_tables where owner=upper('${table_owner}') order by 1;
spool off
EOF
sed -i '/^SQL>/d;/^$/d' ${table_list_file}
## generate sql script for getting table structures
cat /dev/null >${get_table_structures_sql}
# format sqlplus output as .xls file
echo -e "set term off verify off feedback off pagesize 999 \nset markup html on entmap ON spool on preformat off">> ${get_table_structures_sql}
#
echo -e "alter session set current_schema=\"${table_owner}\";" >>${get_table_structures_sql}
echo -e "spool ${table_structures_file}" >>${get_table_structures_sql}
echo -e "col Count_Tables for 999999999\ncol GaterDate for a30\n col TableOwner for a20">>${get_table_structures_sql}
echo -e "select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') GatherDate,'${table_owner}' TableOwner,(select count(1) from dba_tables where owner='${table_owner}') Count_Tables from dual;">>${get_table_structures_sql}
echo -e "col comment for a30" >>${get_table_structures_sql}
for table_name in `cat ${table_list_file}`
do
echo "select table_name,column_name,data_type,' ' as \"comment\" from DBA_TAB_COLS where owner=upper('${table_owner}') and table_name=upper('${table_name}');">>${get_table_structures_sql}
done
echo -e "spool off" >>${get_table_structures_sql}
## get table structures
sqlplus -S / as sysdba <<EOF
@${get_table_structures_sql}
EOF
result_status="$?"
if [ ${result_status} -eq 0 ];then
echo "Info: the table structure file of ${table_owner} is ${table_structures_file}."
else
echo "Critical: something is wrong with ${table_structures_file} !"
fi
本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/