Even if you create a view with an implicit query like select * from t1
, that doesn't mean that the view's columns are dynamic. When creating the view, Oracle uses t1.*
and t2.*
to get explicit lists of column names for the tables at that moment in time from the data dictionary, and creates those columns explicitly in the view. If the source table is altered to add a column later, that column will not suddenly appear in the view: you will need to recreate the view to add the column.
Use of implicit queries in DDL and in code generally is considered unwise: you don't want a change to a table to ripple in unpredictable ways that could break code that depends on explicit conditions. Strong code is explicit code (everything named, fully qualified, etc.).
That said, your command failed specifically because it couldn't reconcile duplicate column names for id
between the tables. Also note that without an explicit join, you are creating a cartesian product of the two tables for your view.
If you want a view that is just a union of the two tables (all the data, each column represented once, no joins: id, name), then use a query like this:
create view v1 as
select t1.id, t1.name1 as name from t1
union
select t2.id, t2.name2 as name from t2
If you want a view that shows all the columns for both tables in a single row (i.e. t1.c1, t2.c1, t1.c2, t2.c2) then use a query like this, but join the rows in the two tables on the primary key:
create view v1 as
select t1.id as id1,
t2.id as id2,
t1.name1 as name1,
t2.name2 as name2
from t1
join t2 on (t2.id = t1.id)
Either way, use explicit naming in your queries so that your code will always match what's in the data dictionary and you won't accidentally break something 8 levels of dependency away.