How Oracle exports all the table structures for a master user in Excel format

#!/bin/bash

# Desc: This script is used to export all table structures for a primary User: indication, field name, field type

# # 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 out 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} '

Todo

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

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/