A very old rule in Oracle has always been not user “select *” clause to create a view, technically can be done, but if you take a look at the following example;
TE*ANTO> create view anr_view as select * from anr_dba_users;
Being anr_users a copy of the dba_users.
Create the next view using “select *” clause;
TE*ANTO> create view anr_view as select * from anr_dba_users;
We looked at the definition of the view in the dba_views;
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';
TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME" from anr_dba_users
Oracle translates the “select *” to all the columns that exist in the table at the time of the creation of the view.
Now a problem is when you add columns or delete columns.
TE*ANTO> ALTER TABLE ANR_DBA_USERS ADD (NEWCOL VARCHAR2(10)); Vista creada. TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW'; TEXT ________________________________________________________________________________ select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE ","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_ CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users
The table has changed but continued the same view, use a compile does not solve the problem. The only way is to do a create/rebuild;
TE*ANTO> CREATE OR REPLACE
2 view anr_view as select * from anr_dba_users;
Vista creada.
Transcurrido: 00:00:00.21
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';
TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users
If we look we see that the new column NEWCOL longer appears.
Recommendation: Always use the fields you want to use or need in the definition of the view.
HTH – Antonio NAVARRO