通过SHELL脚本获取表结构

ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/db

export CRS_HOME=/oracle/crs

export ORACLE_SID=pmsjx1

export LANG=en_US

export NLS_LANG=American_America.UTF8

cnt=0;

>mytable.txt

for tb in `/$ORACLE_HOME/bin/sqlplus -s /nolog << EOF

conn / as sysdba

set feedback off heading off pagesize 0

select table_name from dba_tables where owner='MDSYS';

exit

EOF`

do

cnt=$((cnt+1))

echo \"所有者：MDSYS 表名：$tb\">>mytable.txt

result=\"`/$ORACLE_HOME/bin/sqlplus -s /nolog << EOF

conn / as sysdba

set lines 400 pages 10000

col table_name for a36

col COLUMN_NAME for a30

col DATA_TYPE for a13

col NULLABLE for a8

col DATA_DEFAULT for a20

break on table_name skip 1;

select COLUMN_NAME,DATA_TYPE,data_length,NULLABLE,DATA_DEFAULT from DBA_TAB_COLUMNS where owner='MDSYS' and table_name='$tb';

exit

EOF`\"

echo \"$result\\n\">>mytable.txt

done

cp mytable.txt /dblog/test.doc

rm mytable.txt