A view does not physically exist in the database. It is derived from other database tables. When the data in its source table is updated , the view reflects the updates as well.
If structure of a view’s source table is altered, or if a view’s source table is dropped, then the view becomes invalid and can no longer be used until it is dropped and re-created or replaced.
Creating and Manipulating Simple Views
CREATE VIEW view_name AS sql_query;
The view name must follow the Oracle naming standards.
The view name can not already exist in the user’s database schema.
You must have sufficient object privileges are related with the view to manage the records.
CREATE OR REPLACE VIEW view_name AS Sql_query;
CREATE VIEW faculty_view as
SELECT f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank
FROM faculty
SELECT f_last, f_first, bldg_code, room
FROM faculty_view, location
WHERE faculty_view.loc_id = location.loc_id;
Creating and Manipulating Complex Views
Complex views, unlike simple views, cannot be used to directly manage data in the underlying database tables.
CREATE VIEW inventory_view AS
SELECT item_desc, item_size, color, price, qoh, price*qoh AS value
FROM inventory, item
WHERE item.item_id = inventory.item_id
ORDER BY item_desc;
Dropping Views
DROP VIEW view_name;
Using Data Dictionary Views to Retrieve Information about the Views
Dictionary views: All_views
User_views
You can only see information about views for which you have been granted object privileges.
SELECT view_name
FROM ALL_VIEWS
WHERE owner = ‘SYSTEM’;
