卸载oracle表结构到文本中的脚本(UNIX下)

由于不太喜欢 ORACLE 的 EXP 因为他的结果不能转为文本 而且由于 metadata 没有安装 所以 get ddl 不能成功 最近没有看到有类似 informix 的 dbschema 的工具 所以就自己写了个脚本 还是比较幼稚 目前还不能处理视图 同义名 存储过程 希望大家多提意见 使他能更好的服务客户 涉及的系统表 1 User tables 用户表 2 User tab columns 用户表的字段 3 User view 用户视图 4 User indexes 用户表的索引 5 User ind columns 用户表字段的索引 6 User constraints 用户表的限定 7 User cons columns 用户表字段的限定 使用方法 dbschema sh 功能 根据一个数据库或表名来卸载结构 并在当前目录中生成对应的数据结构文件 包含表结 构 主键 外键 索引等 语法 dbschema sh userid passwd connection table name userid passwd oracle sid 登录数据库的信息 tablename 卸载的表名 可选 举例说明 dbschema sh test test testdb 逐个处理该用户的表 在当前目录中生成 test sql 脚本 dbschema sh test test testdb TT 处理 test 用户的 tt 表 在当前目录中生成 tt sql 脚本 脚本说明 usr bin ksh 模块 dbschema sh 描述 根据一个数据库或表名来获取相应的结构脚本 参数 1 用户名 密码 实例名 参数 2 表名 视图名 可选 作者 Bing He 修改记录 日期 修改人 修改描述 10 20 2003 Bing He 开始编写 局部变量定义 lv argc 0 命令行入参个数 lv loginfo 命令行中的登录信息 lv table name 命令行中的表名信息 lv filename 输出的文件名 lv tab number 0 需要处理的表的个数 lv sep 分隔符 lv grid str t 输出的距行首的空格 lv deal table 当前处理的表 lv file temp1 get ddl temp1 临时文件名 lv file temp2 get ddl temp2 临时文件名 lv file temp3 get ddl temp3 临时文件名 lv file tab col get ddl col1 临时文件名 lv file tab con get ddl con1 临时文件名 lv file col con get ddl con2 临时文件名 lv file tab ind get ddl ind1 临时文件名 lv file col ind get ddl ind2 临时文件名 检查用户名密码的权限 f check userid sqlplus lv loginfo dev null set echo off set heading off desc user tables exit if ne 0 then echo Error f check userid failed echo Please check the username passwd lv loginfo exit fi 检查表是否存在 f check tablename sqlplus lv loginfo dev null set echo off set heading off spool lv file temp1 select count from user tables where table name lv table name spool off exit if ne 0 then echo Error f check tablename failed echo Please check the lv table name exit fi lv number cat lv file temp1 grep v SQL grep v grep v rows selected if lv number eq 0 then echo Error f check tablenaem failed echo Please check the table lv table name in lv loginfo exit fi 初始化输出文件 f generate file if lv argc eq 1 then lv str echo lv loginfo cut d f1 tr upper lower lv str main Structure For User lv str else lv str echo lv table name tr upper lower lv str main Structure For Table lv str fi lv filename drop index lv str drop index lv filename drop foreign lv str drop foreign lv filename drop table lv str drop table lv filename create table lv str create table lv filename create foreign lv str create foreign lv filename lv str sql rm f lv filename drop index rm f lv filename drop foreign rm f lv filename drop table rm f lv filename create table rm f lv filename create foreign rm f lv filename 生成基础数据 lv str1 echo n lv str1 lv filename drop index echo 第一步 删除索引 lv filename drop index echo lv str1 n lv filename drop index lv str1 echo n lv str1 lv filename drop foreign echo 第二步 删除外键 lv filename drop foreign echo lv str1 n lv filename drop foreign lv str1 echo n lv str1 lv filename drop table echo 第三步 删除表 lv filename drop table echo lv str1 n lv filename drop table lv str1 echo n lv str1 lv filename create table echo 第四步 创建表结构 主键 索引 lv filename create table echo lv str1 lv filename create table lv str1 echo n lv str1 lv filename create foreign echo 第五步 创建外键 lv filename create foreign echo lv str1 n lv filename create foreign lv str1 echo n lv str1 lv filename echo DESC lv str main lv filename echo AUTHOR Bing He lv filename echo DATE 20 date y m d lv filename echo lv str1 n lv filename 获取用户下的表列表 f get tables rm f lv file temp1 sqlplus lv loginfo dev null set colsep lv sep set echo off set feedback off set heading off set pagesize 0 set linesize 1000 set numwidth 12 set termout off set trimout on set trimspool on spool lv file temp1 select table name from user tables spool off exit if ne 0 then echo Usage f get tables failed exit fi if f lv file temp1 then lv table name cat lv file temp1 grep v SQL tr d tr lower upper echo lv table name 1 out else echo Error f get tables failed lv file temp1 file not found exit fi rm f lv file temp1 生成表的基本结构 f generate tab column lv str1 lv str2 Table lv deal table lv str3 create table lv deal table lv str4 tablespace lv tab tablespace name lv col name lv col nullable lv col data type lv col data length lv col data precision lv col data scale echo n n lv str1 lv filename create table echo lv str2 lv filename create table echo lv str1 n lv filename create table echo lv str3 lv filename create table lv tab col rows wc l lv file tab col lv index 1 for lv line info in cat lv file tab col sed e s g do lv col name echo lv line info awk F print 2 lv col nullable echo lv line info awk F print 3 lv col data type echo lv line info awk F print 4 lv col data length echo lv line info awk F print 5 lv col data precision echo lv line info awk F print 6 lv col data scale echo lv line info awk F print 7 lv str5 lv grid str lv col name lv grid str lv col data type lv col data lengt h if lv col data type DATE then lv str5 lv grid str lv col name lv grid str lv col data type fi if lv col data type LONG RAW then lv str5 lv grid str lv col name lv grid str LONG RAW fi if lv col data type NUMBER then if X lv col data precision X then lv str5 lv grid str lv col name lv grid str lv col data type else lv str5 lv grid str lv col name lv grid str lv col data type lv col data preci sion lv col data scale fi fi if lv col nullable N then lv str5 lv str5 lv grid str NOT NULL fi if lv index eq lv tab col rows then echo lv str5 lv filename echo lv str5 lv filename create table else echo lv str5 lv filename echo lv str5 lv filename create table fi lv index expr lv index 1 done echo lv str4 lv filename echo lv str4 lv filename create table rm f lv file tab col 获得表的限定 f generate tab constraint lv constraint name lv constraint type lv r constraint name lv search condition lv str1 alter table lv deal table add constraint for lineinfo in cat lv file tab con sed e s g do lv constraint name echo lineinfo awk F print 1 lv constraint type echo lineinfo awk F print 2 lv r constraint name echo lineinfo awk F print 3 lv search condition echo lineinfo awk F print 4 lv str2 echo lv search condition if lv constraint type P then sqlplus lv loginfo dev null set echo off set heading off spool lv file temp2 select position column name from user cons columns where table name lv deal table and constraint name lv constraint name order by position spool off exit cat lv file temp2 grep v SQL grep v grep v rows selected lv file col con lv col rows wc l lv file col con lv index 1 for col line in cat lv file col con do lv str3 echo col line awk F print 2 if lv index eq lv col rows then lv str2 lv str2 lv str3 else lv str2 lv str2 lv str3 fi lv index expr lv index 1 done lv str2 lv str1 lv constraint name primary key lv str2 echo lv str2 lv filename create table fi if lv constraint type C then lv str2 lv str1 lv constraint name check echo lv search condition sed e s g echo lv str2 lv filename create table fi if lv constraint type R then lv str2 lv str1 lv constraint name check echo lv search condition sed e s g 获取本表的外键字段 sqlplus lv loginfo dev null set echo off set heading off spool lv file temp2 select position column name from user cons columns where table name lv deal table and constraint name lv constraint name order by position spool off exit cat lv file temp2 grep v SQL grep v grep v rows selected lv file col con lv col rows wc l lv file col con lv index 1 for col line in cat lv file col con do lv str3 echo col line awk F print 2 if lv index eq lv col rows then lv str2 lv str2 lv str3 else lv str2 lv str2 lv str3 fi lv index expr lv index 1 done lv str2 lv str1 lv constraint name foreign key lv str2 获取外键的字段信息 sqlplus lv loginfo dev null set echo off set heading off spool lv file temp3 select position table name column name from user cons columns where constraint name lv r constraint name order by position spool off exit cat lv file temp3 grep v SQL grep v grep v rows selected lv file col con lv col rows wc l lv file col con lv index 1 lv str4 for col line in cat lv file col con do if lv index eq 1 then lv str6 echo col line awk F print 2 fi lv str5 echo col line awk F print 3 if lv index eq lv col rows then lv str4 lv str4 lv str5 else lv str4 lv str4 lv str5 fi lv index expr lv index 1 done lv str2 lv str2 references lv str6 lv str4 echo alter table lv deal table drop constraint lv constraint name lv filename drop foreign echo lv str2 lv filename create foreign fi echo lv str2 lv filename echo lv str2 lv filename create table done 获取表的索引信息 f generate tab index lv tab ind name lv tab ind uniqueness lv tab ind tablespace for lineinfo in cat lv file tab ind do lv tab ind name echo lineinfo awk F print 1 lv tab ind uniqueness echo lineinfo awk F print 2 lv tab ind tablespace echo lineinfo awk F print 3 lv str2 sqlplus lv loginfo dev null set echo off set heading off spool lv file temp2 select column position column name descend from user ind columns where table name lv deal table and index name lv tab ind name order by column position spool off exit cat lv file temp2 grep v SQL grep v grep v rows selected lv file col ind lv col rows wc l lv file col ind lv index 1 for col line in cat lv file col ind do lv str3 echo col line awk F print 2 lv str4 echo col line awk F print 3 if lv index eq lv col rows then lv str2 lv str2 lv str3 lv str4 else lv str2 lv str2 lv str3 lv str4 fi lv index expr lv index 1 done if lv tab ind uniqueness NONUNIQUE then lv str2 create index lv tab ind name on lv deal table lv str2 tablespace lv tab ind tablespace else lv str2 create lv tab ind uniqueness index lv tab ind name on lv deal table lv str2 tablespace lv tab ind tablespace fi echo lv str2 lv filename echo drop index lv tab ind name lv filename drop index echo lv str2 lv filename create table done 获取单表的信息 并输出到指定文件中 f get tab info lv tab tablespace name lv tab initial extent lv tab next extent lv tab min extents lv tab max extents lv tab pct increase 获取 user table 中表的信息 sqlplus lv loginfo dev null set echo off set heading off spool lv file temp1 select tablespace name initial extent next extent min extents max extents pct increase from user tables where table name lv deal table spool off exit lv str cat lv file temp1 grep v SQL grep v rows selected tr d lv tab tablespace name echo lv str awk F print 1 lv tab initial extent echo lv str awk F print 2 lv tab next extents echo lv str awk F print 3 lv tab min extents echo lv str awk F print 4 lv tab max extents echo lv str awk F print 5 lv tab pct increase echo lv str awk F print 6 获取 user tab columns 表信息 rm f lv file temp1 sqlplus lv loginfo dev null set echo off set heading off spool lv file temp1 select column id column name nullable data type data length data precision data scal e from user tab columns where table name lv deal table order by column id spool off exit cat lv file temp1 grep v SQL grep v grep v rows selected lv file tab col f generate tab column rm f lv file temp1 rm f lv file tab col 获取 user constraints 表信息 rm f lv file temp1 sqlplus lv loginfo dev null set colsep lv sep set echo off set feedback off set heading off set pagesize 0 set linesize 1000 set numwidth 12 set termout off set trimout on set trimspool on spool lv file temp1 select constraint name constraint type r constraint name search condition from user constraints where table name lv deal table and generated USER NAME order by constraint type spool off exit cat lv file temp1 grep v SQL grep v grep v rows selected sed s g sed e s g lv file tab con if s lv file tab con then f generate tab constraint fi rm f lv file temp1 rm f lv file tab con 获取 user indexes 表信息 rm f lv file temp1 sqlplus lv loginfo dev null set colsep lv sep set echo off set feedback off set heading off set pagesize 0 set linesize 1000 set numwidth 12 set termout off set trimout on set trimspool on spool lv file temp1 select index name uniqueness tablespace name from user indexes where table name lv deal table and index name not in select constraint name from user constraints where table name lv deal table spool off exit cat lv file t