Oracle | Create Schema

Oracle Schema Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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.

Does not support CREATE BLOCKCHAIN TABLE in 20c.

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 sys@pdbdev 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@pdbdev

      

       -- 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;