Quantcast

drop view cascade

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

drop view cascade

Jeremy Lemaire
With the new year fast approaching I am trying to update my views to include the new 2011 partitions but when I try to drop the existing views I get an error indicating that I need to cascade the delete.  The problem is that I have no idea what views, tables etc this cascade is going to effect.  

Is there a dry run setting or something similar that will show me what will be removed if I cascade this delete?



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: drop view cascade

John Sichi
Administrator
On Thu, Dec 30, 2010 at 7:07 PM, Jeremy Lemaire <[hidden email]> wrote:
>
> With the new year fast approaching I am trying to update my views to include
> the new 2011 partitions but when I try to drop the existing views I get an
> error indicating that I need to cascade the delete.  The problem is that I
> have no idea what views, tables etc this cascade is going to effect.
>
> Is there a dry run setting or something similar that will show me what will
> be removed if I cascade this delete?

We don't yet have a DBA_DEPENDENCIES view, and the DROP command does
not report the dependencies.

However, we do have a secret weapon:  a not-very-well-documented
metadata query language called LURQL.  We currently only use it
internally, but there's a test UDX which allows you to access it.

First, run these commands to register the UDX:

create schema md;
set schema 'md';
set path 'md';

create function lurql(
server_name varchar(128),
query varchar(65535)
)
returns table(
class_name varchar(128),
obj_name varchar(128),
mof_id varchar(128),
obj_attrs varchar(65535)
)
language java
parameter style system defined java
no sql
external name 'class net.sf.farrago.test.LurqlQueryUdx.queryMedMdr';

Then, execute this to see the first level of dependencies:

select class_name,obj_name from table(lurql(cast(null as varchar(128)),
'select c from class LocalView where name=''YOUR_VIEW_NAME'' then
(follow origin end supplier then (
follow destination end client as c));'
));

(Note that YOUR_VIEW_NAME is surrounded by pairs of single quotes, not
double quotes.)

To see all of the cascaded dependencies recursively, execute this:

select class_name,obj_name from table(lurql(cast(null as varchar(128)),
'select c from class LocalView where name=''YOUR_VIEW_NAME'' then (
recursively (
follow origin end supplier then (
follow destination end client as c)));'));

The results should look like this:

+-------------+-----------+
| CLASS_NAME  | OBJ_NAME  |
+-------------+-----------+
| LocalView   | V3        |
| LocalView   | V2        |
+-------------+-----------+

These assume that your view name is unique across schemas; if that's
not the case, I can give you a longer query which deals with name
qualification.

JVS

------------------------------------------------------------------------------
Learn how Oracle Real Application Clusters (RAC) One Node allows customers
to consolidate database storage, standardize their database environment, and,
should the need arise, upgrade to a full multi-node Oracle RAC database
without downtime or disruption
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...