Views

Metadata System Level

a set of schemas that constitute the description of the entire database (all users combined). System Catalog - tab: tables and views you own, user_tables: stores info on tables owned by you, all_tables: all tables you can access, user_tab_columns: columns in your tables, user_constraints: constraints, user_objects: all objects owned by you

Dynamic View

a virtual table created dynamically by the user, no data is actually stored, based on SQL Select statement on the base table or other views

Updateable Views

DBMS should be allowed to update any views that can be updated. Must not contain Distinct, count, sum, max, min, group by, order by, union, intersect, minus, sub-query in a select list. Can have with read only.

Views accessible by DBA

Oracle 11g, DBA_Tables, DBA_Constraints, and DBA_Users

Metadata User Level

contains descriptions of objects created by a single user (base tables, views, constraints)

Base TAble

contains the underlying data

Materialized View

copy or replication of data, data is stored, must be refreshed periodically to match the corresponding base table

Data Control Language (DCL)

data sub-language type that controls a database, including administering privileges and committing data. Grant and Revoke

Data Manipulation Language (DML)

data sub-language type that maintains and queries a database. Select, instert, delete, update

Data Definition Language (DDL)

data sub-language type, that defines a database, including creating, altering, and dropping tables and establishing constraints. Create/Alter statements

Advantages of Views

establish logical data independence, simplifies query commands, enhances programming productivity, provides customized view for the user, assists with data security with read/write privileges, contain most current base table data, and use little storage space.

View

provide users controlled access to tables. Has a name, select statement, and check option works only for update-able views.

Disadvantage of Views

use processing time each time view is referenced, need to maintain a second set of database objects, materialized views need to be refreshed periodically.