Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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.
The statement does not support CREATE BLOCKCHAIN or IMMUTABLE tables.
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
Note: The schema in which the command is executed must match the AUTHORIZATION schema_name
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;
-- this will fail as the current SYS schema is not the AUTHORIZATION schema
CREATE SCHEMA AUTHORIZATION c##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;
*
ORA-02421: missing or invalid schema authorization identifier
conn uwclass/uwclass@pdbdev
-- this entire statement fails because the view t1t2 does not exist)
CREATE SCHEMA AUTHORIZATION c##scott
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;
*
ORA-02427: create view failed
-- this will work because it is in the correct
schema and the view reference removed
CREATE SCHEMA AUTHORIZATION c##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;