Search notes:

Oracle SQL noun: VIEW

CREATE VIEW

drop   table tq84_tab;
create table tq84_tab (
    num    number,
    txt    varchar2(10)
);
A star * is expanded to the column names when a view is created: 
drop   view  tq84_vw;
create view  tq84_vw as select * from tq84_tab;

select text from user_views where view_name = 'TQ84_VW';
-- select "NUM","TXT" from tq84_tab

alter table tq84_tab add dt date;
select dt from tq84_vw;
-- ORA-00904: "DT": invalid identifier

Specifying the view's column names

The create view statement allows to explicitly specify the column names of the view: 
create view tq84_vw_col_names
   (n, t)
as
select
   num,
   txt
from
   tq84_tab;
Although the select statement in the view selects the columns num and txt, the view now has the column names n and t: 
select n, t from tq84_vw_col_names;
However, the column names are not reflected in text of user_views: 
SQL> select text from user_views where view_name = 'TQ84_VW_COL_NAMES';

select
   num,
   txt
from
   tq84_tab

Forcing the creation of a view

Oracle can be forced to create a view even if the view's select statement is invalid: 
create FORCE view xyz_v as select * from inexisting_table;
--
-- View XYZ_V created.
Of course, the view's status in invalid: 
select status from user_objects where object_name = 'XYZ_V';
--
-- INVALID
More information can be queried from user_errors: 
select text from user_errors where name = 'XYZ_V';
--
-- ORA-54039: table must have at least one column that is not invisible
-- ORA-00942: table or view does not exist

SHARING=…

create view vvv sharing=none as select * from dual;
create view mmm sharing=metadata as select * from dual;
create view ddd sharing=extended data as select * from dual;
sharing=extended data sets the extended data bit (65536) and metadata link bits in obj$ and the common_data bit in view$.

CONTAINER_DATA

An undocumented(?) option of create view is container_data: 
create or replace view abc_v
   CONTAINER_DATA
   sharing=object
   (
      col_1,
      col_2
   )
   as select …

Index