oracle如何以excel格式匯出某屬主使用者的所有表結構

#!/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/