oracle text格式化,学习笔记:Oracle 12C dbms_utility.expand_sql_text查看SQL视图所有基表

天萃荷净

学习在ORACLE 12C使用dbms_utility.expand_sql_text查看SQL视图所有基表

在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_oracleplus1 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_oracleplus2 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_oracleplus

2 as

3 select * from v_oracleplus1

4 union all

5 select * from v_oracleplus2;

视图已创建。

找出查询视图sql对应基表

CDB_PDB@CHF> set linesize 32767 pagesize 0 serveroutput on

CDB_PDB@CHF> declare

2 original_sql clob :='select * from v_oracleplus';

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语句

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C dbms_utility.expand_sql_text查看SQL视图所有基表