Search the Reference Library pages:  

Oracle Schema
Version 11.1
 
General
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.
 
Create Schema

Change Schema
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;
 
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----