Re: Side effect of replacing a table with a view

May be a 3rd alternative?
 Is there a public synonym 'a' on table 'a'?
 Assuming the application does not use the schema-name to connect to the 
database and it uses an user_id to connect to the database AND assuming the 
application code does not have schema-name.'a' but just 'a', then you can 
consider dropping the public synonym and creating a view for the user: 
 drop public synonym a;
 connect as schema
grant select on a to <user_id>;
 connect as user_id
create view <user_id>.a as select <existing columns> from <schema_name>.a
 If this works fine, only then add columns to the table 'a'. Easier to 
backout too...
 Cheers!
Arul
 On 4/22/05, Vlado Barun <vlado@xxxxxxxxxx> wrote: 
> 
> I'm looking for input on what possible negative side effects I could
> encounter, if I replace a table with a view.
> 
> Situation:
> 
> I need to add a few columns to an existing, non-partitioned, heap table
> (let's call it "a"). However, I have identified a large number of "select 
> *
> " queries against this table. Finding where these queries are coming from
> and changing them is not feasible within the current environment.
> 
> So, I'm basically looking at two alternatives:
> 
> 1. rename the existing table to "b", add the new columns to it and
> create a view named "a". The view will not expose any of the new columns
> 2. create another table "a_ext", with the same PK as "a", enforce RI
> (cascade delete), add the new columns to it. Also add an update trigger on
> "a", in case the PK in "a" gets updated, so that the PK in "a_ext" gets
> updated too.
> 
> I prefer #1, but I'm concerned about possible negative side effect of
> renaming the table. Let's not worry about downtime, since it's not an 
> issue
> in this environment. However, since table "a" is a very critical table, we
> need to make sure we do not break any existing code.
> 
> We use Oracle 8.1.7.0 <http://8.1.7.0>, and yes it is .0, not .4
> 
> Question:
> 
> Are there any operations that could fail as the result of replacing the
> table with a view?
> 
> Vlado Barun, M.Sc.
> 
> Senior Data Architect, Cadre5
> 
> www.cadre5.com <http://www.cadre5.com> <http://www.cadre5.com/>
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 


-- 
Arul

--
http://www.freelists.org/webpage/oracle-l

Other related posts: