标签归档：dbms_utility.expand_sql_text

在ORACLE 12C之间的版本，如果一条sql中包含了N多视图,我们要查询这条sql访问了哪些基表，以及他们的关联条件是一件比较麻烦的时候，需要进入一个个视图然后一个个去分析，纯体力活，到了12C之后，ORACLE 提供了dbms_utility.expand_sql_text来获得某条sql所对应的全部基表，不用我们再一级一级的去看.

---

创建测试表
三个表分别来自数据库的v$datafile,v$tablespace,v$tempfile视图

CDB_PDB@CHF> create table datafile
  2  as
  3  select * from v$datafile;

表已创建。

CDB_PDB@CHF> create table tablespace
  2  as
  3  select * from v$tablespace;

表已创建。

CDB_PDB@CHF> create table tempfile
  2  as
  3  select * from v$tempfile;

表已创建。

创建视图
创建三个视图,datafile与tablespace，tempfile与tablespace，这两个视图然后做union all产生新视图

CDB_PDB@CHF> create view v_xifenfei1 as
  2  select d.name dname,t.name tname from datafile d,tablespace t where d.ts#=t.ts#;

视图已创建。

CDB_PDB@CHF> create view v_xifenfei2 as
  2  select d.name dname,t.name tname from tempfile d,tablespace t where d.ts#=t.ts#;

视图已创建。

CDB_PDB@CHF> create view v_xifenfei
  2  as
  3  select * from v_xifenfei1
  4  union all
  5  select * from v_xifenfei2;

视图已创建。

找出查询视图sql对应基表

CDB_PDB@CHF>  set linesize 32767 pagesize 0 serveroutput on
CDB_PDB@CHF>  declare
  2     original_sql clob :='select * from v_xifenfei';
  3     expanded_sql clob := empty_clob();
  4  begin
  5      dbms_utility.expand_sql_text(original_sql,expanded_sql);
  6      dbms_output.put_line(expanded_sql);
  7  end;
  8  /
SELECT "A1"."DNAME" "DNAME","A1"."TNAME" "TNAME" FROM  ( (SELECT "A4"."DNAME" "DNAME","A4"."TNAME" "TNAME" FROM  (SELECT
 "A6"."NAME" "DNAME","A5"."NAME" "TNAME" FROM CHF."DATAFILE" "A6",CHF."TABLESPACE" "A5" WHERE "A6"."TS#"="A5"."TS#") "A4
") UNION ALL  (SELECT "A3"."DNAME" "DNAME","A3"."TNAME" "TNAME" FROM  (SELECT "A8"."NAME" "DNAME","A7"."NAME" "TNAME" FR
OM CHF."TEMPFILE" "A8",CHF."TABLESPACE" "A7" WHERE "A8"."TS#"="A7"."TS#") "A3")) "A1"

PL/SQL 过程已成功完成。

格式化sql语句

/* Formatted on 2013/8/24 22:33:33 (QP5 v5.227.12220.39754) */
SELECT "A1"."DNAME" "DNAME", "A1"."TNAME" "TNAME"
  FROM ( (SELECT "A4"."DNAME" "DNAME", "A4"."TNAME" "TNAME"
            FROM (SELECT "A6"."NAME" "DNAME", "A5"."NAME" "TNAME"
                    FROM CHF."DATAFILE" "A6", CHF."TABLESPACE" "A5"
                   WHERE "A6"."TS#" = "A5"."TS#") "A4")
        UNION ALL
        (SELECT "A3"."DNAME" "DNAME", "A3"."TNAME" "TNAME"
           FROM (SELECT "A8"."NAME" "DNAME", "A7"."NAME" "TNAME"
                   FROM CHF."TEMPFILE" "A8", CHF."TABLESPACE" "A7"
                  WHERE "A8"."TS#" = "A7"."TS#") "A3")) "A1"

这里就非常清晰的看到是datafile与tablespace、tempfile与tablespace做union all的sql语句