viewdep.sql


Print this article  Send the URL to a friend

— ==========================================================================

— 
— Copyright (C) RoughSea Ltd, 2005
— http://www.roughsea.com
— 
— This program is free software; you can redistribute it and/or modify
— it under the terms of the GNU General Public License as published by
— the Free Software Foundation; either version 2 of the License, or
— any later version.
— 
— This program is distributed in the hope that it will be useful,
— but WITHOUT ANY WARRANTY; without even the implied warranty of
— MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
— GNU General Public License for more details.
— 
— You should have received a copy of the GNU General Public License
— along with this program; if not, write to the Free Software
— Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
— 
— ==========================================================================
— 
— Some very bad performance can pretty often be traced to the use in complex
— queries of views built atop some other complex views.
— This query shows rapidly the other views a given view depends on.
— 
— Usage :
— @viewdep <[owner.]viewname>
—  set verify off select lpad(’ ’, level * 2, ’ ’) || REFERENCED_OWNER || ’.’ || REFERENCED_NAME "&1" from all_dependencies where referenced_type = ’VIEW’ connect by owner = prior referenced_owner and name = prior referenced_name start with owner = decode(instr(’&1’, ’.’), 0, sys_context(’USERENV’, ’CURRENT_SCHEMA’), upper(substr(’&1’, 1, instr(’&1’, ’.’) - 1))) and name = upper(decode(instr(’&1’, ’.’), 0, ’&1’, substr(’&1’, 1 + instr(’&1’, ’.’)))) /