Afficher l'ordre SQL d'une vue de façon récursive : DBMS_UTILITY.expand_sql_text - View the SQL order of a view recursively
Introduction
Une vue est un objet tout simple d'Oracle, c'est juste un SELECT sur une ou plusieurs tables ou même sur d'autres vues. Mais un SELECT peut vite devenir très complexe et c'est justement pour masquer cette complexité qu'on crée des vues.
Néanmoins, dans certains cas, nous voulons savoir quelle est la définition précise d'une vue, donc de façon récursive, c'est à dire afficher la définition des tables et vues sous-jacentes à cette vue et ce sur tous les niveaux. Prenons par exemple la vue V1 qui est un SELECT sur la table T1 et la vue V2, la vue V2 est un SELECT sur les tables T2 et T3 et sur la vue V3, la vue V3 est un SELECT sur la table T4.
Pour afficher la définition de cette vue V1, plusieurs techniques sont possibles :
- pour les tables : faire un DESC, utiliser DBMS_METADATA.GET_DLL, etc
- pour les vues : récupèrer la colonne TEXT de la vue DBA_VIEWS pour chaque sous-vue, utiliser DBMS_METADATA.GET_DLL, à nouveau sur chaque sous-vue etc
Néanmoins, pour une vue très complexe, ces techniques deviennent vite impossibles à manipuler puisqu'il faut les appeler pour chaque table et vue constituant la vue. Et c'est là que nous sauve la procédure DBMS_UTILITY.expand_sql_text :-)
Points d'attention
Aucun.
Base de tests
Une base Oracle 12c.
Exemples
============================================================================================
Utilisation de DBMS_METADATA.GET_DDL sur la vue CATALOG
============================================================================================
Nous allons utiliser pour notre test une vue système toute simple, du moins en apparence, CATALOG (CAT de son petit nom), qui liste tous les objets appartenant à un user. En premier lieu, nous allons utiliser DBMS_METADATA.GET_DDL pour voir si cette technique est satisfaisante.
Définition niveau 1
La vue CATALOG est toute simple et se base sur la vue syscatalog_.
SQL> select dbms_metadata.get_ddl('VIEW','CATALOG','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','CATALOG','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."CATALOG" ("TNAME", "CREATOR", "TABLETYPE",
"REMARKS") AS
select tname, creator, tabletype, remarks
from syscatalog_
where creatorid not in (select user# from sys.user$ where name in
('SYS','SYSTEM'))
Définition niveau 2
Cela se complexifie, la vue SYSCATALOG_ se base sur les tables SYS.USER$, SYS.COM$, SYS.TAB$, SYS.OBJAUTH$ mais aussi sur les vues SYS._CURRENT_EDITION_OBJ et V$ENABLEDPRIVS.
SQL> select dbms_metadata.get_ddl('VIEW','SYSCATALOG_','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','SYSCATALOG_','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."SYSCATALOG_" ("TNAME", "CREATOR", "CREATOR
ID", "TABLETYPE", "REMARKS") AS
select o.name, u.name, o.owner#,
decode(o.type#, 2, 'TABLE', 4, 'VIEW', 6, 'SEQUENCE','?'), c.comment$
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.com$ c
where u.user# = o.owner#
and (o.type# in (4, 6) /* view, sequence */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
and o.linkname is null
and o.obj# = c.obj#(+)
and ( o.owner# = userenv('SCHEMAID')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where oa.grantee# in (userenv('SCHEMAID'), 1)
)
or
(
(o.type# in (4) /* view */
or
(o.type# = 2 /* tables, excluding iot-overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
and
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-397/* READ ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
or
( o.type# = 6 /* sequence */
and
exists (select null from v$enabledprivs
where priv_number = -109 /* SELECT ANY SEQUENCE */)
)
)
Définition niveau 3
Comme cela nous emmènerait trop loin de générer le texte de toutes les vues du niveau 2, je vais me limiter à la vue SYS."_CURRENT_EDITION_OBJ". Nous voyons que celle-ci se base encore sur N tables du dictionnaire de données (elles se terminent par $) mais sur aucune vue... dieu merci!
SQL> select dbms_metadata.get_ddl('VIEW','_CURRENT_EDITION_OBJ','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','_CURRENT_EDITION_OBJ','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."_CURRENT_EDITION_OBJ" ("OBJ#", "DATAOBJ#",
"DEFINING_OWNER#", "NAME", "NAMESPACE", "SUBNAME", "TYPE#", "CTIME", "MTIME", "
STIME", "STATUS", "REMOTEOWNER", "LINKNAME", "FLAGS", "OID$", "SPARE1", "SPARE2"
, "SPARE3", "SPARE4", "SPARE5", "SPARE6", "SIGNATURE", "SPARE7", "SPARE8", "SPAR
E9", "OWNER#", "DEFINING_EDITION") AS
select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."
TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."
FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6
",o."SIGNATURE",o."SPARE7",o."SPARE8",o."SPARE9",
o.spare3,
case when (o.type# not in (select ue.type# from user_editioning$ ue
where ue.user# = o.spare3) or
bitand(o.flags, 1048576) = 1048576 or
bitand(u.spare1, 16) = 0) then
null
when (u.type# = 2) then
(select eo.name from obj$ eo where eo.obj# = u.spare2)
else
'ORA$BASE'
end
from obj$ o, user$ u
where o.owner# = u.user#
and ( /* non-versionable object */
( ( o.type# not in (select type# from user_editioning$ ue
where ue.user# = o.spare3)
and o.type# != 88)
or bitand(o.flags, 1048576) = 1048576
or bitand(u.spare1, 16) = 0)
/* versionable object visible in current edition */
or ( o.type# in (select ue.type# from user_editioning$ ue
where ue.user# = o.spare3)
and ( (u.type# <> 2 and
sys_context('userenv', 'current_edition_name') = 'ORA$BASE')
or (u.type# = 2 and
u.spare2 = sys_context('userenv', 'current_edition_id'))
or exists (select 1 from obj$ o2, user$ u2
where o2.type# = 88
and o2.dataobj# = o.obj#
and o2.owner# = u2.user#
and u2.type# = 2
and u2.spare2 =
sys_context('userenv', 'current_edition_id'))
)
)
)
============================================================================================
Utilisation de DBMS_UTILITY.EXPAND_SQL_TEXT sur la vue CATALOG
============================================================================================
Résultat brut
Nous venons de voir qu'utiliser DBMS_METADATA.GET_DLL permet de facilement avoir le code DDL d'une vue MAIS d'une vue et une seule et uniquement sur le niveau en-dessous de celle-ci. Dans la cas d'une vue basée sur N vues et ce sur plusieurs niveaux, il faut utiliser la procédure DBMS_UTILITY.expand_sql_text; en un seul appel, Oracle va générer le code DDL incluant toutes les vues et tables sous-jacentes à CATALOG jusqu'au niveau des tables du dictionnaire de données!
set serveroutput on
declare
v_in_view clob := 'SELECT * FROM SYS.CATALOG';
v_out_view clob;
begin
dbms_utility.expand_sql_text(v_in_view, v_out_view);
dbms_output.put_line(v_out_view);
end;
/
La sortie sous SQL*Plus ou Toad est malheureusement non formatée... voilà le texte brut. Vous noterez l'abondance d'alias créés par Oracle sur les objets, allant de A1 à A23, et aussi l'absence des commentaires obtenus avec DBMS_METADATA.GET_DDL.
SELECT "A1"."TNAME" "TNAME","A1"."CREATOR" "CREATOR","A1"."TABLETYPE" "TABLETYPE","A1"."REMARKS" "REMARKS" FROM (SELECT "A2"."TNAME" "TNAME","A2"."CREATOR" "CREATOR","A2"."TABLETYPE" "TABLETYPE","A2"."REMARKS" "REMARKS" FROM (SELECT "A5"."NAME" "TNAME","A6"."NAME" "CREATOR","A5"."OWNER#" "CREATORID",DECODE("A5"."TYPE#",2,'TABLE',4,'VIEW',6,'SEQUENCE','?') "TABLETYPE","A4"."COMMENT$" "REMARKS" FROM "SYS"."USER$" "A6", (SELECT "A17"."OBJ#" "OBJ#","A17"."DATAOBJ#" "DATAOBJ#","A17"."OWNER#" "DEFINING_OWNER#","A17"."NAME" "NAME","A17"."NAMESPACE" "NAMESPACE","A17"."SUBNAME"
"SUBNAME","A17"."TYPE#" "TYPE#","A17"."CTIME" "CTIME","A17"."MTIME" "MTIME","A17"."STIME" "STIME","A17"."STATUS" "STATUS","A17"."REMOTEOWNER" "REMOTEOWNER","A17"."LINKNAME" "LINKNAME","A17"."FLAGS" "FLAGS","A17"."OID$" "OID$","A17"."SPARE1" "SPARE1","A17"."SPARE2" "SPARE2","A17"."SPARE3" "SPARE3","A17"."SPARE4" "SPARE4","A17"."SPARE5" "SPARE5","A17"."SPARE6" "SPARE6","A17"."SIGNATURE" "SIGNATURE","A17"."SPARE7" "SPARE7","A17"."SPARE8" "SPARE8","A17"."SPARE9" "SPARE9","A17"."SPARE3" "OWNER#",CASE WHEN ( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "A23" WHERE
"A17"."TYPE#"="A23"."TYPE#" AND "A23"."USER#"="A17"."SPARE3") OR BITAND("A17"."FLAGS",1048576)=1048576 OR BITAND("A16"."SPARE1",16)=0) THEN NULL WHEN "A16"."TYPE#"=2 THEN (SELECT "A22"."NAME" "NAME" FROM SYS."OBJ$" "A22" WHERE "A22"."OBJ#"="A16"."SPARE2") ELSE 'ORA$BASE' END "DEFINING_EDITION" FROM SYS."OBJ$" "A17",SYS."USER$" "A16" WHERE "A17"."OWNER#"="A16"."USER#" AND ( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "A21" WHERE "A17"."TYPE#"="A21"."TYPE#" AND "A21"."USER#"="A17"."SPARE3") AND "A17"."TYPE#"<>88 OR BITAND("A17"."FLAGS",1048576)=1048576 OR BITAND("A16"."SPARE1",16)=0 OR EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "A20" WHERE "A17"."TYPE#"="A20"."TYPE#" AND "A20"."USER#"="A17"."SPARE3") AND ("A16"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "A16"."TYPE#"=2 AND "A16"."SPARE2"=SYS_CONTEXT('userenv','current_edition_id') OR EXISTS (SELECT 0 FROM SYS."OBJ$" "A19",SYS."USER$" "A18" WHERE "A19"."TYPE#"=88 AND "A19"."DATAOBJ#"="A17"."OBJ#" AND "A19"."OWNER#"="A18"."USER#" AND "A18"."TYPE#"=2 AND "A18"."SPARE2"=SYS_CONTEXT('userenv','current_edition_id'))))) "A5","SYS"."COM$" "A4" WHERE "A6"."USER#"="A5"."OWNER#" AND ("A5"."TYPE#"=4 OR "A5"."TYPE#"=6 OR "A5"."TYPE#"=2 AND NOT EXISTS (SELECT 0 FROM "SYS"."TAB$" "A11" WHERE "A11"."OBJ#"="A5"."OBJ#" AND (BITAND("A11"."PROPERTY",512)=512 OR BITAND("A11"."PROPERTY",8192)=8192))) AND "A5"."LINKNAME" IS NULL AND "A5"."OBJ#"="A4"."OBJ#"(+) AND ("A5"."OWNER#"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$" "A10" WHERE "A5"."OBJ#"="A10"."OBJ#" AND ("A10"."GRANTEE#"=USERENV('SCHEMAID') OR "A10"."GRANTEE#"=1)) OR ("A5"."TYPE#"=4 OR "A5"."TYPE#"=2 AND NOT EXISTS (SELECT 0 FROM "SYS"."TAB$" "A9" WHERE "A9"."OBJ#"="A5"."OBJ#" AND (BITAND("A9"."PROPERTY",512)=512 OR BITAND("A9"."PROPERTY",8192)=8192))) AND EXISTS (SELECT 0 FROM (SELECT "A12"."PRIV_NUMBER" "PRIV_NUMBER" FROM (SELECT "A13"."INST_ID" "INST_ID",(-"A13"."KZSPRPRV") "PRIV_NUMBER" FROM SYS."X$KZSPR" "A13") "A12" WHERE "A12"."INST_ID"=USERENV('INSTANCE')) "A8" WHERE "A8"."PRIV_NUMBER"=(-45) OR "A8"."PRIV_NUMBER"=(-47) OR "A8"."PRIV_NUMBER"=(-397) OR "A8"."PRIV_NUMBER"=(-48) OR "A8"."PRIV_NUMBER"=(-49) OR "A8"."PRIV_NUMBER"=(-50)) OR "A5"."TYPE#"=6 AND EXISTS (SELECT 0 FROM (SELECT "A14"."PRIV_NUMBER" "PRIV_NUMBER" FROM (SELECT "A15"."INST_ID" "INST_ID",(-"A15"."KZSPRPRV") "PRIV_NUMBER" FROM SYS."X$KZSPR" "A15") "A14" WHERE "A14"."INST_ID"=USERENV('INSTANCE')) "A7" WHERE "A7"."PRIV_NUMBER"=(-109)))) "A2" WHERE NOT EXISTS (SELECT 0 FROM "SYS"."USER$" "A3" WHERE LNNVL("A2"."CREATORID"<>"A3"."USER#") AND ("A3"."NAME"='SYS' OR "A3"."NAME"='SYSTEM'))) "A1"
Procedure PL/SQL terminee avec succes.
Résultat formaté via TOAD
L'outil TOAD permet de formater du code SQL, voilà sa sortie, infiniment plus lisible!
SELECT "A1"."TNAME" "TNAME",
"A1"."CREATOR" "CREATOR",
"A1"."TABLETYPE" "TABLETYPE",
"A1"."REMARKS" "REMARKS"
FROM (SELECT "A2"."TNAME" "TNAME",
"A2"."CREATOR" "CREATOR",
"A2"."TABLETYPE" "TABLETYPE",
"A2"."REMARKS" "REMARKS"
FROM (SELECT "A5"."NAME" "TNAME",
"A6"."NAME" "CREATOR",
"A5"."OWNER#" "CREATORID",
DECODE ("A5"."TYPE#",
2, 'TABLE',
4, 'VIEW',
6, 'SEQUENCE',
'?')
"TABLETYPE",
"A4"."COMMENT$" "REMARKS"
FROM "SYS"."USER$" "A6",
(SELECT "A17"."OBJ#" "OBJ#",
"A17"."DATAOBJ#" "DATAOBJ#",
"A17"."OWNER#" "DEFINING_OWNER#",
"A17"."NAME" "NAME",
"A17"."NAMESPACE" "NAMESPACE",
"A17"."SUBNAME" "SUBNAME",
"A17"."TYPE#" "TYPE#",
"A17"."CTIME" "CTIME",
"A17"."MTIME" "MTIME",
"A17"."STIME" "STIME",
"A17"."STATUS" "STATUS",
"A17"."REMOTEOWNER" "REMOTEOWNER",
"A17"."LINKNAME" "LINKNAME",
"A17"."FLAGS" "FLAGS",
"A17"."OID$" "OID$",
"A17"."SPARE1" "SPARE1",
"A17"."SPARE2" "SPARE2",
"A17"."SPARE3" "SPARE3",
"A17"."SPARE4" "SPARE4",
"A17"."SPARE5" "SPARE5",
"A17"."SPARE6" "SPARE6",
"A17"."SIGNATURE" "SIGNATURE",
"A17"."SPARE7" "SPARE7",
"A17"."SPARE8" "SPARE8",
"A17"."SPARE9" "SPARE9",
"A17"."SPARE3" "OWNER#",
CASE
WHEN ( NOT EXISTS
(SELECT 0
FROM SYS."USER_EDITIONING$"
"A23"
WHERE "A17"."TYPE#" =
"A23"."TYPE#"
AND "A23"."USER#" =
"A17"."SPARE3")
OR BITAND ("A17"."FLAGS", 1048576) =
1048576
OR BITAND ("A16"."SPARE1", 16) = 0)
THEN
NULL
WHEN "A16"."TYPE#" = 2
THEN
(SELECT "A22"."NAME" "NAME"
FROM SYS."OBJ$" "A22"
WHERE "A22"."OBJ#" = "A16"."SPARE2")
ELSE
'ORA$BASE'
END
"DEFINING_EDITION"
FROM SYS."OBJ$" "A17", SYS."USER$" "A16"
WHERE "A17"."OWNER#" = "A16"."USER#"
AND ( NOT EXISTS
(SELECT 0
FROM SYS."USER_EDITIONING$"
"A21"
WHERE "A17"."TYPE#" =
"A21"."TYPE#"
AND "A21"."USER#" =
"A17"."SPARE3")
AND "A17"."TYPE#" <> 88
OR BITAND ("A17"."FLAGS", 1048576) =
1048576
OR BITAND ("A16"."SPARE1", 16) = 0
OR EXISTS
(SELECT 0
FROM SYS."USER_EDITIONING$"
"A20"
WHERE "A17"."TYPE#" =
"A20"."TYPE#"
AND "A20"."USER#" =
"A17"."SPARE3")
AND ( "A16"."TYPE#" <> 2
AND SYS_CONTEXT (
'userenv',
'current_edition_name') =
'ORA$BASE'
OR "A16"."TYPE#" = 2
AND "A16"."SPARE2" =
SYS_CONTEXT (
'userenv',
'current_edition_id')
OR EXISTS
(SELECT 0
FROM SYS."OBJ$" "A19",
SYS."USER$" "A18"
WHERE "A19"."TYPE#" =
88
AND "A19"."DATAOBJ#" =
"A17"."OBJ#"
AND "A19"."OWNER#" =
"A18"."USER#"
AND "A18"."TYPE#" =
2
AND "A18"."SPARE2" =
SYS_CONTEXT (
'userenv',
'current_edition_id')))))
"A5",
"SYS"."COM$" "A4"
WHERE "A6"."USER#" = "A5"."OWNER#"
AND ( "A5"."TYPE#" = 4
OR "A5"."TYPE#" = 6
OR "A5"."TYPE#" = 2
AND NOT EXISTS
(SELECT 0
FROM "SYS"."TAB$" "A11"
WHERE "A11"."OBJ#" = "A5"."OBJ#"
AND ( BITAND (
"A11"."PROPERTY",
512) = 512
OR BITAND (
"A11"."PROPERTY",
8192) = 8192)))
AND "A5"."LINKNAME" IS NULL
AND "A5"."OBJ#" = "A4"."OBJ#"(+)
AND ( "A5"."OWNER#" = USERENV ('SCHEMAID')
OR EXISTS
(SELECT 0
FROM "SYS"."OBJAUTH$" "A10"
WHERE "A5"."OBJ#" = "A10"."OBJ#"
AND ( "A10"."GRANTEE#" =
USERENV ('SCHEMAID')
OR "A10"."GRANTEE#" = 1))
OR ( "A5"."TYPE#" = 4
OR "A5"."TYPE#" = 2
AND NOT EXISTS
(SELECT 0
FROM "SYS"."TAB$" "A9"
WHERE "A9"."OBJ#" =
"A5"."OBJ#"
AND ( BITAND (
"A9"."PROPERTY",
512) = 512
OR BITAND (
"A9"."PROPERTY",
8192) =
8192)))
AND EXISTS
(SELECT 0
FROM (SELECT "A12"."PRIV_NUMBER"
"PRIV_NUMBER"
FROM (SELECT "A13"."INST_ID"
"INST_ID",
(-"A13"."KZSPRPRV")
"PRIV_NUMBER"
FROM SYS."X$KZSPR"
"A13") "A12"
WHERE "A12"."INST_ID" =
USERENV (
'INSTANCE'))
"A8"
WHERE "A8"."PRIV_NUMBER" = (-45)
OR "A8"."PRIV_NUMBER" = (-47)
OR "A8"."PRIV_NUMBER" = (-397)
OR "A8"."PRIV_NUMBER" = (-48)
OR "A8"."PRIV_NUMBER" = (-49)
OR "A8"."PRIV_NUMBER" = (-50))
OR "A5"."TYPE#" = 6
AND EXISTS
(SELECT 0
FROM (SELECT "A14"."PRIV_NUMBER"
"PRIV_NUMBER"
FROM (SELECT "A15"."INST_ID"
"INST_ID",
(-"A15"."KZSPRPRV")
"PRIV_NUMBER"
FROM SYS."X$KZSPR"
"A15") "A14"
WHERE "A14"."INST_ID" =
USERENV (
'INSTANCE'))
"A7"
WHERE "A7"."PRIV_NUMBER" = (-109))))
"A2"
WHERE NOT EXISTS
(SELECT 0
FROM "SYS"."USER$" "A3"
WHERE LNNVL ("A2"."CREATORID" <> "A3"."USER#")
AND ("A3"."NAME" = 'SYS' OR "A3"."NAME" = 'SYSTEM')))
"A1"
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)