Views

Create

CREATE OR REPLACE VIEW [schema.]<ViewName> AS <SELECT_Statement>;

CREATE OR REPLACE VIEW hr.emp_fl 
AS SELECT * FROM hr.emp WHERE state='FL'; 

Display

-- All User Created Views

SELECT owner, view_name 
FROM dba_views 
WHERE owner NOT IN('DBSNMP','PUBLIC','OUTLN','SYS','SYSTEM');

-- All Views in a Schema

SELECT owner, view_name FROM dba_views WHERE table_owner = '&SCHEMA';

-- Find a Particular View

COL owner     FORMAT a15
COL view_name FORMAT a15
SELECT owner, view_name FROM dba_views WHERE VIEW_NAME like '%MYVIEW%';

-- DDL for View

set long 2000
SELECT DBMS_METADATA.GET_DDL('VIEW','MY_VIEW_NAME','SCOTT') from dual;

Drop

DROP VIEW [schema.]<ViewName>;

DROP VIEW hr.emp_fl;

Create View Session

Logged in as your user account: SCOTT

0. Ensure privs set.
   GRANT create procedure TO SCOTT;
   GRANT create view TO SCOTT;

1. Create Table and test data.

   CREATE TABLE test42 (
   kb_id          number,
   title          varchar2(50) CONSTRAINT kb_title_nn  NOT NULL,
   detail         CLOB         CONSTRAINT kb_detail_nn NOT NULL,
   author         varchar2(25),
   last_modified  date,
   reviewer       varchar2(25),
   last_reviewed  date,
   keywords       varchar2(100),
   CONSTRAINT kb_pk PRIMARY KEY("KB_ID") USING INDEX TABLESPACE users
   )
   TABLESPACE users;

  INSERT INTO test42 (KB_ID, TITLE, DETAIL, AUTHOR) VALUES ('1', 'A', 'A', 'A')
  INSERT INTO test42 (KB_ID, TITLE, DETAIL, AUTHOR) VALUES ('2', 'B', 'B', 'B')
  INSERT INTO test42 (KB_ID, TITLE, DETAIL, AUTHOR) VALUES ('3', 'C', 'C', 'C')

2. Ensure the table(s) you want to create View on exist.
   desc test42;

3. Verify you can see the table and the data.
   SELECT count(*) FROM test42;

4. Create View
   CREATE OR REPLACE VIEW view_test42 AS SELECT * FROM test42;

5. Test
   SELECT * FROM view_test42;

6. Clean Up
   DROP VIEW view_TEST42;
   DROP TABLE test42;