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

原创

wx6323d657ef2b3 2022-09-16 13:05:18 博主文章分类：SQL ©著作权

文章标签 数据库 shell awk oracle 数据 文章分类 云平台云计算

©著作权归作者所有：来自51CTO博客作者wx6323d657ef2b3的原创作品，请联系作者获取转载授权，否则将追究法律责任

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