自动查找oracle视图的基础表(原创)

近期项目有这么个需求，需要将数据从生产环境导入到开发环境。以便有真实的数据进行项目上线前的测试。手头上的文档只列出了所需的视图，并没有直接给出标名。这样也就需要通过查询视图定义找到底层表，然后在进行数据的导入。如果人工一层层找，主要的问题有以下3个：

1、很多视图是建立在视图之上的，这也就意味着需要人工一层层往下找，才能找到基表。

2、需要操作的视图数量较多。

3、刚需求日后还会有。

基于上述三点，我写了个shell脚本来自动查询出视图的基表。基本思路是通过存储过程判断输入的对象是表还是视图，然后再通过shell的正则表达式对输出内容进行处理。用shell是由于笔者对oracle地下的文本处理不是很熟悉，如果有了解的，希望指教。

具体脚本如下

存储过程

create or replace PROCEDURE view_verify(v_ob_name  in all_objects.OBJECT_NAME%type)
as
a_OB_TYPE   all_OBJECTS.OBJECT_TYPE%type;
a_OB_NAME   all_OBJECTS.object_name%type;
a_OB_OWNER  all_OBJECTS.owner%type;
a_CHAR      VARCHAR2(32767);
cursor         obj_cur is
            SELECT OBJECT_TYPE,OBJECT_NAME,OWNER
            FROM all_OBJECTS WHERE OBJECT_NAME= UPPER(v_ob_name);
begin
    open obj_cur;
    loop
        fetch obj_cur into a_OB_TYPE,a_OB_NAME,a_OB_OWNER;
        exit when obj_cur%notfound;
----------------------determine the object type----------------------
        IF a_OB_TYPE = 'VIEW' THEN
            select dbms_metadata.get_ddl(a_OB_TYPE,a_OB_NAME,a_OB_OWNER) into a_CHAR from dual;
            DBMS_OUTPUT.PUT_LINE(A_CHAR);
        elsif a_OB_TYPE = 'TABLE' THEN
            DBMS_OUTPUT.put_line('Table: ' ||a_ob_name);
        ELSE
            NULL;
        end if;
----------------------------------------------------------------
    end loop;
    close obj_cur;
end;
/

shell 脚本

#!/bin/bash
#######################declare variable##########################

export PATH=/u01/app/oracle/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
CONTROL=1
X=1
cat /dev/null > temp_result
cat /dev/null > result
cat /dev/null > output

#######################juge input argument#######################
if [ -f $1 ] ;then
        cat $1 > name_file
else
        echo  $1 > name_file
fi
#######################Loop######################################
while [ $CONTROL -gt 0 ]
  do
        cat /dev/null > temp_output
        for i in `cat name_file`
          do
                sqlplus -S scott/scott << EOF >> temp_output
                                        set serveroutput on;
                                        exec view_verify('$i');
EOF
          done
        sed -e '/[Ff][Rr][Oo][Mm] *$/N; s/\n/ /g' temp_output |grep -i from |grep -v Disconnected >  middle$X
        grep  "Table:" temp_output >> temp_result
        awk 'BEGIN{FS="[Ff][Rr][Oo][Mm]"}{if(NF>2) print $0}' middle$X >> exception
        sed -e 's/[Ff][Rr][Oo][Mm]/from/g' middle$X | grep -oP '(?<=from |join )\w*' |sort -u > name_file
        CONTROL=`grep -ci "from" middle$X`
        rm middle$X
        X=`expr $X + 1`
        cat temp_output >> output
  done
cat temp_result | sort -u > result
#######################remove temporary file#####################
rm temp_output
rm temp_result
#rm name_file

执行结果

[oracle@orcl ~]$ cat czm
ghi
dd

[oracle@orcl ~]$ ./eee czm
[oracle@orcl ~]$ cat result
Table: DEPT
Table: EMP

脚本我就不具体分析了，注释已经很详细了，如果有更高效的想法，希望联系我，谢谢。

 

本文原创，转载请注明出处、作者

如有错误，欢迎指正
邮箱：[e-mail]