oracle表能有多个属主吗,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 <

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 <

@${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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25856403/viewspace-1074709/，如需转载，请注明出处，否则将追究法律责任。