{"id":352,"date":"2014-05-18T21:12:12","date_gmt":"2014-05-19T02:12:12","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=352"},"modified":"2026-02-07T15:34:45","modified_gmt":"2026-02-07T21:34:45","slug":"two-cool-utilities-in-dbms_utility","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2014\/352\/","title":{"rendered":"Two Cool Utilities in DBMS_UTILITY"},"content":{"rendered":"<p>Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus):<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">SQL&gt; set serveroutput on\nSQL&gt; exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW');\n-\nDEPENDENCIES ON HR.EMP_DETAILS_VIEW\n------------------------------------------------------------------\n*VIEW HR.EMP_DETAILS_VIEW()\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES');\n-\nDEPENDENCIES ON HR.EMPLOYEES\n------------------------------------------------------------------\n*TABLE HR.EMPLOYEES()\n    VIEW HR.EMP_DETAILS_VIEW()\n    TRIGGER HR.SECURE_EMPLOYEES()\n    TRIGGER HR.UPDATE_JOB_HISTORY()\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt;<\/pre>\n<p>Oracle Database 12c has a new procedure\u00a0to provide you with the SQL behind the SQL. DBMS_UTILITY.EXPAND_SQL_TEXT. Useful to expand the views and see the base tables involved, as well as to reveal the real SQL, especially when clauses like &#8220;FETCH and OFFSET&#8221; are used.<\/p>\n<p>There are two CLOB parameters to this procedure &#8211; input SQL text, and output SQL text. Try this in your Oracle Database 12c&#8230;<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">SQL&gt; set long 32000\nSQL&gt; variable x1 CLOB\nSQL&gt; exec dbms_utility.expand_sql_text('SELECT TABLE_NAME FROM DBA_TABLES',:x1);\nSQL&gt; print x1<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus): SQL&gt; set serveroutput on SQL&gt; exec dbms_utility.get_dependency(&#8216;VIEW&#8217;,&#8217;HR&#8217;,&#8217;EMP_DETAILS_VIEW&#8217;); &#8211; DEPENDENCIES ON HR.EMP_DETAILS_VIEW &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; *VIEW HR.EMP_DETAILS_VIEW() PL\/SQL procedure successfully completed. SQL&gt; exec &#8230; <a title=\"Two Cool Utilities in DBMS_UTILITY\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2014\/352\/\" aria-label=\"Read more about Two Cool Utilities in DBMS_UTILITY\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[133],"tags":[],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack-related-posts":[{"id":1519,"url":"https:\/\/bijoos.com\/oraclenotes\/2015\/1519\/","url_meta":{"origin":352,"position":0},"title":"Oracle Tidbits &#8211; April 2015","author":"Biju","date":"May 1, 2015","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in April\u00a02015. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and use\u2026\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2188,"url":"https:\/\/bijoos.com\/oraclenotes\/2018\/2188\/","url_meta":{"origin":352,"position":1},"title":"Oracle Tidbits &#8211; May 2018 #oratidbit","author":"biju.thomas","date":"May 31, 2018","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on\u00a0Facebook,\u00a0Twitter,\u00a0and\u00a0Google+\u00a0during weekdays\u00a0in May 2018. Hope you find these helpful to learn something new or to remind you of its existence and use. #oratidbit #db18c When cloning a non-CDB as a PDB or relocating a PDB, use the DBMS_PDB.EXPORTRMANBACKUP procedure to export RMAN backup metadata into\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/04\/oratidbit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":153,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/153\/","url_meta":{"origin":352,"position":2},"title":"OracleDB12c New Feature: Last Login Time of User","author":"Biju","date":"July 9, 2013","format":false,"excerpt":"The SYS.USER$ table in Oracle Database 12c includes a column to store the last login time of the user. In the previous releases, to be able to know the last login time of the user, we had to enable \"AUDIT SESSION\" and then query the AUD$ table. As part of\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2631,"url":"https:\/\/bijoos.com\/oraclenotes\/2020\/2631\/","url_meta":{"origin":352,"position":3},"title":"Oracle Tidbits &#8211; November 2020 #OraTidbit","author":"biju.thomas","date":"November 30, 2020","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on\u00a0Facebook &Twitter,\u00a0during weekdays in November 2020. I hope you find these helpful to learn something new or to remind you of its existence and use. #OraTidbit #SQLcl Oracle SQLcl (SQL Developer Command-Line) is a Java-based command-line interface for Oracle Database. Using SQLcl, you can interactively\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1473,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/1473\/","url_meta":{"origin":352,"position":4},"title":"Oracle 12c &#8211; READ Privilege","author":"Biju","date":"December 23, 2014","format":false,"excerpt":"The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or \u201cselecting\u201d from) the table, you are\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1516,"url":"https:\/\/bijoos.com\/oraclenotes\/2015\/1516\/","url_meta":{"origin":352,"position":5},"title":"Preventing CREATE (only) for a User","author":"Biju","date":"April 27, 2015","format":false,"excerpt":"The PL\/SQL reference guide has a short section and example for the INSTEAD OF CREATE ON SCHEMA\u00a0trigger.\u00a0Since the description was so little and could not find much detail on the internet, tried some examples as below. This feature is good to prevent any CREATE operation for an user, giving him\/her\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":3022,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352\/revisions\/3022"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}