Oracle Schema
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Create multiple tables and views and perform multiple grants in your own schema in a single transaction. If all statements execute successfully, then the database commits them as a single transaction. If any statement results in an error, then the database rolls back all of the statements.
Data Dictionary Objects
ALL_TAB_PRIVS_MADE CDB_TABLES USER_TAB_PRIVS_MADE
ALL_OBJECTS CDB_VIEWS USER_OBJECTS
ALL_TABLES DBA_OBJECTS USER_TABLES
ALL_VIEWS DBA_VIEWS USER_VIEWS
CDB_OBJECTS    
System Privileges
CREATE SESSION CREATE TABLE CREATE VIEW
 
Create Schema
Create Schema Objects CREATE SCHEMA AUTHORIZATION <schema_name>
<create table or view or grant statement>;
conn / as sysdba

CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;

GRANT create session TO uwclass;
GRANT create table TO uwclass;
GRANT create view TO uwclass;

conn uwclass/uwclass

-- first one that doesn't work (t3 does not exist)
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t3 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;

-- then one that does
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t2 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;

Related Topics
Constraints
System Privileges
TABLES
TABLESPACE
USERS
VIEWS

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved